Migration guide: Access to Azure SQL Database

Article
11/04/2022

In this guide, you learn how to migrate your Microsoft Access database to an Azure SQL database by using SQL Server Migration Assistant for Access (SSMA for Access).

For other migration guides, see Azure Database Migration Guide.

Prerequisites

Before you begin migrating your Access database to a SQL database, do the following:

Verify that your source environment is supported.
Download and install SQL Server Migration Assistant for Access.
Ensure that you have connectivity and sufficient permissions to access both source and target.

Pre-migration

After you've met the prerequisites, you're ready to discover the topology of your environment and assess the feasibility of your Azure cloud migration.

Assess

Use SSMA for Access to review database objects and data, and assess databases for migration.

To create an assessment, do the following:

Open SSMA for Access.
Select File, and then select New Project.
Provide a project name and a location for your project and then, in the drop-down list, select Azure SQL Database as the migration target.
Select OK.
Select Add Databases, and then select the databases to be added to your new project.
On the Access Metadata Explorer pane, right-click a database, and then select Create Report. Alternatively, you can select the Create Report tab at the upper right.
Review the HTML report to understand the conversion statistics and any errors or warnings. You can also open the report in Excel to get an inventory of Access objects and understand the effort required to perform schema conversions. The default location for the report is in the report folder within SSMAProjects. For example:

drive:\<username>\Documents\SSMAProjects\MyAccessMigration\report\report_<date>

Validate the data types

Validate the default data type mappings, and change them based on your requirements, if necessary. To do so:

In SSMA for Access, select Tools, and then select Project Settings.
Select the Type Mapping tab.
You can change the type mapping for each table by selecting the table name on the Access Metadata Explorer pane.

Convert the schema

To convert database objects, do the following:

Select the Connect to Azure SQL Database tab, and then do the following:

a. Enter the details for connecting to your SQL database.
b. In the drop-down list, select your target SQL database. Or you can enter a new name, in which case a database will be created on the target server.
c. Provide authentication details.
d. Select Connect.
On the Access Metadata Explorer pane, right-click the database, and then select Convert Schema. Alternatively, you can select your database and then select the Convert Schema tab.
After the conversion is completed, compare the converted objects to the original objects to identify potential problems, and address the problems based on the recommendations.

Compare the converted Transact-SQL text to the original code, and review the recommendations.
(Optional) To convert an individual object, right-click the object, and then select Convert Schema. Converted objects appear in bold text in Access Metadata Explorer:
On the Output pane, select the Review results icon, and review the errors on the Error list pane.
Save the project locally for an offline schema remediation exercise. To do so, select File > Save Project. This gives you an opportunity to evaluate the source and target schemas offline and perform remediation before you publish them to your SQL database.

Migrate the databases

After you've assessed your databases and addressed any discrepancies, you can run the migration process. Migrating data is a bulk-load operation that moves rows of data into an Azure SQL database in transactions. The number of rows to be loaded into your SQL database in each transaction is configured in the project settings.

To publish your schema and migrate the data by using SSMA for Access, do the following:

If you haven't already done so, select Connect to Azure SQL Database, and provide connection details.
Publish the schema. On the Azure SQL Database Metadata Explorer pane, right-click the database you're working with, and then select Synchronize with Database. This action publishes the MySQL schema to the SQL database.
On the Synchronize with the Database pane, review the mapping between your source project and your target:
On the Access Metadata Explorer pane, select the check boxes next to the items you want to migrate. To migrate the entire database, select the check box next to the database.
Migrate the data. Right-click the database or object you want to migrate, and then select Migrate Data. Alternatively, you can select the Migrate Data tab at the upper right.

To migrate data for an entire database, select the check box next to the database name. To migrate data from individual tables, expand the database, expand Tables, and then select the check box next to the table. To omit data from individual tables, clear the check box.
After migration is completed, view the Data Migration Report.
Connect to your Azure SQL database by using SQL Server Management Studio, and validate the migration by reviewing the data and schema.

Post-migration

After you've successfully completed the migration stage, you need to complete a series of post-migration tasks to ensure that everything is functioning as smoothly and efficiently as possible.

Remediate applications

After the data is migrated to the target environment, all the applications that formerly consumed the source need to start consuming the target. Accomplishing this will in some cases require changes to the applications.

Perform tests

The test approach to database migration consists of the following activities:

Develop validation tests: To test the database migration, you need to use SQL queries. You must create the validation queries to run against both the source and target databases. Your validation queries should cover the scope you've defined.
Set up a test environment: The test environment should contain a copy of the source database and the target database. Be sure to isolate the test environment.
Run validation tests: Run validation tests against the source and the target, and then analyze the results.
Run performance tests: Run performance tests against the source and the target, and then analyze and compare the results.

Optimize

The post-migration phase is crucial for reconciling any data accuracy issues, verifying completeness, and addressing performance issues with the workload.

For more information about these issues and the steps to mitigate them, see the Post-migration validation and optimization guide.

Migration assets

For more assistance with completing this migration scenario, see the following resource. It was developed in support of a real-world migration project engagement.

Title	Description
Data workload assessment model and tool	Provides suggested "best fit" target platforms, cloud readiness, and application/database remediation levels for specified workloads. It offers simple, one-click calculation and report generation that helps to accelerate large estate assessments by providing an automated, uniform target-platform decision process.

The Data SQL Engineering team developed these resources. This team's core charter is to unblock and accelerate complex modernization for data platform migration projects to Microsoft's Azure data platform.

Next steps

For a matrix of Microsoft and third-party services and tools that are available to assist you with various database and data migration scenarios and specialty tasks, see Service and tools for data migration.
To learn more about Azure SQL Database see:
- An overview of SQL Database
- Azure total cost of ownership calculator
To learn more about the framework and adoption cycle for cloud migrations, see:
To assess the application access layer, see Data Access Migration Toolkit (preview).
For information about how to perform Data Access Layer A/B testing, see Overview of Database Experimentation Assistant.