In previous article, we have seen how to create a single database. During creation of single database, we also have created a new database server.

There is one more offering under SQL Database, Elastic Pool. Let’s have a look at what does it mean, when should we go for it and how to create the elastic pool.

The Scenario

It is very common that one company has more than one application, each with its own database. These applications have different purposes, and hence the usage patterns might also be varying and unpredictable.

Similarly, you may be working on a multi-tenant application. You may have different database for every customer. The customers may be from different parts of the world. Each of the customer database may have varying peak times, the times at which the database utilization reaches it peak.

So, there are two ways by which you can opt for database servers:

• You can provide the resources based on resource utilization in peak hours. But if the peak hours is less than 50% of the overall time, then this will also result in overpay and over-provision for average hours.
• Alternatively, if peak hours are very less, you can buy for only those resources which are required for average utilization of database hours. This will reduce the database server cost, but it will also result in bad user experience during peak hours as many users will not be able to connect to the database because of limited database resources.

So either you will overpay or you will lose your customers. How can you solve this issue ? The answer is Elastic Pool.

What is Elastic Pool ?

Elastic Pool will enable you to purchase resources which will be shared by set of databases (rather than a single database). This will help to accommodate varying and unpredictable usage patters of individual databases.

You can configure resources for the pool based either on the DTU-based purchasing model or the vCore-based purchasing model.  The developer simply adds databases to the pool, sets the minimum and maximum resources for the databases and then sets the resources of the pool based on their budget.

The advantage here is the database which has heavy load will user more number of resources. The database which has lighter load will use less number of resources. The database which is not in use, will not use any resources.

Provisioning resources for the entire pool rather than for single databases simplifies your management tasks. Plus, you have a predictable budget for the pool. 

Identify candidates for Elastic Pool

The elastic pool allows the multiple databases in single pool, to share the unused resources among them. So, very common way to check if elastic pool can be used will be:

• Analyze the peak resource usage and average resource usage of the database. If the difference in peak usage minus average usage is considerable, then it be good candidate for elastic pool
• The peak utilization of all the databases in single elastic pool happens at different time. It does not occur at same time.

After identifying the candidates using this analysis, you can also analyze if the elastic pool will result in cost saving. If it is also enabling you to save the cost, then it would be better to move to elastic pool.

How to create Elastic Pool ?

You will need azure subscription to follow the next steps in this article. If you don’t have an Azure subscription, create a free account.

Sign in to the Azure portal and click on “Create a resource” button. Search for Azure SQL and then click on create button.

On next screen, select SQL Databases card and in that dropdown, select elastic pool.

Once you can click on create button, the create panel will open. It will ask you below information:

• Subscription, the Azure subscription under which resource should be created
• Resource Group, for logically organizing the resources. You can either create a new resource group by clicking on Create new or you can select existing group from the dropdown.
• Elastic Pool Name, a name for the elastic pool
• Server, the database server (physical server) on which the pool will be created. You can either create a new database server by clicking on Create new, or you can select existing database server from the dropdown. This will determine the compute and storage assigned to the elastic pool.

If you select existing database server, then you need to note that you can use only databases which are on that database server. Or else, you will have to move databases from one server to other, which may need down time.

You can also go to next screen of wizard, which is for Tags. The tags are key value pairs, which is to annotate the Azure resource.

Then you can click on “Review + create” button to trigger creation of elastic pool. The elastic pool will be created instantly.

Alternative Way

If you have the database server already created, then you can directly create the elastic pool by clicking on Add Pool button from overview of database server.

I hope you enjoyed this article. Let me know your thoughts.