Moving SQL Databases to the Cloud with the Data Migration Assistant


Microsoft offers several applications and services to help you move your on-premises SQL databases into Azure. In this article, we’ll take a closer look at one particular tool – the Microsoft Data Migration Assistant (currently at version 4.3).

The assistant is a free tool you can download and install locally. Once installed, you can use the tool to find any compatibility issues that might create problems when you move your local database into an Azure SQL instance. Once you’ve finished an assessment, you can use the tool to migrate both the database schema and the data to the target database.

The Assessment

The first step to take with the assistant is to create an assessment project. For the assessment, you’ll point the assistant at an existing database, and select a target database type. The assistant will let you target SQL Server versions 2012, 2014, 2016, and 2017 (both Windows and Linux), as well as Azure SQL instances and Azure managed instances. Once the assistant connects to your local database, the tool will begin an assessment and discover problems that might impact your migrations.

setup assesment
Starting an assessment project

Currently, the tool supports two types of assessment reports. The first type of report is the feature parity report. Keep in mind, for this report the tool is looking at what features exist in a database, which is a superset of the features your applications might need. For example, if you have SQL Server Service Broker enabled in your local database, the assessment will flag Service Broker as a feature error, because Azure SQL instances do not support Service Broker. The tool would recommend you use Azure Service Bus instead of SQL Service Broker, but you’d need to check with the application development team to know if an application uses Service Broker, or not.

check features
The assessment report

The assistant also performs a number of compatibility checks to make sure your migration won’t be negatively impacted by breaking changes, behavioral changes, or deprecated features. For example, the use of deprecated DBCC commands.

The Migration and Estimation

Once you are comfortable with the assessment results, you can begin a migration project. A migration project can move a database schema, or the database data, or both the schema and the data. You’ll be able to select the objects you want to script for a schema move, and select the tables you want to move for a data move.

script schema
Scripting out the schema move

To move data into Azure you’ll need an Azure SQL database available. A common question for Azure database migrations is to estimate the amount of power you’ll need in the cloud, where Azure SQL instance power is measured in abstract units known as DTUs. Installed with the assistant is a Powershell script you can use to enable performance counters and collect metrics from your local database. The suggestion is to collect data for at least 40 minutes while the local database is experiencing an average workload. The result will be an output file you can feed into the assistant for recommendations. The assistant will be able to recommend standard and premium SKUs (S0, P1, etc), as well as make managed instance recommendations. The recommendation output will exist in a TSV file, which you can also feed into the assistant to provision all the databases you need. For more information about this process, see Identify the right Azure SQL Database/Managed Instance SKU for your on-premises database.

sku recommand
Powershell script is found in the assistant’s installation folder

The Alternatives

One alternative to the Database Migration Assistant is Azure’s Database Migration Service. While the assistant is a free download you run on your local hardware, the migration service is a highly available cloud service. The migration service has a price, but also offers more options for migration, including online, continuous sync migration so you can move your database with no database downtime. The migration service is also continually adding new features and migration scenarios, as well as more database sources and targets (PostgreSQL, MySQL, Oracle, and MongoDB are all supported for specific scenarios). Read more about the Azure Database Migration Service, or read more about the Database Migration Assistant.