Lab: SQL Server Migration (Backup → Blob → Restore)

Migrate AdventureWorks2022 from a simulated on-premises SQL Server VM to an Azure SQL Server on VM using the backup to URL → blob storage → restore pattern. A fully automated, real-world migration workflow.

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

Architecture

What Gets Deployed

Component	Resource	Purpose
Source VM	sqlvm-onprem	SQL Server 2022 on Windows 2022, simulates on-prem. Auto-downloads AdventureWorks2022.
Target VM	sqlvm-azure	Clean SQL Server 2022 with SQL IaaS Agent extension in full management mode
Storage Account	stmigration<random>	Blob container for backup transfer
Resource Group	rg-migration-lab	Contains all resources

Lab Steps

Deploy Lab Environment

One-command deployment of both VMs + storage using Bicep templates

Verify Source Database

Connect to source VM, confirm AdventureWorks2022 is restored and healthy

Backup to Blob Storage

Create SAS credential, backup database to Azure Blob Storage URL

Restore on Target

Download backup on target VM and restore with MOVE to new file paths

Validate Migration

Compare row counts, check data integrity, verify key objects

Step 1: Deploy Lab Environment

# Clone the migration lab
git clone https://github.com/ehabmostafa_microsoft/sql-migration-lab.git
cd sql-migration-lab

# Deploy (creates 2 VMs + storage, ~20-25 min)
.\deploy-migration-lab.ps1 -location "northeurope" -adminPassword "<YourStrongPassword>"

This creates:

• Resource group rg-migration-lab
• Source VM (sqlvm-onprem) with AdventureWorks2022 pre-loaded
• Target VM (sqlvm-azure) — empty SQL Server
• Storage account with a migration blob container
• Connection details saved to connection-info.json

Step 2: Verify Source Database

Connect to the source VM using VS Code SQL Server extension:

Server: <source-public-ip>,1433
Authentication: SQL Login
User: sqladmin
Password: <your password>

-- Verify AdventureWorks exists and has data
USE AdventureWorks2022;
SELECT
    s.name AS [Schema],
    t.name AS [Table],
    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)
WHERE p.rows > 0
ORDER BY p.rows DESC;

Step 3: Backup to Blob Storage

# The migration script handles credential creation + backup + restore
.\migrate-database.ps1 -sqlPassword "<YourStrongPassword>"

What the script does internally:

-- 1. Create credential on source pointing to blob storage
CREATE CREDENTIAL [https://stmigration1234.blob.core.windows.net/migration]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS_TOKEN>';

-- 2. Backup to URL
BACKUP DATABASE [AdventureWorks2022]
TO URL = 'https://stmigration1234.blob.core.windows.net/migration/AdventureWorks2022.bak'
WITH FORMAT, COMPRESSION, STATS = 10;

⚠️ Watch Out

Backup to URL requires a SAS token with Read, Write, List permissions on the blob container. The credential's IDENTITY must be exactly 'SHARED ACCESS SIGNATURE' (not the storage account name).

Step 4: Restore on Target

-- Create credential on target
CREATE CREDENTIAL [https://stmigration1234.blob.core.windows.net/migration]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS_TOKEN>';

-- Restore from URL with MOVE to new file paths
RESTORE DATABASE [AdventureWorks2022]
FROM URL = 'https://stmigration1234.blob.core.windows.net/migration/AdventureWorks2022.bak'
WITH
    MOVE 'AdventureWorks2022' TO 'F:\Data\AdventureWorks2022.mdf',
    MOVE 'AdventureWorks2022_log' TO 'G:\Log\AdventureWorks2022_log.ldf',
    STATS = 10,
    REPLACE;

🏢 Real-World DBA Note

In production migrations, use Azure Database Migration Service (DMS) for online migrations with minimal downtime. The backup/restore pattern shown here is ideal for offline migrations or when DMS doesn't support your source (e.g., older SQL versions).

Step 5: Validate Migration

-- Run on BOTH source and target, compare results
USE AdventureWorks2022;

-- Row count comparison
SELECT
    s.name + '.' + 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
ORDER BY s.name, t.name;

-- Check key database properties
SELECT
    name,
    compatibility_level,
    recovery_model_desc,
    state_desc,
    collation_name
FROM sys.databases
WHERE name = 'AdventureWorks2022';

-- Verify indexes, views, stored procedures migrated
SELECT type_desc, COUNT(*) AS [Count]
FROM sys.objects
WHERE is_ms_shipped = 0
GROUP BY type_desc
ORDER BY [Count] DESC;

Verification Checklist

Check	Source	Target	Status
Database exists	✅	✅
Row counts match	X rows	X rows
Compatibility level	160	160
Recovery model	FULL	FULL
Stored procedures	X	X
Indexes	X	X

Common Failures

Symptom	Likely Cause	Fix
DMA assessment shows blocking issues	Source uses unsupported features (cross-DB, CLR, etc.)	Refactor or pick MI/IaaS target instead of DB
BACPAC import fails midway	Tier too small, ran out of log space	Import to higher tier first, scale down after
Schema migration succeeds but data missing	Identity columns reseeded; FK constraints rejected rows	Disable FK during data load, re-enable after
Connection from on-prem still hits old server	DNS not updated; client connection string cached	Update CNAME; bounce app pools
Logins exist but users can't connect	SIDs differ between on-prem and Azure	Recreate users mapped to Azure AD or recreate SQL logins with WITH SID =

⚠️ Watch Out

Test cutover in lower environment first. Production cutover is not the time to discover that the app's connection string is hardcoded in 14 places, or that a backup job runs during your migration window.

Variations to Try

• Run the same migration via 3 methods (BACPAC, DMS, MI Link) — compare cutover time
• Test migration with active write workload running — measure delta-sync lag
• Migrate logins, jobs, linked servers alongside DB — catch the often-missed pieces
• Layer DMA + Azure Migrate for multi-DB assessment
• Run post-migration perf comparison — same query, on-prem vs Azure

Production Application

• Greenfield Azure migration, DB < 100 GB, can take downtime → BACPAC export/import.
• Online migration, large DB, minimal cutover → DMS (Azure SQL DB) or MI Link (Azure SQL MI).
• Cross-version upgrade + migration → stage via MI (always backward-compat) then move within Azure.
• Anti-pattern: don't run BACPAC on a busy 500 GB OLTP — cutover window will be hours, not minutes.

Clean Up

az group delete --name rg-migration-lab --yes --no-wait

Key Takeaways

🧠 Smart order (weak cards first)

What are the three main migration approaches for SQL Server to Azure?

Click to reveal answer

1. **Backup/Restore** (offline, any target) — used in this lab. 2. **Azure Database Migration Service** (online, minimal downtime). 3. **BACPAC import/export** (schema + data, for Azure SQL DB). For SQL VM targets, backup/restore is the most common approach.

1 / 1

🧠 Smart order (weak cards first)

What permissions does a SAS token need for BACKUP TO URL?

Click to reveal answer

The SAS token needs Read, Write, and List permissions on the blob container. The SQL credential IDENTITY must be exactly 'SHARED ACCESS SIGNATURE' (not the storage account name). The SECRET is the SAS token without the leading '?'.

1 / 1

🧠 Smart order (weak cards first)

Why use MOVE in the RESTORE command?

Click to reveal answer

The source and target likely have different drive letters and file paths. MOVE maps the logical file names to new physical paths on the target. In Azure SQL VMs, best practice is: data on F: (Premium SSD), logs on G: (separate Premium SSD), tempdb on D: (ephemeral/temp disk).

1 / 1

🧠 Smart order (weak cards first)

What does the SQL IaaS Agent Extension provide?

Click to reveal answer

Registered in Full management mode, it provides: automated backups, automated patching, Azure portal integration, storage configuration, tempdb management, AG management, and license optimization (Azure Hybrid Benefit). It's a DP-300 exam topic.

1 / 1

🏢 Real-World DBA Note

Source code: Full Bicep templates and migration scripts at github.com/ehabmostafa_microsoft/sql-migration-lab.