Azure SQL Elastic Pool

Introduction

 

Many organizations struggle with unpredictable workloads upon their multiple databases holding various applications. Over-pay to high resources all the time is based on peak usage calculations. Compromise on performance by assigning the lower resources and experience ineffective solutions.

 

Azure Elastic pools allow us to manage multiple databases that have varying performance. In an Elastic pool, multiple databases can share DTUs amongst themselves as and when they need which can result in better performance and cost savings. An Elastic database pool provides elastic database transaction units (eDTUs) and storage (GBs) that are shared by multiple databases. It also allows us to allocate a shared set of computing resources to a collection of Azure SQL databases, meaning that your databases are running in a shared resource pool on a co-tenanted Azure server over which you have no direct control. The benefit of using an Elastic Pool in Azure SQL Server database is that using it, a single database can be moved in and out of an elastic pool, which gives us flexibility. The elastic pool is a collection of a single database with a shared set of resources, such as CPU or memory. Single databases can be moved into and out of an elastic pool.

 

Understanding eDTUs and DTUs

 

This article assumes that you are familiar with the term DTUs. DTUs are a bit abstract but determine the relative horsepower of the database in comparison with others. It does not match a certain number of operations/second but rather a comparison between the different instance scales(if you are new to DTUs you can find some useful info here). So 5 DTUs are the smallest version and should be used for very low usage. A 20 DTU Database is 4 times more capable than the 5 DTU database.

 

Resource Allocation for Azure Elastic SQL Pools

 

A large difference between the peak and average utilization of a database indicates prolonged periods of low utilization and short periods of high utilization. This utilization pattern is ideal for sharing resources across databases. A database should be considered for a pool when its peak utilization is about 1.5 times greater than its average utilization.

 

All databases in an elastic pool share the same allocation of resources, such as CPU, memory, worker threads, storage space, tempdb, on the assumption that only a subset of databases in the pool will use compute resources at any given time. Azure SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single server and share a set number of resources at a set price. Elastic pools solve this problem by ensuring that databases get the performance resources they need when they need it. They provide a simple resource allocation mechanism within a predictable budget. The DTU allocation per database is unaffected, but now we have an overall eDTU limit for the pool. A 200eDTU elastic pool, for example, provides the same compute size as an S4(200 DTU) Azure SQL Database. Of course, now those 200 DTUs are shared by however many databases you have in the pool.

 

There is some additional cost to pooling: eDTUs are 1.5x the price of DTUs. This is explained by pooled resources being more likely to be used, meaning that the Azure platform. The minimum configurable data storage is 1 GB.

 

Single Database DTU and Storage Limits

 

	Basic	Standard	Premium
Maximum storage size per database	2 GB	1 TB	1 TB
Maximum storage size per pool	156 GB	4 TB	4TB
Maximum eDTUs per database	5	3000	4000
Maximum eDTUs per pool	1600	3000	4000
Maximum number of databases per pool	500	500	100

 

Elastic Pool eDTUs, Storage, and pooled database limits

 

Among the most important things to know about the elastic database pools are that there are limits on how much/little eDTUs you can use for the individual databases. For

For example, for basic pools you have max usage by a single database set to 5 DTUs (so it does not matter if you have 100 eDTUs available in the pool, your individual database can only utilize 5.

 

Elastic Database Pools come in the classic Basic, Standard, Premium tiers that pack different capacities.

 

	Basic	Standard	Premium
Maximum size per database	2BG	1 TB	1 TB
Maximum storage size per pool	156 GB	4 TB	4 TB
Maximum eDTUs per database	5	3000	4000
Maximum eDTUs per pool	1600	3000	4000
Maximum number of databases per pool	500	500	100

 

Why use SQL Elastic Pools?

 

Let's say you have two S4 Azure SQL Databases, meaning that each has access to a maximum of 200 DTUs. Would you want to put them together in a 200eDTU elastic pool, and would you want to allow either one of the databases to use all the pool's DTUs at any time? It depends. In most organizations, database activity does not spread evenly across all databases. Some are much busier than others. Similarly, few databases show even levels of activity throughout the day; the workloads are often "spiky," with periods of high user activity levels and resource use interspersed with quieter periods.

 

Figure 1 shows the two S4 Azure SQL Databases, each with spiky workloads, as reflected in the DTU loads for different periods.

 

For both databases, there are significant periods where you'll be paying for resources you're not using unless you spend a lot of time scaling up and down by the hour, as required.

 

Elastic pools are compelling in this case because we have two databases that are loaded at different times and, together, the load spreads evenly across the day. For example, if you have two similarly-resourced databases, one of which is used primarily during business hours and the other is primarily used overnight, placing the two databases in an elastic pool allows you to run the overnight process using the resources you're already paying for to serve the daytime database.

Similarly, if you have several rarely-used databases, you might consider placing them in one elastic pool.

 

While Figure 2 looks great in theory, in practice, there will inevitably be some overlap in the high-activity periods of the two databases. This means that the tricky thing about putting databases in elastic pools is being sure that competition for the pooled resources doesn't affect either database's performance. If you look at a pooled database in isolation, it may seem to be adequately-provisioned, running at say 50 percent CPU. However, this is 50 percent of the maximum possible CPU assuming no other load in the pool: activity on other databases in the pool might mean that there is no additional processing power available to that database. This is why it's so important to monitor utilization both for the databases and the elastic pool.

 

How to determine the database is good for the Elastic pool?

 

An S3 Database that peaks to 100 DTUs and an average uses 67 DTUs or less is a good candidate for sharing eDTUs in a pool. Alternatively, as S1 database that peaks to 20 DTUs and on average uses 13 DTUs or less is a good candidate for a pool.

 

Elastic Pool Benefits

• Significant cost savings for ISV/SaaS vendors hosting a large number of databases in elastic pools compared to standalone databases.
• Databases in elastic pools perform at the same level as standalone databases, and sometimes even better thanks to the ability to spike in resource consumption while other databases are idle within the same pool.
• Ability to move the database in and out of elastic pools without incurring downtime, and no application code change is required either.
• Works well with other elastic features like elastic jobs, elastic query to manage and query databases in pools efficiently, just like stand-alone databases.
• Fully compatible with BCDR feature including active geo-replication, point in time restore, and geo-restoration for basic/standard/premium tiers of pools.

Elastic Pools - Best Practices

• For large number of database across many servers, consolidate databases into fewer number of servers and established proper pool size.
• Use PowerShell or REST API to manage a large number of databases in pools.
• Use portal and SCOM management packs for Azure SQLDB to monitor elastic pools and database.
• Most features of standalone databases are compatible with elastic pools with the exception of in-memory OLTP, which is not supported yet.
• SLO change could be time-consuming, which could impact failover and pool rebalance scenarios.
• SLO change could induce small performance degradation during the change, try to operate during non-peak hours.