Azure SQL Database Migration Assistant

Welcome in this video in which you will learn about migrating the SQL database to Azure SQL using the free Microsoft Database Migration Assistant. In this section, I have only a single topic to talk about, being the SQL Data Migration Assistant, where I’m going to start with describing what the tool is mainly built for, and then ending the section with another nice demo.

Now, let me start with sharing a few words on what is Data Migration Assistant tool actually is. Now, besides being a database migration tool, as the name says, it is actually a really powerful assessment tool. And to me, it’s even more important than actually migrating your database because we got a couple of other different ways to do that.

Now, you don’t want to migrate your database to Azure and finding out after your migration that the database format is not supported, or some specific database features would not be available in Azure SQL. So the key functionality is running your assessment. And then, obviously, once your assessment is done, you can also use it as a through migration tool, where it’s not just copying over the data, but it also allows you to run it during a life source environment.

So if your environment is having a couple of different SQL business critical databases, you could maybe use this tool to migrate, since you’re not really touching anything in your source environment. Now, running a migration is rather straightforward using this tool. Now, once the assessment is done again, you’ll run your initial migration, starting with the database schema replication, after which it will copy over all the data entries from your database.

Now, during the migration, again, your source environment is accessible for your applications. Now, if you prefer an online migration, you can use this tool to migrate the full schema and initial data, followed by one or multiple delta migrations. And this would really help you in speeding up the final migration and eventually swapping to your Azure SQL instance.

Now, instead of talking about it, why not just doing an Azure demo showing you how to use this Data Migration Assistant for running an assessment and eventually for running an actual data migration? So I’m connecting to MySQL Server, but you could also run this from a remote machine on which I installed the free Microsoft Data Migration Assistant.

From the welcome screen, I can run a new project where I need to decide between assessment and migration. So for now let’s start with the assessment part. You can define the source server and target server, where my source is SQL Server, and the target is an Azure SQL database.

It’s going to run a couple of different checks, validating the database compatibility and checking on feature parity. If anything that I’m using in my on-prem database or my source database doesn’t need to be on-prem, it could also run in an Azure virtual machine– is still supported. Obviously, it’s going to ask me my log on credentials since I’m using Windows pretty straightforward.

And it detects my source database. If I have multiple databases, I can run this assessment for multiple databases at once. So my source environment has been selected. And we’re going to run our assessment. Now, depending on the size of your database, this obviously might take quite some time.

Since we’re using an AdventureWorks demo sample database, it’s only taking a couple of seconds. But even from here, it’s already detecting a couple of unsupported features, like cross database references are not supported. And I don’t have SQL Server reporting services available once I move to an Azure SQL environment.

Now, by the way, if you’re using SQL Reporting Services, then the workaround would be deploying a virtual machine, installing SQL Reporting Services there, or integrating and moving to a cloud version of reporting services out of Microsoft Power BI. And then a second one, the partially supported feature, is the full text search.

So again, it depends a bit on, obviously, your source environment, if there’s anything coming up or not. But most important is that everything looks fine, with a couple of exceptions that are not supported. But for my demo environment, nothing really to worry about.

So I’m going to move over to the second step, where I’m going to run an actual migration. The source is SQL Server, and the target is Azure. And I’m going to migrate schema and data together. So the source environment remains the same, SQL VM, where it’s recognizing my database.

Now, for my Azure SQL database, as you can see up here, there’s a link redirecting you to the Azure portal allowing you to first create a new SQL database. Now, since I already created a few of them in previous demos, why not reusing that one?

So the one I’m going to use is the AZ SQL portal. The database is dot Windows dot net, if I remember that name correctly. Now, as you can see, it’s going to read out the full schema of my database. If I don’t need to migrate everything, then obviously, you can also disable certain parts of that.

And then up here, let me go back. It’s going to highlight those databases where these ones are the ones not having that search index available anymore after the migration. So again, a pretty straightforward, intuitive tool allowing me to run that assessment. So I’m going to push that Generate SQL Script. And from here, some of you SQL database admins might recognize what the tool is actually doing because in the end, what it does is creating a full collection of SQL query statements allowing to extract the schema, connecting to the target database, and creating the new schema there.

So you don’t really have to use this tool to migrate your data. But in the end, it could actually help you in optimizing and speeding up your migration. Even if you need to run the same for multiple databases, you could save the script, copying it, finding, replacing whatever information you need, pointing to a new database, and again, allowing you to not really use or needing to use this tool.

So I’m going to push my Deploy Schema. And it’s going to run for– so after a few minutes, we have the full schema replicated. And this allows me to move over to my actual data migration. So it’s going to read out my source tables.

And again, like I mentioned before, if you don’t want to use all your data in your Azure SQL replicator database, then why not de-flagging and just selecting the ones we want to copy? And just selecting only a few of my tables for now and then running my actual data migration where, again, this will take a few minutes. And this completes my migration using SQL Data Migration Assistant.

And this basically brings me to the end of this section here, where you learned about the Microsoft Data Migration Assistant tool. And I showed you in a demo how it works for running an assessment and eventually running a schema migration and a data migration. Hope to see you again in one of the other Azure SQL videos.