Azure SQL Database Elastic Pools

Azure SQL Database Elastic Pools are a mechanism for grouping your Azure SQL Databases together into a shared resource pool. Imagine for a moment that you had a physical server on premise. On that server, you have a single SQL Server instance and a single database. This example is similar to how Azure SQL Database works. You have a fixed amount of resources and you pay for those resources, even when you are not using them.

An Elastic Pool is analogous to that same server and instance, instead you add several databases to the instance. The databases will share the same resource pool which can be cheaper than paying for separate sets of resources, as long as your databases’ peak usage times do not align with each other.

Before we get too far into Elastic Pools, it is important to understand DTUs.

Database throughput units (DTUs / eDTUs)

A DTU is a unit of measure that Microsoft created. The DTU is based on the Azure SQL Database Benchmark (ASDB) which was designed to abstract performance away from the hardware. The ASDB analyzes the following metrics over time:

• % Processor Time
• Logical Disk Reads/sec
• Logical Disk Writes/sec
• Database Log Bytes Flushed/sec

The ASDB functions off of a schema with six tables. The tables are loaded in three different ways.

• Fixed-size
• Scaling
• Growing

As the name describes, fixed-size is the same no matter what service tier or database performance. The scaling tables do not grow while the workload is running but is scaled to a size which is proportional to the database performance or number of DTUs expected. The growing tables are sized like the scaling tables and will then grow during the workload.

ASDB runs a OLTP workload with a read/write ratio of about 3:2.

These DTUs are calculated the same whether they are for a single database or many databases in an Elastic Pool (eDTUs). Microsoft provides a DTU calculator which can be run on your existing database(s) to generate metrics. Those metrics can then be uploaded to the DTU calculator website to perform the necessary analysis and translation.

Service tiers

In addition to choosing the correct number of DTUs/eDTUs, you must also decide on a service tier. The service tier effects:

• The number of databases per pool
• Individual database size
• Maximum total storage in the pool
• Database backup retention period

There are four service tiers.

MSDN SQL Database Service Tiers

Elastic Pools

Now that we have an understanding of our units and measurements, we can turn our attention to workloads. Elastic Pools are all about fully utilizing the resources that you are paying for. The concept is extremely similar to the over subscribing configurations you are likely already performing on premise in your virtualized environments. Microsoft has created a few valuable visualizations to demonstrate. I have included these images here.

With this database, you would need to pay for 90 DTUs, or let us say 100 DTUs to give a bit of wiggle room. That expense would be waste for the majority of the day.

With four databases, who have varying workload spikes, you would still need to pay for 100 DTUs per database.

With Elastic Pools, you are able to pay for the same 100 DTUs but include many databases. The key is that the databases are expected to peak at different times than each other or peak unpredictably. If each database had a predicable workload, then automating the scaling up and down of the resources would become another valid option to consider.

These types of unpredictable workloads with high peaks are often seen in Software as a Service (SaaS) applications and other multi-tenanted systems.

Take away

Think about the performance of each of your database which are using Azure SQL Database or that you intend to migrate to Azure. Then consider your other workloads so you can be smart about your money. It is features like this that truly bring out the value of the public cloud and put Infrastructure as a Service (IaaS) in the past for a lot of systems.