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
- Assess: feature compatibility, size, log generation rate, bandwidth to Azure.
- Prepare Azure: the MI and its subnet, a storage account, a container, a SAS token or an identity allowed to read.
- Back up the source (FULL recovery model,
CHECKSUMoption) and drop the files in the container. - Start the replay service.
- Feed it: keep dropping log backups until you are ready.
- 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
- Stop application writes.
- Take a last log backup and drop it in the container.
- Complete the replay, giving the name of that last file (
Complete-AzSqlInstanceDatabaseLogReplay -LastBackupName ...). The database becomes readable and writable. - Replay what a restore does not carry (list below).
- 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
- Logins and their SIDs: recreate them, or switch to Entra groups. A user whose login is missing becomes orphaned.
- SQL Agent jobs, operators, alerts (
msdb). - Linked servers, instance settings, Database Mail.
- TDE encryption: if the source is encrypted with its own certificate, you must provide it to the MI before restoring.
Pitfalls and lessons learned
- During replay the database is not readable: you cannot test it before the cut-over. Rehearse on a copy.
- Log chain: a missing file, or a log taken by another backup tool, breaks the sequence. Only one tool should back up the source during the migration.
- Identity: if the on-premises Active Directory is not synchronised with Entra, Windows accounts do not carry over. I had to decide early between hand-created Entra groups and SQL logins for services.
- Network: the MI lives in a dedicated subnet; outbound flow to storage and inbound application access must be planned from the start.
- Rehearse the cut-over at least once on a test environment: stopwatch in hand, with the checklist above.
- Validate: row counts on key tables,
DBCC CHECKDB, compatibility level, execution plans of critical queries.