This chapter covers Azure SQL elastic pools, a critical cost-optimization feature for managing multiple Azure SQL databases with variable usage patterns. Understanding elastic pools is essential for the DP-900 exam, particularly under Objective 2.2 (Relational Data) which focuses on provisioning and managing relational database services. Approximately 10-15% of exam questions touch on scalability and cost management of Azure SQL Database, with elastic pools being a key concept. This chapter provides a deep dive into how elastic pools work, when to use them, configuration details, and exam traps.
Jump to a section
Think of Azure SQL elastic pools like a group health insurance plan for a company. In a company, each employee has different health needs — some rarely visit the doctor, others have chronic conditions requiring frequent care. If the company bought individual insurance for each employee at a fixed premium, it would overpay for the healthy ones and risk underinsuring the sick ones. Instead, a group plan pools all employees together: the total premium is based on the aggregate expected usage of the entire group. Employees share a common pool of coverage, and as long as the total claims don't exceed the pool's capacity, everyone is covered. The insurance company monitors the pool's utilization and can adjust the premium (scale up) if the group's overall health worsens. This mirrors elastic pools: databases with variable, low-average usage share a pool of eDTUs (or vCores). Instead of provisioning fixed DTUs per database (like individual insurance), you set a total eDTU cap for the pool. Each database can burst up to a per-database max eDTU (like an employee's maximum coverage), but the aggregate consumption across all databases must stay under the pool's eDTU limit. If the pool's total eDTU usage consistently exceeds the limit, you must scale up the pool (increase premiums). This model is cost-efficient when databases have sporadic spikes that don't coincide — just as a group plan is cheaper when employees' health issues are not all simultaneous.
What Are Azure SQL Elastic Pools?
Azure SQL elastic pools are a deployment option for Azure SQL Database that allows you to manage and scale a group of databases collectively. Instead of provisioning individual DTUs (Database Transaction Units) or vCores for each database, you allocate a shared pool of resources (eDTUs or eVCores) that all databases in the pool consume. This is cost-effective when databases have low average utilization but occasional spikes, because the pool absorbs the spikes without needing to over-provision each database.
Why Elastic Pools Exist
The primary driver for elastic pools is cost optimization. If you have multiple databases, each with its own DTU quota, you must size each database for its peak load. This leads to significant waste because most databases are idle most of the time. Elastic pools allow you to buy a pool of resources and let databases share them. The key metric is the pool's eDTU or vCore limit. As long as the aggregate consumption of all databases stays under this limit, no database is throttled. Only when the pool's total usage exceeds the limit does throttling occur, and then only the databases that are actively consuming resources are affected.
How Elastic Pools Work Internally
When you create an elastic pool, you specify: - Service tier: Basic, Standard, Premium (DTU model) or General Purpose, Business Critical (vCore model). - Pool size: eDTUs (in DTU model) or vCores (in vCore model). - Per-database settings: Minimum and maximum DTUs/vCores per database.
Each database in the pool is assigned a minimum resource guarantee (min DTU/vCore) and a maximum limit (max DTU/vCore). The pool's total resources are the sum of all database minimums plus a shared buffer. When a database's workload increases, it can consume resources up to its per-database max, drawing from the pool's shared resources. If multiple databases spike simultaneously, they share the pool's capacity up to the pool's total limit. If the pool's total demand exceeds the limit, the databases are throttled based on their priority (the min and max settings).
Key Components, Values, and Defaults
DTU Model:
Service tiers: Basic (5 eDTUs max), Standard (10-3000 eDTUs), Premium (125-4000 eDTUs).
Per-database min eDTUs: 0 for Standard, 0 or 25 for Premium.
Per-database max eDTUs: depends on tier; for Standard, max is up to 3000 eDTUs but cannot exceed pool size.
vCore Model:
Service tiers: General Purpose (2-80 vCores), Business Critical (2-80 vCores).
Per-database min vCores: 0-80.
Per-database max vCores: up to pool size.
Default values: When creating a pool, the per-database min is 0, and per-database max is set to the pool size. This allows any database to consume all pool resources, but also means a single database can starve others.
Configuration and Verification Commands
Using Azure CLI to create an elastic pool:
az sql elastic-pool create --resource-group myRG --server myServer --name myPool --edition Standard --dtu 100 --db-dtu-min 0 --db-dtu-max 50To add a database to the pool:
az sql db create --resource-group myRG --server myServer --name myDB --elastic-pool myPoolTo monitor pool usage:
az monitor metrics list --resource /subscriptions/.../elasticPools/myPool --metric dtu_consumption_percent --interval 5mIn Azure Portal, you can view the pool's eDTU utilization chart and per-database eDTU usage.
Interaction with Related Technologies
Elastic pools are closely tied to Azure SQL Database elastic jobs (for administrative tasks across databases) and elastic query (for cross-database queries). They also integrate with Azure Monitor for alerting on pool utilization. When a pool reaches 100% eDTU consumption, you must either scale up the pool or reduce the load by moving databases out. Auto-scaling is not automatic; you must configure alerts and use Azure Automation or Logic Apps to trigger scaling.
Cost Optimization Scenarios
Elastic pools are cost-effective when:
You have multiple databases with low average usage (e.g., 10-20% DTU utilization).
Databases have sporadic spikes that do not overlap in time.
You need to manage many databases (e.g., SaaS multi-tenant apps).
They are NOT cost-effective when:
A few databases consume high resources consistently (e.g., >50% of pool capacity each).
Databases have predictable, simultaneous high load (e.g., all spike at 9 AM).
In such cases, individual databases with reserved capacity may be cheaper.
Exam Traps
Common exam wrong answers: - "Elastic pools automatically scale databases without any configuration." Reality: You must set per-database min and max limits; scaling the pool is manual or requires automation. - "Elastic pools are always cheaper than individual databases." Reality: Only beneficial for low-average, spiky workloads. - "You can add databases from different servers to the same pool." Reality: All databases in a pool must be on the same Azure SQL Database server. - "The pool's eDTU limit is the sum of per-database max values." Reality: The pool limit is a separate value; per-database max cannot exceed the pool limit.
Key Numbers for the Exam
Standard tier pool sizes: 10, 20, 50, 100, 200, 400, 800, 1200, 2000, 3000 eDTUs.
Premium tier pool sizes: 125, 250, 500, 1000, 1500, 2000, 2500, 3000, 4000 eDTUs.
Maximum databases per pool: 4000 (Standard and Premium).
Minimum per-database eDTU: 0 (Standard), 25 (Premium).
Maximum per-database eDTU: up to pool size but capped by tier limits (e.g., Standard max is 3000).
Remember: The exam focuses on the DTU model for elastic pools; vCore model is tested less frequently.
Identify Workload Pattern
Before creating an elastic pool, analyze the DTU consumption of each database over a representative period (e.g., 7 days). Look for databases with low average DTU (e.g., <20%) but occasional spikes (e.g., 50-80%). If the spikes are not simultaneous, an elastic pool can save cost. Use Azure SQL Analytics or Query Performance Insight to gather metrics. The key metric is the aggregate eDTU usage of all candidate databases; if the sum of their peak DTUs exceeds the pool's eDTU limit by a small margin, the pool is a good fit. If the sum of their average DTUs is close to the sum of their peak DTUs, individual databases are better.
Choose Pool Size and Tier
Based on the aggregate eDTU consumption, select a pool size that is slightly above the average aggregate usage but below the sum of peak usages. For example, if 10 databases each have an average of 5 DTU and peak of 50 DTU, the average aggregate is 50 DTU, peak aggregate could be up to 500 DTU if all spike together. A pool of 100 eDTU might be sufficient if spikes rarely coincide. Choose the service tier (Standard or Premium) based on required performance and availability SLAs. Standard offers up to 3000 eDTU; Premium up to 4000 eDTU and higher IO. Set per-database min to 0 and per-database max to a value that prevents a single database from consuming all pool resources (e.g., 50 eDTU).
Create Elastic Pool
Using Azure Portal, CLI, or PowerShell, create the elastic pool on the same server that hosts the databases. For example, with Azure CLI: `az sql elastic-pool create --resource-group myRG --server myServer --name myPool --edition Standard --dtu 100 --db-dtu-min 0 --db-dtu-max 50`. This creates a Standard pool with 100 eDTU, per-database min 0, per-database max 50. The pool is billed hourly based on the chosen eDTU count. After creation, move existing databases into the pool by updating their elastic pool property. This operation is online but may cause a brief connection interruption.
Monitor Pool Utilization
After moving databases, monitor the pool's eDTU consumption percent. In Azure Portal, the elastic pool blade shows a chart of average eDTU usage over time. Set up alerts when utilization exceeds 80% for sustained periods (e.g., 30 minutes). If the pool consistently hits 100%, you need to scale up. Use Azure Monitor metrics: `dtu_consumption_percent` for the pool. Also check per-database eDTU usage to identify any database that is consistently using its max — it may need to be moved out or have its max increased.
Scale Pool as Needed
If monitoring shows the pool is over-utilized (e.g., >90% average over an hour), scale up the pool by increasing the eDTU count. This can be done via Portal, CLI (`az sql elastic-pool update --dtu 200`), or PowerShell. Scaling is an online operation with no downtime, but it may take a few minutes. Conversely, if the pool is under-utilized (e.g., <20% average over a week), scale down to save cost. There is no automatic scaling; you must manually adjust or use automation. Also consider removing databases that consistently use high resources — they are better off as individual databases with reserved capacity.
Enterprise Scenario 1: SaaS Multi-Tenant Application
A SaaS company hosts a separate database for each customer (tenant). Each tenant has low activity most of the time (e.g., 5 DTU average) but experiences spikes during monthly reporting (e.g., 50 DTU). The company has 500 tenants. If each database were individually provisioned at 50 DTU, the cost would be enormous. Instead, they create a Standard elastic pool with 1000 eDTU. Per-database min is set to 0, max to 50. The aggregate average load is 2500 DTU (500 * 5), which exceeds the pool's 1000 eDTU? Wait, that's not right — if average per database is 5 DTU, total average is 2500 DTU, which would require a pool of at least 2500 eDTU. This scenario is where careful sizing is needed. Actually, if average is 5 DTU, but spikes are 50 DTU, and spikes are rare and not simultaneous, the pool size can be much less than the sum of peaks but must be above the sum of averages. In this case, the sum of averages is 2500 DTU, so the pool must be at least 2500 eDTU. But that's still cheaper than 500 individual databases at 50 DTU each (25,000 DTU total). The pool allows the occasional spikes to be absorbed because not all tenants report simultaneously. The company sets up an alert at 80% pool utilization and has an automation runbook to scale up if needed. They also use elastic jobs to run maintenance across all tenant databases.
Enterprise Scenario 2: Development and Test Environments
A large enterprise has 200 databases used for development and testing. Usage is unpredictable: developers run tests sporadically, causing short bursts of high DTU consumption. Average utilization is 10% of a Standard S2 (50 DTU) database, but peaks reach 100% for 5 minutes. Individually provisioning S2 databases would be wasteful. They create a Standard elastic pool with 500 eDTU, per-database min 0, max 100. The pool comfortably handles the aggregate load because tests rarely run simultaneously. They save approximately 60% compared to individual databases. They also implement auto-pause for databases not used after hours (using elastic pool feature? Actually, auto-pause is for individual databases, not pools — another exam trap).
Common Misconfiguration
A common mistake is setting per-database max too high (e.g., equal to pool size). This can cause a single database to consume all pool resources, starving others. For example, a pool with 100 eDTU and a database with max 100 eDTU can monopolize the pool during a spike. Best practice is to set per-database max to a value that limits any single database to a reasonable portion (e.g., 20-50% of pool size). Another mistake is not monitoring the pool after creation — many administrators set it and forget it, only to find the pool consistently throttled during peak hours.
Production Scale and Performance
Elastic pools can support up to 4000 databases. However, with many databases, the management overhead increases. At scale, consider using Azure SQL Database elastic jobs for schema management and data movement. Performance-wise, the pool's total IOPS and log rate are proportional to the eDTU count. For example, a Standard pool with 1000 eDTU provides 20,000 IOPS (20 IOPS per eDTU). If the workload is IO-heavy, ensure the pool size provides sufficient IOPS.
Exam Focus: Azure SQL Elastic Pool Cost Optimisation
Objective 2.2: Provision and manage relational database services – This includes understanding elastic pools as a scaling and cost optimization option. The exam tests your ability to identify scenarios where elastic pools are appropriate and where they are not.
Common Wrong Answers and Why Candidates Choose Them:
"Elastic pools automatically scale databases up and down based on load." This is false. Elastic pools provide a shared pool of resources, but the pool itself does not auto-scale. You must manually scale the pool or use automation. Candidates confuse the per-database burst capability with auto-scaling.
"Elastic pools are always cheaper than individual databases." False. They are cheaper only when databases have low average utilization and sporadic spikes. If databases have high average utilization, individual databases with reserved capacity are cheaper. Candidates overlook the workload pattern requirement.
"You can add databases from different Azure SQL Database servers to the same elastic pool." False. All databases in a pool must be on the same server. Candidates think pools are cross-server, similar to elastic query.
"The pool's total eDTU is the sum of all per-database max values." False. The pool's eDTU is a separate limit; per-database max cannot exceed the pool limit, but the pool limit is independent. Candidates misinterpret the relationship.
Specific Numbers and Terms That Appear on the Exam:
Standard tier pool sizes: 10, 20, 50, 100, 200, 400, 800, 1200, 2000, 3000 eDTUs.
Premium tier pool sizes: 125, 250, 500, 1000, 1500, 2000, 2500, 3000, 4000 eDTUs.
Maximum databases per pool: 4000.
Per-database min eDTU: 0 (Standard), 0 or 25 (Premium).
Per-database max eDTU: up to pool size, but capped by tier (e.g., Standard max is 3000).
Edge Cases and Exceptions:
The exam may present a scenario where a pool is created with per-database min set to a non-zero value. This guarantees a minimum resource for each database, which can be useful for critical databases but reduces the sharing benefit. Candidates must know that setting min >0 reduces the pool's effective shared capacity.
Another edge case: if the pool's eDTU is too small, databases will be throttled. The exam may ask about the symptom: increased query latency and timeouts.
The exam might test that moving a database into a pool does not require downtime but may cause a brief connection interruption (seconds).
How to Eliminate Wrong Answers:
If the question mentions "cost savings" and "variable workloads," look for elastic pool.
If the question says "consistent high load" or "predictable spikes," elastic pool is likely not the answer.
If the question involves multiple servers, elastic pool is not possible.
If the question says "auto-scale," it's a trap — elastic pools do not auto-scale; you must scale manually or use automation.
Memorize the DTU model pool sizes and the fact that per-database min can be 0. The exam rarely tests vCore model specifics for elastic pools.
Elastic pools share resources among databases on the same server, reducing cost for low-average, spiky workloads.
The pool's total eDTU (or vCore) limit is independent of per-database min/max settings.
Per-database min guarantees a minimum resource; per-database max limits a single database's consumption.
Standard tier pool sizes: 10, 20, 50, 100, 200, 400, 800, 1200, 2000, 3000 eDTU.
Premium tier pool sizes: 125, 250, 500, 1000, 1500, 2000, 2500, 3000, 4000 eDTU.
Maximum databases per pool is 4000.
Elastic pools do NOT auto-scale; manual scaling or automation is required.
All databases in a pool must be on the same server and same service tier.
Moving a database into a pool is an online operation with brief interruption.
Monitor pool utilization using dtu_consumption_percent metric; set alerts at 80%.
These come up on the exam all the time. Here's how to tell them apart.
Elastic Pool (DTU Model)
Shared pool of eDTUs across multiple databases.
Cost-effective for databases with low average usage and sporadic spikes.
Per-database min and max limits control resource sharing.
Maximum of 4000 databases per pool.
Billing based on pool eDTU count regardless of individual database usage.
Individual Databases (DTU Model)
Each database has its own fixed DTU allocation.
Cost-effective for databases with consistent high usage or predictable spikes.
No sharing; each database is isolated.
No limit on number of databases (subject to server limits).
Billing based on each database's DTU count.
Mistake
Elastic pools automatically scale the databases within them.
Correct
Elastic pools do not auto-scale. The pool itself must be manually scaled up or down. Per-database min and max limits are static until changed. Auto-scaling requires additional Azure services like Azure Automation or Logic Apps to adjust pool size based on metrics.
Mistake
Elastic pools are always more cost-effective than individual databases.
Correct
Elastic pools are cost-effective only when databases have low average utilization and sporadic, non-overlapping spikes. If databases have high average utilization or simultaneous spikes, individual databases with reserved capacity are cheaper. The total cost depends on the workload pattern.
Mistake
You can add databases from different servers to the same elastic pool.
Correct
All databases in an elastic pool must reside on the same Azure SQL Database server. You cannot pool databases from different servers. This is a common exam trap.
Mistake
The pool's eDTU is the sum of all per-database max eDTU values.
Correct
The pool's eDTU is an independent limit. Per-database max eDTU cannot exceed the pool's eDTU, but the pool's total eDTU is not the sum of per-database max values. For example, a 100 eDTU pool can have per-database max of 50 eDTU for each of 10 databases, but the pool limit is still 100 eDTU.
Mistake
Elastic pools support databases from different service tiers (e.g., Standard and Premium) in the same pool.
Correct
All databases in a pool must be in the same service tier. You cannot mix Standard and Premium databases in one pool. The pool's tier determines the tier of all databases in it.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
Use an elastic pool when you have multiple databases with low average utilization (e.g., <20%) but occasional spikes that do not coincide. The pool allows you to buy a smaller total resource than the sum of individual peak requirements, saving cost. Avoid pools if databases have high average utilization (>50%) or if their spikes occur simultaneously, as you would need a large pool that negates the cost benefit.
Yes, you can change the service tier of an elastic pool (e.g., from Standard to Premium) using Azure Portal, CLI, or PowerShell. This operation is online but may cause a brief connection interruption. All databases in the pool are moved to the new tier. Note that changing tier may affect pricing and performance.
When the pool's total eDTU consumption hits 100%, databases will be throttled. Throttling affects only the databases that are actively consuming resources, and they will experience reduced throughput and increased latency. To resolve, you must either scale up the pool (increase eDTU) or reduce the load by moving some databases out of the pool.
No. In the DTU model, all databases in a pool must be in the same service tier (Basic, Standard, or Premium). However, within a tier, you can set different per-database min and max eDTU values to effectively give some databases more resources. In the vCore model, all databases share the same service tier (General Purpose or Business Critical).
You can monitor per-database eDTU usage in Azure Portal by navigating to the elastic pool, then selecting 'Resource Usage' and viewing the per-database chart. You can also use Azure Monitor metrics: `dtu_consumption_percent` for the pool, and for individual databases, use `dtu_used` metric. Additionally, Query Performance Insight shows query-level resource consumption.
Yes, the maximum number of databases per elastic pool is 4000 for both Standard and Premium tiers. This limit is per pool. If you need more databases, you can create multiple pools on the same server. Also, the server itself has a limit on total databases (e.g., 5000 for Standard tier servers).
Yes, elastic pools are available for both DTU and vCore purchasing models. In the vCore model, you specify eVCores (elastic vCores) for the pool. The same principles apply: shared resources, per-database min and max vCores. The vCore model offers more granular scaling and reserved instance pricing. The exam focuses more on DTU model, but know that vCore pools exist.
You've just covered Azure SQL Elastic Pool Cost Optimisation — now see how well it sticks with free DP-900 practice questions. Full explanations included, no account needed.
Done with this chapter?