Migration Planning: Online vs Offline
Evaluate migration requirements for Azure SQL. Understand the differences between online and offline migration strategies, and when to use each approach.
Planning a migration
Think of migration like moving house.
Offline migration is packing everything into a truck, driving to the new house, and unpacking. While the truck is in transit, nobody lives in either house. Simple, but there’s downtime.
Online migration is like moving one room at a time while still living in the old house. You move the big furniture first (initial sync), then keep forwarding mail (continuous replication) until you flip the mailbox to the new address (cutover). Much more complex, but near-zero downtime.
Migration assessment — what to evaluate
Before moving anything, Kenji runs a thorough assessment of NorthStar’s 200 databases:
Compatibility assessment
| Assessment Area | What to Check | Tool |
|---|---|---|
| SQL compatibility | Deprecated features, unsupported T-SQL, compatibility level | Azure Migrate, Data Migration Assistant (DMA) |
| Feature parity | Features used on-prem but not available on target (FILESTREAM, CLR, SSRS) | Azure Migrate, DMA |
| Database size | Total data size affects migration duration and target tier selection | sys.database_files, Azure Migrate |
| Dependencies | Cross-database queries, linked servers, applications connecting to the database | Application dependency mapping |
| Downtime tolerance | How long can the application be offline during cutover? | Business requirements |
Target selection rules
| If the source uses… | Target should be… |
|---|---|
| SQL Server Agent, cross-DB queries, linked servers | Azure SQL Managed Instance |
| No instance-scoped features, modern app | Azure SQL Database |
| FILESTREAM, SSRS, SSIS on same server, third-party tools | SQL Server on Azure VMs |
| Any SQL version, minimal changes wanted | Azure SQL MI or SQL on VMs |
Azure Migrate vs Data Migration Assistant
Azure Migrate is the centralised hub for discovery, assessment, and migration. It includes:
- Discovery of on-prem SQL instances
- Assessment reports (compatibility, readiness, SKU recommendations)
- Integration with Azure Database Migration Service for execution
Data Migration Assistant (DMA) is the standalone tool for detailed compatibility assessment:
- Identifies breaking changes, behaviour changes, and deprecated features
- Recommends target Azure SQL platform
- Generates a readiness report with remediation steps
The exam may use either tool name — know that Azure Migrate is the overarching service, DMA is the assessment-specific tool.
Online vs offline migration
This is the core decision in migration planning.
| Aspect | Offline | Online |
|---|---|---|
| Downtime | Hours to days (depends on DB size) | Minutes (cutover only) |
| Complexity | Simple — stop, copy, start | Complex — initial sync + continuous replication + cutover |
| Data loss risk | None (application is stopped) | Minimal (replication lag during cutover) |
| Source availability | Source offline during migration | Source stays online throughout |
| Tools | Backup/restore, BACPAC, BCP, DMS offline | DMS online, log replay, transactional replication |
| Best for | Small databases, acceptable downtime windows | Large databases, 24/7 operations, minimal downtime tolerance |
| Cost | Lower (simpler setup) | Higher (requires DMS, network bandwidth, monitoring) |
When to choose offline
Kenji uses offline migration for NorthStar’s smaller databases (under 100 GB) that have a maintenance window:
- Stop the application
- Take a final backup
- Restore to the target (or use BACPAC export/import)
- Validate data
- Redirect the application to the new target
- Done
Methods:
- Native backup and restore — to Azure SQL MI or SQL on VMs (via Azure Blob Storage)
- BACPAC export/import — to Azure SQL Database (schema + data in a portable file)
- BCP (Bulk Copy Program) — for individual tables, flexible but manual
- DMS offline mode — guided migration with validation
When to choose online
Kenji uses online migration for NorthStar’s mission-critical ERP database (800 GB, 24/7 operations):
- Set up Azure Database Migration Service (DMS)
- Initial full copy — bulk data transfer to target
- Continuous sync — DMS tails the transaction log, replicating changes in near real-time
- Application remains online during steps 2 and 3
- Cutover — stop writes to source, let sync catch up, redirect application. Downtime: minutes.
Methods:
- Azure DMS online mode — to Azure SQL Database or MI
- Log Replay Service (LRS) — to Azure SQL MI (continuous restore from log backups in Azure Blob Storage)
- Transactional replication — to Azure SQL Database or MI
- Always On Availability Group — extend an on-prem AG to include Azure as a replica (SQL on VMs)
Log Replay Service (LRS) for MI migration
LRS is purpose-built for migrating to Azure SQL Managed Instance:
- Take full + differential + log backups to Azure Blob Storage
- Start LRS — it continuously restores log backups as they arrive
- When ready, complete the final log backup and trigger cutover
- LRS restores the final log and brings the database online
LRS is free (included with MI) and works well for databases of any size. It’s the recommended online migration method for MI.
Migration tools at a glance
| Tool | Target | Online? | Offline? | Notes |
|---|---|---|---|---|
| Azure DMS | SQL DB, MI | Yes | Yes | Recommended first choice for most migrations |
| Native backup/restore | MI, SQL VM | No | Yes | Via Azure Blob Storage. Simplest for MI offline. |
| Log Replay Service | MI | Yes | No | Purpose-built for MI online migration |
| BACPAC | SQL DB | No | Yes | Export/import via SqlPackage or Azure Portal |
| Transactional replication | SQL DB, MI | Yes | No | Continuous replication, complex setup |
| BCP / BULK INSERT | All | No | Yes | Table-level, good for specific data loads |
| Azure Data Factory | All | No | Yes | ETL pipeline, good for data transformation during migration |
Kenji’s migration checklist
Before any migration, Kenji validates:
- Compatibility report — no blocking issues from DMA/Azure Migrate
- Network bandwidth — sufficient for data transfer (calculate: DB size / available bandwidth = transfer time)
- Target sizing — target tier can handle the source workload (vCores, storage, IOPS)
- Application changes — connection strings, driver versions, retry logic for transient faults
- Rollback plan — what happens if migration fails? Keep source running until validation is complete.
- Validation queries — row counts, checksum comparisons, application smoke tests
Kenji needs to migrate an 800 GB mission-critical database to Azure SQL Managed Instance with less than 15 minutes of downtime. Which migration method should he use?
Tomas is assessing a database for migration. He discovers it uses FILESTREAM, SSRS installed on the same server, and a third-party monitoring agent. Which target platform should he recommend?
Next up: Execute and Troubleshoot Migrations — implement online and offline migrations, move between Azure SQL services, and troubleshoot common migration issues.