Azure SQL Database elastic pools are on the vogue. Lately, a straightforward and cost-effective solution to scale and manage multiple databases that may have unpredictable and varying demands. All databases on the elastic pool may be residing on the same server and will share many resources at the given price. Elastic pools also enable Software as a Service (SaaS) based developers to optimize the price performance and performance elasticity on each of the databases in a group of DBs.
About SQL elastic pools
As we know, SaaS developers construct enterprise applications on the big-scale data-tiers, which consist of many databases. Usually, the pattern followed is by provisioning a single database to each customer. However, in many cases, different customers may have varying user patterns, so it becomes difficult for the developers to predict the resource requirements at the first point for each user. Traditionally, there are two options:
- Over-provisioning of the resources based on the usage and overpay, or
- Under-provision of resources to save cost, but by compromising performance/customer satisfaction at peaks.
This is the major challenge solved using elastic pools by making sure that the databases get all the performance resources they need as and when they need it the most. They also offer a very simple and easy resource allocation within a predictable budget.
In the case of elastic pools, there is no charge perdatabase. You will be billed for the usage per hour when the pool exists at the highest vCores or eDTU regardless of whether the pool remained active for less than an hour of the actual use.
Elastic pools will also help the developer purchase the resources for a given pool as shared by many databases to accommodate unpredictable periods of individual database use. You may custom configure the given pool’s resources based either on the DTU-based buying model or vCorepurchase model. The overall utilization of the database will determine resource needs for a pool. The budget of development generates the resources made available to the pool. Developers can add any number of databases to this pool by optionally setting the maximum and minimum resources for that database. Developers can then set the pool resources based on their project budget. It is also possible for developers to use their pools to grow seamlessly from a learn startup to further develop and mature business at an ever-increasing scale.
Within a given pool, the individual databases have the flexibility to auto-scale the usage within the given parameters. Given a heavy load, databasesmay consume more and more resources to meet the demand. The databases having a lighter load may consume only fewer resources and, under zero load, may consume no resources. Provisioning these resources for the entire pool period than for each database will simplify management tasks and development efforts.
You can also have a predictable budget for the given pool, with which you can getadditional resources added to the existing pool with zero downtimes to do it. Similarly, if there are no extra resources needed anymore, then it can also be removed instantly from the existing pool at any given point. You may also add or remove the databases from the pool, and if a database is under-utilizing the resources, you may turn it off. While moving the databases into an elastic pool or moving it out, the users will not experience any downtime except for a very short period at the end of the operation when the database connection is dropped. For better support in remote database management, you can explore the offerings of RemoteDBA.com.
When to consider an SQL DB elastic pool?
Elastic pools are ideal for a large number of DBs with specific utilization patterns. The pattern of a given database is characterized by the average utilization and the infrequent utilization spikes. On the other hand, multiple DBs with medium to his utilization may not be placed in the same elastic pool. The more number of DBs you add to a pool means the greater savings you enjoy. Based on your application utilization pattern, it is also possible to see the savings going multifold.
The price of an elastic pool is based on the function of pool eDTUs. When the eDTU unit price for a given pool is 1.5 times greater than the DTU unit price for one database, then the pool eDTUs can be ideally shared by multiple databases, and we need only a fewer total eDTUs. Such distinctions in pricing and eDTU sharing will be based on the price savings potential that the pools may offer.
If the aggregate number of resources for the given database/stend to be 1.5x the resource needs or higher for the given pool, using elastic pools is the most cost-effective approach. With shared resources, all the pool databases may not simultaneously use all the resources up to the maximum limit for any single database. A fewer number of databases that concurrently peak and lower the pool resources can be set, making the pool more cost-effective. Overall, not more than two-thirds of the pool’s databases should peak simultaneously to their optimum resource limit.
Resource utilization per each database
Considering the above points, there is a vast difference between peak usage and average utilization of the database, which indicates a prolonged period of low utilization or short periods of high utilization equal. This pattern is ideal if you want to share the same resources across various databases. A database also must be considered for the pool while the peak utilization is more than 1.5 times greater than average utilization.
To choose the appropriate pool size, you need to know that the ideal pool size for you depends on the aggregate resources needed for all the databases in the given pool. This also involved determining maximum resource utilization by all databases into the pool, vCores based on the purchasing model chosen, maximum utilized storage bytes, available service tiers, limits of each resource model, etc.
