Recommend a Solution for Database Scalability — Lesson

Reference: Designing Microsoft Azure Infrastructure Solutions (AZ-305) — the attached exam study guide is the source material for this lesson.

Database scalability on Azure is the craft of matching compute and storage capacity to a moving workload without rewriting the application every time the curve bends. The AZ-305 exam frames this as a sequence of design choices — does the database need to autopause when idle, share compute across a portfolio of sibling databases, offload readers to a replica, or split into shards that each host a subset of the data? Each option sits at a different point on the cost / complexity / throughput triangle, and the correct answer depends almost entirely on the workload shape described in the scenario.

This lesson stays strictly on the scale-out side of the Azure SQL family — Serverless autopause, elastic pools, read scale-out, and horizontal sharding. Vertical sizing decisions (picking a Business Critical vs. General Purpose tier, or choosing a vCore count) live in the neighbouring LO on database performance. High-availability and DR design (failover groups, zone-redundant deployments, geo-replication) live in Unit 3 on business continuity. Keep those boundaries in mind — the exam rewards learners who choose read scale-out for reporting on a busy OLTP primary, elastic pool for many small SaaS tenants with spiky, uncorrelated usage, and sharding only when a single logical database cannot fit in the largest single-instance tier.

Why This Matters

Azure SQL is rarely sized for the average hour — it is sized for peak, idle, and growth simultaneously, and scalability is how you stop paying for all three at once. Architects who conflate "bigger VM" with "more scale" end up with one expensive Business Critical database serving a cold dev workload overnight, or a thousand tiny tenant databases that each demand their own provisioned vCores. On the exam this mistake shows up as a distractor that is technically correct but financially indefensible, and spotting it eliminates two answers per question.

Beyond the certification, this is the LO that maps most directly to the Well-Architected pillar of Cost Optimization, with strong second-order effects on Performance Efficiency and Operational Excellence. The scale-out patterns you pick here set the floor for how cheaply the platform runs at the 10th percentile of load, and the ceiling for how fast it can absorb a Black-Friday spike at the 99th. Get this right and the rest of the data-platform architecture is a rounding error; get it wrong and no amount of cache, compression, or query tuning will close the gap.

Prerequisites

• Azure SQL deployment models (Single Database, Elastic Pool, Managed Instance) — Self-check: Can you name a workload that Managed Instance serves but Single Database does not?
• vCore vs DTU purchasing model — Self-check: Which purchasing model is required for the Serverless compute tier?
• Service tiers (General Purpose, Business Critical, Hyperscale) — Self-check: Which tier supports more than 4 TB of data and named readable replicas?
• Database connection strings and ApplicationIntent — Self-check: What does ApplicationIntent=ReadOnly do to a connection routed at a Business Critical primary?
• Partitioning theory (horizontal vs vertical) — Self-check: In one sentence, what is a shard key and what happens if you choose a bad one?
• Basic T-SQL and connection-pool exhaustion — Self-check: Why does pool exhaustion show up earlier on elastic pools than on individual single databases?

Learning Objectives

By the end of this lesson you will be able to:

1. Analyse a described Azure SQL workload and classify it along four scaling axes — idle density, tenant multiplicity, read-heavy amplification, and data-size ceiling.
2. Evaluate the trade-offs between Serverless, Provisioned vCore, elastic pool, read scale-out, and horizontal sharding against a Well-Architected lens (Cost, Performance Efficiency, Operational Excellence).
3. Recommend the correct scale-out pattern for SaaS-tenant, reporting-offload, seasonal, and unbounded-growth scenarios drawn from the exam blueprint.
4. Design an elastic-pool configuration (vCore ceiling, per-database min and max, tier) that meets a workload profile without over-provisioning.
5. Justify the use of the Hyperscale tier — including its named replicas and storage separation — when the dataset exceeds single-instance limits.
6. Diagnose common scale-out anti-patterns (Serverless on a $24/7$ workload, sharding before the Hyperscale ceiling, mixing pools and Serverless) in a scenario description.

Building Blocks

Serverless compute tier — Analogy: a serverless database is to a provisioned one what a dimmer switch is to a regular light switch — it scales brightness (vCores) between min vCores and max vCores based on demand, and turns off completely when nobody is in the room. Formal: the Serverless compute tier in Azure SQL Database bills per second for compute used within a configured min and max vCore range, and automatically pauses the database after a configurable auto-pause delay (default 60 minutes). Why it matters: dev/test, intermittent line-of-business apps, and unpredictable workloads run 40–70% cheaper on Serverless than on the equivalent Provisioned compute.

Elastic pool — Analogy: a boarding house where 100 tenants share one big kitchen and one big water heater — each tenant has a private bedroom, but they can burst into the common resources when they need to cook. Formal: an elastic pool is a shared pool of eDTU or vCore capacity allocated across a group of databases on the same logical server, where each database draws from the pool up to a configured per-database max. Why it matters: pools are the dominant cost-optimisation pattern for SaaS vendors with hundreds of small tenant databases whose peaks are uncorrelated.

Read Scale-Out — Analogy: a library branch system — one main branch where all the new books are filed (writes) and several reading rooms that always carry synchronised copies (reads). Formal: on Premium, Business Critical, and Hyperscale tiers, Azure SQL exposes one or more read-only replicas that clients reach by setting ApplicationIntent=ReadOnly in the connection string. Why it matters: it offloads reporting and analytical queries from the primary at no additional cost on Premium/BC, or at minimal cost per named replica on Hyperscale.

Sharding — Analogy: splitting a library across several buildings, where each building holds only the books whose author's surname falls into a specific range of letters. Formal: horizontal partitioning of a logical database across multiple physical databases (shards), with a Shard Map Manager database storing the routing metadata that tells the application which shard a given tenant, customer, or key range lives on. Why it matters: the only way to scale past the ~100 TB ceiling of Hyperscale, or to isolate noisy-neighbour tenants that a pool cannot contain.

Hyperscale tier — Analogy: a database with the storage of a data lake bolted onto the compute of a relational engine, so the compute node only caches hot pages and the rest sits on distributed page servers. Formal: a service tier in Azure SQL Database that decouples compute from storage, supports up to 100 TB of data, adds storage in 10 GB increments, and offers up to four HA replicas plus up to thirty named replicas for scale-out reads. Why it matters: it is the exam's default answer whenever a scenario names a database "larger than 4 TB" or demands fast restore times on a very large dataset.

Shard Map Manager (SMM) — Analogy: the card catalogue that tells a librarian which branch holds the book you asked for. Formal: a dedicated Azure SQL database storing the list of shards, the routing key (range or list mapping), and the connection strings. The Elastic Database client library uses the SMM to direct each query to the correct shard. Why it matters: picking the right shard key is the single most consequential decision in a sharded design — a bad key forces cross-shard joins, and cross-shard joins melt.

Deep Dive

Serverless compute and autopause — the "dimmer switch" pattern

The Serverless compute tier of Azure SQL Database is the right answer whenever a scenario describes unpredictable, intermittent, or low duty-cycle usage. Billing is per-second within the configured min vCores / max vCores range, and — crucially — when no session has touched the database for the full auto-pause delay, compute is rescaled to zero. You pay only for storage during that paused window, at the same price as the Provisioned-compute storage component.

[!TIP] Autopause has a cold-start penalty of roughly 30–60 seconds on the first query after a pause. For workloads that cannot tolerate that delay on their SLA, either disable autopause (keeping the per-second compute bill) or raise min vCores so the compute floor stays warm.

The classic mis-application is to enable Serverless for a production OLTP system that services customer-facing traffic around the clock. Because the database is rarely idle for the full auto-pause delay, it never actually pauses, and the per-second Serverless billing ends up more expensive than the equivalent Provisioned tier for the same peak vCore count. The exam likes to frame this as "Contoso's OLTP database has 5000 concurrent users between 9 AM and 9 PM" — that is decidedly not a Serverless workload.

The snippet above provisions a General Purpose Serverless database with min = 0.5 vCores, max = 4 vCores, and an auto-pause delay = 60 minutes — a safe default for an internal HR reporting database that is idle overnight and on weekends.

Elastic pools — the "shared kitchen" pattern

An elastic pool is a shared budget of eDTU or vCore capacity owned by a group of databases on a single logical server. Each database is still isolated at the engine level (its own process, its own storage file), but they compete for shared compute and memory against the pool's ceiling. Each database has a per-database min (a reserved floor it always gets) and a per-database max (a burst cap beyond which it cannot steal from the pool).

The design condition that makes a pool cheaper than the sum of individual databases is uncorrelated peaks. If every tenant in a SaaS application peaks at 9 AM on Monday, the pool must be sized for the sum of those peaks — which is no cheaper than individual provisioning. If tenants peak on independent schedules (timezone-spread customers, demand-spread workloads, $24/7 fleet of dev/test sandboxes), a pool sized for the P_{95}$ of aggregate demand costs a small fraction of the individually-provisioned equivalent.

[!WARNING] Elastic pools do not support the Serverless compute tier — pools run on Provisioned compute only. A scenario that needs both "shared compute across many databases" and "scale to zero when idle" cannot be solved by a single feature; the usual answer is to put the steady tenants in a Provisioned pool and the idle ones in individual Serverless databases.

The standard pool tiers (Basic, Standard, Premium, GP, BC, and Hyperscale) also cap the number of databases per pool — 500 on Standard/Premium and 100 on the vCore model — so very large SaaS fleets need to shard across multiple pools, usually by tenant region or tenant size band.

The T-SQL above adds a tenant database into a pool and then moves it into a different pool — a cheap, online operation that is the basis for tenant rebalancing across pools.

Read Scale-Out — the "free reading room" pattern

Every Premium, Business Critical, and Hyperscale database already maintains one or more high-availability (HA) replicas in the background for failover. Read Scale-Out exposes one of those replicas to read-only clients that set ApplicationIntent=ReadOnly in their connection string. The feature is enabled by default on those tiers, and costs nothing extra on Premium / Business Critical.

The Hyperscale tier takes the pattern further with named replicas — up to thirty additional read-only replicas, each billed separately, each with its own endpoint, each independently-scalable in vCores. Named replicas are the exam's default answer when a scenario describes an OLTP database on Hyperscale plus an analytics team that needs a dedicated query surface that cannot impact the primary.

[!NOTE] Read Scale-Out is eventually-consistent with the primary — typical lag is well under 1 second, but for scenarios demanding strictly-consistent reads after writes, you must route the read to the primary, not the replica.

Sharding with the Shard Map Manager — the "book-by-author split" pattern

Sharding is the last-resort scale-out pattern because it shifts complexity into the application tier. You pick a shard key (tenant ID, customer region, user bucket), you split the logical dataset into $N$ physical databases each holding rows whose keys fall into that shard's range, and you use the Elastic Database client library plus a Shard Map Manager database to route every query to the correct shard.

The two shard-map strategies on the exam are:

• List map — discrete values map to specific shards. Typical shard key: tenant_id. Good for SaaS multi-tenancy where each tenant lives wholly on one shard.
• Range map — continuous ranges map to shards. Typical shard key: created_at date or a numeric range. Good for time-series or append-heavy workloads.

[!IMPORTANT] Cross-shard queries using Elastic Database query (the fan-out feature) are supported but expensive — they scale with the number of shards queried, not the number of rows returned. A shard key that forces the application to fan-out for every request is a failed design; pick a key that keeps $> 95\%$ of queries single-shard.

The exam signals a sharding question with phrases like "exceeds the 100 TB limit", "needs to isolate noisy tenants", or "must provide hard tenant data-isolation boundaries". If the scenario does not include those signals, sharding is almost never the correct answer — the combination of Hyperscale + named replicas + elastic pools handles most scaling problems without the operational cost of a shard map.

Choosing between the four patterns

The fastest heuristic on the exam:

1. Does the scenario say the data is "larger than 4 TB" or "needs fast restore on a large database"? → Hyperscale first, then consider named replicas.
2. Does the scenario describe "many small databases" or "SaaS tenants with uncorrelated peaks"? → Elastic pool.
3. Does the scenario describe "idle outside business hours" or "dev / test" or "unpredictable low-traffic"? → Serverless.
4. Does the scenario describe "reporting on an OLTP" or "analytics without impacting production"? → Read Scale-Out (named replica on Hyperscale, ordinary read replica elsewhere).
5. Does the scenario describe "exceeds 100 TB" or "hard tenant isolation"? → Sharding.

Worked Examples

Example 1 — Easy: Dev database that is idle outside business hours

Problem: Contoso's development team runs a 4 GB Azure SQL Database that supports an internal HR tool. The tool is used from 9 AM to 6 PM on weekdays only, with an average load of 2 vCores. The team wants the lowest possible bill without giving up their automated overnight build process, which connects once at 02:00 for a five-minute smoke test. Which compute tier should you recommend?

Solution:

1. Usage shape = weekdays 9–18, plus a 5-minute nightly touch at 02:00. This is intermittent and predictable, with long idle windows.
2. The nightly connection resets the auto-pause timer, but only for the duration of the smoke test — the database will auto-pause again shortly afterwards.
3. Choose General Purpose Serverless with min = 0.5 vCores, max = 4 vCores, auto-pause delay = 60 minutes. The database will be paused most of the night and all weekend.
4. Billing: roughly 45 hours of compute per week instead of 168, so a $\sim 73\%$ cost reduction versus Provisioned.

[!NOTE] The 30–60 second cold-start on the first 02:00 query is acceptable because the smoke test is a tolerant automated job, not a user-facing request.

Example 2 — Medium: SaaS vendor with uncorrelated tenant peaks

Problem: Synthetic scenario (AZ-305 style). Fabrikam is a SaaS vendor hosting 300 customers. Each customer has their own Azure SQL Database (1 vCore equivalent average load, occasional burst to 4 vCores). Peaks are distributed uniformly across timezones and never more than 40 customers peak simultaneously. Storage per tenant averages 20 GB, ceiling 250 GB. Which deployment model and capacity should you recommend?

Solution:

1. Observation 1 — 300 databases on a single server is below the elastic-pool database-count ceiling (500 on Standard/Premium, 100 on vCore) but fits comfortably if we split across a few pools or pick Standard. We will use one pool of vCore model for uniform tooling.
2. Observation 2 — aggregate peak is $40 \times 4 = 160$$ vCore-equivalent in the worst case; aggregate average is $300 \times 1 = 300 vCore-equivalent. The 300 figure assumes every tenant is at average simultaneously, which never happens with uncorrelated peaks — $P_{95}$ aggregate is closer to 100–120 vCore-equivalent.
3. Choose GP elastic pool sized at 128 vCores total, per-database max = 4, per-database min = 0 (so idle tenants reserve nothing). Split into two pools of 150 databases each for blast-radius isolation.
4. Storage pool of $$300 \times 20$$ GB = 6 TB, which fits a GP pool's storage ceiling.

[!NOTE] If the scenario later adds "we need to scale to 2000 customers", the answer shifts toward sharding — multiple pools by region and by tenant-size tier, coordinated by a shard map.

Example 3 — Hard: Analytical team crushing an OLTP primary

Problem: Tailwind Traders runs a 6 TB OLTP database on Azure SQL Business Critical with 32 vCores. A new analytics team wants to run complex reporting queries (large table scans, window functions, multi-table joins) during business hours. Initial testing shows the reports starve the OLTP workload of CPU. The team does not want to move to a separate data warehouse and wants the lowest-effort change that lets both workloads coexist. What do you recommend?

Solution:

1. The dataset is larger than 4 TB, so either Business Critical or Hyperscale applies. BC tops out at 4 TB in most regions — migrating to Hyperscale is the necessary first step.
2. On Hyperscale we get up to 30 named replicas, each independently-sized and independently-billed. For the analytics workload, provision one named replica at 16 vCores.
3. Update the analytics team's connection string to target the named replica's endpoint (or use ApplicationIntent=ReadOnly plus the read-routing endpoint, depending on the naming convention).
4. The OLTP primary is unaffected; analytical queries hit the replica; the reports see sub-second lag relative to the primary.

[!NOTE] A common wrong answer is "put the analytics team on the ordinary HA read replica with ApplicationIntent=ReadOnly". That replica is shared with HA failover and its size cannot be independently-scaled — for heavy scans you need a named replica precisely because it can be scaled up without affecting the primary.

Visual Explanations

Scale-out decision tree

Caption: A top-down decision tree for choosing the right Azure SQL scale-out pattern from a scenario description. Start with the data-size ceiling, then branch by workload shape. Exam scenarios almost always provide enough signal to walk this tree in one pass.

Elastic pool topology (TikZ)

Caption: An elastic pool sits on a single logical server and shares its vCore and storage budget across all member databases. Each tenant database is still isolated at the engine level, but they compete for the pool's shared compute.

Service tier vs scale-out feature matrix

Caption: Which scaling features are available at each service tier. Hyperscale is the only tier that supports both named replicas and storage above 4 TB, which is why it anchors many exam answers.

Sharding fan-out behaviour

Caption: The application resolves a shard key (here, tenant_id) through the Shard Map Manager, which returns the connection string of the target shard. Single-shard queries are cheap; cross-shard fan-out queries scale with the number of shards, not the number of rows, and become the primary driver of cost in a badly-keyed design.

Scale-out cost / complexity landscape

Caption: Each pattern sits at a different point on the effort/leverage curve. The exam rewards picking the leftmost pattern that actually solves the scenario — if Serverless works, do not reach for an elastic pool; if a pool works, do not reach for sharding.

Workload-signal cheat sheet

Caption: A quick translation from the keywords the exam uses to the pattern they map to. If two rows look plausible for a scenario, pick the leftmost (simplest) pattern.

Common Mistakes

❌ Myth: "Serverless is always cheaper than Provisioned." ✅ Reality: Serverless is cheaper only when the database is idle enough to auto-pause for meaningful periods, or when the duty cycle is well below the max vCore ceiling. A $24/7$OLTP at 80**Why it's tricky:** Serverless is marketed as the cost-optimised option, so learners assume "cheaper by default". The break-even analysis is usage-shape dependent, and the exam often hides a $24/7$ workload description in a Serverless distractor.

❌ Myth: "Elastic pools improve availability by spreading databases across multiple servers." ✅ Reality: An elastic pool lives on a single logical server. All pool member databases share that server's availability fate. Pools are a cost pattern, not an HA pattern — for HA spread, use zone-redundant tiers or failover groups. Why it's tricky: "Elastic" sounds like "highly available" to many learners. The exam uses distractors that pair "elastic pool" with "high availability" — that pairing is a trap unless followed by explicit zone-redundant or geo-replication configuration.

❌ Myth: "Read Scale-Out on Business Critical adds cost per replica." ✅ Reality: On Premium and Business Critical, the HA replicas are already paid for via the tier's SLA — Read Scale-Out simply exposes one of them as read-only. It is free. Named replicas on Hyperscale are the only variant that adds per-replica cost. Why it's tricky: Every other scale-out feature costs something, so learners pattern-match the same assumption onto Read Scale-Out. The trap is a distractor that says "add read replicas at $X per replica on Business Critical" — incorrect on BC.

❌ Myth: "You can put a Serverless database inside an elastic pool." ✅ Reality: Elastic pools support Provisioned compute only. Mixing Serverless and a pool requires either a Provisioned pool plus individual Serverless databases outside it, or Hyperscale (which is neither Serverless nor poolable in the traditional sense). Why it's tricky: Both features are described as "cost-optimised", so learners assume they compose. They do not — pick one per database.

❌ Myth: "Sharding is the right scaling pattern as soon as you have 500+ databases." ✅ Reality: Sharding imposes a meaningful application-layer complexity cost (shard map, fan-out logic, cross-shard transaction rules). Up to the Hyperscale data ceiling and the pool database-count ceiling, simpler patterns win. Sharding is the right answer only when the scenario pushes past those ceilings or demands hard tenant isolation. Why it's tricky: "Many databases" sounds like "shards", and SaaS architecture content often conflates the two. The exam rewards picking the simplest pattern that clears the scenario's ceiling — usually an elastic pool, sometimes Hyperscale.

Practice Exercises

🟢 Easy — Identify the right compute tier

A retail customer runs an internal reporting database of 12 GB. Business hours are 08:00–18:00 Monday to Friday. Average workload is $1.5 vCores. What tier do you recommend?

▶💡 Hint

The database is idle for at least 14 hours per weekday and all weekend. That idle window triggers a specific compute tier designed for it.

▶✅ Solution

General Purpose Serverless with min = 0.5 vCores, max = 2 vCores, auto-pause delay = 60 minutes. The $14+$ hour idle windows pay off the Serverless model several times over, and the max cap aligns with the average workload plus headroom.

🟢 Easy — Read Scale-Out eligibility

Your team wants to offload reporting queries to a read-only replica. The database runs on General Purpose with 8 vCores. What is your first recommendation?

▶💡 Hint

Read Scale-Out is not available on every service tier. Check the matrix in the Visual Explanations section.

▶✅ Solution

General Purpose does not support Read Scale-Out. Recommend a tier change to Business Critical (enables a free read replica) or Hyperscale (enables named replicas). Whether to pick BC or HS depends on data size and whether a dedicated replica surface is required.

🟡 Medium — Pool sizing

A SaaS vendor has 200 tenant databases with an aggregate $P_{95}$ demand of 80 vCores and 3 TB of total storage. The per-tenant burst requirement is 4 vCores. Size an elastic pool.

▶💡 Hint

The pool's vCore ceiling must cover $P_{95}$ plus headroom. The per-database max must not exceed the pool's vCore ceiling and must meet the burst requirement.

▶✅ Solution

GP elastic pool at 128 vCores (comfortably above the 80 vCore $P_{95}$ with 60% headroom for growth), per-database max = 4, per-database min = 0. Storage allocation of 4 TB on GP is within tier limits. Consider splitting into two pools of 100 databases each if blast-radius isolation is important.

🟡 Medium — Hyperscale named replicas

A 30 TB OLTP database on Hyperscale runs on 24 vCores. The customer adds three analytics teams, each wanting a dedicated query surface. Team A runs heavy scans, team B runs short ad-hoc queries, team C runs a weekly month-end close report. Design the read-scale-out strategy.

▶💡 Hint

Named replicas can be individually-sized. You can have up to 30 named replicas on Hyperscale. Not every team needs the same size.

▶✅ Solution

Provision three named replicas: Team A (heavy scans) at 16 vCores, always-on; Team B (short queries) at 4 vCores, always-on; Team C (monthly) at 8 vCores, scaled down or deleted between close cycles (named replicas can be dropped and recreated cheaply). Route each team's connection string to its dedicated replica endpoint. Primary is unaffected by all three.

🔴 Hard — Sharding decision

Synthetic scenario (AZ-305 style). You manage a SaaS platform with 5000 tenants. Total data is 180 TB. Individual tenant data ranges from 10 GB to 3 TB. The largest tenants have stringent isolation requirements (regulated industry) and demand that their workload cannot be impacted by noisy neighbours. How do you design storage?

▶💡 Hint

The 180 TB figure breaks the single-database ceiling. The isolation requirement for large tenants rules out a pure pool design.

▶✅ Solution

A three-tier sharded design coordinated by a Shard Map Manager. Tier 1 (large regulated tenants, ~50 customers): one Hyperscale database per tenant, hard isolation, independently-scalable. Tier 2 (medium tenants, ~950 customers): GP elastic pools, ~100 tenants per pool, split into ~10 pools across multiple logical servers. Tier 3 (small tenants, ~4000 customers): Standard elastic pools with 500 tenants per pool, ~8 pools. The SMM holds the routing metadata; the application's Elastic Database client library resolves tenant_id → pool/database on every request. Cross-shard queries are kept out of the hot path (use per-tenant aggregation; ship to a separate warehouse for cross-tenant analytics).

🔴 Hard — Cost pitfall

A developer provisions a Business Critical 16-vCore database for a line-of-business app with 20 users who work 09:00–17:00. Monthly cost is flagged by finance as too high. List three scale-down options in order of effort and state which you recommend.

▶💡 Hint

Think about why BC was picked. If the answer is "for HA", check whether Zone-redundant General Purpose would meet the SLA. If the answer is "for Read Scale-Out", check whether the workload actually uses it.

▶✅ Solution

Option 1 (low effort): switch from Business Critical to General Purpose (GP gives 99.99% SLA single-zone, 99.995% zone-redundant — likely sufficient for a 20-user LOB app). Removes IOPS-heavy BC pricing. Option 2 (medium effort): switch to General Purpose Serverless with min = 0.5, max = 8 vCores, auto-pause delay = 60 minutes. Evenings and weekends become free. Option 3 (high effort): re-platform to an elastic pool alongside other small LOB apps. Requires other candidate apps and regression testing. Recommendation: option 2. Fastest payoff, lowest migration risk, matches the 9–17 usage shape perfectly.

🟡 Medium — Diagnosing a cross-shard hotspot

Your sharded system uses tenant_id as the shard key. Most tenants are well-behaved, but one tenant has grown to 80% of that shard's compute. What options do you have?

▶💡 Hint

A shard is a physical database. You can move a tenant between shards by changing the shard map entry and migrating the data.

▶✅ Solution

Three options: (1) Split-merge — use the Elastic Database split-merge service to migrate the large tenant to a dedicated shard (a shard of one). Update the SMM to route that tenant to the new shard. (2) Scale the hot shard up — if the tenant cannot easily be isolated, increase the vCore count of the hot shard alone (each shard is an independent database, so shards can be sized independently). (3) Sub-shard at the application layer — split the noisy tenant's data into sub-tenant shards (by region_id or product_line_id). This requires application-level awareness — expensive; use only if options 1–2 do not close the gap. Recommendation: option 1 — it preserves the shard key and contains blast radius in a single operation.

Summary & Concept Map

• Scale-out on Azure SQL is a decision tree, not a single knob — data size, workload shape, and isolation requirements each point to a different pattern.
• Serverless is the answer for idle-heavy, unpredictable, or dev/test workloads; auto-pause delay is the lever that controls cost leverage.
• Elastic pools are for SaaS fleets of many small databases with uncorrelated peaks; they are a cost pattern, never an HA pattern.
• Read Scale-Out is a free lunch on Premium and Business Critical; pair it with ApplicationIntent=ReadOnly in the connection string.
• Named replicas on Hyperscale are the right surface for heavy analytics on a very large OLTP — up to 30, each independently-sized and independently-billed.
• Sharding is the last resort, reserved for the $> 100$ TB ceiling or hard tenant-isolation mandates; the shard-key choice is the decisive design decision.
• Always walk the decision tree in order — data size first, then workload shape, then reporting needs, then isolation. Picking the leftmost pattern that clears the scenario is almost always the correct exam answer.