Migrating a SQL IaaS Database To Azure SQL Using DMA (Data Migration Assistant)

Overview

This document describes how to migrate a SQL database from an on-premises SQL Server (SQL IaaS) to Azure SQL using the Microsoft DMA utility (Data Migration Assistant). There are other ways to do this, but this is the preferred approach.

Data Migration Assistant

The Data Migration Assistant (DMA) assists you in upgrading to a modern data platform by detecting database compatibility issues in your new version of SQL Server or Azure SQL Database. DMA suggests speed and reliability enhancements for your target environment and enables you to migrate your schema, data, and uncontained objects from your source server to your target server.

Get Data Migration Assistant

To install DMA, download the latest version of the tool from the Microsoft Download Center, and then run the DataMigrationAssistant.msi file.

Supported source and target versions

DMA is the successor of SQL Server Upgrade Advisor, and it should be used to upgrade most SQL Server versions. The following source and target versions are supported:

Sources

SQL Server 2005 (deprecated)
SQL Server 2008
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019
Amazon RDS for SQL Server

Targets

SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017 on Windows and Linux
SQL Server 2019
Azure SQL Database single database
Azure SQL Managed Instance
SQL Server running on an Azure Virtual Machine

Pre-requisites

In order to do this, you need the following:

DMA installed and running on the SQL IaaS server (Source SQL Server).
A destination database in Azure SQL
Credentials and the full server name for the Azure SQL database
The expected size for the Azure SQL database (would be the DTU count)

STEPS

Step 1

Create the new database in Azure SQL – do NOT use an existing database as the presence of data will cause issues.

In https://portal.azure.com go to SQL Databases (using the search box at the top) and click Create

Migrating a SQL IaaS Database to Azure SQL using DMA

Select the appropriate Subscription and Resource Groups
Enter the name for the database and select a server the DB will be hosted on
Leave Use elastic pool set to no (is default)
Click Configure database in the Compute Storage section and in the Service, Tier drop down box, select DTU based model -> Standard and pick a size – should be S7 to allow the process to complete faster
Leave backup setting default – geo-redundant backup
Click Review + create
Validate all info is correct and click Create

Step 2

Once the DB provisioning is complete you will see the following:

Migrating a SQL IaaS Database to Azure SQL using DMA

*** Now that the DB has been created, we can start the migration from SQL IaaS to Azure SQL using DMA ***

Step 3

On the server that is running the SQL IaaS database, install DMA if it has not already been installed

Step 4

Open DMA from the Windows Start Menu – when prompted select Cancel on the New Version is Available screen

Step 5

Click + to create a new Migration

Step 6

Select Migration and give it a Project Name, leave the other options default and click Create.

Step 7

On the Select Source screen you will need to:

Enter the Source SQL Server name
Select the Authentication type
Click Connect – you should get a list of databases to select from
Select the Source Database you want to migrate to Azure SQL
Click Next

Migrating a SQL IaaS Database to Azure SQL using DMA

Step 8

On the Select Target screen you will need to:

Enter the Azure SQL Server name (can get it from the Deployment screen from step 2)
Change Authentication Type to SQL Server Authentication
Enter username to the Username field
Enter the password for the user account to the Password field
Make sure Encrypt connection and Trust server certificate are selected
Click Connect
Select the database you want to restore to and click Next

Migrating a SQL IaaS Database to Azure SQL using DMA

Step 9

You will see the following on the next screen (Select Object) this is expected and will last for 40 to 60 minutes.

Step 10

Once the preparing section is complete you will see the following.

Migrating a SQL IaaS Database to Azure SQL using DMA

Click on each line where you see Fix Opportunity and select Apply all Changes. You will need to scroll through the list in the column on the left as there may be more results at the bottom that need to be addressed.
Once you fix all that you can (please double check) click Generate SQL Script (bottom right)

Step 11

You will now see this screen until the schema has been scripted and is ready to apply.

Step 12

When done the buttons on the bottom of the screen will be active again. When they are, please click Deploy Schema.

Migrating a SQL IaaS Database to Azure SQL using DMA

Step 13

You will be able to track progress here as the Deployment Results column on the right lists what script of the total you are on.

Migrating a SQL IaaS Database to Azure SQL using DMA

Step 14

Once the Schema Deploy is complete, the Migrate Data button will be available. Click this button now. This prepares the data to be migrated.

Migrating a SQL IaaS Database to Azure SQL using DMA

Step 15

Next step is to click the Start data migration button.

Migrating a SQL IaaS Database to Azure SQL using DMA

Step 16

During processing you will see how many tables are in process:

Migrating a SQL IaaS Database to Azure SQL using DMA

Step 17

When the migration is complete, check to make sure Warnings and Failed are both 0.

Migrating a SQL IaaS Database to Azure SQL using DMA

Step 18

In the database blade in the Azure portal for the new database, open Compute + Storage and size the database to the expected DTU Usage – in this case is S6 – 400 DTU. To change the size either type in the expected DTU usage (it will round up or down to fit a level) or use the circle and slide left or right (left to reduce usage or right to expand usage).

Migrating a SQL IaaS Database to Azure SQL using DMA

Summary

In this article, we have learned how to Migrate a SQL IaaS Database to Azure SQL using DMA (Data Migration Assistant). If you have any suggestion/query, please share your thoughts in the comment section.

Thanks for reading and I hope you like it.