Microsoft Access Database to SQL Server: Migration Overview
Microsoft's Access Database is extremely popular and is adequate for most company applications. But Microsoft's Access is meant for small projects with few users. As frequently happens, a small Access application grows and slowly becomes the heart and soul of a company administration Application.
As volumes and the number of users increase, the limitations of Microsoft Access soon become apparent. In a nutshell, Microsoft Access does not scale well. Slow response times, routines that hang, esoteric error messages, unstable functions, security issues and data corruption are common symptoms.
When the Access Application needs to support more features, more records and more users, a robust Back-End database Application such as SQL Server is required.
SQL Server: There is less Chance of Data Corruption
In Access, the database MDB file is opened directly. If the network connection is unexpectedly broken or a PC or the Server computer breaks down, you are almost guaranteed that data will be corrupted.
SQL Server is different – it runs as a service. The Front-End Access database does not have direct access to the Back-End database file. If the server shuts down unexpectedly or the network connection is broken, the integrity of the data is maintained.
SQL Server: 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.
The log records contain all the changes to the data and enough information to undo the modifications made during each transaction. In the event of system failure, the log file can be used to recover the data.
SQL Server: Supports More Concurrent users
Users Access supports a maximum of 255 concurrent users. But this is a theoretical limit, bearing no relationship to reality. In the real world, it is common to experience major performance issues with as few as 10 users (and sometimes much less) attempting to simultaneously use the Back-End Access database over a network.
SQL Server supports a concurrent user base that is limited only by available system memory. Because of SQL Server's optimised query-processing engine and ability to utilise simultaneously multiple computers, multiple processors and hard drives, it can scale to meet any requirement.
SQL Server: Supports a Larger Database
Access supports a maximum database size of 2 gigabytes plus linked tables. Although use of linked tables theoretically enables more much data to be stored, it is common to experience performance issues when handling large volumes of records.
Consider upgrading an Access database when the database records exceed 100 megabytes in size.
SQL Server, on the other hand, has vastly improved storage capabilities, allowing for 1,048,516 terabytes of data to be stored efficiently across multiple devices. It also has self-repairing and self-compacting features, making it a particularly robust and efficient database solution.
SQL Server: Performance and Administration
Access has limited backup features and does not support point-in-time restores. Access does not have performance monitoring features.
SQL Server provides wizards that allow the database administrator to monitor and tune performance. SQL Server also contains tools to automate data backup and secure the data.
Upgrading Microsoft Access to SQL Server
Upgrading a Microsoft Access Back-End database to SQL Server is reasonably straightforward. The amount of work involved depends upon the quality of the Access database.
Here are some of the issues that may need attention:
- Amend Tables with non numeric indexing or no indexing
- Ensure all Tables have a Primary Key
- Ensure AutoNumbers are in the first column of a Table
- Reserved words need special treatment
- Remove duplicate Indexes from Tables
- Add a Timestamp to all Tables
- Use DRI (Declared referential Integrity) and not Triggers
- Change all nvarchar, ntext and text fields to varchar and varchar(max)
- Change all money to decimal(8,2)
- Change all DateTime fields to Date
- Change Tables with Subdatasheet property of Auto to None
- Change True/False Integer fields to Yes/No
Most of these issues are easy to resolve.
To create the SQL Server database, use the SQL Server Wizard in the Database Tools of the Access Ribbon. Then use ODBC to link to the SQL Server Tables. Visual Basic can be used to manipulate the SQL Server data with ADO and an OLEDB connection. And away you go!
Note that setting up SQL Server efficiently, securely and maintaining it requires a new set of skills.