Migration Strategy — Planning & Execution
Quick Reference: Migration at a Glance
| Question | Answer |
|---|---|
| Smallest downtime? | DMS Online, MI Link, Log Replay Service |
| Simplest method? | BACPAC (offline, complete copy) |
| Oracle → Azure? | SSMA assessment → DMS migration |
| Largest databases? | MI Link (streaming), Log Replay (backup chain) |
| Azure SQL DB target? | BACPAC, DMS, Transactional Replication |
| MI target? | MI Link, LRS, DMS, Backup to URL |
| VM target? | Backup/Restore, AG seeding, Log Shipping |
| Assessment first? | Always. Use DMA (SQL→Azure) or SSMA (Oracle→Azure) |
Migration Decision Framework
Migration Decision Framework
Assess
Azure Migrate discovery
DMA compatibility check
SSMA for Oracle/MySQL
Choose Target
SQL Database (PaaS, no admin)
Managed Instance (near 100% compat)
SQL VM (full control)
Online vs Offline
Offline: simple, hours downtime
Online: complex, minutes downtime
Execute & Validate
Schema + data migration
Post-migration validation
Performance baseline
Online vs Offline Migration
| Aspect | Offline | Online |
|---|---|---|
| Downtime | Hours to days | Minutes |
| Complexity | Low | Medium-High |
| Data loss risk | None (full copy) | Near-zero (continuous sync) |
| Best for | Small DBs, dev/test, planned maintenance windows | Production, large DBs, 24/7 availability |
| Tools | BACPAC, backup/restore, BCP, DMA | DMS, MI Link, Transactional Replication, Log Replay |
🎯 Exam Focus
Exam frequently asks: "Which migration method provides minimal downtime?" → Answer is always an online method (DMS online mode, MI Link, Log Replay Service, Transactional Replication). "Acceptable downtime" → offline methods are simpler.
Migration Tools — Complete Reference
Assessment Tools
| Tool | What It Does | Source → Target |
|---|---|---|
| Azure Migrate | Discovers and assesses on-prem databases | Any → Azure |
| Data Migration Assistant (DMA) | Compatibility check + feature parity | SQL Server → Azure SQL |
| Azure SQL Migration extension (Azure Data Studio) | Assessment + SKU recommendation + migration | SQL Server → Azure SQL DB/MI/VM |
| SSMA (SQL Server Migration Assistant) | Schema + data conversion | Oracle/MySQL/Access → SQL Server/Azure SQL |
🏢 Real-World DBA Note
Oracle DBA parallel: SSMA is your primary tool for Oracle → Azure SQL. It converts Oracle schemas, PL/SQL to T-SQL, and data types. Run the assessment first — it flags conversion issues (autonomous transactions, varrays, Oracle-specific types). Plan for manual fixes on ~10-20% of complex PL/SQL.
Migration Methods by Target
Migration Methods by Target
Azure SQL Database
BACPAC, DMS (online/offline), Transactional Replication, BCP
SQL Managed Instance
MI Link, Log Replay Service (LRS), DMS, Backup/Restore to URL
SQL Server on VM
Backup/Restore, Detach/Attach, Log Shipping, Always On AG seeding
Key Migration Methods — Architecture
BACPAC (Azure SQL Database)
BACPAC Workflow
Export
Source must be quiesced
Schema + data exported to .bacpac file
Upload
Upload .bacpac to Azure Blob Storage
Or export directly via SqlPackage
Import
Import via Portal, SqlPackage, or PowerShell
Creates new database on target server
Validate
Recreate logins (not preserved)
Verify schema, data, and permissions
Key facts:
- Contains both schema AND data (unlike DACPAC which is schema-only)
- Offline only — source must be quiesced during export
- Max import size: depends on DTU/vCore tier
- Does NOT preserve SQL logins — must recreate on target
Database Migration Service (DMS)
DMS Online Migration
Initial Full Load
Full snapshot of source database
Bulk copy to target Azure SQL
Continuous CDC Sync
Change Data Capture tracks ongoing changes
Incrementally applies to target
Cutover
Stop application writes to source
Apply final changes, switch connection strings
Supports both online and offline modes:
- Offline: Full copy, then switch
- Online: Initial load + continuous Change Data Capture → cutover when ready
- Requires Azure DMS instance in the same VNet as target (for MI)
MI Link (Managed Instance)
MI Link Replication
On-Prem Primary
SQL Server 2016+ remains primary
Distributed AG technology
Near Real-Time Sync
Continuous log replication to MI
MI secondary is readable
Break Link & Cutover
MI becomes new primary
No downtime cutover
Why MI Link is powerful:
- Near real-time replication from on-prem to MI
- Secondary (MI) is readable during migration — offload reporting
- No downtime cutover — when ready, break the link and MI becomes primary
- Uses Distributed Availability Group technology
🎯 Exam Focus
MI Link is the recommended migration path for SQL Server → Managed Instance when you need minimal downtime. It supports SQL Server 2016+. For SQL Server 2012/2014, use Log Replay Service or DMS instead.
Log Replay Service (LRS) — MI Only
Log Replay Service (LRS)
Backup Chain
Full + differential + transaction log backups
From on-prem SQL Server 2012+
Upload to Blob
Upload .bak + .trn files to Azure Blob
MI reads from Blob container
LRS Replays
MI applies backup files in order
Database stays in Restoring state
Complete Restore
Autocomplete when last log applied
Database comes online on MI
Key facts:
- Upload backup files to Azure Blob → LRS replays them on MI
- MI stays in Restoring state until you complete the restore
- Supports autocomplete mode (finishes when last log is applied)
- Good for SQL Server 2012+ when MI Link isn't available
Table Partitioning
Splits a large table into smaller physical segments based on a column value (usually date).
Table Partitioning Components
Partition Function
Defines boundary values (e.g., date ranges) that split data into partitions
Partition Scheme
Maps each partition to a filegroup for physical storage placement
Partitioned Table
Created on the scheme. Queries benefit from partition elimination — only scan relevant partitions
When to partition:
- Tables with 100+ million rows
- Queries consistently filter by the partition column (date ranges)
- Archival requirements (switch out old partitions)
- Available on: SQL DB (vCore), MI, SQL VM (not DTU Basic/Standard)
Architecture components:
- Partition Function — defines boundary values (e.g., date ranges)
- Partition Scheme — maps partitions to filegroups
- Partitioned Table — created on the partition scheme
🎯 Exam Focus
Partition elimination is the key performance benefit — SQL Server only scans partitions that match the query filter. If the exam asks "how to improve query performance on a 500M row table filtered by date" → table partitioning.
Data Compression
Reduces storage size and improves I/O performance (fewer pages to read).
| Type | Compression | Performance Impact | Best For |
|---|---|---|---|
| Row | ~15-25% | Minimal CPU overhead | OLTP tables with many NULLs/short values |
| Page | ~60-75% | Moderate CPU overhead | Read-heavy/archive tables |
| Columnstore | ~90%+ | Significant savings | Analytics/data warehouse |
Architecture insight: Compression works at the page level. When SQL Server reads a compressed page, it decompresses in memory. The tradeoff: less I/O (huge win) vs more CPU (usually acceptable).
🎯 Exam Focus
Key exam fact: Use sp_estimate_data_compression_savings to estimate savings before applying. Page compression gives the best ratio for most workloads. Columnstore compression is for analytics tables only.
SQL Data Sync
Bi-directional sync between Azure SQL databases OR between Azure SQL and on-prem SQL Server.
SQL Data Sync — Hub-Member Topology
Hub Database (Azure SQL)
Central database that coordinates sync. Always an Azure SQL Database.
Member Databases
Azure SQL DBs or on-prem SQL Servers (via sync agent). Bi-directional sync.
Use cases: Distributed data distribution, hybrid sync. NOT for migration — for ongoing synchronization.
Post-Migration Validation Checklist
| Check | What to Verify |
|---|---|
| Schema | All objects migrated (tables, views, SPs, functions, triggers) |
| Data | Row counts match, checksums validate, sample data spot-check |
| Performance | Query plans are good, no regressions, update statistics |
| Security | Logins recreated, permissions correct, TDE enabled |
| Connectivity | App connection strings updated, firewall/PE configured |
| Compatibility | ALTER DATABASE SET COMPATIBILITY_LEVEL matches source if needed |
Tool Selection: "When NOT to Use This Tool"
| Tool | Do NOT use when... |
|---|---|
| BACPAC | DB > 200 GB (slow export), need minimal downtime, target is MI (use backup/restore instead) |
| DMS Offline | Zero downtime required — use DMS Online mode instead |
| DMS Online | Source is Oracle — use SSMA + DMS, not DMS alone |
| MI Link | Source is SQL 2012/2014 — requires SQL 2016+. Use LRS instead. |
| Log Replay Service | Need read access during migration — MI stays in Restoring mode until cutover |
| SSMA | Source is SQL Server (not Oracle/MySQL) — use DMA instead |
| Backup/Restore to URL | Target is Azure SQL DB (doesn't support native restore) — use BACPAC or DMS |
Migration Anti-Patterns
| Anti-Pattern | Why It Fails | What to Do Instead |
|---|---|---|
| Skip assessment | Discover blocking issues in production cutover | Always run DMA/SSMA assessment first |
| Test in dev, deploy in prod | Performance differs at scale | Test with production-size data and workload |
| Migrate schema and data separately with manual scripts | Error-prone, inconsistent | Use DMS or BACPAC for atomic migration |
| Ignore compatibility level | New optimizer behavior causes plan regressions | Keep source compat level, upgrade gradually with Query Store |
| Migrate on Friday evening | Weekend outage if problems arise | Migrate early in the week with rollback window |
| No rollback plan | Stuck if migration fails | Keep source running until validation complete |
| Single massive BACPAC | Timeout on large databases | Split into smaller databases or use online migration |
Common Post-Migration Mistakes
| Mistake | Impact | Fix |
|---|---|---|
| Forgetting to update statistics | Query optimizer uses stale stats → bad plans | Run sp_updatestats or full statistics update after migration |
| Not rebuilding indexes | Fragmented indexes from bulk insert during migration | Run index rebuild on all tables post-migration |
| Leaving old compatibility level forever | Miss IQP improvements (batch mode, adaptive joins) | Upgrade compat gradually with Query Store monitoring |
| Not testing under load | Works in test, fails at production concurrency | Load test with realistic concurrent users before cutover |
| Ignoring login/user mapping | CREATE USER on target but orphaned SIDs | Use ALTER USER ... WITH LOGIN to fix orphaned users |
| No performance baseline | Can't tell if post-migration performance is normal | Capture Query Store baseline before and after migration |
🎯 Exam Focus
Common exam trap: "After migrating to Azure SQL DB, queries are slower than on-prem." The answer almost always involves: 1) Update statistics, 2) Check compatibility level, 3) Review Query Store for plan regressions, 4) Verify correct service tier (not under-provisioned).
Oracle → Azure Migration Mindset
If you're coming from Oracle, these mental shifts help during migration planning:
| Oracle Thinking | Azure SQL Reality |
|---|---|
| "I'll export with Data Pump" | Use SSMA for schema conversion, DMS for data migration |
| "I need RAC for HA" | Business Critical tier gives you 4-node AG automatically |
| "Data Guard handles DR" | Failover Groups with auto-failover and single endpoint |
| "I'll tune the instance (SGA, PGA)" | You tune the workload (queries, indexes, tier selection) |
| "I need to manage backups" | Backups are automatic (PaaS). You just set retention. |
| "PL/SQL stored procedures" | Convert to T-SQL via SSMA. Plan for 10-20% manual fixes. |
| "Oracle Wallet for encryption" | TDE is ON by default. Key Vault for BYOK. |
| "I'll set up the OS and install SQL" | Choose PaaS (SQL DB/MI) unless you need full OS access |
Flashcards
Online vs Offline migration — key difference?
Click to reveal answer
Offline = downtime required (BACPAC, backup/restore). Online = near-zero downtime (DMS online, MI Link, Log Replay Service, Transactional Replication).
1 / 9
Quiz
A company needs to migrate a 500 GB SQL Server database to Azure SQL MI with minimal downtime. Which method is recommended?