Why would I want to upgrade my Access app to SQL Server?

[ SQL Server Upsizing | Access Tips | Main ]


The biggest problems are in stability of the hardware and the number of users making changes. Reporting or inquiry only users don't make a difference.

Also your big concerns are how mission critical is the data and can the data be rekeyed if you lose a day.

Mission critical means can you afford to lose an hour if the database is down. Frequently the data can't be rekeyed. A classic example being a call centre where you are receiving incoming calls.

I recall a posting by someone working for a large casino/hotel operation in the mid to late 80s stating to what lengths they went to ensure they would never lose a reservation.  They spend over a million dollars duplicating their IBM S/38 mini-computer in another offsite location with data inserts and updates being copied from the main system to the backup system in under a second.

Their attitude was that they could never afford to lose a room reservation. Imagine the mess if they lost a days worth of phone calls. <shudder> And the newspaper stories by the upset clients. And expenses while they placate the customers so they don't go to the newspapers. <smile>

Another option, if you have the Office Developers version is to use MSDE but there are other considerations here. MSDE is SQL Server without many tools and with a limitation of five connections. Which is *not* the same as five users.

Should you require access via slow connections, defined as any connection speed less than 10 mpbs, SQL Server can also be a good solution.  Once the Access app is fully converted to using stored views rather than Access queries SQL Server handles the selection, updating and appending of records rather than Access. Which will minimize network traffic.

However, depending on the speed of the connection, this may not be sufficient.  You may wish to also investigate Terminal Server.

For more details see my SQL Server Random Thoughts page..


Using Microsoft Data Engine

You need to understand you application to determine how many users can access MSDE server. There are reports in the newsgroups of up to 100 users depending on what they are doing.   Problems here can include bound forms with many combo/list boxes on them as Access will send several simultaneous query requests to the server. You can monitor this using Profiler trace.

There is a DBCC command (see the MSDE doc on SQL Books On Line) that you can issue to determine how many times you might have exceeded the 5 batch limit to determine when you need to upgrade to the Standard edition.

[ SQL Server Upsizing | Access Tips | Main ]