Lab: Azure SQL Database — Every Tier and Flavor Hands-On

Deploy Azure SQL Database in every purchasing model and service tier — DTU (Basic, S0-S12, P1-P15), vCore (GP, BC, Hyperscale), Serverless, and Elastic Pool. Run the same workload on each, compare performance, cost, and behavior. See what you're actually getting for your money.

DP-300 Domain: 1 — Plan & Implement (20-25%) + 3 — Monitor & Optimize (20-25%)

🏢 Real-World DBA Note

Customers constantly ask: "Should I use DTU or vCore? What tier do I need?" This lab lets you deploy every option side-by-side, run identical workloads, and see the real performance differences — not just the marketing specs.

Architecture

SQL DB tiers & flavors lab architecture: 7 standalone databases (Basic, S2, P1, GP, BC, Hyperscale, Serverless) plus a 3-database Elastic Pool, with workload script targeting all

Model Comparison

Purchasing Models

📊

DTU Model

Bundled CPU + Memory + IOPS

Simple: pick a number (5-4000)

Fixed resources 24/7

Tiers: Basic, Standard (S0-S12), Premium (P1-P15)

Cheaper for predictable workloads

⚙️

vCore Model

Choose vCores + memory + storage independently

Tiers: GP, BC, Hyperscale

Serverless option (auto-pause)

Azure Hybrid Benefit (save 55%)

More control, better for variable workloads

Lab Steps

Deploy DTU Databases

Create Basic, Standard S2, and Premium P1 databases on the same server

Deploy vCore Databases

Create General Purpose, Business Critical, and Hyperscale databases

Deploy Serverless

Create a Serverless database with auto-pause and auto-scale settings

Deploy Elastic Pool

Create a Standard pool with 3 databases sharing DTUs

Run Benchmark Workload

Execute identical workload on each tier, measure DTU%, CPU%, IOPS, latency

Compare Results

Analyze the performance/cost ratio of each tier for your workload profile

Test Tier-Specific Features

Read replicas (BC), named replicas (HS), auto-pause (Serverless)

Step 1: Deploy DTU Databases

$resourceGroup = "rg-tier-lab"
$location = "westeurope"
$suffix = Get-Random -Maximum 9999
$serverName = "sql-tiers-$suffix"
$adminPassword = "<YOUR_STRONG_PASSWORD>"

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

az sql server create `
    --resource-group $resourceGroup `
    --name $serverName `
    --admin-user sqladmin `
    --admin-password $adminPassword `
    --location $location

# Allow your IP
$myIp = (Invoke-RestMethod -Uri https://api.ipify.org)
az sql server firewall-rule create `
    --resource-group $resourceGroup `
    --server $serverName `
    --name AllowMyIP `
    --start-ip-address $myIp `
    --end-ip-address $myIp

# DTU Basic (5 DTU, 2 GB max)
az sql db create --resource-group $resourceGroup --server $serverName `
    --name BasicDB --service-objective Basic --sample-name AdventureWorksLT

# DTU Standard S2 (50 DTU, 250 GB max)
az sql db create --resource-group $resourceGroup --server $serverName `
    --name StandardDB --service-objective S2 --sample-name AdventureWorksLT

# DTU Premium P1 (125 DTU, 500 GB max, in-memory OLTP)
az sql db create --resource-group $resourceGroup --server $serverName `
    --name PremiumDB --service-objective P1 --sample-name AdventureWorksLT

Step 2: Deploy vCore Databases

# vCore General Purpose (2 vCores, remote storage)
az sql db create --resource-group $resourceGroup --server $serverName `
    --name vCoreGP --edition GeneralPurpose --family Gen5 --capacity 2 `
    --compute-model Provisioned --sample-name AdventureWorksLT

# vCore Business Critical (2 vCores, local SSD, read replica)
az sql db create --resource-group $resourceGroup --server $serverName `
    --name vCoreBC --edition BusinessCritical --family Gen5 --capacity 2 `
    --compute-model Provisioned --sample-name AdventureWorksLT

# Hyperscale (2 vCores, instant scale, up to 100 TB)
az sql db create --resource-group $resourceGroup --server $serverName `
    --name HyperscaleDB --edition Hyperscale --family Gen5 --capacity 2 `
    --sample-name AdventureWorksLT

Step 3: Deploy Serverless

# Serverless General Purpose (auto-pause after 1 hour idle)
az sql db create --resource-group $resourceGroup --server $serverName `
    --name ServerlessDB --edition GeneralPurpose --family Gen5 `
    --compute-model Serverless `
    --min-capacity 0.5 `
    --capacity 2 `
    --auto-pause-delay 60 `
    --sample-name AdventureWorksLT

⚠️ Watch Out

Serverless auto-pause: the database pauses after 60 minutes of inactivity. The first connection after pause has a cold-start delay of ~1 minute while the database resumes. Set auto-pause-delay to -1 to disable auto-pause if this is unacceptable.

Step 4: Deploy Elastic Pool

# Create Standard elastic pool (50 eDTU shared)
az sql elastic-pool create `
    --resource-group $resourceGroup `
    --server $serverName `
    --name pool-demo `
    --edition Standard `
    --dtu 50 `
    --db-dtu-max 50 `
    --db-dtu-min 0

# Create 3 databases inside the pool
for ($i = 1; $i -le 3; $i++) {
    az sql db create --resource-group $resourceGroup --server $serverName `
        --name "PoolDB$i" --elastic-pool pool-demo --sample-name AdventureWorksLT
}

# Verify pool membership
az sql elastic-pool list-dbs `
    --resource-group $resourceGroup `
    --server $serverName `
    --name pool-demo `
    --query '[].name' -o table

Step 5: Run Benchmark Workload

Connect to each database and run the same workload:

-- ============================================
-- BENCHMARK: Run on EACH database to compare
-- ============================================

-- 1. Create workload table
IF OBJECT_ID('dbo.BenchmarkResults', 'U') IS NOT NULL DROP TABLE dbo.BenchmarkResults;
CREATE TABLE dbo.BenchmarkResults (
    TestName NVARCHAR(50),
    DurationMs INT,
    RowsProcessed INT,
    TestedAt DATETIME2 DEFAULT SYSUTCDATETIME()
);

-- 2. Test: Sequential Inserts (tests write IOPS + commit latency)
DECLARE @start DATETIME2 = SYSUTCDATETIME();
DECLARE @i INT = 0;
WHILE @i < 5000
BEGIN
    INSERT INTO SalesLT.ProductDescription (Description, rowguid, ModifiedDate)
    VALUES ('Benchmark test ' + CAST(@i AS VARCHAR), NEWID(), GETDATE());
    SET @i += 1;
END
INSERT INTO dbo.BenchmarkResults (TestName, DurationMs, RowsProcessed)
VALUES ('Sequential Inserts', DATEDIFF(MILLISECOND, @start, SYSUTCDATETIME()), 5000);

-- 3. Test: Full Table Scan (tests read throughput)
SET @start = SYSUTCDATETIME();
DECLARE @cnt INT;
SELECT @cnt = COUNT(*) FROM SalesLT.SalesOrderDetail
CROSS JOIN SalesLT.ProductDescription;  -- Creates ~5M row scan
INSERT INTO dbo.BenchmarkResults (TestName, DurationMs, RowsProcessed)
VALUES ('Table Scan', DATEDIFF(MILLISECOND, @start, SYSUTCDATETIME()), @cnt);

-- 4. Test: Complex Aggregation (tests CPU)
SET @start = SYSUTCDATETIME();
SELECT
    p.ProductCategoryID,
    COUNT(*) AS OrderCount,
    SUM(od.LineTotal) AS TotalRevenue,
    AVG(od.UnitPrice) AS AvgPrice,
    STDEV(od.UnitPrice) AS PriceStdDev
FROM SalesLT.SalesOrderDetail od
JOIN SalesLT.Product p ON od.ProductID = p.ProductID
CROSS JOIN (SELECT TOP 100 * FROM SalesLT.Product) x  -- Amplify workload
GROUP BY p.ProductCategoryID
HAVING COUNT(*) > 1;
INSERT INTO dbo.BenchmarkResults (TestName, DurationMs, RowsProcessed)
VALUES ('Complex Aggregation', DATEDIFF(MILLISECOND, @start, SYSUTCDATETIME()), 0);

-- 5. View results
SELECT * FROM dbo.BenchmarkResults ORDER BY TestName;

Step 6: Compare Performance Metrics

After running the benchmark on all databases, use Azure Monitor to compare:

# Get DTU consumption for DTU-model databases
az monitor metrics list `
    --resource "/subscriptions/$(az account show --query id -o tsv)/resourceGroups/$resourceGroup/providers/Microsoft.Sql/servers/$serverName/databases/StandardDB" `
    --metric "dtu_consumption_percent" `
    --interval PT1M `
    --start-time (Get-Date).AddMinutes(-30).ToString("yyyy-MM-ddTHH:mm:ssZ") `
    --query "value[0].timeseries[0].data[-5:].{time:timeStamp, dtuPct:average}" -o table

# Get CPU percentage for vCore databases
az monitor metrics list `
    --resource "/subscriptions/$(az account show --query id -o tsv)/resourceGroups/$resourceGroup/providers/Microsoft.Sql/servers/$serverName/databases/vCoreGP" `
    --metric "cpu_percent" `
    --interval PT1M `
    --start-time (Get-Date).AddMinutes(-30).ToString("yyyy-MM-ddTHH:mm:ssZ") `
    --query "value[0].timeseries[0].data[-5:].{time:timeStamp, cpuPct:average}" -o table

Use this SQL query on each database to check resource consumption:

-- Resource consumption stats (last 1 hour)
SELECT
    end_time,
    avg_cpu_percent,
    avg_data_io_percent,
    avg_log_write_percent,
    avg_memory_usage_percent,
    CASE
        WHEN dtu_limit IS NOT NULL THEN CAST(dtu_limit AS VARCHAR) + ' DTU'
        ELSE CAST(cpu_limit AS VARCHAR) + ' vCores'
    END AS resource_limit
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;

Step 7: Test Tier-Specific Features

7a: BC Read Replica

-- Connect to vCoreBC with ApplicationIntent=ReadOnly
-- Verify read-only
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');
-- → READ_ONLY

-- Reads work
SELECT TOP 5 * FROM SalesLT.Product;

-- Writes blocked
INSERT INTO SalesLT.Product (Name) VALUES ('test');
-- Error: cannot modify read-only database

7b: Hyperscale Named Replicas

# Create a named replica (Hyperscale only)
az sql db replica create `
    --resource-group $resourceGroup `
    --server $serverName `
    --name HyperscaleDB `
    --partner-server $serverName `
    --partner-database HyperscaleDB-Replica1 `
    --secondary-type Named `
    --family Gen5 `
    --capacity 2

# Named replica has its own independent endpoint and can scale independently

🎯 Exam Focus

Hyperscale named replicas are unique — they can be different compute sizes from the primary and have their own endpoint. Use them for read-offload, analytics, and reporting without affecting primary performance. Each Hyperscale DB supports up to 30 named replicas.

7c: Serverless Auto-Pause

# Leave ServerlessDB idle for 60+ minutes, then:
$startTime = Get-Date

# This first query will "resume" the database (cold start ~60 sec)
az sql db show --resource-group $resourceGroup --server $serverName --name ServerlessDB `
    --query status -o tsv
# If "Paused" — connect and run a query to resume

$resumeTime = (Get-Date) - $startTime
Write-Host "Resume time: $($resumeTime.TotalSeconds) seconds"

Comparison Summary

Collect your benchmark results into this table:

Database	Model	Tier	Cost/mo (est.)	Insert 5K (ms)	Scan (ms)	Agg (ms)
BasicDB	DTU	Basic (5)	~$5	___	___	___
StandardDB	DTU	S2 (50)	~$75	___	___	___
PremiumDB	DTU	P1 (125)	~$465	___	___	___
vCoreGP	vCore	GP 2 vCores	~$370	___	___	___
vCoreBC	vCore	BC 2 vCores	~$920	___	___	___
HyperscaleDB	vCore	HS 2 vCores	~$500	___	___	___
ServerlessDB	vCore	GP Serverless	~$50-370	___	___	___

Decision Guide

Scenario	Recommended Tier
Dev/test, minimal load	Serverless GP (auto-pause saves $$$)
Small production app, predictable load	DTU S2-S4 or vCore GP 2 vCores
Mission-critical OLTP	Business Critical (local SSD, read replica, fast failover)
Large databases (> 4 TB)	Hyperscale (up to 100 TB, instant scaling)
Many small databases, variable load	Elastic Pool (shared DTU/vCores)
Cost-sensitive, variable workloads	Serverless (pay for actual usage, not provisioned)
Azure Hybrid Benefit available	vCore (55% savings with existing SQL licenses)

Can you mix DTU and vCore databases on the same logical server?

Click to reveal answer

Yes! The logical server is just a management boundary. Each database independently chooses its purchasing model and tier.

1 / 1

What is the only tier that supports in-memory OLTP in Azure SQL DB?

Click to reveal answer

Business Critical (vCore) and Premium (DTU). General Purpose and Standard do NOT support in-memory OLTP.

1 / 1

What happens when a Serverless database is paused?

Click to reveal answer

Compute is deallocated (no CPU charges). Storage charges continue. First connection after pause triggers a resume (~60 sec cold start). Auto-pause delay is configurable (min 1 hour) or can be disabled.

1 / 1

How many named replicas does Hyperscale support?

Click to reveal answer

Up to 30 named replicas. Each can have different compute size and its own endpoint.

1 / 1

Common Failures

Symptom	Likely Cause	Fix
Scale up rejected: "insufficient capacity in region"	Region/AZ capacity hit	Pick a different AZ; raise quota; deploy to paired region
DTU model migration to vCore loses perf	DTU → vCore mapping is approximate	Re-baseline workload; size by `avg_cpu_percent` + IOPS, not DTU
Hyperscale revert to GP not allowed	Hyperscale is one-way	BACPAC export + new GP DB; plan tier choice carefully
Serverless cold start spikes p99 latency	Auto-pause delay on first connection	Increase auto-pause delay or switch to provisioned for latency-sensitive
Business Critical reports IOPS lower than expected	App not using read scale-out	Add `ApplicationIntent=ReadOnly` to reporting connection string

⚠️ Watch Out

Hyperscale is a one-way door. You can scale compute up/down freely, but you cannot convert a Hyperscale DB back to General Purpose or Business Critical. Choose carefully — BACPAC export + recreate is the only way back.

Variations to Try

Test serverless auto-pause — measure cold start, billing impact
Provision a Hyperscale named replica — point reporting workload at it
Compare GP vs BC with same vCores under identical OLTP load — measure latency
Try Zone Redundant option on BC and Hyperscale — verify failover during AZ outage
Wire Database Watcher across all 3 tiers for unified metrics

Production Application

Dev/test, intermittent workloads → Serverless GP, auto-pause 1h.
Standard OLTP, predictable load → Provisioned GP, vCore sized by p95 CPU.
Mission-critical low-latency OLTP → Business Critical, Zone Redundant, read scale-out.
Very large DB (>4 TB) or fast scale + named replicas → Hyperscale.
Anti-pattern: don't default to Hyperscale for small DBs — GP/BC is cheaper and reversible.

Clean Up

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

Architecture​

Model Comparison​

Lab Steps​

Step 1: Deploy DTU Databases​

Step 2: Deploy vCore Databases​

Step 3: Deploy Serverless​

Step 4: Deploy Elastic Pool​

Step 5: Run Benchmark Workload​

Step 6: Compare Performance Metrics​

Step 7: Test Tier-Specific Features​

7a: BC Read Replica​

7b: Hyperscale Named Replicas​

7c: Serverless Auto-Pause​

Comparison Summary​

Decision Guide​

Common Failures​

Variations to Try​

Production Application​

Clean Up​

Architecture

Model Comparison

Lab Steps

Step 1: Deploy DTU Databases

Step 2: Deploy vCore Databases

Step 3: Deploy Serverless

Step 4: Deploy Elastic Pool

Step 5: Run Benchmark Workload

Step 6: Compare Performance Metrics

Step 7: Test Tier-Specific Features

7a: BC Read Replica

7b: Hyperscale Named Replicas

7c: Serverless Auto-Pause

Comparison Summary

Decision Guide

Common Failures

Variations to Try

Production Application

Clean Up