Azure SQL Database Migration Tutorial

Step-by-step guide to migrating your database to Azure SQL

This tutorial guides you through the process of migrating your existing SQL Server database to Azure SQL Database. We'll cover common scenarios and best practices to ensure a smooth transition.

Prerequisites

Before you begin, ensure you have the following:

Phase 1: Assessment and Planning

1. Assess your database

Use tools like the Azure Data Migration Assistant (DMA) to assess your source database for compatibility issues and feature parity with Azure SQL Database. DMA provides recommendations for remediation.

You can download DMA from the Microsoft Download Center.

2. Choose your Azure SQL deployment option

Decide whether to migrate to Azure SQL Database (single database, elastic pool) or Azure SQL Managed Instance, based on your application's needs for compatibility, features, and management overhead.

3. Plan your migration strategy

Determine the migration approach:

  • Offline migration: Suitable for applications that can tolerate downtime.
  • Online migration: Minimizes downtime by continuously synchronizing changes.

Consider network bandwidth, data volume, and acceptable downtime. Tools like Azure Database Migration Service (DMS) can assist with both strategies.

Phase 2: Migration

4. Create your target Azure SQL resource

In the Azure portal, create your Azure SQL Database or Managed Instance. Configure the server, database name, compute and storage settings, and firewall rules.

For example, to create a single database using Azure CLI:

az sql db create --resource-group <your-resource-group> \
    --server <your-azure-sql-server> \
    --name <your-database-name> \
    --edition Basic \
    --service-objective Basic \
    --max-size 2 \
    --collation SQL_Latin1_General_CP1_CI_AS
5. Migrate the database schema

Use DMA or SQL Server Management Studio (SSMS) to deploy your database schema to the target Azure SQL resource. Ensure all objects are migrated correctly.

6. Migrate the data

For offline migration: Use tools like BACPAC files or Azure Data Studio to migrate the data.

For online migration: Set up Azure Database Migration Service (DMS) to perform an initial load and then continuous synchronization.

Example using BACPAC:

  1. Export your source database to a BACPAC file.
  2. Import the BACPAC file to your Azure SQL Database.

Phase 3: Post-Migration and Cutover

7. Reconfigure applications

Update your application connection strings to point to the new Azure SQL Database endpoint. Test application connectivity thoroughly.

8. Perform cutover

For online migrations, once synchronization is complete and applications are tested against the synchronized database, stop the source database, perform the final synchronization, and redirect traffic to Azure SQL Database.

9. Optimize and monitor

After migration, monitor performance, review query execution plans, and tune your database for optimal performance in Azure. Consider enabling features like Automatic Tuning.

Related Resources