Steps to Migrate your On-Premises SQL Database to Azure

November 12, 2021

When companies want to move their data to the cloud platform, their first impression is that the cloud only stores files and documents. But that’s not entirely true. You can store all your enterprise’s data files in a cloud-based application.

Microsoft SQL Server is the best on-premises database management tool companies use to store their business data. When they migrate the on-premises resources to the cloud, they don’t prefer to migrate the SQL data. But there is Azure Database Migration Service that you can use to migrate the SQL data to Azure Cloud Server.

This article will go step-by-step through migrating an on-premises SQL Server database to Azure’s online database:

A. Use Data Migration Assistant to assess the on-premises data.

Before starting the migration, you should first evaluate your SQL Server’s on-premises data. Data Migration Assistant’s separate utility will analyze you and check for any errors or bottlenecks.

Here are its steps –

Launch the tool, click on the New (+) option, and select the Assessment option.
Specify a project name, select SQL Server as the source server and Azure Server as the target server. Then click the Create button.
In the Select option, connect with your SQL Server.
In the Add Sources option, add the database you want to migrate, and then click the Start Assessment option.
The Data Migration Wizard performs a scan and generates a full report. The report includes information about compatibility issues and other migration-related details.

B. Use the Data Migration Wizard to migrate a sample schema.

After performing the assessment and obtaining satisfactory results, you can create a migration project for a small sample database schema. To migrate the database schema, perform the following steps –

Launch the Data Migration Wizard, click the New (+) icon and select New Migration under Project Type.
Select SQL Server as the source server and Azure Server as the target server.
Click on the Schema-only option available under the migration scope.
Finally, select the Create option to create a project. Connect to your SQL Server here and select the database.
Select the Azure server for the target server and connect it.
Select the schema objects from your database.
To create the SQL scripts, select the Generate SQL Scripts option.
Finally, deploy the Schema to the Azure SQL database.

C. Create an Azure Database Migration Service instance

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

First, you need to register as a Microsoft. Data migration resource provider by taking its subscription.
After registering the resource provider, you can go to Azure and create a resource under Azure Database Migration Service.
Provide a descriptive name for the migration service, its subscription, and the resource group.
Select the location to create the migration service.
Either create a virtual network or select an existing one.
Select the price level and create the migration service.

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

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

Under Services, select Azure Database Migration Services and select the migration service you created in the previous step.
Select the + New migration project option.
Select SQL Server as the source server and Azure SQL Server as the target server.
Now click on Online Data Migration.
The Create and run activity option allow you to create and run the migration.
Mention the source and target database details.

Ready to experience the full power of cloud technology?

Our cloud experts will speed up cloud deployment, and make your business more efficient.

E. Start the migration

When you click the Run Migration button, the migration activity is started.

F. Monitor the migration

There is a separate migration activity screen to see the migration status. You can click on a specific database to see the status of the Incremental Data Synchronisation and Full Data Transfer operations.

G. Perform the migration cutover

After the total load is complete, you can perform the migration cutover.

Choose Start Cutover and stop all incoming transactions in the database.
Choose Confirm; choose Apply.

Conclusion

The manual and automated methods are suitable for migrating SQL databases to Azure SQL. However, if you use SQL Server’s Kernel Migrator, you can complete the migration in a matter of minutes.

You can discuss in depth with an expert if you plan to migrate your organization from SQL database to Azure database.