SQL Scalability
Elastic pools · Read scale-out · HA and named replicas · Sharding
Practice questions for this topic →
Scale patterns
| Pattern | Use for |
|---|---|
| Scale up (vCores/tier) | More power for one database; brief reconnect |
| Elastic pool | Many databases with varying, unpredictable peaks sharing one budget (SaaS database-per-tenant) |
| Read scale-out | Offload read-only queries to a built-in replica with ApplicationIntent=ReadOnly |
| Hyperscale HA replicas | Hot standbys for failover (0 to 4), same name and service objective, hidden |
| Hyperscale named replicas | Independent read replicas with their own size, name and access |
| Geo-replica / failover group read-only listener | Read workloads in another region, or reads in tiers without replicas |
| Sharding (Elastic Database tools) | Horizontally partition data across many databases to scale writes |
Facts to know
- Elastic pool billing: per hour at the highest eDTU or vCores, regardless of usage; no per-database charge. Databases in a pool share the pool's resources within optional per-database minimum and maximum limits.
- Read scale-out is enabled by default on new Premium, Business Critical and Hyperscale databases (Hyperscale needs at least one secondary replica). It's always on for Managed Instance Business Critical. It's at no extra cost in Premium and Business Critical.
- Basic, Standard and General Purpose have no replicas in their HA architecture, so read scale-out isn't available; use a geo-replica, or the failover group read-only listener for Managed Instance.
- Hyperscale HA replicas: 0 to 4; same server and database name and the same service objective as the primary; billed at the primary's compute rate with no storage charge; failover is automatic and the connection string doesn't change (applications should retry dropped connections).
- In Hyperscale, ApplicationIntent=ReadOnly with no secondary replica routes to the primary.
Exam Tips
Tick each tip once you can answer it without looking.
“200 tenant databases with unpredictable, non-overlapping peaks, minimize cost” → Elastic pool. Trap: Sizing each database for its peak wastes money.
“Reporting queries slow the OLTP primary; no extra cost” → Read scale-out replica (Business Critical/Premium).
“Data volume exceeds what one database can hold and must scale out writes” → Sharding (Elastic Database tools) or Hyperscale.
“Multiple independent read replicas with different sizes” → Hyperscale named replicas.
“Users in another region need fast reads” → Readable geo-replica in that region.
“Need to change compute size quickly for month-end” → Scale up vCores (online, short reconnect).
“Elastic pool billing” → Per hour at the highest eDTU or vCores, regardless of usage; no per-database charge. Trap: Pools suit databases with varying, unpredictable peaks.
“Hyperscale HA replicas” → 0 to 4; same server, database name and service objective as the primary; hidden; billed at the primary compute rate with no storage charge. Trap: Failover is automatic and the connection string doesn't change.
“Where read scale-out is available” → Premium, Business Critical (enabled by default) and Hyperscale with at least one secondary replica. Trap: Basic, Standard and General Purpose have no replicas.