Azure SQL Deployment Options
The Decision Tree
The #1 question on DP-300: "Which deployment option should you recommend?" Use this mental model:
| Result | When to Choose |
|---|---|
| 🔴 SQL Server on VM | OS access, FILESTREAM, MSDTC, 3rd-party software |
| 🔵 Managed Instance | Lift-and-shift migration, need Agent/CLR/cross-DB, ~99% compat |
| 🟢 Azure SQL Database | Greenfield cloud-native apps, serverless, elastic pools |
| 🟣 Hyperscale | Databases > 4 TB, up to 100 TB, instant backups |
Comparison Matrix
| Feature | Azure SQL Database | SQL Managed Instance | SQL Server on VM |
|---|---|---|---|
| Type | PaaS | PaaS | IaaS |
| Compatibility | ~95% T-SQL | ~99% SQL Server | 100% SQL Server |
| You manage | Queries, indexes, security | + Agent jobs, instance settings | + OS, patching, HA config |
| Microsoft manages | Patching, HA, backups, OS | Patching, HA, backups, OS | Hardware only |
| OS Access | ❌ | ❌ | ✅ |
| Max Size | 100 TB (Hyperscale) | 16 TB | Unlimited |
| Agent Jobs | ❌ (Use Elastic Jobs) | ✅ | ✅ |
| Cross-DB Queries | Elastic Query only | ✅ (USE works) | ✅ |
| CLR | ❌ | ✅ | ✅ |
| Linked Servers | ❌ | ✅ | ✅ |
| FILESTREAM | ❌ | ❌ | ✅ |
| MSDTC | ❌ | ❌ | ✅ |
The migration evaluation order: MI first → VM only if MI can't support the workload → SQL DB for greenfield. This is the official Microsoft recommendation and appears frequently in exam scenarios.
Azure SQL Database — Architecture Deep Dive
Fully managed database engine. Think of it as "database as a service" — you never touch the instance.
Architecture
Azure SQL Database uses a Logical Server as a management boundary (not a compute instance). Each database gets its own compute + storage. Security policies (firewall, auditing, TDE) are set at server level.
Key architectural concepts:
- Logical server = management container (firewall rules, admin login, auditing). NOT a compute instance.
- Each database has its own compute + storage (they don't share unless in an Elastic Pool).
- Elastic Pools = shared DTU/vCore budget across multiple databases. Perfect for SaaS (database-per-tenant).
Purchasing Models (Critical for Exam)
| DTU Model | vCore Model | |
|---|---|---|
| Analogy | Meal deal (bundled) | A la carte (pick components) |
| CPU | Abstracted | Explicit vCore count |
| Storage | Included per tier | Independent — pay for what you provision |
| Serverless | ❌ | ✅ (auto-pause to $0 compute) |
| Hyperscale | ❌ | ✅ (up to 100 TB) |
| Azure Hybrid Benefit | ❌ | ✅ (save up to 55%) |
| Best for | Simple/dev workloads | Production, migrations, cost optimization |
Three facts the exam loves: 1) Azure Hybrid Benefit = vCore only. 2) SQL Managed Instance = vCore only (no DTU). 3) Serverless and Hyperscale = vCore only. If the question mentions any of these → answer involves vCore.
SQL Managed Instance — Architecture Deep Dive
Best for lift-and-shift migrations. Think of it as "SQL Server instance as a service".
Key architectural concepts:
- VNet-native — deployed inside YOUR virtual network (unlike SQL DB which has a public endpoint by default)
- SQL Agent — fully supported (critical for migrations from on-prem)
- Cross-database queries —
USE [OtherDB]just works (SQL DB can't do this) - Link feature — near real-time replication from on-prem SQL Server (great for staged migrations)
- Subnet must be dedicated — no other resources allowed, minimum /27
Oracle DBA parallel: MI is like having a CDB/PDB architecture — one instance, multiple databases, cross-DB queries work natively. SQL DB is more like a single PDB — isolated, no cross-DB.
SQL Server on Azure VMs — When You Need Full Control
Full SQL Server, full OS access. Use when you need:
- FILESTREAM, MSDTC — not supported in MI
- Third-party software on the same server (backup agents, monitoring tools)
- Exact SQL Server version matching (e.g., SQL 2016 compatibility)
- Cluster features not available in MI (FCIs with shared disks)
Key architectural fact: Register SQL VMs with the SQL IaaS Agent extension to get:
- Automated backups, patching, and monitoring
- Storage configuration optimization
- Azure portal integration
- License management (AHB tracking)
SQL VM Storage Architecture
SQL VM storage best practices (heavily tested on DP-300):
- Separate disks for data, log, and tempdb — never put them on the same disk
- TempDB on local SSD (D: drive) — fastest I/O, zero cost, but ephemeral (lost on VM restart)
- Premium SSD P30+ for data/log — minimum 5000 IOPS
- Enable Read Caching on data disks, disable caching on log disks
- Use Storage Spaces to stripe multiple disks for higher IOPS/throughput
Common exam trap: "You need full SQL Server compatibility" → NOT always VM! MI offers ~99% compatibility. VM is only needed for the ~1% of features MI doesn't support (FILESTREAM, MSDTC, FCI, third-party OS software).
Storage Architecture — The Complete Comparison
This is the most important architecture difference between the three deployment options. Understanding storage = understanding performance, cost, and HA behavior.
Side-by-Side Architecture
Detailed Storage Comparison
| Aspect | SQL Server on VM | SQL Managed Instance | Azure SQL Database |
|---|---|---|---|
| Storage type | Azure Managed Disks (you choose) | Azure Premium Storage (GP) or Local SSD (BC) | Remote storage (GP) or Local SSD (BC/HS) |
| Max size | Unlimited (add disks) | 16 TB | 4 TB (GP/BC), 100 TB (Hyperscale) |
| IOPS control | Full — pick disk SKU, count, stripe | Limited — tied to tier/vCores | Limited — tied to tier/vCores |
| Data disk IOPS | 160–160,000+ (Ultra Disk) | GP: ~500-7,500 / BC: ~30K-400K+ | GP: ~500-7,500 / BC: ~30K-400K+ |
| Latency | ~1-2ms (Premium SSD) | GP: ~5-10ms / BC: ~1-2ms | GP: ~5-10ms / BC: ~1-2ms |
| TempDB | Local SSD (D: drive) — YOU configure | Managed — scales with vCores | Managed — scales with compute |
| Storage scaling | Add/resize disks (may need restart) | Grow online (no downtime) | Grow online (no downtime, GP/HS) |
| Backup storage | You manage (Blob, disk, URL) | Automated — Azure Premium Storage + LRS/GRS | Automated — Azure Blob + LRS/ZRS/GRS |
| Read caching | You configure per disk (Host Caching) | Managed by Azure | Managed by Azure |
| Disk striping | Storage Spaces (you configure) | Managed by Azure | Managed by Azure |
| Encryption at rest | SSE + optional ADE (BitLocker) | TDE (always on) | TDE (always on) |
General Purpose Storage Architecture (MI & SQL DB)
How it works:
- Compute node is stateless — it can be replaced without data loss
- RBPEX (local SSD cache) keeps hot pages — improves read latency
- Remote Premium Storage holds all data — durable even if compute dies
- Failover: Azure provisions new compute, attaches to same storage (~30 seconds)
RBPEX is the secret sauce of General Purpose tier. It's a local SSD buffer that caches frequently accessed data pages. If your working set fits in RBPEX, GP performance approaches BC performance. If it doesn't → you see ~5-10ms storage latency.
Business Critical Storage Architecture (MI & SQL DB)
Key facts: Failover ~5-10 sec | SLA 99.995% (zone-redundant) | 1 free read replica | In-Memory OLTP supported
How it works:
- 4 replicas, each with its own local NVMe SSD (full data copy)
- All writes go to primary → synchronously committed to all 3 secondaries
- Failover: promote a secondary (~5-10 seconds) — data already local
- One free read-only replica — use
ApplicationIntent=ReadOnly
Hyperscale Storage Architecture (SQL DB Only)
How it works:
- Page Servers each cache a portion of the database in memory + local SSD
- Log Service handles write-ahead logging (fast, persistent)
- Azure Blob is the ultimate durable store — holds the complete database
- Backups are near-instant (snapshot-based, regardless of DB size)
- Named Replicas can have different compute sizes — perfect for isolating workloads
Hyperscale is the only tier that:
- Supports databases > 4 TB (up to 100 TB)
- Has near-instant backups regardless of size
- Supports Named Replicas (independent compute scale)
- Can reverse-migrate back to General Purpose (if ≤ 1 TB) If the exam mentions a 10+ TB database → Hyperscale is the answer.
Storage Decision Quick Reference
| Question | Answer | Recommendation |
|---|---|---|
| Need full disk control? | Yes | SQL VM — Premium SSD + Ultra Disk |
| Need < 2ms latency or read replicas? | Yes | Business Critical — Local NVMe SSD, 4-node AG |
| Database > 4 TB? | Yes | Hyperscale — Page Servers + Blob, up to 100 TB |
| None of the above | Default | General Purpose — Remote storage + RBPEX cache, best price/perf |
SQL VM Disk Configuration Cheat Sheet
| Disk Role | Recommended SKU | Caching | IOPS Example | Notes |
|---|---|---|---|---|
| OS | Premium SSD P6 (64 GB) | Read/Write | 240 | Default, no tuning needed |
| Data (.mdf) | Premium SSD P30+ or Ultra | Read-Only | 5,000-160,000 | Stripe multiple for higher IOPS |
| Log (.ldf) | Premium SSD P20+ or Ultra | None | 2,300+ | Sequential writes — caching hurts |
| TempDB | Local SSD (D: drive) | N/A | Very high | Free, ephemeral, lost on restart |
| Backup | Standard SSD or Blob | None | N/A | Cost-efficient, URL backup to Blob preferred |
Oracle DBA parallel: Think of this as Oracle ASM disk groups but simpler. Data disks = +DATA, Log disks = +REDO, TempDB = +TEMP on local flash. The key difference: Azure handles redundancy at the storage layer (triple replication) — you don't need ASM mirroring.
Elastic Pools — The Multi-Tenant Architecture
| Without Pool | With Elastic Pool | |
|---|---|---|
| DB 1 | 50 DTU (peak 2pm) | Shares pool |
| DB 2 | 50 DTU (peak 6pm) | Shares pool |
| DB 3 | 50 DTU (peak 10am) | Shares pool |
| Total cost | 150 DTU (3 x 50) | 80 eDTU (peaks don't overlap) |
| Savings | — | ~47% less |
When to use: Databases with spiky, unpredictable, non-overlapping usage patterns. Rule of thumb: Pool makes sense when 1.5x eDTUs × count < sum of individual DTUs.
Elastic Pools support both DTU and vCore models. They're available for Azure SQL Database only — NOT for Managed Instance. MI itself is a single-instance concept (no pooling).