1 · Azure SQL Family Overview
2 · Purchase Models: DTU vs vCore
- DTU (Database Transaction Unit) — bundled CPU + IO + memory. Simple & predictable billing. Best for stable, well-understood workloads.
- vCore — independently scale compute (vCores) and storage. Supports Azure Hybrid Benefit (reuse on-prem SQL licenses for ~55% savings). Best when you need fine-grained control or license portability.
Sizing tip: Start with DTU for dev/test simplicity; switch to vCore for production where hybrid benefit or Hyperscale tier is needed.
3 · Service Tiers
4 · Elastic Pools
Share eDTUs or vCores across many databases that have unpredictable, bursty usage. Ideal for SaaS multi-tenant patterns.
- Right-sizing: Pool eDTUs ≈ sum of average per-DB usage (not peaks). Set per-DB min/max to prevent noisy-neighbor.
- When to pool: Average utilisation per DB < 50% of peak. Pools save cost when DBs don't peak simultaneously.
- Limits: Up to 500 DBs per pool; mix of active and idle DBs is fine.
5 · Geo-Replication & Auto-Failover Groups
- Active geo-replication: Up to 4 readable secondaries in any region. Manual failover. Per-database.
- Auto-failover groups: Group-level automatic failover with grace period. Provides a listener endpoint for transparent reconnection. Works with SQL Database and Managed Instance.
- RPO: < 5 seconds (async). RTO: < 30 seconds with auto-failover groups.
6 · SQL Managed Instance Deep Dive
MI deploys into a dedicated subnet within your VNet — no public endpoint by default. It supports:
- Cross-database queries, linked servers, SQL Agent jobs
- CLR, Service Broker, Database Mail
- Native backup to Azure Blob (URL), automated backups with PITR (up to 35 days)
- Instance-scoped features: tempdb, server-level collation, logins
Networking: Requires a /27 or larger dedicated subnet (Microsoft recommends /26). NSG rules and UDRs managed partly by the service.
Migration path: Near-zero downtime via Azure Database Migration Service (DMS) online mode, or offline via .bak restore from Blob storage.
7 · Security Features
- TDE (Transparent Data Encryption): Enabled by default. Encrypts data at rest with service-managed or customer-managed keys (BYOK via Key Vault).
- Always Encrypted: Client-side encryption — data stays encrypted in-flight and at-rest. Server never sees plaintext. Great for PCI/PHI columns.
- Dynamic Data Masking: Obfuscates sensitive columns for non-privileged users without app changes.
- Azure AD Authentication: Centralised identity; supports MFA, Conditional Access, managed identities for app auth.
- Private Endpoints / VNet Service Endpoints: Restrict traffic to your virtual network. MI is VNet-native by design.
- Microsoft Defender for SQL: Vulnerability assessment + Advanced Threat Protection (SQL injection detection, anomalous access).
8 · Migration Paths
- Assessment: Azure Migrate + Data Migration Assistant (DMA) — identifies compatibility issues and SKU recommendations.
- Offline migration: Backup/restore (.bak to Blob → MI), BACPAC import (SQL Database), or DMS offline mode. Simple but incurs downtime.
- Online migration: DMS online mode uses log replay (transactional replication under the hood) for near-zero downtime cutover.
- SQL Database: BACPAC, DMS, or transactional replication for ongoing sync.
- Post-migration: Update compatibility level, enable Query Store, validate with Database Experimentation Assistant (DEA).
9 · Real-World Scenario
SaaS Company: Elastic Pool vs Managed Instance
Context: A B2B SaaS vendor serves 200 tenants. Each tenant has its own database (~5 GB). The application uses cross-database queries for analytics and SQL Agent jobs for nightly ETL.
Option A — SQL Database Elastic Pool: Cost-efficient resource sharing, auto-scaling per DB. But lacks cross-DB queries natively and no SQL Agent.
Option B — SQL Managed Instance: Supports cross-DB queries and SQL Agent natively. Higher baseline cost but eliminates the need to refactor ETL into Azure Data Factory.
Decision: They chose MI (General Purpose, 16 vCores) because:
- Cross-database joins used in 30+ stored procedures — rewriting would take months.
- SQL Agent jobs run complex multi-step ETL with error handling already built.
- VNet-native deployment satisfied their compliance requirement for no public endpoints.
Cost optimization: Azure Hybrid Benefit (existing SA licenses) reduced MI cost by 55%, making it comparable to an elastic pool for their workload.
💡 Exam Tip
When a scenario mentions cross-database queries, SQL Agent, Service Broker, CLR, or linked servers — the answer is SQL Managed Instance. If it mentions needing 100 TB+ databases or fast read scale-out, think Hyperscale. For simple single-DB apps with minimal management, SQL Database (single) is cheapest. Elastic pools shine when multiple DBs have bursty, non-overlapping peaks.