[ 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 ]