Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

Outgrowing Microsoft Access, what next?

We have a microsoft access database which everyone in our company uses (70 or so users).  Basically what's happening is that people are inputting data into this thing at a rate which is cause the size to double basically every month (the size is now 16 megs).  This of course isn't sustainable since we're already experiencing slowdowns and sluggish behavior.  We were wondering if hosting the database on a dedicated server would expedite things, or is that just putting a band-aid on things, or will that have little effect?  I don't know much about SQL server, but is that the solution to our problem or is that overkill?  Please let me know, looking for general insights on solving this issue.  Thanks.
0
LB1234
Asked:
LB1234
  • 6
  • 5
  • 2
  • +5
1 Solution
 
tbsgadiCommented:
Hi LB1234,

Check out the following link
http://www.granite.ab.ca/access/sqlserverupsizing.htm

Good Luck!

Gary
0
 
LB1234Author Commented:
Hmmm, provides some insights into problems with upgrading to SQL, but I'm looking to know whether that's the solution in the first place.  Thanks anyhoo.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Kevin CrossChief Technology OfficerCommented:
LB1234,

I haven't been able to read the previous suggested link in full, so apologize for duplication; however, IMHO your next move is to MS SQL Server 2008 Express Edition (MSDE -- but may as well use newer technology) which has a limit of 2GB and is the free version of the MS SQL Server platform.

The express edition will allow you to see how the transition will be to MS SQL without big costs as it is free.  The only costs involved will be the soft dollars of your learning and r&d of migrating your application.

I suggested that as your development team is probably use to VB / T-SQL the Microsoft way having worked with MS ACCESS, but alternative is MySQL 5.x+ as it is also free; however, the learning curve may be more and the changes to front-end may need more work.

In the transition period you can have your frontend in MS ACCESS and the backend in SQL Server.  As time progresses you can add a more rich frontend using .NET which again you can enter in for free with downloads of .NET redistributable and Visual Studio Express Edition(s).
0
 
rseabirdCommented:
I don't know the exact configuration. Normally, if we are working with Access applications, we use to Access files, one front-end and one back-end. In this situation it is quit easy to move the back-end database to a SQL server enviroment.
It seems, that running e.g. queries, the Access file size expands a lot. You need to compact the database regularly. (It is also possible to compact a 'remote' access database file, e.g. from the front-end database, compact an other access database file).
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<< This of course isn't sustainable since we're already experiencing slowdowns and sluggish behavior. >>

  First, 16mb is really nothing.  The database size limit for JET is 2GB.  You should have quite a ways to go before you'd start have problems.    As for performance, make sure your compacting on a regular basis.  I'd also look at indexing as a first step.  Over indexing is just as bad as under indexing, especially in a heavy add/update environment.

<<We were wondering if hosting the database on a dedicated server would expedite things, or is that just putting a band-aid on things, or will that have little effect?  >>

   If by dedicated, you mean to just support the database, no, it won't make much difference.  Access with a JET backend is all client based; all the processing is occuring on the clients.  The server is just acting as a file share.

  There are things you can do to make the server more efficent though.  See:

How to keep a Jet 4.0 database in top working condition
http://support.microsoft.com/kb/303528

<< I don't know much about SQL server, but is that the solution to our problem or is that overkill?  Please let me know, looking for general insights on solving this issue.  Thanks.>>

  I think this is something you'd want to look at.  With a SQL Server backend, processing could be moved server side.  This is important as it sounds to me like either:

A. The app is not written well for performance.

B. You have network congestion.

  WIth 70 users, my guess is it's B, with a little of A thrown in.  Apps that grow fast don't get thought through as they should starting off with a handfull of users.  Performance issues are not taken into consideration as they should be up front.  App works well then to a point, but when you hit a certain number of users, it goes downhill fast.  This also causes B to a certain extent.

  Also the number of users is an issue.  Typically about 35 - 40 users is tops for a JET based read/write app because of corruption problems.  With any database, an abnormal disconnect can cause corruption.  Since JET is client based, you have 35-40 users each processing against the DB file rather then one centeral engine server side that can roll things back if something happens as you do with SQL Server.  So when you start getting up into this range, the odds increase to the point where sooner or later, one of those users is going to cause a problem.

  I'm surprised that your not having problems already with the 70 users.

  SQL Server is also more robust and offers more features, such as on-line backups, rollback and forward capabilities, and processing server side.

  JET apps can be pushed farther then that if you have a rock solid network, well trained users (no Ctrl/Alt/Del), and a well written app, but with 70 users, I would really look at another backend database engine.

FWIW,
JimD.


0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

LOL, looks like I took too long to type that response<g>.

JimD.
0
 
chilternPCCommented:
No not overkill, MS SQL would be the easiest upgrade path but
what's more worrying is the growth rate - double each month? so its been going 4 or so months? and this time next year it will be 32Gig ? or do you think the growth rate will fall?
0
 
LB1234Author Commented:
mwvisa1,

So this is a free, scaled down version of SQL Server?
0
 
LB1234Author Commented:
Jdett,

Thanks for the wealth of information!  Really helped a lot.  What is JET though?  And how do we know if our database is JET?

And actually, we use Access Runtime on our client machines, users do not have a full copy of Access on their databases.  Could this be factoring into the issue?
0
 
LB1234Author Commented:
Jdett,

the following was from the link you sent (with my comments below the drawn line):

Use a robust file server
Make sure that you have a robust file server that can handle the number of users and the requests that are being made to the Microsoft Jet database file. Additionally, make sure that the file server is not overtaxed with handling other processes, such as acting as a Windows domain controller, an Exchange server, or a SQL Server.

A problem also occurs if you restart the server to fix a problem with another important service, such as the mail service. Problems also occur when you restart the server after you apply new software or you apply a service pack or a hotfix, and you forget that the Microsoft Jet database is currently shared on the server. When the file server is restarted, unexpected interruption of the client connections to the database occur. This may cause database corruption. To prevent interrupted client connection, all clients must close the database before the file server is restarted or before software updates to the file server are applied.

A file server must also be put in a secured location where the file server cannot be accidentally switched off. The server must have an uninterrupted power supply (UPS) to help protect from intermittent power outages or from power fluctuations. The network file server must also have the following:
"      High-performance hard drives
"      A high-quality network card
"      Lots of RAM to make sure that the server can handle the load


_________________________________________________________

The database files on the backend are currently on a domain controller which is also our file and print server, so that server is doing quite a bit.  I wonder if this factors into the problem, although file and print requests are served up pretty quickly.
0
 
LB1234Author Commented:
We hired an Access person to come in here and set this all up for us.   Basically every person in the firm has their own folder, with a copy of the 16 meg front end in it.  Every time she makes updates to the file she has to copy the 16 meg access file to all of our users.  Seems like a bizarre way to do things, but I guess she's trying to make it so that things aren't corrupted?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Thanks for the wealth of information! Really helped a lot. What is JET though? And how do we know if our database is JET?>>

JET is the default database engine used by Access. Your database's would have the extention .MDB, .MDE, or .MDW (although actually they can have any extention if you want).

JET is a departmental level client side database engine, which is designed to be used over a local LAN.

<<And actually, we use Access Runtime on our client machines, users do not have a full copy of Access on their databases. Could this be factoring into the issue?>>

No. Actually, the "runtime" and the full version are the exact same .EXE. Only difference in the installs is some registry keys. When in "runtime" mode, all design features are disabled. In fact you can put the full version in runtime mode by using the /runtime switch from the command line.

<<The database files on the backend are currently on a domain controller which is also our file and print server, so that server is doing quite a bit. I wonder if this factors into the problem, although file and print requests are served up pretty quickly.>>

Yes, that does sound like a lot. I would use task manager on the server to monitor Memory, CPU Load, and network utilization when everyone is going at it. If you see bottle necks in any of those, then moving to another server would help.

If you don't, then it's probably congestion out on the network that is slowing things down. and hopefully, none of your users are accessing the back end over a WAN. I'm assuming yourt app is split, with a front end on each station and a back end with data only on the server. If not, then this is something you need to do as without doing so, not only is data getting pulled over the network, but all the objects (forms, report, code, etc) as well. That just makes network congestion that much worse.

JimD.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<We hired an Access person to come in here and set this all up for us.   Basically every person in the firm has their own folder, with a copy of the 16 meg front end in it.  Every time she makes updates to the file she has to copy the 16 meg access file to all of our users.  Seems like a bizarre way to do things, but I guess she's trying to make it so that things aren't corrupted?>>

  No, that is actually a good design.  Its called a "split" design and is important to do.  Although there are *simple* ways to make the rollout of a new release automatic.

  So what size is the backend with all the data that is sitting on the server?

  The situation sounds quite a bit different then what I thought it was.

JimD.
0
 
Patrick MatthewsCommented:
LB1234 said:
>>We hired an Access person to come in here and set this all up for us.   Basically every person in the firm has
>>their own folder, with a copy of the 16 meg front end in it.  Every time she makes updates to the file she has
>>to copy the 16 meg access file to all of our users.  Seems like a bizarre way to do things, but I guess she's
>>trying to make it so that things aren't corrupted?

That does not sound unusual: best practice dictates that each user have his/her own copy of the front-end.  There
are tools available that make this front-end update and distribution easier and more automatic, but they are not
free.

Based on having 70 users, I wholeheartedly concur with the other Experts that you need to think about moving to
a more robust RDBMS for the back-end.  SQL Server is probably easiest given that both are MS products, but
any enterprise-class RDBMS (Oracle, DB2, SQL Server, MySQL, others) will do.

Please note, though, that to really get the benefits of the change, your app may have to be substantially rewritten,
to ensure that most of the work gets done on the DB server and not on the client running the front-end.
0
 
Kevin CrossChief Technology OfficerCommented:
LB1234,

Yes, SQL Server Express Edition is the free version of SQL.

Although it has the same limit per database of 2GB, you can have multiple databases and gain all the other robust features of SQL Server.  There are very few things that they left out of Express Edition especially if loaded with Advanced Services.

Think it will allow to do more tuning of performance and pruning on some scheduled basis to keep backend size manageable.  Since you can have stored procedures and other such code, the size of the front end for queries may descrease as well.

My $0.02 anyway.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Although it has the same limit per database of 2GB, you can have multiple databases and gain all the other robust features of SQL Server.>>

  Just to be clear on this, multiple databases apply to JET as well to get around the 2GB limit (you can have multiple backend databases each with a single table of 2GB).  What's important is the features that SQL Server and other RDBMs bring to the table.

  Also, as far as moving to SQL Server, you can do it in a couple of steps.  First would be simply to move the tables into SQL server and then use ODBC links in your Access front end to connect to those tables.

  What you pickup is on-line backups, a more robust situation in terms of corruption (now only one engine is touching the data, which runs on the server), and in most cases, some speed.  This can be achieved with re-writting very little of the app.

  Phase II would be to start using pass-through queries.  It's just what it sounds like; you write a query in the app that gets passed through to the server.  JET doesn't process it locally (JET does stay involved even though your using a SQL Server backend unless you take some extra steps, but no where near to the same degree as if you were using it on it's own).

  Because it is passed through and executed server side, the SQL must be in the dialect of the RDBMS your using.  That requires some changes in your SQL other then just making the query pass-through.

  Phase III is the use of stored procedures and triggers to carry out actions, enforce business rules, etc.  All this happens server side, so it's very fast.

  But Phase II and III require significant re-work of the app.  Most will stay with just moving data into SQL and are happy with that.

  As has been said, SQL Express is free, so you can try that out without much effort and see if it will work for you.

JimD.
0
 
LB1234Author Commented:
Ok guys, checking a few things regarding.  Will be sure to award points tomorrow for all the very helpful responses.  THanks again.
0
 
Gustav BrockCIOCommented:
You forgot to answer the clever question from Jim:

>  So what size is the backend with all the data that is sitting on the server?

>  The situation sounds quite a bit different then what I thought it was.

/gustav
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 6
  • 5
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now