Online vs Offline Migration
| Offline Migration | Online Migration | |
|---|---|---|
| Downtime required | Yes — database goes offline during migration | Minimal — database stays online, brief cutover only |
| How it works | Full backup/restore — database offline during transfer | Initial full backup + continuous log streaming until cutover |
| Best for | Small databases, planned maintenance window available | Large databases, 24/7 availability required |
| Complexity | Simpler | More complex — requires log backup access |
Online Migration for Production For production databases that must remain accessible, always use online migration. DMS streams transaction logs continuously — keeping the Azure target in near-real-time sync with the source. Only a brief cutover window (seconds to minutes) is needed.
Supported Migrations
| Source | Target | Mode |
|---|---|---|
| SQL Server (on-premises/VM) | Azure SQL Database | Online, Offline |
| SQL Server (on-premises/VM) | Azure SQL Managed Instance | Online, Offline |
| MySQL (on-premises/VM) | Azure Database for MySQL | Online, Offline |
| PostgreSQL (on-premises/VM) | Azure Database for PostgreSQL | Online, Offline |
| MongoDB | Azure Cosmos DB (Mongo API) | Online, Offline |
| Oracle | Azure Database for PostgreSQL | Online |
| Amazon RDS MySQL/PostgreSQL | Azure Database for MySQL/PostgreSQL | Online |
Migration Assessment
Before migrating, assess your source database for compatibility issues. Microsoft provides:
- Azure Migrate — Discover and assess on-premises SQL Server workloads
- Database Migration Assessment for MySQL/PostgreSQL — Identify compatibility issues
- Data Migration Assistant (DMA) — Deep SQL Server compatibility assessment for Azure SQL Database/MI
Assessment identifies: unsupported features, deprecated syntax, incompatible data types, and recommended target (SQL Database vs Managed Instance).
Migration Process
Step 1 — Pre-Migration
- Run assessment tool — identify and resolve compatibility issues
- Create target Azure database
- Configure network — source must be reachable from DMS (VPN or ExpressRoute for on-premises)
- Ensure source database has required permissions for DMS
Step 2 — Create DMS Migration Project
- Create DMS instance in Azure Portal
- Create a new migration project (specify source type, target type, migration type)
- Configure source connection (on-premises SQL Server connection details)
- Configure target connection (Azure SQL Database/MI connection details)
- Select databases to migrate
- Map source to target tables (for online migration)
Step 3 — Run Migration
- Offline: DMS takes backup of source, transfers, restores to target. Source goes offline.
- Online: DMS takes initial snapshot, then applies ongoing transaction logs. Source stays online.
Step 4 — Monitor
Monitor progress in the Azure Portal — see replication lag (for online), rows migrated, errors. For online migration, aim to get replication lag to near-zero before cutover.
Cutover Process (Online Migration)
- Wait for replication lag to reach near-zero
- Stop new write operations on the source database
- Wait for replication lag to reach exactly zero
- Initiate cutover in DMS Portal
- DMS finalises the migration
- Update application connection strings to point to Azure target
- Verify application functionality
- Decommission source database
DMS SKUs
| SKU | Use |
|---|---|
| Standard (General Purpose) | Offline migrations |
| Premium (Business Critical) | Online migrations — continuous log streaming requires premium SKU |
Online Migration Requires Premium SKU If you need online (minimal downtime) migration, you must use the Premium SKU. The Standard SKU only supports offline migrations.
Alternative Migration Methods
| Method | Best For |
|---|---|
| BACPAC (SQL) | Small SQL Database migrations — export .bacpac, import to Azure |
| Native backup/restore (SQL MI) | Direct .bak file restore to SQL Managed Instance |
| mysqldump / pg_dump | Simple MySQL/PostgreSQL offline migrations |
| Azure Data Factory | Custom data pipelines with transformation |
AZ-104 Exam Tip Know that online migration requires Premium SKU and provides minimal downtime. Know that offline migration requires downtime. Know DMS supports SQL Server, MySQL, PostgreSQL, MongoDB, and Oracle sources. Know that assessment tools identify compatibility issues before migration.