Step-by-Step Procedure to Migrate SQL Database to Azure SQL

Read time 7 minutes

When businesses consider migrating their data to a cloud platform, their initial perception often revolves around the idea that the cloud primarily serves as a storage solution for files and documents. However, this notion is not entirely accurate, as cloud-based applications can also securely store and manage database files.

Microsoft SQL Server stands out as a top-notch on-premises database management solution favored by organizations for safeguarding their critical business data. Transitioning from on-site infrastructure to the cloud often raises concerns about migrating SQL data. However, Azure Database Migration Service offers a reliable solution for seamlessly transferring your SQL data to an Azure cloud server.

In this article, we will guide you through the process of migrating an on-premises SQL Server database to an Azure database in a step-by-step manner. You’ll need to follow these sequential steps to successfully complete the migration.-

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

1. Use Data Migration Assistant to evaluate the on-premises data

   Prior to commencing the migration process, it is essential to assess the data residing on your on-premises SQL server. To streamline this evaluation, you can employ a dedicated tool known as the Data Migration Assistant, which will conduct an in-depth analysis and identify potential errors or bottlenecks.
   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.

2. Use Data Migration Assistant to migrate a sample schema

   Once you’ve completed the assessment and achieved the desired results, you’re ready to proceed with the creation of a migration project for a small sample of the database schema. To carry out the migration, please follow the steps outlined below. –

   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.
3. 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.
4. 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.
5. Start the migration

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

6. 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.

7. 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.

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 manual and automated methods can be used for migrating SQL databases to Azure SQL. However, when utilizing SQL Server’s Kernel Migrator, you can complete the migration in a matter of minutes.