LLI Wiki — My Digital Knowledge Base

Migrating SQL Server on-premises to Azure SQL Managed Instance: options, cut-over and pitfalls

Backup/restore from URL, Log Replay Service, MI link and DMS: how to choose, steps, cut-over and pitfalls to avoid.

Migrating an on-premises SQL Server to Azure SQL Managed Instance is not hard in itself. What makes the difference is choosing the method that matches the acceptable downtime, and preparing everything a restore does not carry over. Here is my approach.

The four options

Method Principle Downtime When to choose it
Backup / restore from URL BACKUP ... TO URL, then RESTORE ... FROM URL on the MI Long: the whole restore duration Small databases, comfortable window
Log Replay Service (LRS) Full, differential and log backups dropped in Blob Storage and replayed continuously Short: the time of the last log Large databases, little downtime
Managed Instance link Distributed availability group between the source and the MI Very short Compatible source version, reliable private network, controlled switchover
Azure Database Migration Service (DMS) Guided service that orchestrates the migration (online or offline) Depends on the mode When you want a wizard and central tracking

Check the version and network prerequisites of each method in the official documentation before deciding: they change over time.

The flow I use most: LRS

flowchart LR
    A["Source SQL Server (FULL recovery)"] --> B["Backups with CHECKSUM"]
    B --> C["Blob Storage (dedicated container)"]
    C --> D["Log Replay Service on the MI"]
    D --> E["Database in restoring state"]
    F["Applications"] -.->|"cut-over"| G["Last log + Complete"]
    G --> E

Steps

  1. Assess: feature compatibility, size, log generation rate, bandwidth to Azure.
  2. Prepare Azure: the MI and its subnet, a storage account, a container, a SAS token or an identity allowed to read.
  3. Back up the source (FULL recovery model, CHECKSUM option) and drop the files in the container.
  4. Start the replay service.
  5. Feed it: keep dropping log backups until you are ready.
  6. Cut over (see below), then validate.
# Start the replay (generic names)
Start-AzSqlInstanceDatabaseLogReplay `
  -ResourceGroupName rg-dba-prd -InstanceName mi-contoso-prd `
  -Name AppDb `
  -Collation SQL_Latin1_General_CP1_CI_AS `
  -StorageContainerUri "https://stdbacontoso.blob.core.windows.net/migration" `
  -StorageContainerSasToken $sas

# Follow progress
Get-AzSqlInstanceDatabaseLogReplay -ResourceGroupName rg-dba-prd `
  -InstanceName mi-contoso-prd -Name AppDb

The cut-over

  1. Stop application writes.
  2. Take a last log backup and drop it in the container.
  3. Complete the replay, giving the name of that last file (Complete-AzSqlInstanceDatabaseLogReplay -LastBackupName ...). The database becomes readable and writable.
  4. Replay what a restore does not carry (list below).
  5. Repoint connection strings, test, reopen.

Plan a way back: as long as the source has not been modified, it remains plan B.

What a restore does not carry

Pitfalls and lessons learned

Mastodon LLIMastodon SocialFosstodon