Data Migration Assistant Assessment Demo
Start course

Once you've decided to take advantage of cloud technology, resources, and scale, and have set up an Azure infrastructure, there is the small matter of transferring your existing data to the cloud. A migration is successful when the target production system is up and running correctly with the minimum impact on the business' operation. Achieving this requires knowing what data issues you will have in the new environment and minimizing system downtime while the data is transferred. This course looks at strategies and tools to achieve an optimal migration.

Learning Objectives

  • Understand the underlying concepts of migrating relational databases to Azure
  • Explore the factors in determining the optimal migration scenario as well as those affecting the physical movement of data from on-premises to Azure
  • Learn the different tools and services to aid in discovery, assessment, and migration
  • Learn how to migrate an on-premises SQL Server DB to Azure SQL Managed Instance using the Data Migration Service
  • Use Data Migration Assistant to migrate on-Premises SQL Server databases to Azure SQL DB
  • Migrate MySQL to Azure MySQL using MySQL Workbench
  • Migrate a MySQL database to Azure SQL Server using SQL Server Migration Assistant

Intended Audience

This course is ideal for anyone looking to migrate their existing on-premise database environment to an Azure SQL database solution.


To get the most out of this course, you should be familiar with general database and cloud concepts.



I've downloaded and installed the data migration assistant application, and I'm going to create a new assessment project by clicking on the plus button. The project type is assessment, and I'll give it the name of database assessment. I'll leave the assessment type as database engine. The other option is integration services. The source type is SQL Server, and I'm targeting Azure SQL database as the migration destination. Clicking the create button will start the assessment wizard. This first page gives me the option of selecting the compatibility features I want to check. We'll stick with the default of checking everything, and I'll click next. The source server is on the same computer I've installed the data migration assistant, but it can be anywhere within your on-premise network. I'll use SQL Server authentication to log in with the SA user. Once connected, I'll select three databases of varying ages and compatibility levels. Well, two 2008 R2 old ones and one SQL Server 2019 new database. Next, I'll start the assessment.

Once the assessment has finished, we are first presented with the SQL Server feature parity report, and it will come as no surprise that Azure SQL does not support many of the features available in SQL Server 2019. I'm more interested in the compatibility issues when moving my databases from on-premise to Azure. Looking at AGDDW, a 2008 R2 database, we are alerted to the use of old and deprecated data types. More importantly, the assessment has highlighted cross-database references that are not supported in Azure SQL in this form. For this database to be migrated, I would need to add additional tables or use Azure elastic query to interrogate external data sources. LE_CRM also suffers from deprecated data types and cross-database joins as well as unqualified joins and outmoded ways of dealing with XML.

With the assessment complete, I can now upload the report to Azure. Despite this generic authentication error, I can select my subscription and the migration project I've set up in the portal. Clicking upload has successfully uploaded the assessment. Switching over to the portal, I can see the database assessment panel is now populated with assessed instances, databases, and databases ready to migrate. I'm not sure why all the instance and database numbers are zero. Maybe it is related to that login error when I was authenticating from the data migration assistant, but I can click on the zero and drill down into the assessment report. Having a look at the databases, we can see that SalesData and AGDDW  are both ready for Azure SQL, with the proviso that I deal with the cross-database queries. LE_CRM is not ready for Azure SQL, but naturally enough, all databases can be migrated to SQL Server running on a virtual machine.

Drilling down into databases ready for Azure SQL DB, we see the two migration-ready databases, and all databases, as we saw, are ready for Azure SQL VM.

About the Author
Learning Paths

Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a  Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.