When you Outgrow Access
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 key differences to be aware of when migrating from Access to SQL Server. The 4 main reasons to upsize your Access database to SQL Server is as follows:
- Less Chance of Data Corruption
- Database Log Files enable Data Recovery
- Support for More Concurrent Users
- Support for a Larger Database
Less Chance of Data Corruption
In Access, you open the Database file directly. If 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.
Database Log Files enable Data Recovery
SQL Server has a distinct advantage over Access since all database update, insertion and deletion transactions 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.
Support for More Concurrent Users
Access supports a maximum of 255 concurrent users and therefore 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. Due to its optimized query processing engine and ability to simultaneously utilize multiple computers, processors and hard drives, SQL Server can scale to meet any enterprise requirements.
Support for Larger Databases
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 will consider upsizing your Access database(s) if the size of any one of the linked tables reaches 100MB.
SQL Server, on the other hand, has vastly improved storage capabilities. It allows for 524,272 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.
By Admin at 12 Jul 2016, 12:00 PM