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

• 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:

*** 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

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

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. 

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

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.

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.

Step 15

Next step is to click the Start data migration button. 

Step 16

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

Step 17

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

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

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.