How to migrate large SQL Server workloads to Azure with minimal downtime?

Long Answer

A large SQL Server migration to Azure with minimal downtime requires careful assessment, tooling, replication, and staged cutovers. The goal: move terabytes of data, preserve integrity, and ensure applications see little to no disruption.

1) Assessment and planning
Start with Azure Data Migration Assistant (DMA) to scan the SQL Server instance. It flags unsupported features, deprecated T-SQL, and compatibility issues with Azure SQL Managed Instance (MI) or Azure SQL Database. Decide on landing zone:

• Azure SQL Managed Instance → best for near full SQL Server feature compatibility.
  
• Azure SQL Database (PaaS) → scalable but with some feature limitations.
  
• Azure VM with SQL Server → lift-and-shift if custom OS or features are required.
  

Document dependencies, SLAs, and downtime tolerance.

2) Sizing and networking
Right-size the target with Azure SQL sizing calculators. Provision networking: ExpressRoute or VPN Gateway for reliable connectivity. Enable private endpoints for secure access. Plan for throughput to avoid bottlenecks during sync.

3) Migration tooling
Use Azure Database Migration Service (DMS) in online mode for large workloads. DMS supports continuous replication from on-prem to Azure, syncing changes in near real time. This reduces downtime at cutover. For simpler workloads, BACPAC export/import or backup/restore to Azure Blob + restore on target may suffice—but not for multi-TB, high-traffic systems.

4) Schema and compatibility
Migrate schema first. Use DMA or SQLPackage to generate schema scripts. Validate that constraints, indexes, and stored procedures translate correctly. Test application connectivity in a staging Azure instance.

5) Continuous data sync
For minimal downtime, run DMS continuous sync. This streams DML (inserts/updates/deletes) from on-prem to Azure until cutover. For ultra-large DBs, consider transactional replication or Log Shipping to stage changes. Run integrity checks (DBCC, checksum) periodically.

6) Cutover process
When ready, schedule cutover in a low-traffic window:

• Freeze writes on on-prem (or put into read-only).
  
• Allow DMS to finalize synchronization.
  
• Validate schema and row counts in Azure.
  
• Switch connection strings / DNS to new Azure target.
  
• Monitor application telemetry to confirm stability.
  

Downtime is reduced to the final sync + DNS propagation—often under an hour.

7) Performance and optimization
After migration, re-evaluate performance: update statistics, rebuild indexes, and tune queries for Azure’s execution engine. Enable Query Store to monitor regressions. Scale compute tiers up/down as needed.

8) Resilience and security
Enable geo-replication or Auto-failover groups for HA/DR. Secure with Azure AD authentication, firewalls, and auditing. Set up Azure Monitor and Log Analytics for telemetry and alerting.

9) Testing and rollback
Before production cutover, run dress rehearsals with smaller databases. Validate application connections and failover. Have a rollback plan: if validation fails, re-enable writes to on-prem and retry.

10) Post-migration
Run health checks, back up in Azure, and document new endpoints. Hand over runbooks and monitoring dashboards. Conduct a post-migration review to capture lessons learned.

Result: By combining assessment, online DMS replication, careful cutover, and optimization, a large SQL Server workload can be moved to Azure with near-zero downtime, preserving business continuity.

‍

Table

‍

Common Mistakes

A frequent mistake is relying on offline migration (backup/restore, BACPAC) for multi-TB systems—leading to hours or days of downtime. Teams often skip DMA assessment, discovering only at cutover that features like SQL Server Agent jobs aren’t supported. Another issue: under-sizing the Azure target or ignoring throughput, causing sync delays. Skipping schema validation leads to broken procs or missing constraints. Some neglect to configure private networking, leaving DBs exposed. Teams sometimes cut over without rehearsals or rollback plans, causing outages. Finally, failing to re-index, update stats, and tune after migration results in poor performance and user complaints.

‍

Sample Answers (Junior / Mid / Senior)

Junior:
“I’d start with Azure Data Migration Assistant to check compatibility, then use Database Migration Service. I’d run it in online mode to sync changes, and when ready, I’d freeze writes, let sync finish, then point apps to the new DB.”

Mid-Level:
“My approach combines DMA for assessment, schema migration with SQLPackage, and DMS online for continuous replication. Networking is via private endpoints. During cutover, I finalize sync, validate counts, and switch DNS. Downtime is minutes.”

Senior:
“I lead with workload assessment (DMA, perf baselines), then target SQL Managed Instance for full feature parity. Migration uses DMS continuous sync across ExpressRoute. We run rehearsals, build rollback playbooks, and use SLO-based cutovers. Post-migration, we optimize via Query Store, configure HA with failover groups, and integrate Azure Monitor for telemetry. This ensures minimal downtime and a repeatable playbook.”

‍

Evaluation Criteria

Interviewers expect a structured migration plan:

• DMA assessment to detect blockers.
  
• Clear reasoning for target choice (MI vs. SQL DB vs. VM).
  
• Use of online DMS or replication for continuous sync.
  
• Schema migration and validation before data move.
  
• Networking (ExpressRoute/VPN, private endpoints).
  
• Controlled cutover with validation, DNS switch.
  
• Rollback playbook.
  
• Post-migration optimization (Query Store, index tuning).
  
• HA/DR setup and monitoring.
  

Weak answers stop at “backup/restore” or omit downtime mitigation. Strong ones highlight replication, testing, rollback, and HA. Bonus for mentioning cost control, automation, and governance.

‍

Preparation Tips

Practice with a lab: deploy a local SQL Server, create a sample DB (~50GB), then migrate to Azure SQL MI using DMS. Run DMA to document blockers. Try both offline and online modes; note downtime differences. Add schema validation (SQLPackage diff). Configure ExpressRoute or simulate VPN to test throughput. Practice cutover: freeze writes, finalize sync, change connection string. Run perf benchmarks pre/post to tune indexes and stats. Configure failover groups and test a simulated region outage. Document steps as a runbook. In interview prep, rehearse a 60-second narrative: “assess with DMA, choose target, replicate with DMS online, cutover in minutes, rollback if needed, optimize after.”

‍

Real-world Context

A healthcare provider migrated a 5TB SQL Server to Azure SQL MI using DMS online. Initial DMA flagged CLR procedures; they refactored before cutover. Continuous replication ran for 2 weeks, syncing deltas. At cutover, downtime was 30 minutes (final sync + DNS). A fintech firm attempted backup/restore of a 3TB DB; downtime exceeded 36 hours. They switched to DMS online + ExpressRoute, cutting downtime to under 1 hour. A retailer migrated to SQL DB but hit feature gaps (cross-db queries). They rolled back and moved to SQL MI instead—highlighting the value of assessment. Across industries, successful migrations share the same DNA: DMA upfront, DMS online for sync, rehearsed cutover, and optimization after.

‍

Key Takeaways

• Always assess with DMA before migration.
  
• Use DMS online for continuous replication and minimal downtime.
  
• Validate schema and run rehearsals before cutover.
  
• Optimize post-migration (indexes, Query Store).
  

Have rollback and HA/DR strategies.

‍

Practice Exercise

Scenario: You’re migrating a 4TB SQL Server with heavy writes to Azure. Business demands downtime <1 hour.

Tasks:

1. Assessment: Run DMA, identify blockers, decide on Azure SQL MI as target.
   
2. Provisioning: Deploy MI with private endpoints over ExpressRoute. Size compute/storage with headroom.
   
3. Schema migration: Apply schema via SQLPackage. Validate objects.
   
4. Continuous sync: Start online migration with DMS. Let it run for a week, syncing ongoing writes.
   
5. Cutover rehearsal: Freeze writes in staging, finalize sync, validate counts, switch DNS. Time downtime.
   
6. Production cutover: Repeat rehearsal steps, ensuring downtime <60 min.
   
7. Rollback plan: If validation fails, revert DNS and unfreeze on-prem.
   
8. Post-migration: Rebuild indexes, update stats, enable Query Store, configure failover group.
   
9. Monitoring: Wire Azure Monitor and Log Analytics for errors/latency.
   

Deliverable: Present a migration runbook + timing metrics proving downtime <1 hr and rollback tested.