TECH-SPHERES DILEMMA – TO ELASTIC POOL OR NOT TO ELASTIC POOL?
Azure provides SQL database offering either as an independent database or as an elastic pool. Azure SQL uses DTU as a metric for measuring the size of the SQL, which is a combination of CPU, Read/Write throughput and logging throughput.
The sizing and allocation of a standalone SQL is relatively straight forward. You pick the right level of DTU for your requirement and storage size, and deploy the SQL Database. Azure provides sizing tools to help you identify the right DTU for your workload.
However, when it comes to SQL elastic pool, sizing can become tricky, since there are multiple factors to consider.But before we get to sizing, let’s try and understand the benefits of elastic pools and when one should actually consider using them.
The elastic pool essentially helps pool together the DTUs of multiple databases, so that any of the database can tap into the DTU pool, whenever required. Contrast this with the scenario, where you had all your databases as standalone, you would have to size each of the databases to match its peak DTU requirement. Since for most applications the peak usage comes only periodically, the provisioned DTUs remain under-utilised most of the times. Now if we add-up these unused DTUs for all databases, it can be substantial. Since, you pay for the provisioned DTUs, unused DTUs translate to wasted dollars.
The elastic pool thus is a great cost optimisation option, when you have multiple databases, of more or less similar performance requirements. Since the cost per hour of elastic pool is approximately 1.5 times a standalone database, it is important to consider the cost implications before using elastic pool, especially if you have 2 or 3 databases.
For sizing the elastic pool, one has to consider the DTU usage, database size and the peak DTU usage of the databases. The DTU of the elastic pool should be more than the sum of DTU usage of all the databases. The elastic pool size should also be more than the sum of all the sizes of the databases to be added to the pool. When it comes to considering the peak DTU usage of the elastic pool, one has to factor in not just the peak DTU of each database, but also the concurrency of the peaks of the databases. Eg. If two of the databases being considered for the pool are likely to peak at the same time, then you have to add the peaks of both the databases and use that to size the elastic pool. You can also consider creating two or more elastic pools and separate the databases with concurrent peak usage in different pools. This can help pick a lower DTU elastic pool and save on cost.
Similar to stand-alone databases, elastic pool too offers various tiers like Basic, Standard and Premium. These tiers differ by max storage or DB size and more importantly on IO performance and availability. When considering pooling, it is important to pool databases with similar performance and availability expectations together.
Azure SQL elastic pool is a great way to optimise on cost and avoiding over-provisioning of DTUs or storage sizes, provided you have done the maths properly.
