5.3 Database Scalability Design
Key Takeaways
- Vertical scaling (changing compute size/tier) is the simplest lever but has a ceiling; horizontal scaling — elastic pools, read replicas, and sharding — removes that ceiling at increasing levels of application complexity.
- Elastic pools share eDTU/vCore capacity across many databases and are the standard fit for SaaS multi-tenant workloads with non-overlapping usage peaks; pool eDTU pricing runs about 1.5x single-database pricing but total capacity needed is far lower.
- Hyperscale supports up to 30 independently sized named replicas — the correct pattern for isolating reporting/analytics load from a primary OLTP workload without building an ETL pipeline.
- PostgreSQL and MySQL Flexible Server support up to 5 asynchronous read replicas, but only on General Purpose or Memory Optimized tiers — not on Burstable.
- Sharding via the elastic database tools is the last-resort scalability mechanism, used only after vertical scaling, pooling, and read replicas have been ruled out, because it pushes query complexity into the application layer.
Why This Topic Matters
The official bullet "Recommend a solution for database scalability" asks you to go beyond picking a single database's size and instead design how a relational workload scales — vertically, horizontally, or across many databases at once. AZ-305 scenarios in this area typically describe growth (more tenants, more read traffic, more data volume) and ask which scale-out mechanism removes the bottleneck without a full re-architecture.
Vertical vs. Horizontal Scaling
- Vertical scaling (scale up/down): change the compute size or service tier of a single database (e.g., move from 4 vCores to 16 vCores, or General Purpose to Business Critical). This is the simplest lever but has a ceiling — a single database has a maximum size and compute regardless of tier.
- Horizontal scaling (scale out): spread load across multiple database instances — through read replicas, elastic pools, or sharding — so no single database node has to carry the entire workload.
Elastic Pools
An elastic pool lets many databases share a pool of compute and storage resources (either DTU-based eDTUs or vCore-based), instead of provisioning each database for its own peak. This directly targets SaaS multi-tenant architectures where each tenant has a dedicated database but usage peaks at different times.
- Pool eDTU unit pricing is about 1.5x the single-database DTU unit price, but because dozens or hundreds of databases share the same pool, the total eDTUs needed is far less than if every database were provisioned individually for its own peak.
- You can set per-database minimum and maximum resource limits inside the pool (a common pattern: cap any one tenant at roughly 25-40% of total pool capacity) so a single noisy tenant cannot starve the others.
- Elastic pools work with both Azure SQL Database and vCore-based Managed Instance pools (instance pools).
Read Scale-Out and Read Replicas
Several Azure relational services offer readable secondary replicas purely for scaling read traffic away from the primary:
| Service/tier | Read replica mechanism |
|---|---|
| Business Critical / Premium | One free built-in readable secondary via ApplicationIntent=ReadOnly in the connection string |
| Hyperscale | Up to 30 named replicas, each with independently configurable compute size, plus each named replica can itself have up to 4 HA replicas |
| PostgreSQL / MySQL Flexible Server (General Purpose or Memory Optimized only — not Burstable) | Asynchronous physical replication to up to 5 read replicas |
Key distinction for the exam: Hyperscale named replicas can be sized independently from the primary — this means you can attach a large-compute named replica dedicated to a heavy reporting workload while keeping the primary's compute lean for OLTP, which is a design pattern the exam likes to test as "isolate analytics from transactional load without ETL."
Sharding
When a workload's data volume or transaction rate exceeds what any single database (even at maximum vCore/storage) can sustain, the answer is sharding: partitioning data horizontally across multiple independent databases (shards), typically by a key such as customer ID or region. Azure supports this pattern through the elastic database tools (including the split-merge service for moving shards) built for Azure SQL Database. Trade-offs to weigh:
- Sharding removes the practical scale ceiling of a single database, but it pushes complexity into the application layer — cross-shard queries, joins, and transactions become expensive or impossible without additional tooling.
- It is the last scalability lever to reach for, after vertical scaling, elastic pools, and read replicas have been ruled out, because of that added application complexity.
Exam Scenarios
Scenario A: A SaaS vendor has 400 tenant databases. Usage is highly variable — most tenants are idle most of the day, with different tenants peaking at different times. The team wants to avoid provisioning every database for its own worst case. Answer: an elastic pool, with per-database min/max limits to prevent one tenant from starving others.
Scenario B: An e-commerce platform's OLTP database is under heavy load from ad hoc analytics dashboards that finance runs against it every afternoon. The team wants to isolate this reporting load without building an ETL pipeline. Answer: a Hyperscale named replica sized for the reporting workload, separate from the primary's compute.
Scenario C: A single-tenant application has grown beyond the maximum supported size and compute of any single Azure SQL Database tier, and transaction volume continues to climb. Answer: sharding using the elastic database tools, partitioning data by a chosen key (e.g., customer region).
Scenario D: A read-heavy reporting workload against a PostgreSQL Flexible Server database (General Purpose tier) needs to scale reads without impacting the primary's write performance. Answer: add one or more read replicas — up to 5 are supported for General Purpose/Memory Optimized tiers.
Common Traps
- Recommending sharding before ruling out simpler options (vertical scale, elastic pools, read replicas) — the exam rewards the least complex solution that meets the requirement.
- Forgetting that read replicas on PostgreSQL/MySQL Flexible Server are not supported on the Burstable tier — a scenario using Burstable compute cannot use this scale-out mechanism until it moves to General Purpose or Memory Optimized.
- Confusing elastic pools (resource sharing among many databases for cost/utilization efficiency) with read replicas (offloading read traffic from one primary) — they solve different scalability problems and a scenario may require both together.
A SaaS provider runs 300 tenant databases with highly variable, non-overlapping usage peaks and wants to minimize total provisioned capacity. Which Azure SQL Database feature best fits this requirement?
A company needs to run heavy reporting queries against a Hyperscale database without impacting the primary's OLTP performance, using a replica sized independently and larger than the primary. What should you recommend?
A single-tenant application's transaction volume and data size have exceeded what any single Azure SQL Database tier and compute size can support. Vertical scaling, elastic pools, and read replicas have all been ruled out as insufficient. What is the appropriate next step?