Having multiple databases? Switch to Elastic Pool. Now!

Azure SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. But do you know when not to use it?

SQL database offerings are offered by Azure as either a standalone database or an elastic pool. The size of the SQL is determined by Azure SQL using the DTU measure, which combines CPU, Read/Write throughput and Logging Throughput. A standalone SQL is sized and allocated in a fairly simple manner. You deploy the SQL Database after selecting the appropriate level of DTU for your needs and storage capacity. In order to assist you to choose the best DTU for your workload, Azure offers sizing tools. But before we get to sizing, let's try and grasp the advantages of elastic pools and when one should actually consider utilizing them. However, when it comes to SQL elastic pool, sizing can get complex because there are many elements to take into consideration.

In essence, the elastic pool serves to pool the DTUs of several databases so that any database can access the DTU pool as needed. In contrast, if all of your databases were standalone, you would need to size them all to accommodate their individual peak DTU requirements. Since the peak usage for the majority of applications only occurs seldom, the supplied DTUs are frequently underutilized. These unused DTUs for all databases can add up to a lot if we sum them all up. Unused DTUs equates to lost money because you pay for the provided DTUs.

The elastic pool is a fantastic cost-optimization option as a result, especially if you have numerous databases with largely comparable performance requirements. Before adopting an elastic pool, especially if you have two or three databases, it is necessary to evaluate the financial implications because the cost per hour of an elastic pool is roughly 1.5 times that of a standalone database. The DTU utilization, database size, and peak DTU usage of the databases must all be taken into account when sizing the elastic pool. The elastic pool's DTU should be higher than the total DTU usage across all databases. The size of the elastic pool should also be more than the total size of the databases that will be added to the pool.

One must take into account not only the peak DTU of each database but also the concurrency of the peaks of the databases when calculating the peak DTU usage of the elastic pool. Eg. You must add the peaks of both databases and use that information to size the elastic pool if two of the databases being examined for the pool are anticipated to peak at the same time. Additionally, you might think about setting up two or more elastic pools and dividing the databases with concurrent peak consumption into these pools. Choosing an elastic pool with a lower DTU will help you save money.

The elastic pool has different tiers, including Basic, Standard, and Premium, just like standalone databases. These tiers vary in terms of maximum storage capacity or database size, but IO performance and availability are more crucial. Databases with similar performance and availability expectations should be pooled together when considering pooling. If you have done the math correctly, using the Azure SQL elastic pool is an excellent method to reduce costs and prevent over-provisioning of DTUs or storage volumes.

.

“With every benefit, a drawback emerges. The possibility exists that all databases in a pool will compete for resources and performance may suffer since the resources are no longer isolated.”