Solved

access or sql

Posted on 2015-01-28
12
394 Views
Last Modified: 2015-04-07
Hi all,

I am hoping someone can help me out with some rough guidance on a database.

I provide a few days of support for a small business that a friend of mine owns, i do it for free just to help him out.

The business is an electrical company who have a microsoft access database which tracks their engineers diarys, jobs, invoices, parts etc  

I know very very little about databases - the most i have done is link tables in the past at a previous job - in my day job i deal with cisco infrastructure equipment so i'm really in the dark here! I have always approached databases that my little knowledge is dangerous rather than helpful so it is best left to an expert.


The database system is continually crashing, multiple times a day and requires 'compacting'.

The person they are employing to maintain and customise their database continually comes up with excuses as to why the database is crashing and i am not so sure they are all true so i am hoping to get a second opinion on here.

The database has around 7 to 10 users at any one time.
It has been running now for a number of years and over time, has got worse and worse, crashing more frequently.
The business is growing as is the amount of data going into it - from external advice and reading around on the web, SqL seems to be the way forward but the DBA seems to be reluctant to do this or even entertain it.

First the DBA blames the server, so we move the DB to a new server - problem still persists
The DBA then blame office 2007 and makes the company upgrade to 2013 - problem still persists
Then the DBA blames the network - that there must be an issue - testing proves there is no issue

I wanted to ask the question
- Is there a limit to how big a database should be? Or is it too big and that is why it keeps crashing? (if someone can tell me how, i could easily find out the amount of data / number of tables or records etc inside there, or just the file size)
- is there a maximum number of users that should be on at one time?
- would moving to SQL benefit? I am under the impression for a server based, multi user, large (if it is large) database would run much better in sql?
- at the previous company the DBA made a front end and a back end... each user had 'their own' front end which linked to the same backend data and this did make things a bit better, does this normally help?

any help would be appreciated, and i understand the info above is patchy at least, if someone were to guide me i could get any required information...

thanks in advance!
0
Comment
Question by:gorilly
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 167 total points
ID: 40574939
Not an untypical situation....in fact, very typical.

- Is there a limit to how big a database should be? Or is it too big and that is why it keeps crashing? (if someone can tell me how, i could easily find out the amount of data / number of tables or records etc inside there, or just the file size)

  Just look at the size on disk.  The limit for a single container is 2GB.  No limit on the number of records, but if you get into the hundreds of thousands, then you need to be careful with development and performance

- is there a maximum number of users that should be on at one time?

  The limit with JET is 255 concurrent users.   However you will find for practical purposes, that's more like 30 or 40.   Here's the deal and why I say that; JET is a file sharing type setup.   On the server, there is no central process to roll back transactions on the database if an abnormal disconnect occurs.  The server just acts as a file share.

 Each station then is actually manipulating the database file and performing the processing.  By the time you get to 30 or 40 stations, it's hard to keep that large of an environment stable.  Something is almost always happening.

  To illustrate that, I've seen a read only-report app with JET run with 200+ users without issue.  With no writes into the DB, there's little chance of corruption.  So it's not the number of users per say that is the issue.

- would moving to SQL benefit? I am under the impression for a server based, multi user, large (if it is large) database would run much better in sql?

  Yes, but not because of size.  With SQL server, you move to a true client/server situation.  A process is running on the server and it is the only process that touches the database directly.  All processing goes through it.   So if there is a network problem and a client disconnects, the database is protected.

  beyond that, certainly SQL is far more robust than JET with better performance, features like point in time recovery, and  on-line backups.

  Access is excellent front end for SQL Server and you can pretty much convert without too much hassle, but it's not always cut and dry.   Just pushing the data into SQL should be considered the start of the process and not the end, but many just do that and are happy.

- at the previous company the DBA made a front end and a back end... each user had 'their own' front end which linked to the same backend data and this did make things a bit better, does this normally help?

  Yes and that's the way it should be setup right now.  If it's not, that is the first thing I would correct.   Performance and load on the network will be far better.

 if your "developer" has not already done this, then I have to say he/she is not a very knowledgeable Access developers.  This has been SOP for decades.

In addition, I would say that typically, when an app starts to have constant corruption with only  7- 10 users, development of the app has been done poorly and the product gets blamed rather than the fact that it gets used in ways that it's not supposed to (you would be shocked how many try to run it over a WAN, which it was never designed to do).

JJim.
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 84 total points
ID: 40574944
> .. each user had 'their own' front end which linked to the same backend data and
> this did make things a bit better, does this normally help?

If the current database isn't split this way, it is doomed to fail as described with 7-10 users.

Do split the db as the very first.

/gustav
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 166 total points
ID: 40574952
Access databases are limited to 2 GB. IMO if you're getting close to that, you should consider moving to SQL Server.

The hard limit is 255 users, but for most the practical limit is somewhere around 20 - 30 users. Depending on the architecture and skill of the developer, it's entirely possible to have many more users, but doing so requires a very fast and wide network and a LOT of attention to detail on the part of the developer. Doesn't sound like you have either at this point.

Moving your data to SQL Server is often a good idea, but it's not a magic pill, and there is often quite a lot of work to be done to do this properly.

at the previous company the DBA made a front end and a back end... each user had 'their own' front end which linked to the same backend data and this did make things a bit better, does this normally help?
This is possibly the most disturbing comment in your post. If you have an Access developer who has NOT split a multiuser database, then I'd start my search for a new one now ... or yesterday, in fact. I can count on one hand the number of applications I've seen that did not need to be split (and those were all single-user ONLY apps). Multi-user applications must be split into FE and BE, and anyone who tells you otherwise doesn't understand Access, or the way it works.

A few questions:

Are all the users local (i.e. in the same physical location)?

Do all the users run the same version of Access/Office?

What sort of network are they using (wired/wireless, etc)?
0
 

Author Comment

by:gorilly
ID: 40574975
Thank you for all your replies

The server is running raid 10 - 15k rpm SAS drives, entire network is wired 1Gbps. All workstations are of a good spec.
I have also tested the db as temporary measure on SSD drives which improved the performance of the DB greatly but isnt a viable long term solution due to life span of the drives - this was simply done as a 'get out of trouble' test.

All of the users now run access 2013

All are in the same physical location, no cable will exceed cat5e/cat6 limitations.

It is a small office - infact the set up there is better than most offices of a similar size (a lot still running on 10/100 networks, older servers with 5,400/7,200rpm drives in a raid 1 or raid 5, older computers etc. This is why i am very confident that it is not the server.

This DB has been run from 3 servers in total

old dell server first
moved to SSD in RAID 10 as a test
now moved to a brand new dell poweredge, 32GB ram, RAID 10, 15k SAS drives etc...

I will check the front end and back end set up (i expect this has been done, i just wanted to throw the point into conversation incase it isnt.)

I will also check the size of the DB

Also in regard to MS-JetEngine i know this may be a stupid question - if the DB is running in access 2013, does this mean it is certainly in JE by default or is there another type it could be in?

thanks
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 167 total points
ID: 40575036
Just one point on this:

<<This is why i am very confident that it is not the server.>>

Never say never.   All it would take is one bad nic, cable, or driver and the network might get very congested.

Access/JET will complain long before other apps because things are happening on the Milli-second level.  When it comes to the health of the network, an Access app using JET is the canary in the coal mine.  That's why too Scott asked about wireless.   JET really doesn't like it, but it works for just about everything else.

There have also been issues with SMB2/3 that causes JET problems.  Again, Access/JET is different than other apps because it caches DB pages as part of its normal processing, something which other apps don't do.   SMB2/3 deals with that by implementing OPLOCKS, which allows client side caching of a file at the OS level.   That gets in the way of what JET does beyond there being out right bugs in SMB2/3.

 So you never can say never, but from what you describe, it sounds more like the setup and development of the app more than anything.

<<Also in regard to MS-JetEngine i know this may be a stupid question - if the DB is running in access 2013, does this mean it is certainly in JE by default or is there another type it could be in?>>

  With 2007 it's called ACE, but it's JET with just a few tweaks.   And out of the box, that is the default engine and what works with MDB/ACCDB files.

 Even if you used SQL Server as a back end to hold the data, ACE/JET would still be in the picture as your "front end" (everything but the tables) would be a MDB/ACCDB file.

Jim.
0
 

Author Comment

by:gorilly
ID: 40575053
<<This is why i am very confident that it is not the server.>>

Yes! I never say never! Ha i think i learnt this lesson the hard way back about 8 years ago when i first started out as a jr!

All three servers the DB has resided on are still in place so thats 3 sets of nics, 3 sets of drivers, 3 cables etc... ive also tried two switches - luckily this has all happened during an expect upgrade of their domain controller - so ive had an abundance of equipment available which has been helpful - but yes i agree, never say never, i am just confident due to the number of different checks i have made that is is not the setup but still continuing to make different checks hence this post.

Back in november i eliminated all wifi from the network with this in mind.

i will take a look at the things you have suggested and come back to you all, thank you very much for the help so far
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:gorilly
ID: 40575059
sorry one final questions.

in regards to FE/BE.

Should each user have their own front end... ie. copy on their desktop with linked tables or is it safe for all users to use the same front end located on a server? I assume the local copy is the way to go?
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 166 total points
ID: 40575063
Each user should have their own copy of the FE installed locally on their machine. Sharing a FE is nearly as bad as sharing a monolithic application.

One of the common complaints we hear is "If I don't share the FE it's too hard to update". There is some validity to that, however it's quite simple to create a batch file the user launches from their workstation that (a) creates a copy of the FE on the server and (b) moves that to their local workstation and then (c) launches that copy. There are also some commercial Updaters out there as well.
0
 
LVL 57
ID: 40575081
As Scott said, own front end and on their station.   Doing that solves a number of issues and it's a defacto standard of the way an Access app should be setup.

Right now every single thing a user does (open a form or report, execute code, etc) is all going over the wire.   You want only data going over the wire and nothing else.

Which by the way is no different than any other app.  You install a program on a station and it accesses a data source somewhere (which might be local, but if multi-user, it's on a server).

It's a bit confusing for some however with Access because application components (forms, reports, etc) can live within the same DB file along with tables.   It should never be just shared out like that however.

Jim.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 83 total points
ID: 40575296
Here is the batch file I use to copy a new FE for each worker each time they open the app.  it takes about 4 seconds to copy the FE and open it so even though it might happen multiple times a day, it isn't getting in the way.
md c:\ClientMgt
del c:\ClientMgt\AOAClientManagement.accdr
copy \\newfiscal\Afox\AFox\AccessApplications\ClientMgt\AOAClientManagement.accdr c:\ClientMgt
c:\ClientMgt\AOAClientManagement.accdr

Open in new window

This is the curmudgeon approach but others will require slight modifications to the app and you can do this immediately once the db is split.  Each user has a shortcut that runs the .bat file.

Moving from Jet/ACE to SQL Server is pretty easy if you understand client/server and developed the app with that in mind.  Given what we already know about your developer, moving to SQL server would probably make your app slower although it would probably be more stable just because the BE is SQL server.

I agree with the others, no Access developer worth his salt would allow 7-10 people to concurrently use a monolithic database.
0
 
LVL 9
ID: 40578799
I'm late to the party - no points for me please.

Regarding moving to SQL Server - without knowing how big the BE database is, or the number of records in the largest tables, it's hard to say.  But there are other reasons to move to SQL Server.

I've written a PowerPoint presentation on techniques for using Access as a client-server front-end to SQL Server databases.  It's called "Best of Both Worlds" at our free J Street Downloads Page.

It includes some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.

Regarding the developer - if SQL Server seems like a good idea but they aren't comfortable with it, you can always find someone who is.  Migrating an Access app to a SQL Server back-end is a straightforward process that many consultants, including us, do all the time.

If the database isn't split, then your developer is definitely not following best practices.  It's one of the signs in my article 9 Signs that an Amateur Built Your Access Application.

Regarding the network reliability - it's not just the NIC in the server that can cause a problem.  I've seen a bad NIC on just one PC cause the database to corrupt.  It would take some testing, but you could do trial periods of one PC at a time not using the database, and see if the corruption stops during that time.

Cheers,
Armen Stein
Access MVP
0
 

Author Comment

by:gorilly
ID: 40709903
Hi All

thank you for all your help

i put forward the info to my friend and it seems a lot of bad decisions were made, most of which were highlighted above.

As expected it was not down to the hardware but poor DB design.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now