What is an Azure elastic pool?

9 min to read Contributors

SQL DB elastic pools provide a simple cost effective solution to manage the performance goals for multiple databases that have widely varying and unpredictable usage patterns.

Note

Elastic pools are generally available (GA) in all Azure regions except West India where it is currently in preview. GA of elastic pools in this region will occur as soon as possible.

How it works

A common SaaS application pattern is the single-tenant database model: each customer is given their own database. Each customer (database) has unpredictable resource requirements for memory, IO, and CPU. With these peaks and valleys of demand, how do you allocate resources efficiently and cost-effectively? Traditionally, you had two options: (1) over-provision resources based on peak usage and over pay, or (2) under-provision to save cost, at the expense of performance and customer satisfaction during peaks. Elastic pools solve this problem by ensuring that databases get the performance resources they need and when they need it. They provide a simple resource allocation mechanism within a predictable budget. To learn more about design patterns for SaaS applications using elastic pools, see Design Patterns for Multi-tenant SaaS Applications with Azure SQL Database.

In SQL Database, the relative measure of a database's ability to handle resource demands is expressed in Database Transaction Units (DTUs) for single databases and elastic DTUs (eDTUs) for elastic databases in an elastic pool. See Introduction to SQL Database to learn more about DTUs and eDTUs.

A pool is given a set number of eDTUs, for a set price. Within the pool, individual databases are given the flexibility to auto-scale within set parameters. Under heavy load, a database can consume more eDTUs to meet demand. Databases under light loads consume less, and databases under no load consume no eDTUs. Provisioning resources for the entire pool rather than for single databases simplifies your management tasks. Plus you have a predictable budget for the pool.

Additional eDTUs can be added to an existing pool with no database downtime or no impact on the databases in the elastic pool. Similarly, if extra eDTUs are no longer needed they can be removed from an existing pool at any point in time.

And you can add or subtract databases to the pool. If a database is predictably under-utilizing resources, move it out.

Which databases go in a pool?

SQL databases sharing eDTUs in an elastic database pool.

Databases that are great candidates for elastic pools typically have periods of activity and other periods of inactivity. In the example above you see the activity of a single database, 4 databases, and finally an elastic pool with 20 databases. Databases with varying activity over time are great candidates for elastic pools because they are not all active at the same time and can share eDTUs. Not all databases fit this pattern. Databases that have a more constant resource demand are better suited to the Basic, Standard, and Premium service tiers where resources are individually assigned.

Price and performance considerations for an elastic pool.

eDTU and storage limits for elastic pools and elastic databases

The following table describes the characteristics of Basic, Standard, and Premium elastic database pools.

Basic elastic pool limits

Pool size (eDTUs) 50 100 200 300 400 800 1200 1600
Max storage per pool* 5 GB 10 GB 20 GB 29 GB 39 GB 78 GB 117 GB 156 GB
Max number DBs per pool 100 200 500 500 500 500 500 500
Max concurrent workers per pool 100 200 400 600 800 1600 2400 3200
Max concurrent logins per pool 100 200 400 600 800 1600 2400 3200
Max concurrent sessions per pool 30000 30000 30000 30000 30000 30000 30000 30000
Min eDTUs per database {0, 5} {0, 5} {0, 5} {0, 5} {0, 5} {0, 5} {0, 5} {0, 5}
Max eDTUs per database {5} {5} {5} {5} {5} {5} {5} {5}

Standard elastic pool limits

Pool size (eDTUs) 50 100 200 300 400 800
Max storage per pool* 50 GB 100 GB 200 GB 300 GB 400 GB 800 GB
Max number DBs per pool 100 200 500 500 500 500
Max concurrent workers per pool 100 200 400 600 800 1600
Max concurrent logins per pool 100 200 400 600 800 1600
Max concurrent sessions per pool 30000 30000 30000 30000 30000 30000
Min eDTUs per database {0,10,20,
50}
{0,10,20,
50,100}
{0,10,20,
50,100}
{0,10,20,
50,100}
{0,10,20,
50,100}
{0,10,20,
50,100}
Max eDTUs per database {10,20,
50}
{10,20,
50,100}
{10,20,
50,100}
{10,20,
50,100}
{10,20,
50,100}
{10,20,
50,100}

Standard elastic pool limits (continued) **

Pool size (eDTUs) 1200 1600 2000 2500 3000
Max storage per pool* 1.2 TB 1.6 TB 2 TB 2.4 TB 2.9 TB
Max number DBs per pool 500 500 500 500 500
Max concurrent workers per pool 2400 3200 4000 5000 6000
Max concurrent logins per pool 2400 3200 4000 5000 6000
Max concurrent sessions per pool 30000 30000 30000 30000 30000
Min eDTUs per database {0,10,20,
50,100}
{0,10,20,
50,100}
{0,10,20,
50,100}
{0,10,20,
50,100}
{0,10,20,
50,100}
Max eDTUs per database {10,20,
50,100}
{10,20,
50,100}
{10,20,
50,100}
{10,20,
50,100}
{10,20,
50,100}

Premium elastic pool limits

Pool size (eDTUs) 125 250 500 1000 1500
Max storage per pool* 250 GB 500 GB 750 GB 750 GB 750 GB
Max number DBs per pool 50 100 100 100 100
Max concurrent workers per pool 200 400 800 1600 2400
Max concurrent logins per pool 200 400 800 1600 2400
Max concurrent sessions per pool 30000 30000 30000 30000 30000
Min eDTUs per database {0,25,50,75,
125}
{0,25,50,75,
125,250}
{0,25,50,75,
125,250,500}
{0,25,50,75,
125,250,500,
1000}
{0,25,50,75,
125,250,500,
1000,1500}
Max eDTUs per database {25,50,75,
125}
{25,50,75,
125,250}
{25,50,75,
125,250,500}
{25,50,75,
125,250,500,
1000}
{25,50,75,
125,250,500,
1000,1500}

Premium elastic pool limits (continued) **

Pool size (eDTUs) 2000 2500 3000 3500 4000
Max storage per pool* 750 GB 750 GB 750 GB 750 GB 750 GB
Max number DBs per pool 100 100 100 100 100
Max concurrent workers per pool 3200 4000 4800 5600 6400
Max concurrent logins per pool 3200 4000 4800 5600 6400
Max concurrent sessions per pool 30000 30000 30000 30000 30000
Min eDTUs per database {0,25,50,75,
125,250,500,
1000,1750}
{0,25,50,75,
125,250,500,
1000,1750}
{0,25,50,75,
125,250,500,
1000,1750}
{0,25,50,75,
125,250,500,
1000,1750}
{0,25,50,75,
125,250,500,
1000,1750,4000}
Max eDTUs per database {25,50,75,
125,250,500,
1000,1750}
{25,50,75,
125,250,500,
1000,1750}
{25,50,75,
125,250,500,
1000,1750}
{25,50,75,
125,250,500,
1000,1750}
{25,50,75,
125,250,500,
1000,1750,4000}

* Elastic database share pool storage, so database storage is limited to the smaller of the remaining pool storage or max storage per database

** The 3000 eDTU pool size for a Standard elastic pool and the 4000 eDTU pool size for a Premium elastic pool are currently only available via PowerShell and the REST API. These pool sizes will be available in the Azure portal soon.

If all DTUs of an elastic pool are used, then each database in the pool receives an equal amount of resources to process queries. The SQL Database service provides resource sharing fairness between databases by ensuring equal slices of compute time. Elastic pool resource sharing fairness is in addition to any amount of resource otherwise guaranteed to each database when the DTU min per database is set to a non-zero value.

Elastic pool and elastic database properties

The following tables describes the limits for elastic pools and elastic databases.

Limits for elastic pools

Property Description
Service tier Basic, Standard, or Premium. The service tier determines the range in performance and storage limits that can be configured as well as business continuity choices. Every database within a pool has the same service tier as the pool. “Service tier” is also referred to as “edition.”
eDTUs per pool The maximum number of eDTUs that can be shared by databases in the pool. The total eDTUs used by databases in the pool cannot exceed this limit at the same point in time.
Max storage per pool (GB) The maximum amount of storage in GBs that can be shared by databases in the pool. The total storage used by databases in the pool cannot exceed this limit. This limit is determined by the eDTUs per pool. If this limit is exceeded, all databases become read-only.
Max number of databases per pool The maximum number of databases allowed per pool.
Max concurrent workers per pool The maximum number of concurrent workers (requests) available for all databases in the pool.
Max concurrent logins per pool The maximum number of concurrent logins for all databases in the pool.
Max concurrent sessions per pool The maximum number of sessions available for all databases in the pool.

Limits for elastic databases

Property Description
Max eDTUs per database The maximum number of eDTUs that any database in the pool may use, if available based on utilization by other databases in the pool. Max eDTU per database is not a resource guarantee for a database. This setting is a global setting that applies to all databases in the pool. Set max eDTUs per database high enough to handle peaks in database utilization. Some degree of overcommitting is expected since the pool generally assumes hot and cold usage patterns for databases where all databases are not simultaneously peaking. For example, suppose the peak utilization per database is 20 eDTUs and only 20% of the 100 databases in the pool are peak at the same time. If the eDTU max per database is set to 20 eDTUs, then it is reasonable to overcommit the pool by 5 times, and set the eDTUs per pool to 400.
Min eDTUs per database The minimum number of eDTUs that any database in the pool is guaranteed. This setting is a global setting that applies to all databases in the pool. The min eDTU per database may be set to 0, and is also the default value. This property is set to anywhere between 0 and the average eDTU utilization per database. The product of the number of databases in the pool and the min eDTUs per database cannot exceed the eDTUs per pool. For example, if a pool has 20 databases and the eDTU min per database set to 10 eDTUs, then the eDTUs per pool must be at least as large as 200 eDTUs.
Max storage per database (GB) The maximum storage for a database in a pool. Elastic databases share pool storage, so database storage is limited to the smaller of remaining pool storage and max storage per database.

Elastic database jobs

With a pool, management tasks are simplified by running scripts in elastic jobs. An elastic database job eliminates most of tedium associated with large numbers of databases. To begin, see Getting started with Elastic Database jobs.

For more information about other elastic database tools, see Scaling out with Azure SQL Database.

Business continuity features for databases in a pool

Elastic databases generally support the same business continuity features that are available to single databases in V12 servers.

Point in time restore

Point-in-time-restore uses automatic database backups to recover a database in a pool to a specific point in time. See Point-In-Time Restore

Geo-Restore

Geo-Restore provides the default recovery option when a database is unavailable because of an incident in the region where the database is hosted. See Restore an Azure SQL Database or failover to a secondary

Active Geo-Replication

For applications that have more aggressive recovery requirements than Geo-Restore can offer, configure Active Geo-Replication using the Azure portal, PowerShell, or Transact-SQL.

Additional resources