Recommend a Solution for Migrating Databases — Lesson

A large international bank prepares its 200-database SQL Server estate for full migration to Azure. The team picks "BACPAC export / import" because it's the option they remember from a tutorial. The first database — a 4 TB transactional system — takes 58 hours to export. By the time it imports, the source has moved ahead by two business days; reconciling the gap takes a week. The architect intervenes: switch to Azure Database Migration Service (DMS) in online mode, which continuously replicates changes from source to target. Cutover for the next database takes $\sim 20$ minutes of read-only window instead of 58 hours. This lesson is about choosing the right database-migration tool for each scenario — DMS online vs offline, Managed Instance link, BACPAC, Data Migration Assistant, log shipping — so the cutover window matches the workload's downtime tolerance.

We will work through Azure's database-migration story the way the AZ-305 exam expects you to: tools (DMS, DMA, MI link, SSMA, native backup/restore, BACPAC, log shipping) and decision criteria (size, downtime tolerance, source / target, schema complexity). Reference: the AZ-305 exam study guide, particularly Chapter 4 Skill 4.3 on database migration.

Why This Matters

Databases are usually the hardest part of any migration. The data has gravity, the cutover window matters, schema compatibility issues hide until they bite at the worst time, and consistency must survive transport without loss. The AZ-305 exam tests this LO heavily because the choice of tool determines whether a database migration is a controlled 30-minute event or a multi-day production incident.

The career payoff is concrete: every database modernisation, every "we need to move this DB to Azure" project, every architecture interview at the senior level touches DB migration. If you can match a source-target pair (SQL Server $\to$ MI, Oracle $\to$ Azure SQL, MongoDB $\to$ Cosmos, MySQL $\to$ Flexible Server) to the right tool and pick online vs offline correctly, you will pass this slice and design DB migrations like a senior architect.

Prerequisites

• Backup/restore in SQL Server. Can you describe full, differential, log backups? — Self-check: which backs up only changed pages?
• Replication concepts. Are you fluent with synchronous vs asynchronous replication? — Self-check: which has zero RPO?
• Compatibility levels. Do you know the difference between database compatibility level and SQL Server version? — Self-check: can a 2012-compat-level DB run on SQL Server 2022?
• Downtime tolerance. Can you frame RTO vs RPO for a cutover? — Self-check: which one is "how recent must data be?"
• DMA basics. Have you used Data Migration Assistant for compatibility checks? — Self-check: does it migrate data or just assess?

If any of these feels shaky, review LO-12, LO-15, LO-27, and LO-41 before continuing with this lesson.

Learning Objectives

By the end of this lesson, you will be able to:

1. Analyse a database workload (engine, size, downtime tolerance, schema complexity) and translate it to the right migration tool.
2. Evaluate trade-offs between DMS (offline / online), MI link, BACPAC, native backup/restore, log shipping, SSMA.
3. Recommend an online vs offline migration based on workload downtime tolerance.
4. Design a hybrid migration topology with MI link for SQL Server-to-MI workloads needing minimal cutover.
5. Recognise anti-patterns — BACPAC for large DBs, offline DMS for tight cutovers, ignoring DMA findings — and rewrite them.
6. Plan the cutover sequence (final replication, downtime window, validation, decommission).

Building Blocks

Azure Database Migration Service (DMS) — Microsoft's managed migration orchestrator. Formally, Microsoft.DataMigration/services resource (classic) plus newer modular versions per source/target. Two modes: offline (one-shot full transfer with downtime) and online (continuous replication with minimal cutover). It matters because DMS is the canonical migration tool for most SQL / open-source DB migrations.

Offline migration — Source quiesced; full data copied; target activated. Downtime $=$ copy time. Suits small DBs and tolerant workloads.

Online migration — Source remains live; initial bulk copy plus continuous replication of changes; cutover is a brief read-only window for the last delta. Downtime $\sim$ minutes regardless of DB size. Suits large or low-downtime workloads.

MI link — A near-real-time link from on-prem SQL Server (with AG) to Azure SQL Managed Instance. Formally, transactional replication using a distributed AG. Designed for migration but also useable for hybrid DR. It matters because MI link gives a one-way replication path with `< $5$-second RPO and minutes-scale cutover for SQL Server $\to$ MI moves.

BACPAC — A portable export format (schema + data) for SQL Server / Azure SQL. Formally, a .bacpac file you export from source and import into target. It matters because BACPAC is the simplest manual path for small DBs — but it is offline-only and slow for large data.

Native backup / restore — Standard SQL Server BACKUP + RESTORE between source and target. For Azure SQL MI, supported via RESTORE FROM URL from a blob with .bak files. Effectively offline.

Log shipping — Asynchronous replication via transaction log backups. Formally, source generates log backups, target restores them. Largely superseded by MI link and AG, but still in use.

Data Migration Assistant (DMA) — A pre-migration assessment tool (covered in LO-41) that surfaces compatibility blockers per database. Also has limited data-migration capability for small DBs.

SQL Server Migration Assistant (SSMA) — Schema + data migration tool for non-Microsoft sources (Oracle, MySQL, Postgres, DB2). Converts PL/SQL to T-SQL and ports data.

Compatibility level — A SQL Server property that determines which SQL features the database supports. Azure SQL DB / MI supports compat levels from $100$ up; very old DBs (compat $\le 90$) need upgrade before migration.

Deep Dive

0. The migration lifecycle

Every database migration follows the same lifecycle, regardless of tool. Recognising the phases helps spot where a project is stuck.

The exam tests both individual phases and the lifecycle as a whole — the right answer to "which tool?" sometimes depends on which phase the customer is in.

1. Tool selection — by source and target

[!TIP] When the source is Microsoft SQL Server and downtime is tight, default to MI link (target MI) or DMS online (target DB or MI). Avoid BACPAC for any DB over a few hundred GB.

2. Online vs offline — the cutover question

[!IMPORTANT] Don't pick online "because it sounds modern". Online has more moving parts and longer total project time; offline is simpler when downtime is acceptable.

3. Size and cutover trade-offs — practical heuristics

A useful rule of thumb table for time-pressed architects:

The line "online recommended" shifts based on the workload's downtime budget. A read-mostly catalogue might tolerate $4$ hours offline at $500$ GB; a high-write OLTP system might demand $\le 5$ minutes regardless of size.

4. DMS — modes and prerequisites

DMS online requires CDC (Change Data Capture) on the source for some configurations, network connectivity over Private Endpoint or VPN, and target schema pre-created (via DMA or scripts). The flow:

[!WARNING] DMS online for some scenarios requires CDC; for others uses log-reading. Confirm via the DMS prerequisites checklist for your specific source/target pair before assuming the source schema is ready.

5. MI link — the SQL-Server-to-MI specialty

MI link is a Microsoft-supported one-way replication from an on-prem SQL Server $2016+$ instance (configured as an AG) to an Azure SQL MI. Built on distributed availability groups, it replicates continuously with $\le 5$-second RPO.

Cutover with MI link is the cleanest of all SQL paths: a brief read-only window while the final delta replicates, then the application points at MI.

6. Cosmos and open-source DB migrations

For Cosmos DB, the question is also which API to target — pick to match the source (MongoDB-source $\to$ MongoDB API), unless you're rebuilding (then NoSQL API is the modern default).

11. Cutover-day checklist

7. Network and bandwidth planning

Database migrations move large volumes of data over the wire. The exam tests recognising the bandwidth math.

[!TIP] The math is simple: $1$ Gbps roughly equals $\sim 450$ GB/hour effective (accounting for protocol overhead and bursts). When the source-to-Azure pipe is narrow, online replication will struggle on initial bulk copy regardless of the tool. Consider Azure Data Box for the initial bulk seed and switch to online replication for the delta.

8. Schema conversion and code rewrites

For SQL-Server-to-Azure-SQL migrations within the Microsoft stack, schema is usually portable. For non-Microsoft sources, schema conversion is non-trivial.

SSMA produces a per-object compatibility report. Objects fall into three categories: convertible automatically, convertible with manual fix-up, manual conversion required. Plan effort accordingly.

9. Validation strategies

Every migration needs post-cutover validation. The four standard checks:

[!WARNING] Row counts alone are not enough. Two tables can have identical row counts and different data (e.g., if a row was updated during replication catch-up). Always include hash or checksum comparison for tier-$1$ tables.

10. Common cutover-day surprises

[!IMPORTANT] Document and run a cutover dry-run before the real day. The dry-run surfaces all the above except the last (which requires identity changes that may be irreversible without prep).

12. Identity, security, and post-migration hardening

Database security in Azure shifts from network-perimeter ("trust anyone in the corporate VLAN") to identity-and-private-link. The migration is the right moment to align with the modern posture.

[!TIP] Migration is the cheapest moment to make these security upgrades. The team is already touching connection strings and access patterns. Bolting on Entra ID auth after migration costs $5{-}10\times$ as much labour.

13. Cost optimisation for migrated databases

Database cost dominates Azure spend for many migrated estates. Levers to apply at migration time:

The exam often pairs migration with sizing; check that the proposed migration plan also right-sizes the target instead of cloning the on-prem allocation.

Worked Examples

Easy — pick the tool

Problem. A $200$ GB SQL Server $2019$ database needs to move to Azure SQL DB. Cutover window: $4$ hours. Recommend a tool.

Solution. DMS offline. $200$ GB transfers in well under $4$ hours on a properly sized DMS instance. Online would also work but adds complexity unnecessary for this downtime budget. Run DMA first to confirm Azure SQL DB compatibility.

Medium — large DB, tight window

Problem. A $5$ TB SQL Server transactional DB must move to Azure SQL MI with under $30$ minutes of read-only time. Recommend.

Solution. MI link. Configure the source SQL Server's AG with a distributed AG to the target MI. Replicate continuously for days; at cutover, stop writes, wait for catch-up ($\sim$ minutes), fail over. Total cutover window: $\sim 15{-}30$ minutes regardless of DB size.

Hard — Oracle migration with schema conversion

Problem. A $1.5$ TB Oracle database with PL/SQL functions must move to Azure SQL MI. Recommend.

Solution. Use SSMA for Oracle to convert the schema and migrate code (PL/SQL $\to$ T-SQL). For data, SSMA can move smaller volumes; for $1.5$ TB, use SSMA's bulk-copy or external ETL (Azure Data Factory copy activity with parallelism). Plan a cutover window proportional to data size, or stage data in advance and run a final delta.

14. Hybrid / coexistence patterns

A real migration rarely cuts everything over on one day. Three common hybrid patterns:

Phased per-database. Move databases in waves. Apps switch connection strings per wave. Risk: app dependencies on multiple DBs may force coordinated cutovers.

Read-replica preview. Stand up an Azure copy as a read replica; gradually shift read-heavy queries; cutover writes last. Works for analytics or reporting workloads.

Dual-write transition. App writes to both source and target during a transition window. Rare; brittle; only used for very critical cutovers with tight RPO.

For most workloads the MI link path achieves the cleanest single-cutover transition while still allowing weeks of replication before the actual switch.

Visual Explanations

Figure 1 — Tool decision

Figure 2 — Online migration topology

Figure 3 — Quick chooser

Figure 4 — Tool feature comparison

Two more practical notes:

Resource sizing for DMS. DMS itself is a managed service, but it needs adequate compute and network bandwidth to keep up with the replication rate of busy sources. For high-throughput sources, use the Premium SKU and ensure source-to-DMS bandwidth matches expected change volume.

Replication health monitoring. Watch the lag metric. A growing lag during initial bulk means the target tier is undersized; lag during ongoing replication means the network or DMS instance is overwhelmed. Both surface in AzureDiagnostics for Microsoft.DataMigration.

Common Mistakes

❌ Myth: "BACPAC is the standard SQL Server migration path." ✅ Reality: BACPAC is offline and slow. Use it only for small DBs with hours of available downtime. Production migrations of any size prefer DMS or MI link.

❌ Myth: "Online migration is always better." ✅ Reality: Online migration adds prerequisites, replication concerns, and a longer total project time. Use online only when downtime tolerance demands it.

❌ Myth: "Skip DMA — DMS will catch incompatibilities." ✅ Reality: DMS migrates; DMA assesses. Running DMA first surfaces blockers before you waste replication time on an incompatible target.

❌ Myth: "MI link is just MI's version of DMS online." ✅ Reality: MI link is built on distributed AGs and gives a stronger consistency model and lower cutover overhead. It's worth choosing over DMS online when the source is SQL Server $\to$ MI.

Practice Exercises

🟢 Exercise 1. $50$ GB SQL Server $2017$ $\to$ Azure SQL DB. $2$-hour cutover OK.

▶✅ Solution

DMS offline (or BACPAC for the simplest path). DMA pre-check first.

🟡 Exercise 2. $3$ TB SQL Server $\to$ Azure SQL MI. $15$-minute cutover required.

▶✅ Solution

MI link. Configure AG + distributed AG; replicate days in advance; cutover by failover.

🟡 Exercise 3. $500$ GB Oracle $\to$ MI. Cutover window $\sim 6$ hours.

▶✅ Solution

SSMA for Oracle for schema conversion and migration. Plan cutover window proportional to data volume; pre-stage if needed.

🔴 Exercise 4. $10$ MongoDB databases ($\sim 100$ GB each) $\to$ Cosmos DB for MongoDB. Cutover window: $1$ hour.

▶✅ Solution

DMS for MongoDB (online mode). Replicate continuously; cutover during the $1$-hour window. Validate using application connection-string switching.

🔴 Exercise 5. $20$ MySQL DBs need to move. Mixed sizes ($1$ GB to $1$ TB). Cutover: per-DB negotiable.

▶✅ Solution

DMS online for the large DBs (tight cutover); DMS offline for the small. Run DMA equivalent (mysql_upgrade_check) for compatibility. Sequence by size — start large DBs replicating first while small DBs cutover quickly.

🟢 Exercise 6. True or false: BACPAC migrates data and ongoing changes.

▶✅ Solution

False. BACPAC is a one-shot export-import. Ongoing changes require DMS online or MI link.

🟡 Exercise 7. Sketch the SQL to enable CDC on a source table before DMS online migration.

▶✅ Solution

sqlCopy

USE [OrdersDB]; EXEC sys.sp_cdc_enable_db; EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Orders', @role_name = NULL;

Summary & Concept Map

• Pick the tool by source / target / downtime tolerance. SQL $\to$ MI, low downtime: MI link. SQL $\to$ DB: DMS online. Oracle: SSMA.
• Online migrations for tight cutovers; offline for tolerant cutovers.
• Pre-migration assessment (DMA / SSMA) is mandatory.
• MI link delivers the cleanest SQL-Server-to-MI cutover.
• BACPAC is the right answer only for small DBs with tolerant cutovers.
• Plan the cutover sequence carefully — final delta, repoint, smoke test, decommission.