Lesson: Database Migration with Azure Database Migration Service (DMS)

Introduction: Why Azure DMS?

Migrating databases from on-premises environments or other clouds to Azure is a critical task for any cloud migration strategy. Doing this manually—exporting large SQL dumps, managing network throughput, and ensuring data consistency—is error-prone and time-consuming.

Azure Database Migration Service (DMS) is a fully managed service designed to enable seamless migrations from multiple source databases to Azure data platforms with minimal downtime. By automating the migration process, DMS reduces the risk of data loss, minimizes application downtime, and provides a structured path for moving to Azure SQL Database, Azure Database for MySQL, or Azure Database for PostgreSQL.

Understanding the Architecture

Azure DMS operates by establishing a connection between your source database and your target Azure database. It performs two types of migrations:

1. Offline Migration: The source database is taken offline, data is migrated, and the application is pointed to the new Azure database. This is best for non-critical workloads where downtime is acceptable.
2. Online Migration: DMS performs an initial data load and then continuously synchronizes data changes (using transaction logs) until you manually cut over. This allows for near-zero downtime.

Key Components

• DMS Instance: The compute resource that orchestrates the data movement.
• Data Migration Assistant (DMA): A desktop tool used to assess the source database for compatibility issues before starting the migration.
• Integration Runtime (Self-hosted): Required if your source database is behind a firewall, ensuring secure data transit.

Practical Example: Migrating SQL Server to Azure SQL

Let’s walk through the standard workflow for migrating an on-premises SQL Server instance to an Azure SQL Database.

Step 1: Assessment with DMA

Before migrating, you must ensure your schema is compatible.

1. Download and install the Data Migration Assistant (DMA).
2. Select Assessment and point it to your source SQL Server instance.
3. Review the report for "Breaking Changes" (e.g., deprecated features or cross-database queries).

Step 2: Provisioning the DMS Instance

You can provision DMS via the Azure Portal or Azure CLI.

Example: Provisioning via Azure CLI

# Create a Resource Group
az group create --name MigrationRG --location eastus

# Create the DMS Service
az dms create --resource-group MigrationRG \
              --name MyMigrationService \
              --location eastus \
              --sku Basic_1vCore

Step 3: Configuring the Migration Project

Once the service is active, you create a project specifying the source and target.

• Source: SQL Server
• Target: Azure SQL Database
• Migration Type: Online Data Migration

Step 4: Monitoring the Migration

During an online migration, you will see a status dashboard. Once the "Full Load" is complete, the status will change to "Ready for Cutover."

Code Snippet: Monitoring via PowerShell

# Get the status of a specific migration project
Get-AzDataMigrationProject -ResourceGroupName "MigrationRG" `
                           -ServiceName "MyMigrationService" `
                           -Name "SQLToAzureMigration"

Best Practices for Successful Migrations

1. Perform a Pilot Migration: Never move production data without a successful dry run. Use a restored backup of your production database in a staging environment to test the DMS workflow.
2. Network Throughput: If migrating large volumes of data, ensure your ExpressRoute or Site-to-Site VPN has sufficient bandwidth to handle the initial sync without impacting production traffic.
3. Clean Up Beforehand: Use the migration as an opportunity to purge old, unused data, logs, and temporary tables. This reduces the size of the dataset and speeds up the migration.
4. Security First: Always use encrypted connections (SSL/TLS) during the migration. Ensure the service principal used by DMS has the minimum required permissions (e.g., db_owner on the source).
5. Test Application Connectivity: After the schema is migrated but before the data cutover, update your application connection strings to point to the new Azure environment to verify connectivity.

Common Pitfalls to Avoid

• Ignoring Compatibility Reports: Many administrators skip the DMA assessment. This leads to migration failures midway through because of unsupported features (like CLR assemblies or SQL Agent jobs).
• Under-sizing the DMS SKU: If you have a terabyte-scale database, using the "Basic" tier will result in sluggish migration speeds. Use "Premium" tiers for high-throughput requirements.
• Firewall Misconfiguration: The most common cause of "Connection Timeout" errors is failing to whitelist the DMS instance IP address on the source network firewall.
• Forgetting Agent Jobs: DMS migrates data and schema, but it does not migrate SQL Agent Jobs, SSIS packages, or Server-level logins. You must migrate these manually or via scripts.

Key Takeaways

• Assessment is Mandatory: Use the Data Migration Assistant (DMA) to identify blockers before you ever touch the Azure portal.
• Choose Your Strategy: Use Offline migrations for simple, low-downtime tolerance projects; use Online migrations for mission-critical production systems.
• Automation is Key: Utilize Azure CLI or PowerShell for repeatable migration tasks, especially if you have hundreds of databases to move.
• Post-Migration Tasks: Remember that DMS handles the data. You are still responsible for re-creating logins, jobs, and configuring Azure-specific features like Auto-scaling and Threat Protection after the migration.
• Monitor Closely: Keep a close eye on the "Data Latency" metric during online migrations to ensure your target database is keeping pace with source transactions.

Important: Always decommission the DMS instance immediately after the migration is complete to avoid incurring unnecessary costs.