Azure SQL databases can be deployed either as a single database, sometimes called “singletons” or into an Elastic Pool. The question I sometimes get is, how do you pick which one you should use?
In this post, which will be the first in a series to introduce you to the various deployment options of Azure SQL Database, we’ll look at what are some main considerations for selecting a single database or an Elastic Pool.
Deploying single Azure SQL Database
If you’ve read my previous post, about the Logical Servers in Azure SQL, you already know that computing resources for Azure SQL Databases are configured at the database or a pool level. Single database deployments are the simplest option, as we configure resources for that database alone. After setting the compute resources, we can either scale it up or down, depending on the workload that we’re observing.
While being the simplest way to assign and to manage compute resources of a database, it’s also the most difficult one to optimize. This is because you need to configure the compute resources to satisfy the peak workloads, rather than average. I’ve seen the quote, that in cloud you pay for what you use, but that’s not really the whole truth. You also pay for the resources you reserve, and you pay for them whether you use them or not.
Is there anything we can do to optimize single databases, then? The answer is yes, of course. But we’ll need to do it the hard way. We can always optimize queries, indexes, and the schema in general. We can ensure that only the data we need is stored in the database, and the unused data is archived elsewhere.
All of this is (mostly) fine, if and when you’re lucky enough to manage no more than a handful of databases. However, I don’t really recall meeting that many lucky DBAs. When the size of your Azure SQL Database estate grows, as it eventually will, you’re going to be really busy trying to manage and tune single database workloads.
Or you could consider the other option, which is to use Elastic Pools.
Deploying Azure SQL Database in Elastic Pools
With the Elastic Pools, you assign compute resources for a group of databases. This makes Elastic Pools great for two, very specific reasons. Foremost, it allows us to manage compute resources at scale. Secondly, it requires less over provisioning per database to accommodate for compute intensive periods.
However, to make most out of using Elastic Pools, we need to have good number databases. I have seen the claim, that you could optimize just with two, at least S3 databases. Out of my personal experience, a good number seems to be more than three databases. The more we have, the easier it is to find workloads that are compatible with each other.
So, what does it mean, when I say compatible workloads? In simple terms, it’s about identifying what, if any, time-based seasonality each database workload has. The highest granularity I usually go is hourly and for the lowest, the daily. Once we identify the busy hours or days for each database, we then find the ones that have different seasonalities to group together.
Let’s add a simple visualization, what this could look like.
Let’s say that in the example above, each column represents a one-hour timeframe. For the database DB1, the first hour is the busiest one, followed by two quiet hours before the workload spikes up again. If this were a single database, we’d size it to accommodate the peak workload during the hour one, and we’d also pay for those resources during hours two and three, even if we didn’t need them.
When you have multiple databases with different seasonality, it makes much more sense to create a larger pool of resources, where you can fit multiple databases that would always use nearly 100% of the allocated compute resources. In a real life, we’ll probably not get a perfect, near 100% usage 24/7, and there will always be some overhead in the pool sizes. However, in most cases it’ll be less wasted compute than we’d see with having multiple, single databases.
When multiple databases share resources, that inevitable leads to a question on how to deal with…
Noisy neighbors in the Elastic Pool?
Elastic Pools come with resource governance, meaning that we can set the resource limits for each database within the pool. The resources you can limit, depend on the purchasing model of the pool.
When the purchase model is DTU, you can limit:
- Minimum DTU per database
- Maximum DTU per database
- Maximum storage per database
When the purchase model is vCore, you can limit:
- Minimum vCores per database
- Maximum vCores per database
- Maximum storage per database
Naturally, you can also resize the pool itself, if any of the database workloads grow too large to be contained by the Elastic Pool. It’s also quite common to move databases between pools, if their workload seasonality changes over time.
Wrapping it up
Thank you for reading! In the next post, we’ll look into purchasing options for Azure SQL Databases. See you then!
Leave a Reply