A move from Microsoft Access to SQL Server is usually recommended for performance, security and stability reasons. This process is known as upsizing. There are a number of differences to be aware of when migrating from to SQL Server but the 4 primary benefits of upsiizing to SQL Server are:

  • Reduced Risk of Data Corruption
  • Improved Data Recovery with Database Log Files
  • Increased Concurrent Users capacity
  • Increased Database capacity

Reduced Risk of Data Corruption

The MDB file is opened directly in Access. This increases the risk of data corruption when  a network connection is lost or a server or client shuts off without a proper application shut down.

On the other hand, SQL Server runs as a service so you do not access the file directly. Data requests are managed from the SQL service, so an unexpected shut down or broken network connection will not be performed which will ensure data integrity.

Improved Data Recovery with Database Log Files

An advantage of SQL Server over MS Access is that all database deletion, insertion and update transactions are maintained in a database log file which can be used to undo the changes if needed. Data recovery in the case of system failure can also be performed using this log file.

Improved Concurrent Users Capacity

With a limitation of 255 concurrent users, MS Acess is not a realistic enterprise storage solution. And realistically speaking, Access performance issues can occur with as little as 20 concurrent users on the system.

The number of concurrent users on SQL Server is only limited by the system memory availability. SQL Server’s query processing engine is optimized and able to use multiple hard drives, processors and computers ito provide maximum scalability.

Increased Database Capacity

With a database size limit including linked tables of 2GB. In theory linked tables enable more data storage, but performance problems are common when large amounts of data are processed. Upsizing MS Access to SQL Server is recommended when any linked table exceeds 100MB.

On the other hand, SQL Server’s storage capacity is vastly improved allowing data of up to 1,048,516 TB to be  efficiently stored across multiple devices. It’s self-compacting and self-repairing features make it a robust database solution.