Step-by-step procedure to migrate SQL database to Azure SQL

Read time 7 minutes

When business organizations want to move their data to the cloud platform, their first impression is that the cloud is only supposed to save files and documents. But it is not entirely true. You can keep your database files in cloud-based applications.

Microsoft SQL Server is 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 is Azure Database Migration Service which you can use and migrate the SQL data to Azure cloud server.

In the present article, we will go through the step-by-step migration of on-premises SQL Server database to the online Azure database. There are following steps which you need to perform the migration sequentially –

1. Use Data Migration Assistant to evaluate the on-premises data
2. Use Data Migration Assistant to migrate 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. Check the migration progress
7. Perform the migration cutover

Use data migration assistant to evaluate the on-premises data

Before starting the migration, first evaluate the on-premises data of your SQL server. There is a separate utility tool called Data Migration assistant which does the analysis for you and checks for any possible error or bottleneck.
Here are its 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.

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

Use data migration assistant to migrate 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.DataMigration 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

There is a separate migration activity screen where you can click the Refresh button and see the Status of the migration. 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; select Apply.

A quicker way for SQL to Azure SQL migration

Kernel Migrator for SQL Server is a simple SQL Migration tool to migrate offline MDF and NDF file from your SQL Server to the Microsoft Azure cloud server. Let’s see the easy steps to complete the migration –

1. Start the software, and the Select SQL Database will pop up. Click the Browse button.
   
2. After browsing the database, the second step requires selecting the scan mode and clicking the Recover button.
   
   NOTE – The Recover option works to remove the corruption from the database file. It does not affect the normal file.
3. After retrieving the database, the database objects are displayed in a tree structure. Here, you can get a clear preview of the objects. Select the required objects and click the Migrate button.
   
4. Select the first option of Azure SQL. Then input the server name and its complete credentials. Then click the Connect Now button.
   
5. After a successful connection, the tool will provide a successful message. Click OK.
   
6. Now, select the destination database from the drop-down list.
   
7. After selecting the database, click the OK button.
   
8. The tool has successfully migrated the database, and it gives a successful message. Click OK.
   

Final words

Both the manual and the automated methods are suitable for SQL database to Azure SQL migration. However, while using the Kernel Migrator of SQL Server, you can perform the migration within minutes.