Migrating SQL Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool

By | November 25, 2018

Migrating SQL Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool

Contents

  • Introduction
  • Microsoft Database Migration Assistant (DMA) tool
  • Migrate SQL Server database To Azure SQL Instance using DMA tool
    • Detecting the compatibility issues using Assessment Type project
    • Migrating the database using the Migration project type
    • Verifying the migrated database on Azure SQL Instance
  • Summary

Introduction

In my previous articles on migrating On premise SQL Database to Azure SQL Database, I have already discussed two migration methods using SSMS Deploy & SSMS Export/Import Methods and their step by step process. Now, we will discuss the 3rd method of migrating the On Premise SQL Database to Azure SQL Database. In this article we learn migrating SQL Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool.

Microsoft Database Migration Assistant (DMA) tool

Microsoft Database Migration Assistant (DMA) tool helps in detecting compatibility issues & features which are not supported or partially supported by the Target server (Azure SQL Instance). This issues can impact database functionality on the target server. DMA has 2 project types. First is Assessment Project type. This section helps in getting the report on compatibility issues and the SQL Server features which are not supported or partially supported on the target server ( Azure ). The second project type is Migration which migrates the database from On premise SQL database to Azure SQL database. It not only allows migrating the data & Schema but also uncontained SQL objects from Source  Server (On premise SQL Server) to the target server (Azure SQL database).

Download Microsoft Database Migration Assistant (DMA) tool

You can download the  from the below mentioned Microsoft link

https://www.microsoft.com/en-us/download/details.aspx?id=53595

You can learn more about DMA tool from here 

let start discussing the step by step process of database migration to Azure SQL Instance using (DMA) tool

Migrate SQL Server database To Azure SQL Instance using DMA tool

After downloading and installing the DMA tool, when you open it, you will get the following screen.

Figure-1 Welcome to Data Migration Assistant

Figure-1 Welcome to Data Migration Assistant

In this screen click the “+”to start the new project. It will bring the below screen. By default Assessment Project type is selected.

Figure-2 Choose the project type

Figure-2 Choose the project type

Detecting the compatibility issues using Assessment Type project

Figure-3 Define Project Type, Project Name, Source Server & Target Server

Figure-3 Define Project Type, Project Name, Source Server &  Target Server

In the above screen, for detecting the compatibility issues, choose the Radio button Assessment (If not already selected). After choosing the project type, define the project name, source server name and the target server name. After that click the Create button. It will open the below screen:

Figure-4 Select the report options.

Figure-4 Select the report options.

In the above screen we need to select the report type options. Since I want to check the compatibility issues as well as features which are either not supported or partially supported, I will go with the first option Check database compatibility as well as second option Check feature parity.

Once you select the report options click on the Next button. It will bring the below screen

Figure-5 Connect to the Source SQL Server

Figure-5 Connect to the Source SQL Server

In the above screen, enter the server name & the required login information for connecting it. Then click on the Connect button to connect to the source SQL Server. It will take us to the below screen.

Figure-6 Select the SQL database for migration

Figure-6 Select the SQL database for migration

This screen contains all the database from the source SQL Server. Here we need to select the database which we want to migrate. For this article, we choose the database AdventureWorks2012. Once we choose the database, click on the Add button which will bring up the following screen.

Figure-7  Start Assessment

Figure-7  Start Assessment

In the above screen, you can see the selected SQL database, its compatibility level & its size.  Now click on the Start Assessment button. It will start the assessment process as show in the below mentioned screenshot.

Figure-8 Assessment in process...

Figure-8 Assessment in process…

After completing the assessment process, it will bring the below screen. By default SQL Server feature parity radio option is selected. This screen show the features which are either not supported by the Azure SQL database or features which are partially supported by Azure SQL Database.

Figure-9 Review result  for Unsupported & Partially Supported issues

Figure-9 Review result  for Unsupported & Partially Supported issues

Now, if you chose the radio button option Compatibility issues, it will the below screen. This screen will show all the compatibility issues with their details which may impact the migration process.

Figure-10 Review result for Compatibility Issues

Figure-10 Review result for Compatibility Issues

Now click on the Export Report button and it will asked you to save the report. For example, in this case the report will be look like this

Figure-11 Assessment Report

Figure-11 Assessment Report

Now once you click on the icon below “+”, you will get the below screen where you can see all the assessments.

Figure-12 All Assessments list

Figure-12 All Assessments list

With this, the assessment part is complete. In the next section we will going to discuss how we can do the actual migration using the Microsoft Database Migration Assistant tool ‘s Migration project type..

Migrating the database using the Migration project type

Click on the “+” sign to start new project.

Figure-13 Select Migration Project Type

Figure-13 Select Migration Project Type

Now select the Migration Project type and defined the project name. Also select the Source Server Type, Target Server type and the scope of the migration. In this article, I have chosen the Schema & data as migration scope.

After that click the Create button. It will bring up the “Connect to source server” screen.

Figure-14 Connect to the Source Server

Figure-14 Select Source Server

In this screen, fill the required connection information of the source server as shown in the above screen and click on the Connect button. It will take us to the database selection screen as shown below

Figure-15 Select the source database

Figure-15 Select the source database

For this article, I have chosen the database AdventureWorks2012. Click Next to continue.. It will bring us to the target server selection screen.

Figure-16 Choose the Target Azure SQL Server

Figure-16 Choose the Target Azure SQL Server

In this screen choose the Target Azure SQL Instance and fill the required credential information for connecting to the Azure SQL Instance. After filling the required credential information, click the Connect button. In the next screen, we need to select the database from the list of databases in the target Azure SQL Instance. Since there is no database available in the Azure SQL Instance. So we need to create it.

We can create new Azure SQL Database through multiple ways like through Azure Portal, using SQL Server Management Studio or by clicking the link Create a new Azure SQL Database from the above screen. In this article I will create the new Azure SQL database using the SQL Server Management Studio.

For creating a new azure SQL database, first connect to the Azure SQL instance using SQL Server Management studio. If you don’t know how to connect with Azure SQL Instance using SSMS, this article can help you. Once connected, right-click on the Databases node and select the option New database. It will bring the below mentioned screen

 

Figure-17 Create the new database

Figure-17 Create the new database

 

In the Database name field, define the new database name. In this article, I have define the name as AdventureWorks2012. Now click on the options tab. It will bring the below mentioned screen

Figure-18 Select setting for new Azure SQL database

Figure-18 Select setting for new Azure SQL database

If you notice in the above screen, S2 Service tier is selected by default. We can change the service tier according to our database performance & size requirements. If you want to read more about Azure service tiers, you can refer to the article ” Azure Pricing Models and Service Tiers “. In this article I have change the Service tier to S0 as shown in the below screenshot.

Figure-19 Change the Service tier for the new database

Figure-19 Change the Service tier for the new database

Click OK. This will create the new blank SQL database in the Azure SQL Instance. Now if we again return to the Screen where we have made the connection with the Azure SQL Instance (Figure-16) and click Connect, it will show the newly created database AdventureWorks2012. Since it is the only database in the Azure SQL Instance, it is by default selected.

Figure-20 Select the target database on Azure SQL Instance

Figure-20 Select the target database on Azure SQL Instance

Click on the Next button. It will bring the below screen. In this screen we need to select the schema objects which we want to migrate from source database.

Figure-21 Select the Schema objects for migration

Figure-21 Select the Schema objects for migration

Once the schema objects got selected, click on the Generate SQL Script. It will bring the screen Script & deploy Schema. In this screen you can see the SQL script generated for the Schema Objects.

Figure-22 Generating SQL scripts for selected schema objects

Figure-22 Generating SQL scripts for selected schema objects

Once the SQL Script for Schema objects generated, click on the Deploy Schema button. It will start the process of deploying the selected Schema objects on the target Azure SQL Instance.

Figure-23 Deploying selected schema objects on target Azure SQL database

Figure-23 Deploying selected schema objects on target Azure SQL database

Once this process got completed, click on the Migrate data button. It will bring the Select tables screen. In this screen, select the tables which we wish to migrate to the Azure SQL Instance.

Figure-24 Select the tables for migration

Figure-24 Select the tables for migration

After selecting the tables, click on the Start data migration button. It will take to the Migrate data screen. Here we can see the tables data migration process as shown in the below screenshot.

Figure-25 Selected tables data migration in progress

Figure-25 Selected tables data migration in progress

Once this data migration process got completed successfully, you can see the final  summary & total time taken by it to complete the data migration process as shown in the below screenshot.

Figure-26 Completion of table data migration process

Figure-26 Completion of table data migration process

Verifying the migrated database on Azure SQL Instance

To check the data migration happened successfully or not, you can connect to the Azure SQL Instance using SQL Server management studio (SSMS) and expand the node Databases. It will show the database AdventureWorks2012. Now expands it. There you can see all the tables & other schema objects which shows the success of the migration process.

Figure-27 Tables in the migrated database in Azure SQL Instance

Figure-27 Tables in the migrated database in Azure SQL Instance

If you want to check the migrated data within the tables, you can execute the Select command to see data from any of the table as shown in the below screenshot. It will verify the success of the table data migration.

Figure-28 Verifying table data by executing Select command

Figure-28 Verifying table data by executing Select command

 

Summary

In continuation on the series of articles on migrating On premise SQL Database to Azure SQL Database, this article discussed about migrating SQL Server Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool. The link of the other 2 methods has been mentioned under the Read More section. Microsoft provides this migration tool and have two project types. Assessment & Migration.

Using Assessment project type we can know the compatibility issues and the features which are either not supported or partially supported by the Azure SQL Instance before starting the actual migration process. Since these issues can  have an impact during the migration process, we have the opportunity to resolve these issues and then start the actual migration process.

Migration project type is use for the actual migration process. Here we can choose the schema objects & tables, which we need to migrate and monitor the migration process.

I hope this article will help you in migrating the SQL Server database to the Azure SQL Instance. Please don’t forget to gives your valuable comments for the article or you can send me the direct queries to my Email id askvivekjohari@gmail.com. If you want you can also join our Facebook community here or follow me on Twitter

Read More

How to migrate SQL Database to Azure SQL Database using SSMS Export/Import

Migrating SQL Database to Azure SQL Database using SSMS Deploy

Understanding Azure SQL Database- Introduction

Azure Pricing Models and Service Tiers

Azure – Creating an Azure SQL Database

How to connect with Azure SQL Database

Read more from Tutorials

SQL Tutorial

Azure Tutorial

 

2 thoughts on “Migrating SQL Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool

  1. Rohit Sardana

    Thanks for writing the complete migration steps with Snapshots, this is handy.

    Please give details of other migration topics from SQL to AZURE

    Reply
  2. vivekjohari Post author

    Hi Rohit,
    Thanks for your feedback.
    Except DMA tool which I discussed in this article, there are multiple ways to migrate the database from SQL to Azure like

    1) By exporting the SQL database backup as a .bacpac file and then import it on Azure using SSMS export/import. You can read it step by step from my below article

    How to migrate SQL Database to Azure SQL Database using SSMS Export/Import

    2) Using the SSMS deploy wizard. You can read it step by step from my below article

    Migrating SQL Database to Azure SQL Database using SSMS Deploy

    3) Using the Transaction Replication method.

    Reply

Leave a Reply