Azure Basics – Migrate your SQL DB to Azure SQL

Well, the first thing to migrate a Database to Azure SQL, is actually having a DB to migrate, so, in this example we will use a local Database in a SQL Express as the source (with sample data, credits to https://www.sqlservertutorial.net/load-sample-database/ ) and the DB created in here as target.

In the point that we have an actual DB the migration process starts by installing Azure Data Migration Assistant (https://www.microsoft.com/en-us/download/confirmation.aspx?id=53595 )

But… in this example we will use Data Migration Assistant instead of Azure Database Migration Service, because we can accommodate a little downtime, while with the “Service” migration type we have a very little downtime.

First step, make an Assessment

At this point, the migration starts to get hot, open the Microsoft Data Migration Assistant and hit the “+” button to create a new project.

Microsoft DMA Get started

First thing to do is an Assessment, so we will give it a name to the project and check the assessment radio button, and check the source and target server types, as the image shows.

Microsoft DMA assessment settings

The next step we will check the options for the assessment, compatibility and parity in my case.

Microsoft DMA Assessment source

Now, create a connection to the source server, just putting the correct data in the gapes, like this image.

Microsoft DMA Assessment  local Authentication

When the DMA connect to the server, check the DB to migrate.

Microsoft DMA Assessment Source DB

Now, hit the start assessment button to start the assessment process.

Microsoft DMA Assessment start

When the assessment completes it will show us the possible problems for the migration, and some links to resolve this.

Microsoft DMA Assessment result

Once the problems are solved, we can click the Upload to Azure Migrate button.

Microsoft DMA Assessment upload

The Wizard will ask for the Azure Credentials to connect, so give them to it, and when correctly connected to our Azure tenant it will ask for the Subscription and the Azure migrate projecto to upload to Azure.

Microsoft DMA Assessment upload

It will create a notification when the Assessment is uploaded.

Microsoft DMA Assessment upload completed

Now, in the Azure Portal Search bar we will search for Azure Migrate and then, in the left column search for Databases (only) and it will show the migrationtest project that we just created, as in the image.

Microsoft DMA Assessment upload results

If we click in the Assessed databases (for example), this will show the status of the DB, size, readiness for migration, etc.

Microsoft DMA Assessment migrating results

Once the assessment is complete, and the (possible) problems solved, we can create the migration project, the same as we did before, but checking the Migration radio button in the project.

Let’s go to Data Migration

Now open again the Microsoft Data Migration Assistant, and go for the Migration radio button.

Microsoft DMA Migration

First thing to do is connect to the source SQL Server.

Microsoft DMA Migration source

Then, select the DB to migrate, you can see that this DB can be Assessed before the migration, in our case, we assessed this before so I will uncheck this.

Microsoft DMA Migration pick source db

In the target section, we will hit the Create a new Azure SQL Database link, like the screenshot, and this will take us to the Microsoft page explaining step by step how to create an Azure SQL Database.

Microsoft DMA Migration target server

Now we enter the Servername\instance of the Azure SQL we created.

Microsoft DMA Migration target data

Select the DB we created earlier to do the migration.

Microsoft DMA Migration target db

In the next step we will select the objects to migrate and Generate the SQL script.

Microsoft DMA Migration assets

The DMA will generate the scripts for the deployment, like the following image, in this case we will save the scripts, just for backup, and hit the Deploy schema button at the lower right.

Microsoft DMA Migration scripts

Now we’ll see the progress of the deployment.

Microsoft DMA Migration execution

Once this is done, we can hit the Migrate Data to start the migration.

Microsoft DMA Migration preparation

When the DMA is done collecting the tables, we have to check the tables we want to migrate data from and click on Start Data Migration so the process can start.

Microsoft DMA Migration collecting

It will take some time, depending on the data, but it will finish (hopefully) like this, with no errors.

Microsoft DMA Migration correct

Now we can check if the DB data migrated correctly by connecting to the DB with Azure Data Studio, with the right connection parameters.

Microsoft DMA Migration check data

When the connection is made, we can navigate and run queries so we can see if the data migrated correctly, as we can see in the image following.

Microsoft DMA Migration data migration is ok

Once we see the data of the Database correctly, we will be assured that the migration has completely and succesfully terminated, now the only thing remaining is connect the the app to the new DB with the connection strings and welcome to the cloud computing databases!