How to Migrate an Access Database to SQL Server in 6 Easy Steps
Growth in your business or organization brings an increase in the amount of information you store and manage. As your database size approaches the 2GB mark, which is the limit for MS Access, migrating to another Database Management System (DBMS) should be your immediate thought.
A few great alternative DBM systems present themselves: Oracle, PostgreSQL, SQL Azure Database, and SQL Server, being the most popular options on the market. This DBMS offers a lot in terms of the capability to store and retrieve data safely and accurately. However, since both MS Access and SQL Server have the same roots as Microsoft products, the migration to the SQL Server will be much easier.
This article provides a simple, step-by-step guide on how to migrate from MS Access to SQL Server. We’ll also walk you through some of the advantages of SQL Server over MS Access, and share valuable thoughts from database migration experts.
Why Do Companies Migrate from Access to SQL Server?
When it comes to managing enterprise-level data, MS Access doesn’t cut it for many reasons, including the fact that it’s 30 years too old. Microsoft even announced the retirement of MS Access from its online productivity suite in November 2017, before quietly changing its mind. Updates continued to appear – the latest version of Access in Office 365 was released in September 2020.
Even though MS Access is easier to learn, provides an easier and simple way of creating relational databases, and provides the possibility to use VBA to create fully-fledged data-centric applications, many companies are still migrating from MS Access to SQL Server, and so, over the years.
This is because of the numerous benefits that SQL Server and related DB systems have over MS Access. Microsoft Access is falling behind like FoxPro and COBOL and SQLBase.
To see the big picture, companies migrate Access to SQL Server for the following reasons:
Improved security
SQL Server is more secure than MS Access. It uses 128-bit encryption to provide a more secure way to store sensitive data like Social Security numbers, credit card numbers, and addresses. It also uses the same encryption algorithms to store user and application data in a remote place.
Many IT pros and businesses simply won’t tolerate Access because of its vulnerability to corruption. This means it’ll be harder to expand your business and find new business partners.
Increased availability
With SQL Server, you can dynamically backup the database (incremental, or complete) while it’s in use. The benefit? You do not have to force users to exit the database for every backup session.
As you might suspect, this feature is simply unavailable for MS Access owners. That means the business must pay for downtimes caused by DB inaccessibility for hours, days, or even weeks.
Better performance and scalability
SQL Server is efficient because it performs queries in parallel, thus reducing memory use. The Server also allows multiple concurrent users and supports huge, terabyte-sized databases – compared to the 2GB limit for an Access database. Access is usually preferred for small, general tasks.
Superior database maintenance and integrity
With features like triggers, transaction logs, and repair processes, the SQL Server delivers better stability, integrity, and non-repudiation.
- The MS Access database can become corrupt, thus it’s important to keep it up to date to get the best results. To check the current version of Microsoft you’re using, check the official blog.
Server-based processing
The client/server approach utilized by SQL Server enhances server processing speed by lowering network traffic and dealing efficiently with huge data volumes.
Because Access is a file server, it lacks the capabilities of a database server.
Interoperability with Other DBMS
One problem with Microsoft Access is that it has its own file format that isn’t compatible with any other system. The .accdb format imposed by Microsoft is unique to MS Access alone. This is a huge block to integration and interaction with data types from other database systems.
SQL Server provides good interoperability with other database management systems, allowing developers to create safe and high-performance applications.
Is your issue rather linked to legacy systems built on MS Access? Learn how to integrate legacy systems efficiently, with our complete guide.
Getting Started: MS Access to SQL Server Migration
If you find the SQL Server benefits more attractive than MS Access, and the nature and capabilities of SQL meet your data processing needs, development priorities, and project specifics; it is high time you started upgrading a legacy database to the modern environment.
After that, you need to set up your migration environment. In this guide, we’ll use the SQL Server Migration Assistant for Access (SSMA for Access), a recommended tool from Microsoft, but prepare to conduct manual fixes.
Below is the basic structure of the MS Access application. As a desktop DBMS, MS Access stores all of the components of the database system application within a single file. The DBMS software runs on a desktop computer. Data Entry forms, reports, and queries interact with the database tables stored within the same MS Access Database file.
To begin:
- Develop a data migration strategy for efficient outcomes.
- Download and install SQL Server Migration Assistant for Access (SSMA for Access). SSMA for Access is installed using a Windows Installer-based wizard. We used the Windows installer 3.1 for this guide.
- To get the latest version of SSMA service and other prerequisites for installation, please see the official download page.
- Get connectivity and sufficient permissions on the computer that hosts the target instance of your SQL server.
New SQL Server 2022 is officially available! Explore the new features and plan your migration. SQL Server 2022 Release Date and New Cloud Capabilities 
6 Steps to Migrate MS Access Database to SQL Server
We’ll follow 6 major steps to import the Access database to SQL Server, divided into two phases: the pre-migration and the post-migration phase.
Before taking on the first step, it is advisable to involve an expert to complete some preparatory steps to ensure a smooth transition. These preliminary operations include adding table indexes and primary keys because every table in SQL Server must have a minimum of one index.
Every linked table must feature a primary key – if any update operation is to ever occur in the table. Plus, it is essential to verify primary and foreign key relationships by checking that they are associated with table fields featuring congruous data types and sizes.
Finally, it may help to analyze the whole system and to eliminate attachment columns. This is essential considering that SSMA does not change tables with attachment columns.
Ready to get started with the Access to SQL Server migration? Let’s dive in!
Step 1: Assessment
Use SSMA to review your database objects and the database itself. To do this, open SSMA for Access and click on a file then new project. Then enter a project name and a location to save your project. Select an SQL Server migration target from the drop-down list, and click OK.
Right-click the database you want to assess in the Access Metadata Explorer and then select ‘Create Report‘. You are advised to review the report so as to identify any errors and understand the conversion statistics.
Check your database for vulnerabilities and redundant dependencies.
Learn moreStep 2: Validate the data types
You’re required to change your data types or validate the default data types. To change your data types, select ‘Project settings’ on the tools menu and then select the mapping table tab. Select the table in Access Metadata Explorer to change its mapping type.
Step 3: Convert
Converting database objects takes the object definitions from the Access metadata, converts them into equivalent Transact-SQL (T-SQL) syntax, and then loads this information into the project.
To accomplish this, select the ‘Connect to SQL Server‘ query and fill up the connection information. In your Access Metadata Explorer, right-click the database and choose ‘Convert Schema’.
Compare and review the converted objects to the original objects when the conversion is complete to detect potential flaws and resolve them using the recommendations. Select ‘Review results’ in the output window, and then review the mistakes in the Error List pane.
Finally, select ‘Save Project’ from the File menu. This step allows you to assess the source and target schemas offline and make any necessary changes before publishing the schema to SQL Server.
Step 4: Migrate
Finally, it’s time to move the Access database to SQL Server. The first thing to do here is to publish the schema we converted in the previous step to the SQL Server. In SQL Server Metadata Explorer, right-click the database and select ‘Synchronise with Database’ to publish the schema.
Now, examine the relationship between your source project and your target. To migrate the data, perform a right-click on the database or object you want to migrate in Access Metadata Explorer and select ‘migrate data.’
Alternatively, you can go to the ‘Data Migrate’ tab. Check the box next to the database name to migrate data for the entire database. To migrate some particular tables, Open the database, expand Tables, and then click on the check boxes next to the tables to transfer data from individual tables. Uncheck the boxes corresponding to tables you want to omit. Once the Access database migration to SQL Server is complete, do well to view the data migration report.
But we’re not quite done yet!
MS SQL Database Upgrade
Moving from MS SQL Database version 14 to 17 improved data accessibility and integrity.
VIEW CASE STUDY
Now Connect to your SQL Server instance with SQL Server Management Studio, and verify the data and schema to ensure the Microsoft Access to SQL Server migration is successful.
Before we move on, is your system built on AngularJS? Check out this complete guide on AngularJS to Angular migration, from the ModLogix team.
Post Migration Phase
The post-migration step is critical for resolving data accuracy and functionality concerns, ensuring completeness, and dealing with workload performance difficulties.
Step 5: Perform test
After the upgrade process is complete, certain tests are to be performed for verification and validation, including:
- Developing a validation test, which involves creating validation queries to run against both the source and the target databases.
- Setting up a test environment that will contain a copy of the source database and the target database.
- Running validation tests against the source and the target, and then analyzing the results.
- Running performance tests against the source and the target, and then analyzing and comparing the results.
How to test a legacy codebase without breaking anything? Learn here. Testing Legacy Codebase: What is, Common Problems and Best Practices
You may encounter certain issues after migration, often related to Queries, Date and time, Data Types, Attachments, Hyperlinks, etc.
Step 6: Optimize performance
Choosing whether to use local or remote queries is one of the effective approaches to improve performance with your new back-end SQL Server. Here are some of the things you should consider doing:
- For quickest access, run short, read-only queries on the client.
- Use the server to run extensive read/write queries to make use of the extra processing capacity.
- Filters and aggregation might help you reduce network traffic by transferring only the data you need.
- Minimize loading data in a form or report.
- Consider using local tables for data that rarely changes.
There it is… our ultimate guide on how to convert Access database to SQL efficiently.
One last thing…
Additionally, MS Access databases also integrate with the .NET Framework and .NET Core projects. If you’re considering migrating your applications (built on MS Access) to the .NET Core platform, then here’s everything you need to know about .NET Framework to .NET Core migration.
Consider ModLogix as Your Legacy Software Modernization Partner
Would you want a professional legacy software modernization company to timely handle the entire migration process, so you can avoid the hassle, risks, and all potential migration problems?
ModLogix team can take care of your SQL Server migration and similar software modernization needs for your business, while you focus on creating value for your customers. With 8 years of experience modernizing legacy software under our sleeves, we’re the right partner for you.
Why choose us?
- We do the migrations from legacy databases such as Access, Excel, FoxPro, and DB2 to modern solutions. ModLogix is qualified in heterogeneous and homogeneous migrations. Here’s a successful case study where we worked on MS SQL DB migration of the enterprise system.
- As a part of Langate Corp, ModLogix is a Gold Microsoft partner (MS SQL is a Microsoft product), which means we have access to the most innovative features and tools to perform the migration.
- We’ll make use of “Access to SQL Server migration best practices” to reduce migration problems and inefficiencies.
Final Thoughts
It is high time you move your MS Access databases to a more secure, efficient, and robust DBMS. The SQL Server is the best fit for your needs. We hope that our guide didn’t leave any questions on how to migrate the Access database to SQL Server. If you have any questions or worries, please contact us today to explore your modernization opportunities.
