By: Mohammed Moinudheen | Updated: 2018-05-04 | Comments (2) | Related: > Azure
Problem
We have an on-premises SQL Server database and plan to migrate it to Azure SQL Database or to SQL Server on an Azure Virtual machine. Is there a tool we can use to check for any sort of compatibility issues before migrating?
Solution
Earlier, there was a tool called Upgrade Advisor that we could use. Now, Microsoft has released the Data Migration Assistant (DMA) for this purpose. You can download the latest version of the Data Migration Assistant (DMA) from this link.
Download and install the Microsoft Data Migration Assistant
Just download the media from this link and install on your on-premises server where you are planning to migrate the database. You can also install on a standard central server and run the assessment projects using the Data Migration Assistant tool.
Before you first test your on-premises database for compatibility issues, you may want to download a version of the SQL Server sample database (Wide World Importers) that is available from this link and run a migration assessment project on that database. Download the WideWorldImporters backup file from this link and restore on your test on-premises server. The WideWorldImporters is a sample OLTP database and has incorporated a number of new SQL features and is a good fit for testing for any sort of compatibility issues if you are planning on migrating a database to Azure.
I installed the sample WideWorldImporters database on my test on-premises server as shown below.
Start the Assessment Project on Data Migration Assistant (DMA)
After the Data Migration Assistant tool is installed, start the tool by clicking the icon on the desktop. Once you start it, you will see a screenshot like below. Click on the + sign as shown to start a new project.
Once you click on the + sign, you will be able to start a new project for the assessment. The next screen is below, where you can name your project as shown.
Assessment of Migration from on-premises SQL database to Azure SQL database
In this project, the plan is to assess for a migration from SQL Server to an Azure SQL database target. Select the correct “Source server type” and “Target server type” and click on “Create”.
Once you click on “Create”, you will get this screen where you can select the “Report Type”. In this window, I have selected both “Check database compatibility” and “Check feature parity” options. Choose the options and click “Next”.
Connect to Source Server in Data Migration Assistant
Once you click on next, you will get this window where you input the source server name. I have chosen the default windows authentication for connection. You have the option to choose either SQL authentication, Active Directory Integrated Authentication or Active Directory Password Authentication. Once done, click on “Connect”.
Once you click on Connect, you will be able to see the list of databases on the source server as shown in this screenshot. Select the database for your project and click on “Add”.
Once you click on “Add”, you will see this window where you can start the assessment.
Assessment Results from Data Migration Assistant
Once you click on the “Start Assessment” option, the process starts and completes in a few minutes. You will see the results of the assessment as shown. The results in the first option are the details in the “SQL Server feature parity”. The screenshot of the results is as shown.
Here, you can view the detailed recommendations of the “Unsupported features” and “Partially-supported Features”. The recommendation section provides information on the options available on Azure SQL Database. As we have done this sample assessment on the WideWorldImporters sample database, we are seeing these recommendations. However, in your case, on your own on-premises database you will see these recommendations only if your database is using these features.
Next, click on the “Compatibility issues” radio button, to view the feature compatibility issues. In this report, you can see the list of features that may block the migration to Azure SQL database. Refer to the screenshot of the report.
You can see the list of database options that are not supported in the Azure SQL database. The Data Migration Assistant recommends to disable these options from the database before migration as it is a potential migration blocker. In order to extract the assessment as a report, you can click on the “Export Report” option. The export option allows you to save the report as a JSON or CSV file.
New Assessment for migration of on-premises SQL database to SQL Server on Azure VM
Similar to the above assessment, you may start a new assessment project to check for any issues for migrating the SQL database to a SQL Server on an Azure Virtual machine. Just click on the + symbol as shown.
Once you click on the + symbol, start the new assessment as shown. Select the target server type option as “SQL Server on Azure Virtual Machine”, provide a new project name and click on “Create”.
Once you click on “Create”, you will see the option to select the “Target Version” of SQL Server as shown. You can also select the report type, if you are looking for “compatibility issues” or “New features recommendation”.
The rest of the steps are similar to what was performed for running the assessment for migration of on premise SQL Server to Azure SQL database. You can try out the assessment on different SQL Server target versions to look for any discrepancies. You can also view and re-open all the assessments you have completed by clicking on the <- symbol as shown.
Once you click on the <- symbol, you will see the status of all the assessment projects done using the Data Migration Assistant.
In the next tip, we will see how to use the Data Migration Assistant tool to do the actual migration to the target location.
Next Steps
- With this tip, you were able to use the Data Migration Assistant (DMA) tool to check for compatibility issues before the migration
- Try this tip using your own sample on-premises database
- Try this tip by downloading the sample Wide World Importers database from this link
- To get familiar with Azure, refer the numerous tips available at this link
About the author
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-05-04
