4.2 Implementing Online, Offline, and Azure Migrations

Azure Database Migration Service (DMS)

Azure Database Migration Service (DMS) is the primary Microsoft tool for moving SQL Server databases to Azure SQL Database, SQL Managed Instance, and SQL Server on Azure VMs. It is a region-scoped Azure resource that runs a migration worker (the DMS agent) which connects to the source, reads the backup files or the transaction log, and writes to the target. DMS supports both offline and online modes from the same wizard - the mode is a property of the migration activity, not a separate tool.

Offline Mode

An offline DMS migration takes a full backup of the source, restores it to the target, and performs cutover when the restore completes. The source is unavailable for the duration. Use offline when the database is small enough that the restore fits in the maintenance window, when there is no requirement for continuous application availability, or when the cost and complexity of setting up replication is not justified. The steps:

1. Create a DMS instance in the target Azure region.
2. Create a migration project, choose SQL Server as source and Azure SQL Database Managed Instance (or another target) as target.
3. Supply source SQL Server credentials and target connection details.
4. Select the database(s), provide the backup file share (SMB network share or Azure Storage container SAS URL).
5. DMS reads the full backup, restores it to the target, and marks the activity complete.
6. Point applications at the target and decommission the source.

Online Mode

An online DMS migration starts with the same full backup restore, then keeps the target synchronized by streaming the transaction log from the source - on Managed Instance via backup file restore plus log replay, on Azure SQL Database through SQL Server Replication (transactional) where the source acts as publisher and the target as subscriber. The source stays online and writable throughout. When the target catches up, you perform a cutover: stop application writes to the source, let the final delta drain, verify row counts and critical queries, then repoint connections. Downtime is the cutover duration - typically minutes - not the size-of-data copy.

The exam trap to remember: online migrations to Azure SQL Database use transactional replication (snapshot + ongoing deltas), not CDC directly, and require the source database to be configured as a publisher. Online migrations to SQL Managed Instance use the backup-restore-plus-log-replay path, which is why MI online migrations support larger databases and have fewer source-side prerequisites than Azure SQL Database online migrations.

Managed Instance Link for Near-Zero-Downtime

The Managed Instance link is the newest and most capable online path for SQL Server-to-MI migrations. It establishes a hybrid replication channel between an on-premises or VM-based SQL Server 2016, 2017, 2019, or 2022 instance and an Azure SQL Managed Instance, continuously replicating the database(s) to the managed instance while the source stays fully online and writable. The link is built on distributed availability group technology, so the MI side is a readable secondary.

Key properties the exam tests:

• Near-zero-downtime cutover: at cutover you fail over from the primary (source) to the secondary (MI). The final delta drains in seconds, and the only application downtime is the reconnect window.
• Fallback: during validation you can fail back to the source if a problem is discovered, because the source retains its role and is kept in sync until you sever the link. This is the single biggest operational advantage over DMS online, which has no easy fallback.
• Prerequisites: the source must be Enterprise edition (Standard edition is supported only for limited link scenarios depending on version), the database must be in full recovery model, and the source must be at a supported SQL Server version with the latest cumulative update.
• One-way replication: data flows source-to-MI only; the source remains the primary until cutover. Do not write to the MI replica before cutover.

SQL Server Migration Assistant (SSMA)

SQL Server Migration Assistant (SSMA) is the tool for non-SQL-Server sources: Microsoft Access, DB2, MySQL, Oracle, and SAP ASE (Sybase). SSMA converts the source schema to SQL Server or Azure SQL syntax, reports objects that require manual conversion, and migrates the data in bulk. It is not used for SQL Server-to-Azure SQL migrations - that is DMS or the Azure Data Studio migration wizard. A scenario that asks about moving an Oracle schema to Azure SQL points at SSMA, not DMS.

SSMA produces a conversion report categorizing objects as Automatic, Manual, or Warning. Automatic objects convert cleanly; Manual objects need engineer intervention (often stored procedures using Oracle-specific packages); Warning objects convert with caveats that should be reviewed. After schema conversion, SSMA loads the schema to the target and runs a data migration that streams rows in batches; large tables can be parallelized.

Azure Data Studio Migration Wizard

The Azure Data Studio (ADS) migration extension is the GUI that wraps DMS into a guided flow. From ADS, an engineer can run a DMA assessment against the source, pick a target, register the self-hosted Integration Runtime, configure DMS, start the migration activity, and trigger cutover - all without leaving the editor. The wizard enforces the assessment-before-migration discipline and surfaces the cutover checklist. It supports SQL Server-to-Azure SQL Database, SQL Server-to-MI, and SQL Server-to-SQL-on-VM paths. The exam often treats ADS migration as the modern recommended path because the wizard guides best practices; raw DMS via the portal remains available but is less opinionated.

Backup-Restore Lift-and-Shift

The simplest offline path is backup-and-restore to an Azure Storage account: take a BACKUP DATABASE to a URL using a SAS token, then RESTORE DATABASE FROM URL on the target (SQL Managed Instance or SQL Server on VM). This path needs no DMS resource and no Integration Runtime, just network access from the target to the storage account. It suits one-time migrations with a permissive maintenance window, and is also the standard disaster-recovery-to-Azure pattern. The caveats: the source must be at a version and edition compatible with the target restore (MI requires the source backup be taken with CHECKSUM enabled and MAXTRANSFERSIZE adjusted for large databases), and the storage account should be in the same region as the target to avoid egress charges and latency.

Post-Migration Validation and Cutover

Every implementation path ends with the same cutover and validation sequence. The exam expects you to know the steps in order and the common traps at each:

1. Pre-cutover validation: confirm row counts on critical tables, run a representative set of queries against the target, and validate that DMA blockers were remediated. If the Managed Instance link was used, the target is a readable secondary, so validation queries can run without disruption.
2. Cutover: stop application writes to the source (typically by disabling the application or changing the connection string secret), wait for the final delta to drain (for online paths), then update the connection string to point at the target. With the MI link, cutover is a planned failover that promotes the MI replica.
3. Post-cutover checks: re-run validation queries, monitor Query Store for regressions, check tempdb configuration, and confirm logins and permissions work as expected (see the next section for the orphaned-user trap).
4. Source decommissioning: keep the source read-only for a defined rollback period (often 1-2 weeks) before decommissioning. With the MI link, the source can stay in sync if the link is not severed, which simplifies rollback but adds storage cost.

Implementation Decision Matrix

A frequent exam trap: the Azure Data Studio migration extension and DMS are not mutually exclusive - the ADS extension orchestrates DMS under the hood. The exam distinguishes between the orchestration tool (ADS wizard) and the migration engine (DMS) - know which is which when a scenario describes the workflow.