Migrating a Large On-Premises SQL Database to Azure SQL: A Comprehensive Guide

Discover how to move your on-premises SQL Server database to Azure SQL using Azure Database Migration Service and ensure a seamless transition.

Enterprises are increasingly migrating their on-premises applications and databases to the cloud to leverage the scalability, flexibility, and cost benefits offered by cloud-based platforms like Azure. Migrating a large on-premises SQL Server database, such as a 2 TB database, to Azure SQL can be a challenging task, but with the right tools and guidance, you can ensure a smooth and successful transition. In this blog post, we will explore the different options available for migrating a large on-premises SQL Server database to Azure SQL and provide step-by-step instructions for using Azure Database Migration Service (DMS) to perform the migration.

Migration Options for On-Premises SQL Server to Azure SQL

There are several methods available for migrating your on-premises SQL Server database to Azure SQL, including:

1. BACPAC file and Azure Storage Account using azcopy utility

The BACPAC file and Azure Storage Account method is another approach for migrating SQL Server databases to Azure SQL. A BACPAC file is a compressed file that contains the schema and data of your SQL Server database. You can create a BACPAC file using SQL Server Management Studio (SSMS) or the SqlPackage command-line utility. After creating the BACPAC file, you can upload it to an Azure Storage Account using the AzCopy utility, a high-performance command-line tool for copying data to and from Azure Blob Storage. Once the BACPAC file is uploaded to the Azure Storage Account, you can import it into your Azure SQL Database or Azure SQL Managed Instance. This approach is generally more suitable for smaller databases, as it may require significant downtime and manual intervention during the migration process.

2. Azure Database Migration Service (DMS)

Azure Database Migration Service (DMS) is a fully managed, cloud-based service that simplifies migrating on-premises SQL Server databases to Azure SQL Database or Azure SQL Managed Instance. DMS supports online migrations, which means it can continuously synchronize data between the source and target databases while the source database remains operational. This reduces downtime during the migration process. With its detailed migration guidance, progress tracking, and monitoring features, DMS streamlines complex database migrations and ensures a smooth and successful transition to the cloud.

Each approach has its pros and cons, and the choice depends on factors such as the size of the database, the need for continuous data synchronization, and the complexity of the migration process. For a large 2 TB database, MS recommends using Azure Database Migration Service (DMS) due to its scalability, minimal downtime, and support for continuous data synchronization.

For more information on azcopy, visit the https://learn.microsoft.com/en-us/azure/storage/common/storage-use-azcopy-v10

For more information on Azure SQL Database, visit the official documentation.

For more information on Azure SQL Managed Instance, visit the official documentation.

Why Choose Azure DMS for Large On-Premises Database Migration?

For a large on-premises database (2 TB) migration to Azure SQL, Azure Database Migration Service (DMS) is the recommended choice for several reasons:

• Scalability: Azure DMS is designed to handle large and complex databases, making it an efficient choice for managing the migration of your 2 TB database to Azure SQL.
• Minimal downtime: Azure DMS can perform online migrations, which means that it can synchronize the data continuously between the source and the target while the source database remains operational. This minimizes the downtime during the migration process.
• Monitoring and guidance: Azure DMS provides detailed migration guidance, progress tracking, and monitoring to ensure a smooth and successful migration process.
• Flexibility: Azure DMS supports various source-target pairs, making it a versatile choice for migrating your on-premises SQL Server database to Azure SQL Database or Azure SQL Managed Instance.

Migrating a Large On-Premises SQL Server Database to Azure SQL using Azure DMS

Azure DMS is a fully managed service designed to streamline the migration process for on-premises databases to Azure SQL Database or Azure SQL Managed Instance. The following steps outline how to use Azure DMS to migrate a large on-premises SQL Server database to Azure SQL:

Step 1: Provision an instance of Azure Database Migration Service

• Create a new Azure DMS instance from the Azure portal.
• Select the appropriate subscription, resource group, and region for the DMS instance.
• Choose the desired SKU (e.g., Standard or Premium) based on your migration requirements.

Step 2: Set up and configure the source and target endpoints

• Configure the on-premises SQL Server (source) and Azure SQL Database or Azure SQL Managed Instance (target) endpoints in the Azure DMS instance.
• Ensure that the appropriate network connectivity and firewall settings are in place to allow communication between the source and target endpoints. For more information follow this link:  Azure Database Migration Service - Overview of prerequisites 

Step 3: Create a migration project using Azure DMS

• Create a new migration project within the Azure DMS instance.
• Select the appropriate migration type (e.g., online or offline migration) based on your requirements.
• Add the source and target endpoints to the migration project

Step 4: Run the migration project and monitor progress

• Start the migration project and monitor the progress using the Azure portal.
• Address any issues that arise during the migration process to ensure a smooth transition.

Step 5: Perform validation and testing

• Validate the migrated data in the target Azure SQL environment.
• Test the application functionality to ensure compatibility and optimal performance in the Azure SQL environment.

Step 6: Cut over and decommission the on-premises SQL Server

• Once satisfied with the migration, cut over to the new Azure SQL environment.
• Decommission the on-premises SQL Server once it is no longer needed.

Step 7: Changing the Azure Database Migration Service SKU

After deploying your Azure DMS instance, you may need to change the SKU to scale the service up or down based on your requirements. To change the DMS SKU, follow these steps:

• Navigate to the Azure portal.
• Go to the "Database Migration Services" blade.
• Select your DMS instance.
• In the "Settings" section of the DMS instance blade, click on "Pricing tier."
• Choose the desired SKU (e.g., Standard or Premium) and click "Save."

Keep in mind that changing the SKU may cause a brief service disruption, as the DMS instance will be restarted during the process. It is advisable to stop any ongoing migration activities before changing the SKU to minimize the impact of the service disruption.

For more information on provisioning an Azure DMS instance, visit the official documentation.

For more information on configuring source and target endpoints, refer to the official documentation.

For more information on creating and running a migration project using Azure DMS, refer to the official documentation.

Conclusion:

Migrating a large on-premises SQL Server database to Azure SQL can be a complex task, but by using Azure Database Migration Service (DMS), you can ensure a smooth and successful migration. With its ability to handle large databases, minimal downtime, and continuous data synchronization, Azure DMS is the ideal choice for moving your on-premises database to the cloud. By following the steps outlined in this guide and optimizing your content for search engines, you can help others navigate the migration process and make an informed decision about the best approach for their specific needs.

For more information on Azure Database Migration Service, visit the official documentation.