Database Performance & Scalability
Service tiers, compute tiers, elastic pools, read replicas, and sharding — design a database architecture that scales with your workload without breaking the budget.
Why database performance design matters
Choosing a database tier is like choosing a car. A city car (Basic/Standard) is cheap and fine for daily errands. A sports car (Premium) handles high performance. A truck (Hyperscale) carries massive loads. The wrong choice means you’re either overpaying or stuck in traffic.
The architect’s job: match the service tier to the workload pattern, design scalability for growth, and use cost optimisation techniques like reserved capacity and elastic pools.
Azure SQL Database service tiers
| Factor | General Purpose | Business Critical | Hyperscale |
|---|---|---|---|
| Storage type | Remote storage (Azure Premium Storage) | Local SSD (attached to compute) | Multi-tier: local SSD cache + page servers |
| I/O latency | 5-10 ms (remote storage) | 1-2 ms (local SSD) | Low (cached reads) to variable (uncached) |
| HA model | Remote storage replication (1 replica) | Always On AG (3 replicas + local) | Distributed architecture (named replicas) |
| SLA | 99.99% | 99.99% (99.995% zone-redundant) | 99.99% (99.995% zone-redundant) |
| Max database size | 4 TB | 4 TB | 100 TB |
| Read replicas | No built-in (use geo-replication) | 1 free read-only replica | Up to 4 HA replicas + up to 30 named replicas (independently scalable) |
| Serverless option | Yes | No | Yes (but auto-pause only in GP, not Hyperscale) |
| OLTP optimised | Standard workloads | High-performance OLTP | Variable — scales with demand |
| Best for | Most workloads, budget-conscious | Low-latency, mission-critical OLTP | Very large databases, unpredictable workloads, fast scaling |
🏦 Elena’s tier selection: FinSecure Bank’s trading platform needs sub-2ms query latency for real-time risk calculations. Elena chose Business Critical because:
- Local SSD storage delivers 1-2ms I/O (General Purpose’s 5-10ms would cause trading delays)
- Built-in read replica offloads reporting queries without affecting the trading engine
- Always On AG provides automatic failover — no manual intervention during outages
🚀 Marcus’s approach: NovaSaaS’s tenant databases vary wildly in size (some customers have 50 GB, others have 5 TB). Marcus uses Hyperscale because:
- Scales to 100 TB without pre-provisioning storage
- Named replicas let different tenants get different compute levels
- Near-instant database snapshots for backup (no I/O impact)
Provisioned vs serverless compute
| Factor | Provisioned | Serverless |
|---|---|---|
| Compute | Fixed vCores — always allocated | Auto-scales between min and max vCores |
| Billing | Per-hour for allocated compute | Per-second for actual compute used |
| Auto-pause | No — always running | Yes — pauses after configurable idle period (min 1 hour) |
| Cold start | None | ~10-30 seconds after auto-pause resume |
| Best for | Predictable, consistent workloads | Dev/test, intermittent usage, variable workloads |
Exam tip: Serverless is not always cheaper
Serverless billing is per-second based on actual vCore usage. For databases with consistent load (e.g., always using 4 vCores during business hours), provisioned compute with reserved capacity (1 or 3 years) is significantly cheaper. Serverless shines when the database is idle 50%+ of the time.
Scalability patterns
Elastic pools: shared resources for many databases
Elastic pools share compute and storage across multiple databases — ideal when databases have variable usage patterns that don’t peak simultaneously.
| Factor | Individual Databases | Elastic Pool |
|---|---|---|
| Compute | Each database has its own allocation | Shared pool — databases borrow from each other |
| Cost | Pay for peak capacity per database | Pay for pool capacity — smoother cost curve |
| Best for | Predictable, isolated workloads | Multi-tenant SaaS, variable usage patterns |
| Limits | Per-database DTU/vCore limits | Per-pool eDTU/vCore limits, per-database min/max |
🚀 Marcus’s multi-tenant design: 500 tenant databases, each averaging 10 DTUs but occasionally spiking to 50 DTUs. Instead of provisioning 50 DTUs per database (25,000 total), Marcus uses an elastic pool with 5,000 eDTUs — because tenants spike at different times, the pool handles peaks efficiently.
Read scale-out
| Pattern | How It Works | Best For |
|---|---|---|
| Business Critical read replica | Built-in free read-only replica | Reporting queries, dashboards (same region) |
| Active geo-replication | Readable secondary in another region | DR + read offloading in a different region |
| Hyperscale named replicas | Up to 4 independently scalable replicas | Different workload isolation (OLTP vs analytics) |
Horizontal sharding
For workloads that exceed single-database limits:
| Approach | Description | Complexity |
|---|---|---|
| Application-level sharding | App routes queries to the right shard | Highest flexibility, highest dev effort |
| Elastic database tools | Azure SDK for shard map management | Medium — Microsoft provides the framework |
| Cosmos DB | Built-in horizontal partitioning | Lowest effort (but it’s NoSQL, different paradigm) |
Well-Architected Framework connection
Performance Efficiency: Right-size your tier to the workload. Over-provisioning wastes money; under-provisioning causes latency.
Cost Optimisation: Reserved capacity (1 or 3 years) saves 30-65% vs pay-as-you-go. Elastic pools reduce per-tenant cost for variable workloads. Serverless saves on idle databases.
Reliability: Read replicas improve availability and offload read traffic. Geo-replication enables cross-region DR.
Knowledge check
🏦 FinSecure Bank's trading application requires sub-2ms database query latency, automatic failover, and the ability to offload reporting queries to a read-only copy. The database is 500 GB. Which tier and feature should Elena recommend?
🚀 NovaSaaS has 300 tenant databases. Usage varies: most databases use 5-10 DTUs but occasionally spike to 40 DTUs. Peak usage across tenants doesn't overlap. Marcus wants to minimise cost. Which approach should he recommend?
Next up: Performance is optimised — now let’s protect that data — Database Security & Compliance.