Moving your SQL database to Azure SQL: Using Data Migration Assistant

In the previous article of this three-part series, we covered all the basics to build the SQL infrastructure to support a migration from on-premises to Azure SQL. Now, here in part 3, we are going one step further by assessing the current SQL on-premises and migrating the supported databases into Azure SQL using the Data Migration Assistant (DMA).

Performing the SQL assessment

We can install the Data Migration Assistant on a workstation or server that has access to the SQL servers that we are planning to migrate to Azure SQL.

Open Data Migration Assistant and click on the + icon to start a new project type, which can be either assessment or migration. We are going to perform the assessment, and we will provide a name, the type of assessment source (SQL server), and the target (Azure SQL database). Click on Create.

Azure SQL DMA

The assessment wizard is comprised of three areas: options, sources, and the review. On the Options tab: The assessment can provide a report of database compatibility or feature parity, select both and click Next.

Azure SQL DMA

On the Select Sources tab: We can add as many servers as we want on the list. By default, a connection to the first server will be displayed on a blade on the right side, provide the required information to authenticate on the server, and click on Connect. To add additional servers, click on Add Sources, and provide the authentication process for each new entry.

After being authenticated to the given server, a list of all databases will be listed. Pick the ones that you are planning to migrate and click on Add. After completing the list of SQL servers and databases, click on Start Assessment.

Azure SQL DMA

On the Review Results page: We can select either SQL server feature parity or Compatibility issues (Item 1), and then select the desired database from the list that was assessed (Item 2). On the right side, we will have all the detailed information about issues, changes, features, and blockers, which saves a lot of time from the database administrator to plan the migration of the database to the cloud.

After the assessment is complete, we can move the results to the Azure Migrate Project in Microsoft Azure. Click on Upload to Azure Migrate.

Note: You need an Azure Migrate Project created before this task. If you don’t have it, go to the Azure Portal and create it before continuing this section.

Azure SQL DMA

A new blade to connect to Azure will be displayed on the right side, select Azure and click on Connect, the authentication process including MFA (if necessary) will occur, and after authenticated, we have to choose the subscription and Azure Migrate Project, as depicted in the image below. Click on Upload and wait for the dialogue box saying assessment uploaded and click on OK.

Azure SQL DMA

Microsoft states that it may take up to 15 minutes. However, you can refresh before that, and the number of assessed databases will increase based on the data that was just uploaded.

Azure SQL DMA

The result will be a list of all instance names, their versions, and databases in a single view per Azure Migrate Project. Keep in mind that we can have more than one Azure Migrate Project at any given time.

Migrating your SQL database

We can use the Data Migration Assistant to perform offline migration of SQL databases. The ideal scenario is to have a maintenance window to stop any access to the databases that will be migrated, perform the migration following the steps that we are going over in this section, and, last but not least, get your DBA team to validate the data and connection to the Azure SQL.

When using Data Migration Assistant to migrate the SQL database, the first thing that we must provide is the SQL database itself. Unfortunately, we cannot create it as part of the migration process. The easiest way to check if you have the desired SQL database is by looking at SQL databases item on the SQL server properties.

If you don’t have the database that you are planning to bring to Azure SQL created, provision a new one by clicking on Create Database link located in the Overview blade of the SQL server.

Time to create a new Migration project using the Data Migration Assistant: select migration, define a name, the source, and target, and click on Create.

A small wizard comprised of six steps will begin. In the step Select Source, type in the on-premises SQL server and methods to authenticate, click on Connect and select the database that you are planning to migrate. Click on Next when done.

Azure SQL DMA

In the Select target page. Type in the SQL server FQDN (it has to contain the suffix .database.windows.net), the authentication, and the SQL database that will receive the data. Click Next.

Note from the field: If your DMA is on the Internet, make sure to add your public IP to the list of allowed clients on the Azure SQL server.

Azure SQL DMA

On the Select objects page: We need to select which components we will be migrating to Azure SQL. When complete, click on Generate Script.

On the script & deploy schema page: If you have a good understanding of SQL, then review the script and perform any changes that you may see fit. Click on Deploy Schema.

Note: SQL logins will be re-created with strong random passwords. Those accounts must have their password changed in Azure SQL.

All steps to deploy the script will generate an output on the right side. We can see which ones were successful, and the ones that have failed. If everything went according to plan, click on Migrate Data.

On the Select Tables section: We need to select the tables that we want to move to Azure SQL. Select all tables that must be part of this migration, and click on Start data migration.

Note: When planning to migrate production data, the Microsoft recommendation is to change the SQL database to P15 for an optimal experience.

The Migrate Data page will contain the status of the entire migration. In that single section, we can see all the status, source, and target databases.

A small check to validate if the data was migrated is using the Query editor (preview) at the SQL database level. Click on it and perform the authentication either using SQL or Azure AD accounts. In the new blade, execute a query to list the values of some of the tables migrated to Azure SQL, the amount of data in that query should be the same that you have on-premises.

Azure SQL DMA

Database Migration Assistant: Sounds like a plan

In this, the third and final article of our series, we completed the migration of an on-premises SQL server into Azure SQL using the import method and the Data Migration Assistant. The DMA is more sophisticated and provides assessment and migration in two different stages. The cloud/DBA administrator can plan properly and take action before the actual migration.

Is this everything? Of course not! We can take advantage of the Database Migration Service in Microsoft Azure to keep online synchronization between SQL databases, and another doable option is to use third-party tools to help on the migration process.

Featured image: Shutterstock

About The Author

1 thought on “Moving your SQL database to Azure SQL: Using Data Migration Assistant”

  1. Thanks for this write up, what if anything should we do to the source database during migration. If we can support a downtime window should we put SQL in single user access mode for example?

Leave a Comment

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Scroll to Top