Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using the Data Migration Assistant (DMA) tool to migrate from SQL Server to Azure SQL database


By:   |   Updated: 2018-06-01   |   Comments   |   Related: More > Azure

Storage and High Availability Options for SQL Server in the Cloud

Free MSSQLTips Webinar: Storage and High Availability Options for SQL Server in the Cloud

This webinar will cover best practices for optimizing cloud storage and cost, how to leverage the cloud for disaster recovery, availability options and requirements for SQL Server and key factors to consider in your selection.


Problem

We have an on-premises SQL Server database and we have used the Data Migration Assistant (DMA) to check for any compatibility issues before migration to Azure SQL database. Is it possible to use this tool to perform the actual migration of the database to the new target?

Solution

In part 1, of the tip series, you learned about using the tool to check for any sort of compatibility issues before the migration. You can refer to part 1, to follow the steps to download and install the tool. In this tip, we will perform a demo of the actual migration.

Migrate from SQL Server to Azure SQL database

First, I created a blank Azure database and the plan is to migrate data from my on-premises database. You can refer this tip for creating an Azure SQL database. Once the Azure database is available, get the actual Azure server name. Start the Data Migration Assistant tool and begin the migration. You can refer to the screenshot below.

Migrat to Azure SQLDB

In the migration scope, we have additional options to choose – schema only, data only. In this demo, we will go for migrating both schema and data. Once you selected the options, click on “Create”. In the next window, connect to the source server as shown below.

Connect to Source Server

Connec to Source server

Once you click on “Connect”, you will be able to see the list of databases on the source server.

Databases in Source server

In this demo, we will migrate the sample database “a” to the Azure SQL database. Select that database and click on “Next”. Once done, in the next window, you need to enter the details of the target server. You would have collected the Azure server details while creating the Azure SQL database. Enter the details as shown in this screenshot.

Connect to Target Azure server which hosts the Azure SQL database

Azure Server Target Details

Once you click on “Connect”, you will be able to see the list of Azure SQL databases on that Azure server. In this demo, you can see the sample Azure database that I created for migrating.

Sample Azure DB connected

Select the Azure database and click on “Next”. Once done, you will see this screen which displays the progress. The time taken to progress depends on the size of your source database.

Source schema assessment progress

Once this process completes, you will able to view the schema objects in your source database.

Select Source schema object

Select the schema objects that you would like to migrate and click on “Generate SQL script”. Once done, the process starts scripting the schema objects. In this demo, as only one schema was selected, the process completed in seconds.

Progress of schema scripting

Once this process completes, the scripts get generated for the schema objects. We have the option to review the scripts and modify if required.

Deploy schema on to the Azure SQL database

Generate script for schema

You can now deploy the schema by clicking on the “Deploy Schema” option. This process takes time depending on the number of objects you choose to deploy. In my case, as it was a simple schema, it completed in seconds. Once the process completes, you can view the status of the deployment in the right pane of the window as shown below.

Deployment of Schema

With this, the schema would have deployment successfully on the Azure SQL database too.

Check schema deployment on Azure SQL database

You can then connect to the Azure SQL database to check if the new schema is available. Connect to the Azure SQL database using SQL Server Management Studio (SSMS) and check to see if the schema is deployed.

Schema Deployed Successfully

Here, you can see that the schema was deployed successfully. We had just used the Data Migration Assistant (DMA) tool to perform the deployment. You will notice that the data is not yet migrated.

no data yet

In the next step, we will perform the migration of the actual data.

Migrate data to Azure SQL Database

After the schema validation on the Azure SQL database, click on the option– “Migrate data” as shown below.

Click on Migrate data

This will take you to this screen where you can view the table details and have the option to select the tables for the data migration.

Azure SQL DB Start data migration

As only one table with 2 rows was selected for data migration, the process completed in seconds. In your case, for bigger tables with millions of rows, you may need to plan accordingly and size the Azure DB appropriately on the correct performance tier. Once the data migration process completes, you will see this window.

Data Migration to Azure DB successful

Here you see that the data migration was successful with no issues.

Check for migrated data on Azure SQL database

After schema deployment, you can see that the schema was deployed. Now, let us confirm that the data is migrated after the data migration. Connect to the Azure SQL database and check the table where the data was migrated to.

Confirm data Migrated to Azure SQL DB

Here you can see the data is successfully migrated. All of the migration steps were performed by just using the Data Migration Assistant.

As described earlier, you could use the Data Migration Assistant tool to only migrate the schema or data. In our demo, we used it for migrating both schema and data.

Next Steps
  • With this tip, you were able to use the Data Migration Assistant (DMA) tool to migrate the schema and data to Azure SQL DB
  • Try this tip using your own sample on-premises database
  • Refer this tip to migrate only the schema to an Azure SQL database
  • Refer this tip to migrate only the data to an Azure SQL database
  • In the next tip, we will see how to migrate a database from SQL server to a SQL Server on Azure VM
  • To get familiar with Azure, refer the numerous tips available at this link


Last Updated: 2018-06-01


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.



    



Learn more about SQL Server tools