Recently Microsoft introduced SQL Elastic Database services to their suite of Azure offerings. There is a plethora of information available about exactly how the elastic services function, but if you are like me, reading through the material might just confuse you more than enlighten you. I am going to try to clear up the confusion (at least in my own mind) by providing some clear descriptors of exactly what the SQL Elastic offering provides and what it was designed for. I will also tell you what it isn’t.
What SQL Elastic Databases Are Not
When I first heard about elastic databases I made some assumptions; they all turned out to be completely wrong. As an enterprise DBA I always thought it would be great if I could provision a database in Azure at a level where the performance was acceptable most of the time. If performance started to slip because of increased activity or workload, the virtual framework would just increase RAM and CPU or even disk to meet the new needs. Sounds great right? The best part would be that I would only pay for the low level of provisioning the vast majority of the time and some kind of premium charge for when I needed the extra power. Regardless of the realities of the economic feasibility of such a solution, it is still what I envisioned when hearing the word “elastic.” As the heading might have suggested, that’s not how Azure elastic databases work. That’s the bad news. There is however a great deal of good news since you can provide similar functionality as I describe with the new elastic services. It takes more planning and there are some caveats that have to be well understood, but an effective solution is available.
What SQL Elastic Databases Are
SQL Elastic Databases are more accurately described as SQL Elastic Database Pools. The idea is that you can place multiple Azure databases in to a pool where they all share resources. The pool is configured to over a maximum and minimum amount of computing resources. Microsoft has defined these available resources as a Database Throughput Unit, or DTU. In the case of elastic pools, they are defined as an Elastic Database Throughput Unit, or eDTU. DTUs and eDTUs are calculated essentially the same and are determined through a calculation of measured disk reads, disk writes, processor time and transaction log flushes. The busier the database, the more DTU or eDTUs it will consume. Elastic pools are kind of a resource time share for databases. If you have several databases with varying usage patterns you can place them in a single resource pool. When one of the databases requires more resources, they are there for the taking and performance is maintained. When the usage spike is over, the resources are returned to the pool and are available for the rest of the databases when their spikes occur.
The advantage of pools is that the cost of provisioning a pool with 1000 available eDTUs is less expensive than the cost of provisioning multiple 1000 DTU stand-alone databases. Obvious, right? The big benefit is when we have several databases, all that can spike to high DTU levels. Provisioning them to maintain a certain level of performance covering the spikes forces us to over-provision.
For example, if I have 10 databases that have an average resource consumption of 5 DTUs, but have occasional spikes up to 100 DTUs I would need to provision all 10 at 100 DTUs to maintain acceptable performance. That basically equates to 10 S3 Standard Azure databases that provide 100 DTUs each at about $150.00 a month for a total estimated bill of $1500.00 a month. Since all 10 databases do not spike at the same time, which is an important metric we need to know, we could provision a comparable Standard Elastic Pool that provides a maximum of 100 DTUs per database and a total pool resource of 400 DTUs at $900.00 a month. Four of our ten databases could spike at the same time and we would still provide the needed resources. How cool is that?
As I alluded to, it is important when configuring pools that databases with dissimilar usage patterns are combined. If all 10 databases spiked at the same time, performance would be unacceptable for most of them and the advantage of the resource pool would be lost. The good news is that it is possible to provision multiple pools per server so the databases can be grouped based on their usage pattern.
What SQL Elastic Databases Are Designed for
Microsoft designed the elastic databases for a fairly specific scenario; SaaS providers that host a single database per customer with multiple customers. All the databases share a common schema. For instance, if you provided a solution where all of your customers require a separate database, you can host all the databases in a pool where they share resources. As long as the customer usage patterns are diverse you can adequately provide a reasonable performance level at the fraction of the cost of provisioning multiple stand-alone databases. To make the elastic offering even more compelling is the set of tools Microsoft has released that makes managing, querying and updating all the databases in a pool very easy.
To learn more, contact Northwest Cadence.