⚠️ This page has been archived

✅ New page with updated info: ssw.com.au

Home > Archive > 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:

  1. Less Chance of Data Corruption
  2. Database Log Files Enable Data Recovery
  3. Support for More Concurrent Users
  4. 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