Lab: SQL Managed Instance — Flavors, Replicas & Failover Groups

Deploy both SQL MI tiers (General Purpose + Business Critical), explore read replicas, configure a cross-region MI Failover Group, and see the architecture differences firsthand. Covers every MI HA/DR pattern tested on DP-300.

DP-300 Domain: 1 — Plan & Implement (20-25%) + 5 — HA/DR (20-25%)

🏢 Real-World DBA Note

SQL Managed Instance is the primary target for on-prem SQL Server migrations when you need near-100% compatibility. Understanding the tier differences and their HA behavior is critical for recommending the right architecture to customers.

MI Tier Comparison

General Purpose vs Business Critical

📦

General Purpose

Remote Azure Storage

1 compute + standby

Failover: ~30 seconds

No read replicas

4-80 vCores

Up to 16 TB storage

SLA: 99.99%

Cost: from ~$350/mo

👑

Business Critical

Local SSD (NVMe)

1 primary + 3 secondary replicas

Built-in Always On AG

Failover: ~5-10 sec

1 free read-only replica

4-128 vCores

SLA: 99.99%

Cost: from ~$1,100/mo

Architecture

SQL MI flavors & replicas lab architecture: General Purpose with remote storage and standby, Business Critical with 3 sync replicas and read-only endpoint, cross-region MI Failover Group

What Gets Deployed

Component	SKU	Region	Purpose
MI (General Purpose)	GP_Gen5, 4 vCores	West Europe	Baseline: remote storage, standard HA
MI (Business Critical)	BC_Gen5, 4 vCores	West Europe	Compare: local SSD, read replicas, fast failover
MI (GP — Secondary)	GP_Gen5, 4 vCores	North Europe	Failover group partner for cross-region DR

⚠️ Watch Out

SQL Managed Instance deployment takes 4-6 hours for the first instance in a subnet (creates a virtual cluster). Plan accordingly — deploy the night before if doing this lab in a workshop setting. Subsequent deployments in the same subnet are faster (~1.5 hrs).

Prerequisites

Azure subscription with MI quota (request via Azure Portal if needed)
Azure CLI with sql mi extension
PowerShell 5.1+
Patience — MI provisioning is the slowest Azure resource

# Check if you have MI quota
az sql mi list --resource-group rg-mi-lab -o table 2>$null
# If "Subscription not registered" — register the provider:
az provider register --namespace Microsoft.Sql

Lab Steps

Deploy General Purpose MI

Create VNET, subnet, route table, NSG, and deploy GP MI (4 vCores)

Deploy Business Critical MI

Deploy BC MI in same VNET, different subnet (4 vCores)

Compare Tier Behavior

Test read replicas (BC only), measure I/O performance, compare failover time

Deploy Secondary MI (North Europe)

Create partner MI in another region for failover group

Configure MI Failover Group

Create cross-region failover group with read/write listener

Test MI Failover

Trigger failover, verify DNS flip, measure RTO, test read-only endpoint

Step 1: Deploy General Purpose MI

$resourceGroup = "rg-mi-lab"
$location = "westeurope"
$suffix = Get-Random -Maximum 9999
$vnetName = "vnet-mi-lab"
$adminPassword = "<YOUR_STRONG_PASSWORD>"

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

# Create VNET with MI subnet (requires delegation)
az network vnet create `
    --resource-group $resourceGroup `
    --name $vnetName `
    --location $location `
    --address-prefixes 10.0.0.0/16

# MI subnet — must be delegated to Microsoft.Sql/managedInstances
az network vnet subnet create `
    --resource-group $resourceGroup `
    --vnet-name $vnetName `
    --name mi-gp-subnet `
    --address-prefixes 10.0.0.0/24 `
    --delegations Microsoft.Sql/managedInstances

# Create NSG with required MI rules
az network nsg create --resource-group $resourceGroup --name nsg-mi-gp --location $location

# Required: allow management traffic (MI management service)
az network nsg rule create --resource-group $resourceGroup --nsg-name nsg-mi-gp `
    --name allow_management --priority 100 --direction Inbound --access Allow `
    --protocol Tcp --source-address-prefix SqlManagement --destination-port-ranges 9000 9003 1438 1440 1452
az network nsg rule create --resource-group $resourceGroup --nsg-name nsg-mi-gp `
    --name allow_health_probe --priority 200 --direction Inbound --access Allow `
    --protocol '*' --source-address-prefix AzureLoadBalancer --destination-port-range '*'
az network nsg rule create --resource-group $resourceGroup --nsg-name nsg-mi-gp `
    --name allow_mi_subnet --priority 300 --direction Inbound --access Allow `
    --protocol '*' --source-address-prefix 10.0.0.0/24 --destination-port-range '*'

# Associate NSG with subnet
az network vnet subnet update `
    --resource-group $resourceGroup `
    --vnet-name $vnetName `
    --name mi-gp-subnet `
    --network-security-group nsg-mi-gp

# Create Route Table (required for MI)
az network route-table create `
    --resource-group $resourceGroup `
    --name rt-mi-gp `
    --location $location
az network vnet subnet update `
    --resource-group $resourceGroup `
    --vnet-name $vnetName `
    --name mi-gp-subnet `
    --route-table rt-mi-gp

# Deploy General Purpose MI (⏱️ ~4-6 hours first time!)
az sql mi create `
    --resource-group $resourceGroup `
    --name "mi-gp-$suffix" `
    --location $location `
    --admin-user sqladmin `
    --admin-password $adminPassword `
    --subnet "/subscriptions/$(az account show --query id -o tsv)/resourceGroups/$resourceGroup/providers/Microsoft.Network/virtualNetworks/$vnetName/subnets/mi-gp-subnet" `
    --sku-name GP_Gen5 `
    --vcores 4 `
    --storage 128 `
    --edition GeneralPurpose `
    --license-type LicenseIncluded `
    --no-wait     # Deploy in background — check status later

Write-Host "MI deployment started. Check status:"
Write-Host "  az sql mi show -g $resourceGroup -n mi-gp-$suffix --query provisioningState"

Step 2: Deploy Business Critical MI

# Create separate subnet for BC MI
az network vnet subnet create `
    --resource-group $resourceGroup `
    --vnet-name $vnetName `
    --name mi-bc-subnet `
    --address-prefixes 10.0.1.0/24 `
    --delegations Microsoft.Sql/managedInstances

# NSG for BC subnet
az network nsg create --resource-group $resourceGroup --name nsg-mi-bc --location $location
az network nsg rule create --resource-group $resourceGroup --nsg-name nsg-mi-bc `
    --name allow_management --priority 100 --direction Inbound --access Allow `
    --protocol Tcp --source-address-prefix SqlManagement --destination-port-ranges 9000 9003 1438 1440 1452
az network nsg rule create --resource-group $resourceGroup --nsg-name nsg-mi-bc `
    --name allow_health_probe --priority 200 --direction Inbound --access Allow `
    --protocol '*' --source-address-prefix AzureLoadBalancer --destination-port-range '*'
az network nsg rule create --resource-group $resourceGroup --nsg-name nsg-mi-bc `
    --name allow_mi_subnet --priority 300 --direction Inbound --access Allow `
    --protocol '*' --source-address-prefix 10.0.1.0/24 --destination-port-range '*'
az network vnet subnet update `
    --resource-group $resourceGroup `
    --vnet-name $vnetName `
    --name mi-bc-subnet `
    --network-security-group nsg-mi-bc

# Route table for BC subnet
az network route-table create --resource-group $resourceGroup --name rt-mi-bc --location $location
az network vnet subnet update `
    --resource-group $resourceGroup `
    --vnet-name $vnetName `
    --name mi-bc-subnet `
    --route-table rt-mi-bc

# Deploy Business Critical MI
az sql mi create `
    --resource-group $resourceGroup `
    --name "mi-bc-$suffix" `
    --location $location `
    --admin-user sqladmin `
    --admin-password $adminPassword `
    --subnet "/subscriptions/$(az account show --query id -o tsv)/resourceGroups/$resourceGroup/providers/Microsoft.Network/virtualNetworks/$vnetName/subnets/mi-bc-subnet" `
    --sku-name BC_Gen5 `
    --vcores 4 `
    --storage 128 `
    --edition BusinessCritical `
    --license-type LicenseIncluded `
    --no-wait

Step 3: Compare Tier Behavior

Once both MIs are provisioned, connect and compare:

# Get MI endpoints
az sql mi show -g $resourceGroup -n "mi-gp-$suffix" --query fullyQualifiedDomainName -o tsv
az sql mi show -g $resourceGroup -n "mi-bc-$suffix" --query fullyQualifiedDomainName -o tsv

3a: Test Read Replica (BC Only)

-- Connect to Business Critical MI read-only replica
-- Connection string: add ApplicationIntent=ReadOnly
-- Server: mi-bc-XXXX.xxxxxxxx.database.windows.net
-- Additional connection parameter: ApplicationIntent=ReadOnly

-- On read-only replica — verify it's read-only
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS Updateability;
-- Expected: READ_ONLY

-- Query data — reads work fine
SELECT TOP 10 * FROM sys.objects;

-- Try to write — should FAIL
CREATE TABLE dbo.TestWrite (Id INT);
-- Error: Failed to update database "..." because the database is read-only.

🏢 Real-World DBA Note

Business Critical read-only replica is built-in and free — no extra cost. Use it for reporting workloads, BI dashboards, read-heavy APIs. Just add ApplicationIntent=ReadOnly to the connection string.

3b: Compare I/O Performance

-- Run on BOTH GP and BC instances to compare I/O
-- Create test table with significant data
CREATE TABLE dbo.IOTest (
    Id INT IDENTITY PRIMARY KEY,
    Payload NCHAR(4000) DEFAULT REPLICATE(N'X', 4000)
);

-- Insert 10,000 rows
SET NOCOUNT ON;
DECLARE @i INT = 0;
WHILE @i < 10000
BEGIN
    INSERT INTO dbo.IOTest DEFAULT VALUES;
    SET @i += 1;
END

-- Measure sequential scan performance
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT COUNT(*) FROM dbo.IOTest WHERE Payload LIKE '%Y%';
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
-- BC will be significantly faster (local SSD vs remote storage)

3c: Compare Instance Properties

-- Run on both — see the HA configuration
SELECT
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('EngineEdition') AS EngineEdition,
    SERVERPROPERTY('ProductVersion') AS Version,
    SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled;

-- BC only — see the underlying AG replicas
SELECT * FROM sys.dm_hadr_availability_replica_states;
-- GP will return empty (no AG)
-- BC will show 4 replicas (1 primary + 3 secondaries)

Step 4: Deploy Secondary MI (North Europe)

$drLocation = "northeurope"
$drVnet = "vnet-mi-dr"

# Create DR VNET
az network vnet create `
    --resource-group $resourceGroup `
    --name $drVnet `
    --location $drLocation `
    --address-prefixes 10.1.0.0/16

az network vnet subnet create `
    --resource-group $resourceGroup `
    --vnet-name $drVnet `
    --name mi-dr-subnet `
    --address-prefixes 10.1.0.0/24 `
    --delegations Microsoft.Sql/managedInstances

# NSG + Route Table for DR subnet (same pattern as Step 1)
az network nsg create --resource-group $resourceGroup --name nsg-mi-dr --location $drLocation
az network nsg rule create --resource-group $resourceGroup --nsg-name nsg-mi-dr `
    --name allow_management --priority 100 --direction Inbound --access Allow `
    --protocol Tcp --source-address-prefix SqlManagement --destination-port-ranges 9000 9003 1438 1440 1452
az network nsg rule create --resource-group $resourceGroup --nsg-name nsg-mi-dr `
    --name allow_health_probe --priority 200 --direction Inbound --access Allow `
    --protocol '*' --source-address-prefix AzureLoadBalancer --destination-port-range '*'
az network nsg rule create --resource-group $resourceGroup --nsg-name nsg-mi-dr `
    --name allow_mi_subnet --priority 300 --direction Inbound --access Allow `
    --protocol '*' --source-address-prefix 10.1.0.0/24 --destination-port-range '*'
az network vnet subnet update `
    --resource-group $resourceGroup --vnet-name $drVnet --name mi-dr-subnet --network-security-group nsg-mi-dr
az network route-table create --resource-group $resourceGroup --name rt-mi-dr --location $drLocation
az network vnet subnet update `
    --resource-group $resourceGroup --vnet-name $drVnet --name mi-dr-subnet --route-table rt-mi-dr

# VNET Peering (required for MI failover groups)
$vnet1Id = az network vnet show -g $resourceGroup -n $vnetName --query id -o tsv
$vnet2Id = az network vnet show -g $resourceGroup -n $drVnet --query id -o tsv
az network vnet peering create -g $resourceGroup --name peer-primary-to-dr --vnet-name $vnetName --remote-vnet $vnet2Id --allow-vnet-access
az network vnet peering create -g $resourceGroup --name peer-dr-to-primary --vnet-name $drVnet --remote-vnet $vnet1Id --allow-vnet-access

# Deploy secondary MI (GP tier — must match or exceed primary for FOG)
az sql mi create `
    --resource-group $resourceGroup `
    --name "mi-dr-$suffix" `
    --location $drLocation `
    --admin-user sqladmin `
    --admin-password $adminPassword `
    --subnet "/subscriptions/$(az account show --query id -o tsv)/resourceGroups/$resourceGroup/providers/Microsoft.Network/virtualNetworks/$drVnet/subnets/mi-dr-subnet" `
    --sku-name GP_Gen5 `
    --vcores 4 `
    --storage 128 `
    --edition GeneralPurpose `
    --license-type LicenseIncluded `
    --no-wait

Step 5: Configure MI Failover Group

# Create failover group (links primary GP MI ↔ secondary DR MI)
az sql instance-failover-group create `
    --resource-group $resourceGroup `
    --name "fog-mi-$suffix" `
    --mi "mi-gp-$suffix" `
    --partner-resource-group $resourceGroup `
    --partner-mi "mi-dr-$suffix" `
    --failover-policy Automatic `
    --grace-period 1

# Verify failover group
az sql instance-failover-group show `
    --resource-group $resourceGroup `
    --name "fog-mi-$suffix" `
    --mi "mi-gp-$suffix" `
    --query '{name:name, role:replicationRole, partnerRegion:partnerRegions[0].location, failoverPolicy:readWriteEndpoint.failoverPolicy}'

🎯 Exam Focus

MI Failover Groups replicate ALL user databases — you cannot pick specific databases. This is different from Azure SQL DB failover groups where you choose which databases to include. System databases (master, msdb) are NOT replicated.

Step 6: Test MI Failover

# Record the listener endpoints
az sql instance-failover-group show `
    --resource-group $resourceGroup `
    --name "fog-mi-$suffix" `
    --mi "mi-gp-$suffix" `
    --query '{readWrite:readWriteEndpoint.failoverPolicy, listener:"fog-mi-XXXX.database.windows.net"}'

# Trigger manual failover
$startTime = Get-Date
az sql instance-failover-group set-primary `
    --resource-group $resourceGroup `
    --name "fog-mi-$suffix" `
    --mi "mi-dr-$suffix"
$rto = (Get-Date) - $startTime
Write-Host "Failover completed in: $($rto.TotalSeconds) seconds"

# Verify roles swapped
az sql instance-failover-group show `
    --resource-group $resourceGroup `
    --name "fog-mi-$suffix" `
    --mi "mi-dr-$suffix" `
    --query '{name:name, currentRole:replicationRole}'

Connect to the failover group listener and verify:

-- Connect to: fog-mi-XXXX.database.windows.net
-- The DNS automatically points to whichever MI is primary

SELECT @@SERVERNAME AS CurrentServer;
-- Should show the DR MI name after failover

-- Failback
-- az sql instance-failover-group set-primary -g rg-mi-lab -n "fog-mi-XXXX" --mi "mi-gp-XXXX"

MI Comparison Matrix

Feature	General Purpose	Business Critical
Storage	Remote (Azure Premium)	Local SSD (NVMe)
Max storage	16 TB	4 TB
Max vCores	80	128
Read replicas	None	1 free (built-in)
Failover time	~30 sec	~5-10 sec
IOPS	Lower (remote)	Higher (local SSD)
Tempdb	Remote storage	Local SSD
In-Memory OLTP	No	Yes
Zone redundant	Yes (preview)	Yes
Failover Group	Yes	Yes
Starting price	~$350/mo (4 vCores)	~$1,100/mo (4 vCores)
Use case	Dev/test, cost-sensitive	Mission-critical, OLTP

MI vs SQL DB vs SQL VM — When to Use What

Choose Your Platform

🗄️

Azure SQL Database

Greenfield apps, microservices, SaaS multi-tenant. Per-database model. No SQL Agent, no cross-DB queries.

🏢

SQL Managed Instance

Lift & shift from on-prem. Near-100% compatible. SQL Agent, linked servers, cross-DB queries, CLR, Service Broker.

🖥️

SQL Server on VM

Full control. Third-party software, OS-level access, pre-2012 features, custom HA patterns.

Which MI tier provides a free read-only replica?

Click to reveal answer

Business Critical only. General Purpose has no read replicas.

1 / 1

How many secondary replicas does BC MI maintain internally?

Click to reveal answer

3 secondary replicas (4 nodes total). All use local SSD with synchronous commit. One is exposed as a read-only endpoint.

1 / 1

Do MI Failover Groups replicate system databases?

Click to reveal answer

No. Only user databases are replicated. Logins, jobs, linked servers in msdb/master must be manually kept in sync.

1 / 1

What is the minimum MI deployment time?

Click to reveal answer

First MI in a subnet: ~4-6 hours (virtual cluster creation). Subsequent MIs in the same subnet: ~1.5-2 hours.

1 / 1

Common Failures

Symptom	Likely Cause	Fix
MI provision fails: "subnet not delegated"	Subnet missing `Microsoft.Sql/managedInstances` delegation	Delegate subnet; subnet must also be empty of other resources
First MI takes 6+ hours, subsequent fast	Virtual cluster bootstrap on first MI in subnet	Expected behavior; plan first deploy as overnight
BC read scale-out replica returns stale data	App not using `ApplicationIntent=ReadOnly`	Add to reporting connection string; verify via `sys.fn_servershareddrives`
MI Link breaks after SQL VM patch	Cluster heartbeat interrupted	Re-establish link; patch in maintenance window with `--pause-replication`
Cross-subscription MI move not allowed	MI doesn't support sub-move	Backup/restore or MI Link to new MI in target subscription

⚠️ Watch Out

MI subnet must be dedicated and pre-sized. You cannot add other Azure resources to the MI subnet, and resizing requires deleting all MIs. Size the subnet for max MI count from day one (typical: /27 or /26).

Variations to Try

Deploy GP and BC side-by-side in same subnet — compare latency, cost
Set up MI Link from on-prem SQL Server 2022 — measure replication lag
Add a read scale-out endpoint on BC, route reporting workload to it
Test failover group across regions for MI — measure RTO
Compare MI vs SQL VM for the same workload — ops overhead, cost, feature parity

Production Application

Lift-and-shift legacy SQL with full instance features (SQL Agent, CLR, cross-DB, linked servers) → MI GP for cost-sensitive, BC for low-latency.
Hybrid SQL Server 2022 + Azure → MI Link for near-real-time replication, online migration, or DR.
Multi-region active-passive PaaS for instance-scoped workloads → MI BC + Failover Group.
Anti-pattern: don't pick MI for greenfield apps that fit Azure SQL DB — DB is cheaper, faster to scale, more features for new dev.

Clean Up

# Delete failover group first
az sql instance-failover-group delete `
    --resource-group $resourceGroup `
    --name "fog-mi-$suffix" `
    --mi "mi-gp-$suffix" --yes

# Delete managed instances (can take 1-2 hours each)
az sql mi delete --resource-group $resourceGroup --name "mi-gp-$suffix" --yes --no-wait
az sql mi delete --resource-group $resourceGroup --name "mi-bc-$suffix" --yes --no-wait
az sql mi delete --resource-group $resourceGroup --name "mi-dr-$suffix" --yes --no-wait

# After MIs are deleted, delete the resource group
# az group delete --name $resourceGroup --yes --no-wait

⚠️ Watch Out

MI deletion also takes a long time (~1-2 hours). Use --no-wait and monitor. Don't try to delete the VNET or subnet while an MI is still being deleted — it will fail because the virtual cluster still exists.

MI Tier Comparison​

Architecture​

What Gets Deployed​

Prerequisites​

Lab Steps​

Step 1: Deploy General Purpose MI​

Step 2: Deploy Business Critical MI​

Step 3: Compare Tier Behavior​

3a: Test Read Replica (BC Only)​

3b: Compare I/O Performance​

3c: Compare Instance Properties​

Step 4: Deploy Secondary MI (North Europe)​

Step 5: Configure MI Failover Group​

Step 6: Test MI Failover​

MI Comparison Matrix​

MI vs SQL DB vs SQL VM — When to Use What​

Common Failures​

Variations to Try​

Production Application​

Clean Up​

MI Tier Comparison

Architecture

What Gets Deployed

Prerequisites

Lab Steps

Step 1: Deploy General Purpose MI

Step 2: Deploy Business Critical MI

Step 3: Compare Tier Behavior

3a: Test Read Replica (BC Only)

3b: Compare I/O Performance

3c: Compare Instance Properties

Step 4: Deploy Secondary MI (North Europe)

Step 5: Configure MI Failover Group

Step 6: Test MI Failover

MI Comparison Matrix

MI vs SQL DB vs SQL VM — When to Use What

Common Failures

Variations to Try

Production Application

Clean Up