Migrate SQL Server to Azure SQL Database: A step-by-step Migration Guide

Summary

Table of Contents

What is Azure SQL Database?

Azure SQL Database is a fully managed database service from Microsoft Azure that hosts and manages SQL Server databases in the cloud. It’s completely managed, so you don’t need to stress about the underlying hardware or software. It automatically handles backup updates and ensures your database is always available and running well. This lets developers and businesses focus on building their applications instead of maintaining databases.

Some key features include:

• Automatic backups with the option to recover your data if needed.
• The capability to scale resources according to demand.
• Built-in security like encryption and tools for identifying potential threats.
• Support for T-SQL, making it easy for SQL Server users to get started.

Step-by-Step Guide to Migration

Let’s walk through the essential steps to smoothly migrate your SQL Server to Azure SQL Database using the Migration Extension in Azure Data Studio.

Prerequisites

Before starting the migration, ensure you have the following in place:

1. Set Up Azure Data Studio: Start by downloading and installing Azure Data Studio on your machine.
2. Add the Migration Extension: Visit the Azure Data Studio Marketplace and download the Azure SQL Migration extension.
3. Azure Account Permissions: Ensure your Azure account has the necessary permissions:

• Contributor access for the target Azure SQL Database instance.
• Reader role for the Azure resource group that holds the target SQL Database.
• Owner or Contributor role for the Azure subscription if you’re setting up a new Azure Database Migration Service.

4. SQL Server Access Rights: Make sure the SQL Server login for the source database has the db_datareader role and the login for the target SQL Server has the db_owner role.

Migration Overview

Azure Database Migration Service allows both offline and online migrations. Offline migration involves downtime during the migration process, whereas online migration ensures minimal downtime by keeping the source database available while migration is happening. This tutorial will focus on offline migration, useful when downtime is acceptable.

Step 1: Open the Migrate to Azure SQL Wizard in Azure Data Studio

1. Establish Connection to Your SQL Server:
Launch Azure Data Studio and, from the Connections pane, connect to either your on-premises SQL Server instance or a SQL Server hosted on an Azure VM.
2. Server Management:
Right-click on the server connection and choose the “Manage” option to begin managing the server.
3. Access Azure SQL Migration:

• Under the General tab, select Azure SQL Migration.
• In the Azure SQL Migration dashboard, select Migrate to Azure SQL to launch the migration wizard.

Step 2: Perform a database assessment, gather performance data, and receive Azure recommendations.

1. Databases for Assessment:
In Step 1 of the wizard, choose the databases you want to assess and click Next.
2. Assessment Results:
In Step 2, review the results to ensure no issues with the source databases are found.
Under Choose your Azure SQL target, select Azure SQL Database.
Click View/Select to view the assessment results.
3. Azure Recommendations:
Click Get Azure Recommendation to open the recommendations panel.
Click Collect performance data now to collect performance logs for your source SQL Server.
Save the performance data in a local folder.
After 10 minutes of data collection, Azure Data Studio will generate an Azure SQL Database SKU recommendation based on your performance data. If the performance varies, restart the data collection for more refined recommendations.

Review Recommendations:

Check the recommendations to identify the most suitable Azure SQL Database SKU for your workload.
You can save the recommendation report by clicking Save recommendation report.

Step 3: Configure Migration Settings

1. Select Azure SQL Database:
In Step 3 of the wizard, select the Azure account, Azure subscription, region, and resource group for the target Azure SQL Database.

Choose the target Azure SQL Database server (logical server) and provide the username and password to connect to the database.
Click Connect to verify connectivity.

2. Map Source to Target Database:
Pick the source and target databases to link them together.
Click Next to proceed.

Step 4: Select Migration Mode

1. Choose Offline Migration:
In Step 4, select Offline migration to start the migration with downtime during the migration process.

2. Select Tables to Migrate:
In Step 5, under Data source configuration, enter the source credentials.
Click the Edit pencil icon to select tables for migration.

If no tables exist or if no username and password are provided, the Next button will be disabled.
Select the tables you want to migrate, then click Update.
3. You can also migrate both schema and data using Database Migration Service. If no tables are selected, you must select Schema migration to proceed.

Step 5: Create or Use an Existing Database Migration Service Instance

1. Use an Existing Instance:
If you already have a Database Migration Service instance created, select the Resource group containing the instance and then select the instance.
Click Next to continue.

2. Set Up a New Instance:
If you don’t already have an instance, you can create one.
Pick an existing group or make a new one in the Resource Group section.
Under Azure Database Migration Service, click Create New.
Provide a name for the new instance and click Create.

3. Configure Integration Runtime:
Download and set up the self-hosted integration runtime on a computer that can connect to your SQL Server.

Once installed, launch Microsoft Integration Runtime Configuration Manager and register the runtime.
Use the authentication key provided to complete the configuration.

4. Validate Integration Runtime:
After installing the integration runtime, test the connection to verify it’s successfully connected to the self-hosted integration runtime.
Click Test connection to validate.

Step 6: Start the Database Migration

1. Review Configuration:
In Step 7, review your settings carefully to make sure everything is set up correctly. After that, click “Start migration” to start the process.

Step 7: Monitor the Migration Process

1. Monitor Migration Progress:
Go to the Azure SQL Migration dashboard in Azure Data Studio.
You can track the progress of your migrations by checking the “Database migration status” section.

Click on Database migrations in progress to see detailed migration status.

2. Migration Status:

The following statuses help track the migration progress:

Preparing for copy: The service turns off autostats, triggers, and indexes on the target tables.
Copying: Data is being migrated from the source database to the target database.
Copy finished: The data transfer is complete, and the process is awaiting final steps.
Rebuilding indexes: The service is rebuilding indexes on the target tables.
Succeeded: The migration is successfully completed.

Post-Migration Tasks

After completing your migration, there are several key tasks to perform to ensure everything runs smoothly:

1. Check Application Compatibility: Verify that your applications are compatible with the target Azure SQL Database.
2. Optimize Performance: Leverage Azure SQL Database features like automatic tuning, performance monitoring, and index optimization.
3. Set Up Backup and Monitoring: Enable automatic backups and set up Azure Monitor to track performance and usage.
4. Test the Migration: Perform comprehensive testing to ensure that all data has migrated properly and that the application operates as intended.

Migration Limitations

1. Self-hosted Integration Runtime:
The machine hosting the integration runtime must be powerful enough to handle the CPU and memory load of the data copy process.
The migration speed can be influenced by the type of Azure SQL Database and the performance of the self-hosted integration runtime host.

2. Azure SQL Database Limitations:
The maximum table limit per database is 100,000.
Azure SQL Database migration can become slower with many tables due to overhead during startup.
Migration does not support table names that contain double-byte characters.

Conclusion