Lab: Every SQL Migration Scenario — Hands-On

Execute every major migration path to Azure SQL: backup/restore to VM, BACPAC to SQL DB, DMS (offline) to SQL DB, Managed Instance Link, Log Replay Service, and transactional replication. Compare downtime, complexity, and when to use each.

DP-300 Domain: 1 — Plan and Implement Data Platform Resources (20-25%)

🏢 Real-World DBA Note

Customers always ask: "What's the best way to migrate?" The answer is always "it depends." This lab lets you execute every method and build real experience choosing the right one for each scenario.

Migration Methods Overview

6 Migration Paths

📦

Scenario 1: Backup → Restore

On-prem → Azure SQL VM

Offline, simplest method

Downtime: hours

Max DB size: unlimited

📋

Scenario 2: BACPAC

On-prem → Azure SQL DB

Offline, schema + data export

Downtime: hours

Max practical: ~200 GB

🔄

Scenario 3: DMS (Offline)

On-prem → Azure SQL DB

Offline, one-time migration

Downtime: hours

Note: online not supported for SQL DB

🔗

Scenario 4: MI Link

On-prem → SQL Managed Instance

Online (AG-based), near-zero downtime

Downtime: seconds (cutover)

Best for: large MI migrations

📄

Scenario 5: Log Replay (LRS)

On-prem → SQL Managed Instance

Online, backup-chain streaming

Downtime: minutes (cutover)

Cost: only storage for backups

🔀

Scenario 6: Transactional Replication

On-prem → Azure SQL DB

Online, real-time replication

Downtime: seconds (DNS flip)

Good for: selective table migration

Architecture

Migration scenarios lab architecture: source SQL on VM → 6 migration methods (Backup/Restore, BACPAC, DMS, MI Link, LRS, Replication) → SQL VM, SQL DB, SQL MI targets

Decision Matrix

Method	Target	Downtime	Complexity	Max DB Size	Cost
Backup → Restore	SQL VM	Hours	⭐ Low	Unlimited	$
BACPAC	SQL DB	Hours	⭐ Low	~200 GB practical	$
DMS (Offline)	SQL DB	Hours	⭐⭐ Medium	4 TB	$$
MI Link	SQL MI	Seconds	⭐⭐ Medium	16 TB	$$$
Log Replay (LRS)	SQL MI	Minutes	⭐⭐ Medium	16 TB	$
Transactional Replication	SQL DB	Seconds	⭐⭐⭐ High	4 TB	$$

Prerequisites

Azure subscription
Azure CLI installed
PowerShell 5.1+
SqlPackage installed (for BACPAC)
Azure Database Migration Service (for DMS scenario)

Step 0: Deploy Shared Lab Environment

All scenarios use the same source VM. Deploy once, reuse for all:

$resourceGroup = "rg-migration-lab"
$location = "westeurope"
$suffix = Get-Random -Maximum 9999
$adminUser = "sqladmin"
$adminPassword = "<YOUR_STRONG_PASSWORD>"

# Create resource group
az group create --name $resourceGroup --location $location

# Source VM — on-prem simulation
az vm create `
    --resource-group $resourceGroup `
    --name sqlvm-source `
    --image MicrosoftSQLServer:sql2022-ws2022:sqldev-gen2:latest `
    --size Standard_D2ads_v6 `
    --admin-username azureadmin `
    --admin-password $adminPassword `
    --location $location `
    --public-ip-sku Standard

# Wait for VM, then install AdventureWorks
# RDP into sqlvm-source and run:

# On sqlvm-source — download and restore AdventureWorks
$bakUrl = "https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2022.bak"
$bakPath = "C:\Temp\AdventureWorks2022.bak"
New-Item -Path "C:\Temp" -ItemType Directory -Force
Invoke-WebRequest -Uri $bakUrl -OutFile $bakPath

# Restore
Invoke-Sqlcmd -Query @"
RESTORE DATABASE [AdventureWorks2022]
FROM DISK = '$bakPath'
WITH MOVE 'AdventureWorks2022' TO 'F:\SQLData\AdventureWorks2022.mdf',
     MOVE 'AdventureWorks2022_log' TO 'F:\SQLLog\AdventureWorks2022_log.ldf',
     REPLACE;
"@ -ServerInstance "localhost"

# Set FULL recovery model (required for MI Link and LRS)
Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2022] SET RECOVERY FULL;" -ServerInstance "localhost"

# Take a full backup (required baseline for LRS)
Invoke-Sqlcmd -Query @"
BACKUP DATABASE [AdventureWorks2022]
TO DISK = 'C:\Temp\AdventureWorks2022_full.bak'
WITH COMPRESSION, INIT;
"@ -ServerInstance "localhost"

Scenario 1: Backup → Blob → Restore (to Azure SQL VM)

The simplest migration. Your existing SQL Migration Lab covers this in detail. Here's the summary:

Backup on source

BACKUP DATABASE to disk (compressed)

Upload to Blob

AzCopy the .bak to a storage account container

Download on target VM

AzCopy from blob to target F:\Temp

Restore on target

RESTORE DATABASE with MOVE to correct file paths

Validate

Compare row counts between source and target

# Create target VM
az vm create `
    --resource-group $resourceGroup --name sqlvm-target `
    --image MicrosoftSQLServer:sql2022-ws2022:sqldev-gen2:latest `
    --size Standard_D2ads_v6 `
    --admin-username azureadmin --admin-password $adminPassword `
    --location $location --public-ip-sku Standard

# Create storage account for transfer
$storageAcct = "stmigrate$suffix"
az storage account create -g $resourceGroup -n $storageAcct -l $location --sku Standard_LRS
az storage container create --account-name $storageAcct --name backups

-- On source: backup to local disk
BACKUP DATABASE [AdventureWorks2022]
TO DISK = 'C:\Temp\AW2022.bak'
WITH COMPRESSION, INIT;

-- Upload via azcopy, download on target, then:
-- On target:
RESTORE DATABASE [AdventureWorks2022]
FROM DISK = 'F:\Temp\AW2022.bak'
WITH MOVE 'AdventureWorks2022' TO 'F:\SQLData\AdventureWorks2022.mdf',
     MOVE 'AdventureWorks2022_log' TO 'F:\SQLLog\AdventureWorks2022_log.ldf',
     REPLACE;

Metric	Value
Downtime	Full backup + transfer + restore time (hours for large DBs)
RPO	Zero (consistent backup)
Complexity	Low — just backup and restore
When to use	Small-medium DBs, acceptable downtime, SQL VM target

Scenario 2: BACPAC Export → Import (to Azure SQL DB)

Export BACPAC

SqlPackage exports schema + data to .bacpac file

Upload to Blob

Upload .bacpac to storage account

Import to Azure SQL DB

Import via Portal, CLI, or SqlPackage

Validate schema + data

Compare object counts and row counts

# Export BACPAC from source (run on sqlvm-source or any machine with SqlPackage)
SqlPackage /Action:Export `
    /SourceServerName:"sqlvm-source-ip,1433" `
    /SourceDatabaseName:"AdventureWorks2022" `
    /SourceUser:"sqladmin" `
    /SourcePassword:"$adminPassword" `
    /TargetFile:"C:\Temp\AdventureWorks2022.bacpac"

# Upload to blob storage
az storage blob upload `
    --account-name $storageAcct `
    --container-name backups `
    --file "C:\Temp\AdventureWorks2022.bacpac" `
    --name "AdventureWorks2022.bacpac"

# Create Azure SQL DB server
$dbServer = "sql-migrate-$suffix"
az sql server create -g $resourceGroup -n $dbServer `
    --admin-user sqladmin --admin-password $adminPassword -l $location

$myIp = (Invoke-RestMethod -Uri https://api.ipify.org)
az sql server firewall-rule create -g $resourceGroup --server $dbServer `
    --name AllowMyIP --start-ip-address $myIp --end-ip-address $myIp

# Import BACPAC
$storageKey = az storage account keys list -g $resourceGroup -n $storageAcct --query '[0].value' -o tsv

az sql db import -g $resourceGroup -s $dbServer -n AdventureWorks2022 `
    --storage-key-type StorageAccessKey `
    --storage-key $storageKey `
    --storage-uri "https://$storageAcct.blob.core.windows.net/backups/AdventureWorks2022.bacpac" `
    --admin-user sqladmin --admin-password $adminPassword

⚠️ Watch Out

BACPAC limitations: the source must be quiesced (no active transactions) during export. For databases > 200 GB, export can be very slow. There's no incremental — it's always a full export. For large DBs, use DMS instead.

Validate:

-- On Azure SQL DB
SELECT
    s.name AS [Schema],
    t.name AS [Table],
    SUM(p.rows) AS [RowCount]
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0, 1)
GROUP BY s.name, t.name
HAVING SUM(p.rows) > 0
ORDER BY [RowCount] DESC;

Metric	Value
Downtime	Export + upload + import time (hours)
RPO	Zero (consistent export)
Complexity	Low
When to use	Small DBs (< 200 GB), Azure SQL DB target, acceptable downtime

Scenario 3: Azure DMS Offline Migration (to Azure SQL DB)

One-time, automated schema + data migration. DMS supports offline only for Azure SQL Database targets — online (continuous sync) is not available for SQL DB.

Create DMS instance

Deploy Azure Database Migration Service (Standard tier — offline)

Assess compatibility

DMS checks source for blocking issues

Start offline migration

One-time schema + data load (no continuous sync)

Monitor progress

Track tables and rows as they complete

Complete & validate

Quiesce source for the migration window, then repoint the app

# Create DMS instance (Standard SKU — offline migration to SQL DB)
az dms create `
    --resource-group $resourceGroup `
    --name "dms-migrate-$suffix" `
    --location $location `
    --sku-name Standard_4vCores

# Create target Azure SQL DB (empty — DMS migrates schema + data)
az sql db create -g $resourceGroup -s $dbServer -n AW2022_DMS --service-objective S2

🎯 Exam Focus

For migrations to Azure SQL Database, DMS supports offline only — online (continuous-sync) migration is not available for SQL DB targets. DMS online mode (Premium SKU) applies only to SQL Managed Instance and SQL Server on Azure VM. For minimal-downtime migration to SQL DB, use transactional replication.

Then in the Azure Portal:

Navigate to Azure Database Migration Service → your DMS instance
Select + New Migration Project
Source type: SQL Server, Target type: Azure SQL Database
Activity type: Offline data migration
Configure source connection (to your sqlvm-source)
Configure target connection (to your Azure SQL DB)
Select tables to migrate
Start the migration
Monitor migration progress until all selected tables report Complete, then repoint the application

-- Offline migration: no continuous sync. After the migration completes,
-- validate row counts on the source vs the target to confirm parity.
SELECT COUNT(*) AS DepartmentRows
FROM HumanResources.Department;

-- Run the same query on the target Azure SQL DB and compare the results.
-- (No writes should occur on the source during the migration window.)

Metric	Value
Downtime	Hours (offline — source quiesced for the migration window)
RPO	Zero (one-time consistent migration)
Complexity	Medium (DMS setup, network)
When to use	SQL DB target; for minimal downtime use transactional replication (DMS online not supported for SQL DB)

Scenario 4: Managed Instance Link (to SQL MI)

The best online migration to SQL MI — uses Always On AG technology under the hood.

Prerequisites

SQL Server 2019 CU20+ or 2022, certificate-based auth

Create MI Link

Create distributed AG between source and MI

Monitor sync

Databases replicate continuously via AG

Cutover

Break the link — MI becomes standalone primary

# Deploy MI for link target (if not already done in MI lab)
# See the MI Flavors lab Step 1 for full MI deployment

# Create the MI Link using Azure CLI
az sql mi link create `
    --resource-group $resourceGroup `
    --instance-name "mi-migrate-$suffix" `
    --name "mi-link-aw" `
    --primary-availability-group-name "SourceAG" `
    --secondary-availability-group-name "TargetAG" `
    --source-endpoint "TCP://sqlvm-source.yourdom.local:5022"

Requirements on the source SQL Server:

-- Source must be SQL Server 2019 CU20+ or 2022
SELECT @@VERSION;

-- Must enable HADR
-- (from PowerShell: Enable-SqlAlwaysOn -ServerInstance "sqlvm-source" -Force)

-- Database must be FULL recovery with a backup
ALTER DATABASE [AdventureWorks2022] SET RECOVERY FULL;
BACKUP DATABASE [AdventureWorks2022] TO DISK = 'NUL';

-- Create certificate for AG endpoint authentication
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<StrongPassword>';
CREATE CERTIFICATE MI_Link_Cert
WITH SUBJECT = 'MI Link Certificate';

-- Create mirroring endpoint
CREATE ENDPOINT AG_Endpoint
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
        AUTHENTICATION = CERTIFICATE MI_Link_Cert,
        ROLE = ALL
    );

🏢 Real-World DBA Note

MI Link is the preferred migration method for SQL MI because it uses AG technology — you get real-time sync with near-zero RPO. The cutover (breaking the link) takes seconds. The downside: requires SQL 2019 CU20+ or 2022 on source.

Metric	Value
Downtime	Seconds (break link)
RPO	Near-zero (AG sync)
Complexity	Medium (AG endpoint, certificates)
When to use	SQL MI target, SQL 2019+, large databases, minimal downtime

Scenario 5: Log Replay Service (to SQL MI)

Backup-chain streaming to MI — works with any SQL Server version that supports backup to URL.

Upload backup chain to blob

Full + diff + log backups to storage account

Start LRS job

LRS monitors the container and applies backups in order

Continue log backups

Keep taking log backups on source, uploading to blob

Cutover

Stop source, upload final log backup, complete LRS restore

# Create storage account for LRS
$lrsStorage = "stlrs$suffix"
az storage account create -g $resourceGroup -n $lrsStorage -l $location --sku Standard_LRS
az storage container create --account-name $lrsStorage --name lrs-backups

# Get SAS token for MI to read backups
$expiry = (Get-Date).AddDays(7).ToString("yyyy-MM-ddTHH:mm:ssZ")
$sasToken = az storage container generate-sas `
    --account-name $lrsStorage `
    --name lrs-backups `
    --permissions rl `
    --expiry $expiry -o tsv

On the source:

-- Create credential for blob storage
CREATE CREDENTIAL [https://stlrsXXXX.blob.core.windows.net/lrs-backups]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS_TOKEN>';

-- Full backup to blob
BACKUP DATABASE [AdventureWorks2022]
TO URL = 'https://stlrsXXXX.blob.core.windows.net/lrs-backups/AW2022_full.bak'
WITH COMPRESSION;

-- Log backup (do this periodically during migration)
BACKUP LOG [AdventureWorks2022]
TO URL = 'https://stlrsXXXX.blob.core.windows.net/lrs-backups/AW2022_log_001.trn'
WITH COMPRESSION;

Start LRS on the MI:

# Start Log Replay Service (continuous mode)
az sql midb log-replay start `
    --resource-group $resourceGroup `
    --managed-instance "mi-migrate-$suffix" `
    --name "AdventureWorks2022" `
    --storage-uri "https://$lrsStorage.blob.core.windows.net/lrs-backups" `
    --storage-sas "$sasToken" `
    --auto-complete false

# Check status
az sql midb log-replay show `
    --resource-group $resourceGroup `
    --managed-instance "mi-migrate-$suffix" `
    --name "AdventureWorks2022"

Cutover:

# Stop source application, take final log backup
# (on source): BACKUP LOG [AdventureWorks2022] TO URL = '...' WITH NORECOVERY;

# Complete the restore on MI
az sql midb log-replay complete `
    --resource-group $resourceGroup `
    --managed-instance "mi-migrate-$suffix" `
    --name "AdventureWorks2022" `
    --last-backup-name "AW2022_log_final.trn"

Metric	Value
Downtime	Minutes (final log backup + complete)
RPO	Last log backup interval
Complexity	Medium (backup chain management)
When to use	SQL MI target, any SQL version, cost-effective (no DMS needed)

Scenario 6: Transactional Replication (to Azure SQL DB)

Real-time replication with per-table control — great for phased migration.

Configure source as Publisher

Enable replication on source, create publication

Create subscriber on Azure SQL DB

Configure push subscription from source to Azure SQL DB

Monitor replication

Use Replication Monitor and DMVs to track latency

Cutover

Stop source writes, verify all changes replicated, switch DNS

-- On source — enable replication
-- (run SQL Server Agent if not running)
EXEC sp_replicationdboption
    @dbname = 'AdventureWorks2022',
    @optname = 'publish',
    @value = 'true';

-- Create publication for selected tables
EXEC sp_addpublication
    @publication = 'AW_Migration_Pub',
    @status = 'active',
    @allow_push = 'true',
    @allow_pull = 'false';

-- Add articles (tables to replicate)
EXEC sp_addarticle
    @publication = 'AW_Migration_Pub',
    @article = 'Product',
    @source_table = 'Product',
    @source_owner = 'SalesLT',
    @destination_table = 'Product',
    @destination_owner = 'SalesLT';

-- Add more tables as needed
EXEC sp_addarticle @publication = 'AW_Migration_Pub',
    @article = 'SalesOrderHeader', @source_table = 'SalesOrderHeader',
    @source_owner = 'SalesLT', @destination_table = 'SalesOrderHeader',
    @destination_owner = 'SalesLT';

-- Create subscription pointing to Azure SQL DB
EXEC sp_addsubscription
    @publication = 'AW_Migration_Pub',
    @subscriber = 'sql-migrate-XXXX.database.windows.net',
    @destination_db = 'AW2022_Repl',
    @subscription_type = 'push';

-- Start the snapshot agent (initial sync)
EXEC sp_startpublication_snapshot @publication = 'AW_Migration_Pub';

Monitor replication:

-- Check replication latency
EXEC sp_replmonitorsubscriptionpendingcmds
    @publisher = @@SERVERNAME,
    @publisher_db = 'AdventureWorks2022',
    @publication = 'AW_Migration_Pub';

-- View replication status
SELECT
    publication,
    subscriber_db,
    status,
    last_distsync
FROM distribution.dbo.MSsubscriptions;

Metric	Value
Downtime	Seconds (DNS switch after drain)
RPO	Near-zero (real-time replication)
Complexity	High (Distributor, Publisher, Subscriber setup)
When to use	Selective table migration, phased approach, SQL DB target

Master Comparison

After completing all scenarios, fill in your observations:

Method	Target	Your Downtime	Your Transfer Rate	Difficulty (1-5)	Would Use For
Backup/Restore	SQL VM	___	___	1	___
BACPAC	SQL DB	___	___	1	___
DMS (Offline)	SQL DB	___	___	3	___
MI Link	SQL MI	___	___	3	___
LRS	SQL MI	___	___	2	___
Replication	SQL DB	___	___	4	___

Exam Cheat Sheet

DP-300 Migration Questions

⏱️

"Minimal downtime to SQL DB?"

Transactional Replication (DMS online not supported for SQL DB)

⏱️

"Minimal downtime to SQL MI?"

MI Link (SQL 2019+) or Log Replay Service

📦

"Simplest offline method?"

BACPAC for SQL DB, Backup/Restore for SQL VM

🔍

"Assessment tool?"

DMA for SQL→Azure, SSMA for Oracle→SQL, Azure Migrate for discovery

Does DMS support online migration to Azure SQL Database?

Click to reveal answer

No. DMS supports only offline migration to Azure SQL Database. Online (minimal-downtime) DMS migration is available only for SQL Managed Instance and SQL Server on Azure VM. For minimal downtime to SQL DB, use transactional replication.

1 / 4

Common Failures

Symptom	Likely Cause	Fix
DMA reports "unsupported feature" — e.g. cross-DB query	Target tier doesn't support that feature	Refactor query, switch tier (MI > DB), or pick different migration target
LRS restore stuck "COPYING"	Network bandwidth or large DB	Wait — ~150 GB/hr typical; monitor `sys.dm_database_replica_states` on target
MI Link replication breaks after planned failover	Distributed AG link not torn down before reverse seed	Drop link cleanly with `ALTER AVAILABILITY GROUP ... REMOVE LISTENER` first
BACPAC export fails on large DB	Single-threaded; chokes >200 GB	Switch to LRS or DMS for >100 GB workloads
Cutover window blown by long transactions on source	Final delta sync waited on long-running tx	Pre-drain long transactions; schedule cutover during low activity

⚠️ Watch Out

LRS is one-way and one-shot. Once the restore completes and you cut over, you cannot replicate back. If you need rollback capability, use MI Link (bidirectional) or transactional replication.

Variations to Try

Run DMA assessment first — produces compatibility report, blocks bad migrations early
Compare online (MI Link, DMS) vs offline (BACPAC, LRS) for the same DB — measure cutover window
Migrate the same DB to 3 different targets (DB, MI, IaaS) — compare cost, perf, feature parity
Test rollback path: MI Link reverse seed, or pre-cutover backup of source
Layer Azure Migrate for assessment if migrating dozens of DBs

Production Application

Lift-and-shift, minimal change → LRS (Azure SQL DB), MI Link (Azure SQL MI), or VM-to-VM backup/restore.
Online migration with sub-minute cutover → MI Link or transactional replication (DB has no native online option).
Refactor allowed, modernize tier → BACPAC + DMA-driven schema fixes.
Anti-pattern: don't pick BACPAC for >200 GB or transactional workloads — it's offline, single-threaded, and slow.

Clean Up

# Delete everything
az group delete --name $resourceGroup --yes --no-wait

What's Next?

Direction	Lab
Set up HA for your migrated SQL VM	AlwaysOn HA Setup
Add cross-region DR	Cross-Region AlwaysOn
Secure your migrated database	SQL Security Assessment
Configure DR for Azure SQL DB	Geo-Replication & Failover

Migration Methods Overview​

Architecture​

Decision Matrix​

Prerequisites​

Step 0: Deploy Shared Lab Environment​

Scenario 1: Backup → Blob → Restore (to Azure SQL VM)​

Scenario 2: BACPAC Export → Import (to Azure SQL DB)​

Scenario 3: Azure DMS Offline Migration (to Azure SQL DB)​

Scenario 4: Managed Instance Link (to SQL MI)​

Scenario 5: Log Replay Service (to SQL MI)​

Scenario 6: Transactional Replication (to Azure SQL DB)​

Master Comparison​

Exam Cheat Sheet​

Common Failures​

Variations to Try​

Production Application​

Clean Up​

What's Next?​

Migration Methods Overview

Architecture

Decision Matrix

Prerequisites

Step 0: Deploy Shared Lab Environment

Scenario 1: Backup → Blob → Restore (to Azure SQL VM)

Scenario 2: BACPAC Export → Import (to Azure SQL DB)

Scenario 3: Azure DMS Offline Migration (to Azure SQL DB)

Scenario 4: Managed Instance Link (to SQL MI)

Scenario 5: Log Replay Service (to SQL MI)

Scenario 6: Transactional Replication (to Azure SQL DB)

Master Comparison

Exam Cheat Sheet

Common Failures

Variations to Try

Production Application

Clean Up

What's Next?