Migrating SQL Server Database to Azure SQL using DMA

Microsoft SQL Server is one of the best on-premises database management tool which organizations use to save their business data. When they wish to migrate the on-site resources to the cloud, they hesitate to migrate the SQL data to the cloud. But there are some tools or methods (Data Migration Assistant, Using Bacpac file, Azure Database Migration Service, Azure Migrate Service, etc..) which you can use and migrate the SQL data to Azure cloud server.

In the blogpost, we will try to learn the step-by-step migration of on-premises SQL Server database to the online Azure database using one of the migration method/tool which is Microsoft's DMA (Data Migration Assistant) Tool.

Microsoft Data Migration Assistant

• This tool is used mainly to check the compatibility issues that may affect the database functionality when migrating your databases to a new SQL Server version or to Azure SQL Database.

• It helps by identifying any feature in the current version that is not supported in the new version or in the cloud, what new features in the new version we can benefit from, providing recommendations to enhance the performance and the reliability in the new version and finally migrate the on-premises version to a newer version or to Azure SQL Database.

• It can be used to assess and migrate any SQL Server installed on Windows machine with version 2005 and later, to any SQL Server instance installed on Windows or Linux with version 2012 and later or to Azure SQL Database.

• This tool is recommended as an alternative to the SQL Server Upgrade Advisor tool to assess and upgrade to the new SQL Server versions. For migrating to Azure SQL Database, it is recommended to use the Azure Migration Service instead.

• The Data Migration Assistant can be downloaded from Microsoft Download center and installed to your machine, using a straight-forward installation wizard, as shown below:

Below are the main steps to be followed in order to perform migration of on-premises SQL Server database to the online Azure database using DMA Tool :

1. Evaluate the on-premises data.

2. Perform a test by migrating a sample schema.

3. Create an Azure Database Migration Service instance.

4. Create a new migration project with Azure Database Migration Service

5. Start the migration.

6. Monitor the migration progress.

7. Perform the migration cutover.

Evaluate the on-premises data

Before starting the migration, first evaluate the on-premises data of your SQL server. For evaluation use Data Migration Assistant tool which does the analysis for you and checks for any possible error or bottleneck. Follow below steps:

1. Run the tool and click the New (+) option, and then select the Assessment option.

2. Provide a project name, choose SQL server as source server and Azure server as the destination server. Then click the Create button.

3. In the Select sources option, connect your SQL Server.

4. In the Add sources option, add the database which you want to migrate, and then click the Start Assessment option.

This tool will run an assessment check and provide a complete report. The report will provide information about compatibility issues, and other migration-related details.

Perform a test by migrating a sample schema

After running the assessment and getting the satisfactory results, you can go for creating migration project for a small specimen of the database schema.

To migrate the database schema, follow the upcoming steps:

1. Run the Data Migration Assistant tool, select the New (+) icon, and choose a new Migration option under the Project type.

2. Select the SQL Server as the source server and Azure Server as the destination server.

3. Select the Schema only option under the Migration scope.

4. Finally, go to Create option to create a project. Here, connect to your SQL server and select the database.

5. Select the Azure Server for the destination server and connect it.

6. Choose the schema objects from your database.

NOTE – By default, all the objects are selected.

7. To create the SQL scripts, select Generate SQL scripts option.

8. At last, deploy the schema to the Azure SQL database.

Create Azure Database Migration Service instance

You need to create an instance of the Azure Database Migration Service to create a migration project. Here are the required steps:

1. First, you need to register a Microsoft. Data Migration resource provider by taking its subscription.

2. After registering the resource provider, you can go to the Azure and create a resource under the Azure Database Migration Service.

3. Specify a meaningful name to the migration service, its subscription, and resource group.

4. Select the location where you want to create the migration service.

5. Either create or select an existing virtual network.

6. Select the pricing tier and create the migration service.

Create a new migration project with Azure Database Migration Service

When you have successfully created a new migration service, you can create a migration project at the Azure Portal.

1. Select Azure Database Migration Services under the services, then select the migration service which you had created in the previous step.

2. Select the + New Migration Project option.

3. Select the SQL server as the source server and Azure SQL Server as the destination server.

4. For the type of activity, select Online Data Migration.

5. Create and run activity option allows the create and run the migration.

6. Specify the source and target database details, and review the summary.

Start the migration

When you click the Run migration, and the migration activity starts.

Monitor the migration

1. There is a separate migration activity screen where you can click the Refresh button and see the Status of the migration.

2. You can click on any specific database to get the status of Incremental data sync and Full data load operations.

Perform the migration cutover

After the completion of the initial Full load, you can perform migration cutover.

1. Select Start Cutover and stop all the incoming transactions to the database.

2. Select Confirm.

3. Select Apply.

That's all in this post. If you liked this blog and interested in knowing more about Azure, Please Like, Follow, Share & Subscribeto www.ImJhaChandan.com.