Saturday, December 26, 2020

What is data migration assistant?

The Data Migration Assistant is migration tool developed by Microsoft. It is used for moving data from one storage system to another. The Data Migration Assistant (DMA) helps you upgrade to a modern data platform by detecting compatibility issues that can impact database functionality in your new version of SQL Server or Azure SQL Database. DMA recommends performance and reliability improvements for your target environment and allows you to move your schema, data, and uncontained objects from your source server to your target server.

You can download the latest version of Data Migration Assistant from this link: Download

Once you download it from above link please install it in your local machine.

Data Migration Assistant Capabilities

Data Migration Assistant provide two type capabilities

1.       Assessment: On-premises SQL database assessment

2.       Migration: Migrate On-premises SQL database into Azure SQL database

Assessment

If you're upgrading your on-premises SQL Server instance to a modern on-premises SQL Server instance or to SQL Server hosted on an Azure VM, set the source and target server type to SQL Server. If you're migrating to Azure SQL Database set the target server type to Azure SQL Database.



Need follow these steps to do Assessment in your MS SQL with help of Data Migration Assistant.

1.       Open Data Migration Assistant tool in your local machine

2.       Once tool is opened I will create new project name FunctionApp for Assessment for local machine database which I will migrate on Azure SQL

3.       Filled form as above then click on Create then it will ask to choose the options for SQL database assessment.

a.       Check Database Compatibility

b.       Check Feature Parity

 


4.       Once database assessment options selected like database compatibility then it will ask for on-premises database source. I am using my local machine database.


5.       As above data source details entered just clicked on Connect. It will ask for database name which you want Assess.


6.       Once I have selected database then need to click on Add. These details will be added for Assessment and we can Start Assessment of selected database.



 

7.       When I click on start assessment button it checks both Database Compatibility and Feature Parity as below


As above We can see, have not found any compatibility issue in our local SQL database and We are good to migrate it on Azure SQL. If you want migrate data directly in azure, you can do it by just clicking on Upload to Azure Migrate button.  In below I will show how migrate local database (on-premises) into azure

 

Migration

The Data Migration Assistant provides seamless assessments of SQL Server on-premises and upgrades to later versions of SQL Server or migrations to SQL Server on Azure VMs or Azure SQL Database. Here are step-by-step instructions for migrating SQL Server on-premises (Local database) to Azure SQL Database by using the Data Migration Assistant.


Need follow these steps to do Migration in your MS SQL with help of Data Migration Assistant.

1.       Open Data Migration Assistant tool in your local machine

2.       Once tool is opened I will create new project name LocalToAzure for Migration for local machine database which I will migrate on Azure SQL


3.       Once filled all information as above then click on Create button. It will follow 6 steps for schema and data migrations.

a.       Select Data source (Local database FunctionApp)

b.       Select Target source (Target database Azure database)

c.       Select database tables (Objects)

d.       Generate script for selected objects

e.       Deploy schema in target server

f.        Migrate data into select objects

Select source database which we want to migrate in azure

 


4.       Need to set target source for schema and data migration. We have created this database in last blog. If you want know more about it please read my previous post How to create Azure SQL database 



5.       Select all the objects which you want to migrate in Azure

6.       Once tables are selected then need to click on Generate SQL Script button and it will generate SQL script.


7.       Once script is generated you need to click on Deploy schema. It will deploy schema on Azure database.


8.       Once schema is deployed on Azure you need to click on Migrate data to migrate data into Azure database. You can see, we have only Products table with one record. Once migration is done it will copy both table schema and records in Azure SQL. 


9.       Once all set then click on Start data migration it will deploy and migrate data in azure database as below.


10.   You can connect with Azure database help of SSMS and can see migrated schema and data as below

Hope it will help you to understand, what is Data Migration Assistant and how we will use it for schema and data migration.

Keep sharing keep learning. Cheers

3 comments:

  1. Thanks for sharing this article here about the Data Migration. Your article is very informative and I will share it with my other friends as the information is really very useful. Keep sharing your excellent work. Azure Data Migration Services

    ReplyDelete
  2. It’s great to come across a blog every once in a while that isn’t the same out of date rehashed material. Fantastic read. Best drupal migration service service provider.

    ReplyDelete
  3. Excellent post. I really enjoy reading and also appreciate your work. This concept is a good way to enhance knowledge. Keep sharing this kind of articles, Thank you. Data Migration Services Company

    ReplyDelete