Home > SSW Standards > DeveloperSQLServer > SSW Standard for Upsizing Access to SQL Server
Why Upsize to SQL Server?
This document is a summary of the SSW whitepapers,
Whats New and Different
when Moving Your Backend from Access to SQL Server 2000 and
How to Migrate
Your Backend from Access to SQL Server 2000.
Microsoft Access developers generally consider a move to SQL Server
for performance, security and stability reasons. This process is known
as upsizing and developers will find a number of key differences while
migrating from Access to SQL Server. The 4 main reasons to upsize your
database from Access to SQL Server 2000 are:
- Less Chance of Data Corruption
- Database Log Files Enable Data Recovery
- Support for More Concurrent Users
- Support for a Larger Database
1. Less Chance of Data Corruption
In Access, you open the MDB file directly. If, for example, the
network connection is unexpectedly broken or a client or server machine
shuts off without properly shutting down the application, there is a
good chance your data will be corrupted.
SQL Server, on the other hand, runs as a service you do not have
direct access to the file. Requests for data are managed by the SQL
Server service, so if the server shuts down unexpectedly or the network
connection is broken, the next request for data will not be fulfilled.
Therefore the integrity of the data is maintained.
2. Database Log Files Enable Data Recovery
SQL Server has a distinct advantage over Access in that all
transactions (database updates, insertions and deletions) are kept in a
log file. This log records the changes to the data and enough
information to undo the modifications (if necessary later) made during
each transaction. These logs can also be used to recover your data in
the event of system failure.
3. Support for More Concurrent Users
Access supports a maximum of 255 concurrent users, and
as such is not a feasible enterprise-level data storage solution. In a
real world environment it is common to experience major performance
issues with as few as 20 users attempting to use the Access database
simultaneously over a network.
SQL Server supports a concurrent user base that is
limited only by available system memory, and because of its
optimized query processing engine and ability to simultaneously utilize
multiple computers, processors and hard drives, it can scale to meet any
enterprise requirements.
4. Support for a Larger Database
Access supports a maximum database size of 2GB plus linked tables.
Although use of linked tables theoretically enables you to store much
more data, it is common to experience performance issues due to the
amount of data being processed. Generally you should consider upsizing
your Access database(s) if the size of any one of them reaches 100MB.
SQL Server, on the other hand, has vastly improved storage capabilities,
allowing for 1,048,516 TB of data to be stored efficiently across
multiple devices. It also has self-repairing and self-compacting
features, making it a very robust database solution.
For many more reasons to upsize your database, and steps on how to
perform an effective migration, see the SSW whitepapers,
Whats New and Different
when Moving Your Backend from Access to SQL Server 2000 and
How to Migrate
Your Backend from Access to SQL Server 2000.
More Links on Upsizing
Whats New and
Different when Moving Your Backend from Access to SQL Server 2000
How to Migrate
Your Backend from Access to SQL Server 2000
Sample Release Plan for
upsizing (Upsizing Template)
Acknowledgements
Jatin Valabjee
Marten Ataalla
David Klein