Azure SQL Database — Service Overview
Fully-managed PaaS database for cloud-native OLTP. You bring the schema, queries, and tier choice. Microsoft owns the OS, patching, HA, and backups.
1. Overview
- What it is: Single-database PaaS service in the Azure SQL family. SQL Server engine, but without instance-level surface (no Agent, no MSDTC, no FILESTREAM, no cross-database
USE). - Tier model:
- DTU — bundled compute/IO/memory (Basic / Standard / Premium). Simple, predictable.
- vCore — General Purpose (GP) — remote storage + local SSD cache. Default for most workloads.
- vCore — Business Critical (BC) — local NVMe + 4-node Always On AG. Sub-2ms reads, fastest failover.
- vCore — Hyperscale (HS) — page-server + log-service architecture. Up to 100 TB, near-instant backup, named replicas.
- Serverless (vCore GP only) — auto-scale + auto-pause for intermittent workloads.
- Ownership boundary: You own database, schema, tier, scaling, network rules, identity, queries. Microsoft owns OS, patching, HA, backups, and tempdb.
2. Why Use This Service
Use it when:
- Greenfield cloud-native or SaaS workload — single-database, multi-tenant via elastic pools.
- You want zero-admin HA, automated backups, and predictable scale knobs.
- Workload tolerates the PaaS feature gaps (no Agent, no cross-DB queries, no instance-level features).
Do NOT use it when:
- You need SQL Agent, Service Broker, MSDTC, FILESTREAM, CLR with
EXTERNAL_ACCESS, linked servers, or cross-databaseUSE— go to Managed Instance. - You need OS-level access, custom drivers, third-party SQL Server extensions, or trace flags — go to SQL Server on Azure VM.
- Database > 100 TB or you need custom storage layout — go to VM or split via sharding.
| If you need… | Pick instead |
|---|---|
| Near 100% SQL Server compatibility (Agent, CLR, linked servers) | Azure SQL Managed Instance |
| OS access, FILESTREAM, MSDTC, full trace flag control | SQL Server on Azure VM |
| Document / key-value with global distribution | Azure Cosmos DB |
| Open-source engine | Azure Database for PostgreSQL / MySQL |
3. Architecture
Pattern fit: Data tier in a 3-tier web app, multi-tenant SaaS, or microservices data store.
- Layer: data plane — receives requests from App Service, Functions, AKS, APIM, or Foundry agents.
- Upstream callers: app code via TDS (port 1433) or REST (Data API builder).
- Downstream dependencies: Microsoft Entra ID for identity, Key Vault for CMK / Always Encrypted, Storage for audit + LTR backups, Monitor / Log Analytics for telemetry.
- HA boundary is per-tier:
- GP: compute is stateless; remote storage triple-replicated. Failover ≈ 30 s (re-attach storage to new node).
- BC: 4-node Always On AG with local NVMe; one primary + readable secondary + 2 quorum. Failover ≈ 5–10 s.
- HS: log service + page servers + Azure Blob; near-instant backups regardless of size.
4. Key Components
| Component | Role | Notes |
|---|---|---|
| Logical server | Management + connectivity boundary | Hosts firewall, AAD admin, audit, identity. Not a real instance. |
| Database | Compute + storage unit | Each DB has its own tier, scale, and backup chain. |
| Elastic pool | Shared resource pool across DBs | Cost win when DB peaks don't overlap. |
| Storage tier (per SKU) | GP remote · BC local NVMe · HS page servers + Blob | Drives latency and the failover model. |
| Query Store | Always-on plan history + regression detection | Backbone for Automatic Tuning. |
| Automatic Tuning | Force-last-good-plan + index advisor | Force-plan is on by default; index actions are opt-in. |
5. Data Flow
Read paths bypass storage when the working set fits the local SSD cache (GP) or local NVMe (BC). Hyperscale read paths hit page servers, not Blob, in steady state.
6. Deployment & Configuration
Architect-grade decisions only — full click-paths live in Microsoft Learn.
| Decision | Default | When to override |
|---|---|---|
| Tier | GP Gen5 | BC if log_write_percent saturates GP; HS if size > 4 TB or you need named replicas. |
| Purchasing model | vCore | DTU only for very small / predictable workloads where a single SKU number is easier to reason about. |
| Compute | Provisioned | Serverless for dev/test or workloads idle > 1 hour. |
| Network | Public + firewall | Private Endpoint for any production workload behind a VNet. |
| Identity | SQL admin | Entra ID admin + managed identity for app connections. Disable SQL auth where possible. |
| MAXDOP | 0 (unlimited) | Set per workload via ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = n; — not sp_configure. |
| Compatibility level | Latest | Keep source compat after migration; raise it after Query Store proves stability. |
| Automatic Tuning | Force-last-good-plan ON | Review Create/Drop Index advisors before turning them on in prod. |
MAXDOP guidance
| vCores | OLTP | Reporting / DW | Mixed |
|---|---|---|---|
| 2–4 | 1–2 | 0 | 2 |
| 8 | 4 | 0 or 8 | 4 |
| 16+ | 8 | 0 | 8 |
In Azure SQL DB, MAXDOP is database-scoped, not instance-scoped. sp_configure is rejected. This is one of the most-tested differences vs MI/VM on DP-300.
Compat level migration pattern
- Migrate at the source compat level (don't jump to latest on day one).
- Enable Query Store — it's on by default, confirm retention.
- Raise compat level.
- Watch Query Store for plan regressions; force the previous plan if needed.
FORCE_LAST_GOOD_PLAN(on by default) handles regressions automatically.
Serverless
| Setting | Detail |
|---|---|
| Min / Max vCores | 0.5 → 80 (set the floor + ceiling) |
| Auto-pause delay | ≥ 60 minutes idle |
| Wake-up | ~1 minute on first connection |
| Billing | Per-second compute + storage (storage billed even when paused) |
Serverless wins for dev/test and bursty internal apps. It loses for steady-state OLTP and any latency-sensitive customer-facing workload.
Elastic pools
Multi-tenant SaaS pattern. Rule of thumb: pool wins when 1.5 × eDTUs × DB count < Σ peak DTUs — i.e. peaks don't overlap.
7. Security
Service-specific only. Generic Azure baseline lives in the platform docs.
- Identity: Entra ID + managed identity is the recommended default. Disable SQL auth for production where possible.
- Encryption: TDE with service-managed key is always on. Bring your own key via Key Vault for CMK. Always Encrypted (with secure enclaves on BC/HS) for column-level protection where keys never leave the client.
- Network: Public endpoint is the default — production should use Private Endpoint + denied public access. VNet service endpoints are the older pattern; Private Link is preferred.
- Audit: Native audit to Log Analytics, Storage, or Event Hub. Enable at the logical-server level so new databases inherit the policy.
For deep dives see Authentication & Authorization, Encryption & Network, and Always Encrypted with VBS enclaves.
8. Monitoring & Optimization
The platform exposes resource-governance metrics that map directly to throttling causes. Five metrics solve 80% of performance tickets:
| Symptom | Metric | What it means | Fix |
|---|---|---|---|
| Slow inserts / commits | log_write_percent = 100 | Hitting log-rate cap | Scale up vCores, move to BC, batch writes |
| Slow reads | physical_data_read_percent = 100 | IOPS cap | Scale up, BC for local NVMe, tune indexes |
| Queries queueing | workers_percent > 80 | Worker thread starvation | Scale up, kill long blockers |
| Connection refused | sessions_percent = 100 | Session limit | Scale up, fix connection leaks |
| Generally slow | cpu_percent > 80 | Compute cap | Scale up or out (read replicas) |
Tuning surface:
- Query Store: always on; back-bone for plan analysis and regression hunting.
- Intelligent Query Processing (IQP): activated by compat level, not a switch. Compat 150 unlocks Batch-Mode-on-Rowstore, Memory Grant Feedback, Adaptive Joins, Scalar UDF Inlining. Compat 160 (SQL 2022) adds Parameter-Sensitive Plan, CE Feedback, DOP Feedback.
- Automatic Tuning:
FORCE_LAST_GOOD_PLANon by default;CREATE_INDEXandDROP_INDEXopt-in — review recommendations before enabling in production. - tempdb: fully managed — you can't add files. Optimize queries to reduce tempdb pressure (avoid large sorts, prefer batched DML).
Oracle DBA parallel: Resource governance ≈ Oracle Resource Manager profiles, but enforced by the platform per tier — you cannot disable it. IQP ≈ Oracle Adaptive SQL Plan Management (adaptive plans, statistics feedback). Automatic Tuning ≈ Oracle SQL Plan Baselines + Tuning Advisor, but proactive instead of advisory.
9. Cost Considerations
| Cost driver | Knob |
|---|---|
| Compute (vCore / DTU) | Tier × vCores. Reserved Capacity (1y / 3y) saves up to ~55%. |
| Azure Hybrid Benefit (AHB) | Existing SQL licenses → up to ~55% off vCore compute. Stacks with Reserved Capacity. |
| Storage | Linear with size. HS storage is cheaper per GB than BC at scale. |
| BC premium | BC ≈ 3–4× GP for the same vCores — only pay it if latency or read replicas justify it. |
| Backup retention | Default PITR (7–35 days) is free in tier. LTR (yearly archives) is billed separately. |
| Read replicas / named replicas (HS) | Each replica is its own compute bill. |
| Egress | Cross-region replication and geo-restore traffic. |
Quick wins:
- Serverless auto-pause for dev/test → dollars saved on idle time.
- Elastic pools for non-overlapping multi-tenant peaks.
- AHB + Reserved Capacity for steady-state production.
- Right-size compat level + IQP before scaling up — cheap performance wins.
10. Real Scenarios
-
Multi-tenant SaaS — 200 small DBs. Elastic pool on GP, AAD-only auth, Private Endpoint, audit to Log Analytics. Pool sized at
1.5 × peak eDTUs × tenant_count— saves ~40% vs per-DB SKUs. tempdb contention isn't your problem; query design is. -
Lift-and-shift OLTP — 1.5 TB, latency-sensitive. Start on BC 8 vCore (local NVMe < 2 ms reads, sub-10 s failover, free read replica for reporting). Migrate at source compat, raise after Query Store proves stability. Workloads that need SQL Agent stay on MI — this scenario doesn't, so SQL DB wins on cost.
-
Analytics-adjacent OLTP — 12 TB, growing. Hyperscale, single primary + 2 named replicas (different vCore sizes for reporting vs ops). Backups are near-instant regardless of size. Don't go BC — 12 TB exceeds the 4 TB tier cap, and HS log-service architecture handles the write rate cleanly.
11. Diagram
┌────────────┐ ┌────────────────────┐ ┌──────────────────────┐
│ Client │ → │ Logical Server │ → │ Database (tier) │
│ App / AKS │ │ + AAD admin │ │ GP · BC · HS │
└────────────┘ │ + Firewall / PE │ └──────────┬───────────┘
└────────────────────┘ │
│ ▼
▼ ┌──────────────────┐
┌──────────────────┐ │ Storage tier │
│ Entra ID │ │ Remote · NVMe · │
│ Key Vault CMK │ │ Page servers │
└──────────────────┘ └────────┬─────────┘
│
▼
┌──────────────────┐
│ Monitor / LA / │
│ Audit · Backups │
└──────────────────┘