Migrating On premise SQL database to Azure SQL database using DMA v3.2

Hi all,

This blog covers the steps to migrate the On premise SQL Server database to Azure SQL database using Database Migration Assistant v3.2 (DMA) tool.

Data Migration Assistant (DMA) tool provides 2 options:

1. Assessment: To asses the on premise database and check for any compatibility issues while upgrading to target database platform.
2. Migration: Actual migration of the schema and the data from source database to the target platform.

Latest release of DMA tool (V3.2) includes the following:

• Enabled schema and data migration from on-premises SQL Server databases to Azure SQL Database with a new migration workflow.
• During schema migration to Azure SQL Database, DMA will script the source database objects, gives guidance on how to fix any potential compatibility issues, and then deploy schema to Azure.

The tool can be downloaded from: https://www.microsoft.com/en-us/download/details.aspx?id=53595

Migration of Onpremise SQL Server database to Azure SQL database steps are mentioned below. For demonstration purpose, let's use Adventureworks2012 database.

Below screenshot shows the number of objects present in the on premise Adventureworks2012 database.

For migration purpose, created a blank database named "Adventureworks" blank database on Azure SQL database platform. As per the screenshot below, there are no user objects.

To migrate the database, launch DMA tool and select Migration option and set the migration scope as "Schema and Data".  Select the Source as SQL Server and the Target Server type as Azure SQL Database. With the latest version of DMA, Schema and data can be selected in migration scope option.

Next step is to select the on-premise source database. 

Connect to the target Azure SQL database logical server and select the database created for migration.

Select the objects from the source database which need to be migrated.

Latest version of the tool lists the compatibility issues encountered which need to be mitigated.

Once the compatibility issues are rectified, click on Generate SQL script option:

The T SQL script generated can be saved to a file, which can be applied on the target SQL database manually as well.

Click on deploy schema. Once the schema deployment is complete, review the deployment results.

Once the schema is successfully deployed, click on Migrate Date option. The tool enumerates all the table objects, but picks only the tables selected in the schema creation step.

Below screenshot shows successful data migration.

Next step is to connect to the Azure SQL database and compare the objects with the source database.

Hope the above steps mentioned will help you migrate the On premise SQL database to Azure SQL database.

Please share your feedback, questions and/or suggestions.

Thanks,

Don Castelino | Premier Field Engineer | Microsoft

Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.

• Anonymous
  October 03, 2018
  Hi there..... I am trying to migrate a database. I only want to migrate the schema with DMA, then I'll use Azure DMS to migrate the data. The problem is that the Object Script generation process has been running for about 4 days now and it still has not finished. My database has 32,163 user tables though (it is a Dynamics NAV database with over 20 companies). What is the time expectation for that many objects to be scripted?