Azure SQL Elastic Pool, Single Databases, and DTUs

In this post, I will explain some of the language that is used to specify and size Azure SQL, the database-as-a-service offering from Microsoft Azure.
 

 

DTUs

There are two ways that you can deploy Azure SQL in Microsoft Azure. The first, and the most common one that I see, is the Single database option. When you select a Single database, Azure deploys 1 database that runs in it’s on bubble of performance. The database has several ways to size it’s performance, but the most commonly quoted and confusing one is Database Transaction Units (DTUs).
Every database in Azure SQL offers a maximum number of DTUs. But what the heck are these things? I’ve heard of IOPS, virtual CPUs, RAM, and so on, but DTU was a new one to me.
Microsoft describes DTUs as being:

… a blended measure of CPU, memory, I/O (data and transaction log I/O)

The goal of DTUs is to simplify the planning of performance for a database. There’s a good chance that you’ve had a part in this conversation before:

• Database/App developer: “I’d like a database server, please.”
• IT Pro: “OK. How much CPU do you need?”
• Database/App developer: “Average … whatever the normal is.”
• IT Pro “Uh … OK. How many IOPS do you need?”
• Database/App developer: “What’s an IOP?”

And in the end, either too many or too few resources are provided and no one is happy. DTUs take the metrics that determine the performance of a database and mush them together in a measure that we can use to abstract and compare performance. I have no idea what a “brake horsepower” is, but I know that a car with 500BHP is probably a lot more fun than one with 100BHP. The same goes for databases; the S7 database with 800DTUs offers a performance baseline that is 40 times more than an S1 database with 20DTUs.
 

 
When you deploy an Azure SQL single database, you will pick one of the sizes. The main, but not the only, performance limits of that size will be DTUs. The database will be able to scale up to the documented DTU limit. If a database hits that limit:

• It will have no impact on any other database and no other database will have an impact on it.
• Query performance will degrade in the form of higher latency.

Microsoft provides a number of tools/methods to help you understand the DTU requirements of existing SQL Server databases. Using this information you can correctly size and Azure SQL single database before a migration.

Elastic Pools and eDTUs

A larger organization might have a number of databases that are:

• Hard to size because they are new.
• Very “bursty”, sometimes needing a lot of DTUs and sometimes needing only a few.

In this case, Microsoft offers an alternative to a single database. The elastic pool is a collection of databases that share eDTUs or elastic DTUs. This is a similar kind of measure but how it is used changes. Instead of being applied to a single database, an eDTU applies to a pool of databases that share resources in an elastic pool, measuring the performance potential of the entire pool instead of a single database.
Each database in the pool is capable of using a few or lots of the entire pool’s performance capacity. If a single database needs to have a burst in performance, it can do so within the constraints of the pool. This would impact other databases in the same pool, but has no impact on any database outside of the pool.

 
You might decide that some databases need a change in plan and this is possible:

 

• A single database might underuse it’s DTUs or prove to be “bursty” and might be more suitable to be part of a pool. You can move the database into an elastic pool. The database can use the pool of eDTUs and the cost of owning/operating the database should come down.
• A database that is already in a pool might require too many resources to remain in that pool; you can move it out to become a single database with its own DTUs.