Introduction to Azure SQL Database Elastic Database Pools

Thursday Feb 4th 2016 by Marcin Policht

Traditional deployments of Azure SQL Database involve identifying projected resource requirements and selecting individual Azure SQL Database instances. For fluctuating workloads, this frequently results in over- or under-provisioning. To address this challenge, Microsoft offers another approach to sizing Azure SQL Database that relies on Elastic Database Pools. Read on to learn more.

Traditional deployments of Azure SQL Database involve identifying projected resource requirements (in terms of expected database size and performance load) and selecting the corresponding service tier and performance level. These, in turn, determine the sizing of resulting Azure SQL Database instances. While it is very straightforward to modify these parameters (and transition to a new service tier or performance level without incurring any downtime), trying to make these adjustments to reflect potentially continuous changes in database utilization is typically not realistic. As a result, despite the agility built into the Azure platform, you might still end up with your systems being either over- or under-provisioned. To address this challenge, Microsoft offers another approach to provisioning Azure SQL Database, which relies on Elastic Database Pools.

Before we explore this new provisioning mechanism, let's first review the traditional approach to Azure SQL Database sizing. Performance capacity of an Azure SQL Database is measured in so-called Database Transaction Units (DTUs). This measurement represents relative transaction processing capabilities calculated in Online Transaction Processing (OLTP) requests per second. Effectively, a database with 10 DTU is expected to be able to process twice as many transactions as a database with 5 DTUs (under fully loaded conditions). The amount of DTUs available to an Azure SQL Database is a direct result of your choice of its service tier and performance level (which collectively determine the pricing tier that in turn translates into the monetary cost of the service). Azure SQL Database is available at three service tiers (Basic, Standard, and Premium) along with a range of corresponding performance levels for two of them (S0, S1, S2, and S3 for the Standard tier and P1, P2, P4, P6/P3, and P11 for the Premium tier). The service tier is the indicator of such capabilities as the maximum database size (ranging from 2 GB with Basic up to 1 TB with Premium), retention period of backups available for Point-in-time restores (ranging from 7 days with Basic up to 35 days with Premium), as well as disaster recovery support (ranging from Geo-Restore only with Basic up to Active Geo-Replication with Premium). Individual performance levels that exist within their respective service tiers give you more granular control over such database characteristics as available DTUs, as well as maximum in-memory OLTP storage, maximum concurrent requests, maximum concurrent logins, and maximum sessions (for more details regarding these settings, refer to SQL Database options and performance: Understand what's available in each service tier).

As you can see, you have significant flexibility in choosing the size and performance capabilities of your Azure SQL Database deployment. In addition, you can easily change both the service tier and performance level either directly from Azure portal (via the Pricing tier tile on the database blade) or by using Azure PowerShell (by invoking the Set-AzureRmSqlDatabase cmdlet with the -Edition and the -RequestedServiceObjectiveName parameters). Note, however, that such operation requires that the new configuration supports the sizing and resiliency settings of the current deployment (for example, you cannot switch from Premium to Standard if the existing database size exceeds 250 GB, which is the maximum value supported by the latter). In addition, note that you are limited to 4 such changes within a 24 hour period.

As we pointed out, even with these accommodations in place, there is still a potential over- or under-provisioning of Azure SQL Database instances. Fortunately, there is another deployment approach which allows you to minimize potential utilization gaps. Starting with Azure SQL Database V12, it became possible to take advantage of so called elastic database pools. This feature (in Preview at the time of writing this article) is based on the premise of assigning performance capacity and functional capabilities to a collection of multiple Azure SQL Databases (hosted on the same server), rather than to individual ones. As a result, platform resources are allocated to an entire pool and distributed dynamically across its members. Similar to stand-alone, traditional deployments, there is a standard method of measuring these resources known as elastic Database Transaction Units (eDTUs). They are equivalent to DTUs and provide means for comparing performance offered by different pools.

While eDTUs vary depending on the service tiers available for elastic database pools, there are no specific performance levels within each of them. Effectively, your choices are limited to the Basic, Standard, and Premium service tiers, which in turn dictate database-level settings (such as maximum database size, maximum eDTUs, retention of backups available for point-in-time restores, or disaster recovery capabilities) and pool-level settings (such as maximum number of databases, range of eDTUs, storage range, or maximum concurrent requests and sessions). For more details regarding these values, refer to the same article we mentioned earlier (SQL Database options and performance: Understand what's available in each service tier).

In order to create an elastic database pool, you must have a SQL Database V12 server that will host it. (A server can host multiple pools, although a single pool cannot span multiple servers). You can provision one via Azure portal or Azure PowerShell (alternatively, it is also possible to upgrade an existing SQL Database V11 server directly from the Azure portal). Next, you need to add a pool to the server (in the portal, this operation is invoked by using the Add pool button in the server's blade). During this process, the platform will provide recommendations regarding the pricing tier, number of databases, and the most appropriate eDTU limit. You have the option of either applying the recommended settings or choosing one of the pricing tiers (Basic, Standard, or Premium) on your own. Note that at the time of writing of this article, you are not allowed to change the tier once the pool is created (if any subsequent modifications are needed, you have to add a new pool and move any currently pooled databases into it). Assuming you picked a tier yourself, ignoring Azure recommendations, after the new pool is provisioned, specify databases that it should contain; here again, you will see recommendations offered via the Azure portal interface. Finally, configure pool performance characteristics, including:

  • POOL eDTU - dictates the allocation of capacity (in terms of eDTUs) on the pool level. This should be determined based on the average utilization levels of all databases that belong to the pool.
  • eDTU MIN - guarantees minimum transactional throughput (in terms of eDTUs) for every database in the pool (you can choose not to provide any guarantees by setting this value to 0).
  • eDTU MAX - ensures that individual databases in the pool are able to gracefully handle peaks in resource demand, while minimizing impact on other databases sharing the same pool and without excessive over-provisioning.

It is important to point out that elastic database pools are most suitable in scenarios where utilization trends across multiple databases fluctuate and complement each other. You are not likely to see meaningful benefits of implementing this approach for steady workloads that remain relatively static over time.

This concludes our introduction to this new technology. In our upcoming articles, we will explore its characteristics in more detail.

See all articles by Marcin Policht

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved