Data Migration Assistant (DMA) to Check for SQL Server Compatibility Issues when Migrating to Azure


By:   |   Updated: 2018-05-04   |   Comments (2)   |   Related: More > 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.

ssms database list

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.

Data Migration Assistant

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

Data Migration Assistant new project

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

Data Migration Assistant options

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

Data Migration Assistant connect to server

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

Data Migration Assistant add sources

Once you click on “Add”, you will see this window where you can start the assessment.

Data Migration Assistant databases

 

 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.

Data Migration Assistant assessment report

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.

Data Migration Assistant assessment 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.

Data Migration Assistant new assessment

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

Data Migration Assistant new assessment

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

Data Migration Assistant select sql version

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.

Data Migration Assistant compatibility issues

Once you click on the <- symbol, you will see the status of all the assessment projects done using the Data Migration Assistant.

Data Migration Assistant assessment list

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


Last Updated: 2018-05-04


get scripts

next tip button



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Saturday, August 24, 2019 - 5:11:14 PM - M Moinudheen Back To Top

Hi Venu,

This tip covers only the assessment part. You can refer the other tip: https://www.mssqltips.com/sqlservertip/5443/use-data-migration-assistant-dma-to-migrate-onpremises-sql-server-database-to-azure/ which has the detailed steps for migration. There is a section "Steps to Create Azure File Share (if needed)" which has the steps for creating the file share which can be accessible from both locations.

Thanks,
Mohammed


Saturday, August 24, 2019 - 9:43:29 AM - Venu Gopal Back To Top

Hi Moinudheen,

Thanks for your post on Azure Data Migration Assisant. Could you please help me in below query. 

I am migrating Database Schema from One VM to Other VM. Regardig the same i choose as below

Source Server Type - SQL Server

Target Server Type - SQL Server on Azure Virtual Machine

Post this step, i can see a place where it is asking for "Shared location accesible by source and target servers for backup operation". At this point, i tried many ways to provide shared location, Network map drive but nothing worked out. I tried even Blob an Azure File share path as well, still no luck. Could you please help me in overcoming this issues.

Thanks,

Venu Gopal K



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools