MS Access Database to SQL Server Migration in 6 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.

MS Access database architecture

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 the 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.

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!

The process of a MS Access database to SQL Server migration

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.

Step 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!

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.

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.

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.

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.

Originally published at https://modlogix.com on June 24, 2022.

--

--

ModLogix helps organizations move legacy applications to new secure, stable, and scalable platforms. To explore more, please visit https://modlogix.com/

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
ModLogix

ModLogix

ModLogix helps organizations move legacy applications to new secure, stable, and scalable platforms. To explore more, please visit https://modlogix.com/