The Data Migration Assistant (DMA) provides assessment support for migrating on-premises SQL Server databases to Azure SQL/Azure Virtual macchine or Database Managed Instance by detecting compatibility issues that can impact database functionality on your new version of SQL Server.It collects the metadata about their database schema, detect the blockers, and partially supported or unsupported features that affect migration to recommends performance and reliability improvements for your target environment. It allows you to not only move your schema and data, but also uncontained objects from your source server to your target server.

DMA Features

There are a few things that you learn from the DMA. This will will assess on-premises SQL Server instance(s) migrating to Azure SQL database(s). The assessment workflow helps you to detect issues that can affect your migration and provides detailed guidance on how to resolve them. DMA discovers the compatibility issues that block migrating on-prem SQL Server database(s) to Azure SQL Database(s). It then provides recommendations to help customers remediate those issues.

DMA also detects partially or unsupported features that are currently in use at the source SQL Server. It then provides comprehensive set of recommendations, alternative approaches available in Azure and mitigating steps so that customers can plan ahead this effort into their migration projects.

Here are the supported source and target versions:

• Source: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, and SQL Server 2017
• Target: SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, and Azure SQL Database

You can download the latest version of the Data Migration Assistant (DMA) from this link (https://www.microsoft.com/en-us/download/details.aspx?id=53595) and run the DataMigrationAssistant.msi package. The following step-by-step instructions help you perform your first assessment for migrating to on-premises SQL Server, SQL Server running on an Azure VM, or Azure SQL Database, by using Data Migration Assistant.

Creating an Assessment Project

Select the New (+) icon, and then select the Assessment project type. Set the source and target server type.

If you're migrating your on-premises SQL Server instance to Azure SQL Database or to SQL Server hosted on an Azure VM, set the source and target server type to Azure SQL Database or SQL Server on Azure VM. Click Create (as shown in Fig. 1).

Fig. 1 - Choosing assessment options:

Select the target SQL Server version that you plan to migrate to. Then select the report type. When assessing your source SQL Server instance for migrating to Azure SQL Database, you can choose one or both of the following assessment report types: Check database compatibility and Check feature parity (as shown in Fig. 2)

Fig. 2 - Add databases to assess

Select Add Sources to open the connection flyout menu. Enter the SQL server instance name, choose the Authentication type, set the correct connection properties, and then select Connect (as shown in Fig. 3).

Fig. 3 - Entering Credetials

Select the databases to assess, and then select Add (as shown in Fig. 4).

Fig. 4 - Selecting Databases

Note: You can remove multiple databases by selecting them while holding the Shift or Ctrl key, and then clicking Remove Sources. You can also add databases from multiple SQL Server instances by using the Add Sources button. Click Next to start the assessment (as shown in Fig. 5).

Fig. 5. - Running the Assessment

The duration of the assessment depends on the number of databases added and the schema size of each database. Results are displayed for each database as soon as they're available.

Select the database that has completed the assessment, and then switch between Compatibility issues and Feature recommendations by using the switcher. Review the compatibility issues across all compatibility levels supported by the target SQL Server version that you selected on the Options page. You can review compatibility issues by analyzing the affected object, its details, and potentially a fix for every issue identified under Breaking changes, Behavior changes, and Deprecated features.

For Azure SQL Database, the assessments provide migration blocking issues and feature parity issues. Review the results for both categories by selecting the specific options. The SQL Server feature parity category provides a comprehensive set of recommendations, alternative approaches available in Azure, and mitigating steps. It helps you plan this effort in your migration projects (as shown in Fig. 6).

Fig. 6 - Assessment Results

The Compatibility issues category provides partially supported or unsupported features that block migrating on-premises SQL Server databases to Azure SQL databases. It then provides recommendations to help you address those issues (as shown in Fig. 7).

Fig. 7 - Compatibility Issues

After all databases finish the assessment, select Export report to export the results to either a JSON file or a CSV file(as shown in Fig. 8). You can then analyze the data at your own convenience.

Fig. 8 - Save the Results

You can run multiple assessments concurrently and view the state of the assessments by opening the All Assessments page.

Summary

The above article will help you to use the Data Migration Assistant (DMA) tool for performing an assessment of your on premises SQL Server by detecting compatibility issues before a migration to Azure SQL Database or SQL Server on Azure VM. This tool recommends performance and reliability improvements for your target environment and allows you to move your schema, data, and uncontained objects from your source server to your target server.