4.2 Implementing Online, Offline, and Azure Migrations

DP-300: Administering Microsoft Azure SQL Solutions Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources

2 Planning and Deploying Azure SQL Solutions

2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching

3 Configuring Resources for Scale and Performance

3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression

4 Planning and Implementing a Migration Strategy

4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting

5 Configuring Database Authentication and Authorization

5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management

6 Implementing Security for Data at Rest and in Transit

6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints

7 Implementing Compliance Controls for Sensitive Data

7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL

8 Monitoring Resource Activity and Performance

8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events

9 Monitoring and Optimizing Query Performance

9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights

10 Configuring Database Solutions for Optimal Performance

10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing

11 Creating and Managing SQL Server Agent Jobs

11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates

12 Automating Deployments and Database Tasks in Azure

12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks

13 Planning an HA/DR Strategy and Backup and Restore

13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations

14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups

14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups

15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting

15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions

4.2 Implementing Online, Offline, and Azure Migrations

Key Takeaways Explain This Topic AI

Azure Database Migration Service (DMS) performs offline migrations by restoring a full backup to the target and online migrations by streaming ongoing changes through change data capture until cutover

The Managed Instance link replicates from SQL Server 2016-2022 to SQL Managed Instance with near-zero-downtime cutover and supports fallback to the source during validation

SQL Server Migration Assistant (SSMA) converts schema and migrates data from Access, DB2, MySQL, Oracle, and SAP ASE sources to SQL Server and Azure SQL targets

The Azure Data Studio migration extension wraps DMS into a guided wizard that registers DMA assessments, configures the Integration Runtime, and drives cutover from one pane

Backup-restore lift-and-shift to a storage account URL remains the simplest offline path for SQL Server to Managed Instance or SQL on VM when the maintenance window allows

Last updated: August 2026

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:

Create a DMS instance in the target Azure region.

Create a migration project, choose SQL Server as source and Azure SQL Database Managed Instance (or another target) as target.

Supply source SQL Server credentials and target connection details.

Select the database(s), provide the backup file share (SMB network share or Azure Storage container SAS URL).

DMS reads the full backup, restores it to the target, and marks the activity complete.

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.