Azure SQL Database
Azure SQL Database is the SQL Server engine delivered as a fully managed platform service. Azure runs the patching, backups, high availability, and the host; you get a database (or a logical server full of them) and design schema and queries. It is the default relational database for new applications on Azure.
What it is not is a full SQL Server instance. There is no SQL Server Agent, no cross-database queries by default, no instance-level features — those live in SQL Managed Instance. Reaching for a database when you needed an instance, or the reverse, is the most common early mistake in this chapter.
Purchasing Models
Two purchasing models price the same engine differently. The DTU model bundles compute, memory, and I/O into one pre-mixed unit — simple, and inflexible when your workload is heavy on one dimension. The vCore model exposes compute and storage separately, supports Azure Hybrid Benefit and reserved capacity, and is the model for anything beyond a small app.
Service Tiers
On vCore, three service tiers map to durability and performance needs. General Purpose is the balanced default, with compute and remote storage. Business Critical adds local SSD and a built-in Always On replica set for the lowest latency and fastest failover. Hyperscale decouples storage into a distributed layer that grows to 128 TB with near-instant backups and fast read-scale replicas.
| Tier | Storage | Use for |
|---|---|---|
| General Purpose | Remote, balanced | Most workloads |
| Business Critical | Local SSD + replicas | Low latency, fast failover |
| Hyperscale | Distributed, up to 128 TB | Very large or fast-growing databases |
Single Database and Elastic Pools
A single database has its own dedicated resources. An elastic pool shares a budget of resources across many databases, smoothing cost when each database peaks at a different time — the right model for SaaS applications with a database per tenant, where most tenants are idle at any moment. Provisioning every tenant its own dedicated database instead wastes capacity continuously.
Availability and Backups
Zone-redundant configuration spreads replicas across availability zones for resilience to a datacenter failure. Backups are automatic, with point-in-time restore within the retention window (up to 35 days) and optional long-term retention for compliance. Active geo-replication and failover groups extend this to cross-region read replicas and disaster recovery.
Security
Microsoft Entra authentication replaces SQL logins with directory identities and supports managed identities for applications. Data is encrypted at rest with Transparent Data Encryption by default. Private endpoints keep the database off the public internet, and the default should be Entra auth plus a private endpoint, not a public server with firewall rules.
Azure SQL Database — Fully managed single databases or pools, newest features first. Choose it for new apps that do not need instance-level SQL Server features.
SQL Managed Instance — Near-full instance compatibility — Agent, cross-database queries, CLR. Choose it to migrate an existing SQL Server app with minimal change.
SQL Server on a VM — Full control of the OS and SQL Server, and full operational ownership. Choose it only when you need a specific version or OS-level access.
- Choosing Azure SQL Database for an app that depends on SQL Agent jobs or cross-database queries — those need Managed Instance, and the gap surfaces late in migration.
- Staying on the DTU model for a workload that is heavy on one dimension, paying for bundled capacity you cannot rebalance.
- Provisioning a dedicated database per SaaS tenant instead of an elastic pool, wasting capacity on tenants that are idle most of the time.
- Leaving the logical server on a public endpoint with firewall rules instead of using a private endpoint and Entra authentication.
- Ignoring zone-redundant configuration in production, so a single datacenter failure takes the database down.
- Forgetting that point-in-time restore is capped at 35 days, then needing a recovery point from beyond it with no long-term retention configured.
- Use the vCore model for anything beyond a small app — it separates compute and storage and unlocks reservations and Hybrid Benefit.
- Choose Business Critical for low-latency, fast-failover needs; Hyperscale for very large or fast-growing databases.
- Use elastic pools for database-per-tenant SaaS so idle tenants do not each pay for dedicated capacity.
- Enable zone-redundant configuration in production and use failover groups for cross-region disaster recovery.
- Authenticate with Microsoft Entra and managed identities; reach the database over a private endpoint.
- Set long-term backup retention when compliance needs recovery points beyond the 35-day point-in-time window.
Knowledge Check
An application relies on SQL Server Agent jobs and cross-database queries. Which Azure option fits with the least rework?
- SQL Managed Instance — near-full instance compatibility including Agent and cross-database queries
- Azure SQL Database single database on a standalone vCore deployment
- Azure SQL Database in an elastic pool that shares one vCore budget across many single databases on a logical server
- Azure Cosmos DB with the SQL API for JSON document queries
Why use an elastic pool for a database-per-tenant SaaS application?
- Tenants share a resource budget, so idle tenants do not each pay for dedicated capacity while peaks are smoothed
- It gives each tenant its own guaranteed, permanently isolated set of vCores and memory that never overlap with neighboring tenants
- It is the only deployment model that supports point-in-time restore from automated backups
- It removes the need for zone redundancy by spreading databases across zones
What does the Hyperscale service tier provide?
- Distributed storage to 128 TB with near-instant backups and fast read-scale replicas
- Local SSD storage with a built-in Always On replica set for the lowest possible read latency
- A bundled DTU unit packaging compute, memory, and I/O together
- Instance-level features like SQL Agent and cross-database queries
You got correct