Single Database vs Elastic Pool

This is a part of a Series “Stairway to being an Azure SQL DBA“, in which I am planning to cover everything related to working as an Azure SQL DBA.

As we saw in the last blog DTU vs vCore there are different costing options available for PaaS SQL Servers, and the number of resources allocated depends upon the Model and Service Tier that you have chosen.

We always have databases with different types of usage, and when creating a SQL Database we have to allocate the resources for it and so the costing is pre-decided and many times lead to waste of resources and money.

Assuming 5 different database that peaks to 40 DTUs for once a day for around 30 minutes while it is being used but generally uses only 5-10 DTUs which is used for loading data into it.

In this case we are having underutilized system where 90-95% of time the dedicated resources are not even being used, the cost for such a database is ~$400 for a month. (https://azure.com/e/4e2069169ef5453abf69795675ffe5f9)

In actual Azure environments we do have multiple databases that are being utilized at different times of the day but are billed for the peaks. For such environments to reduce the billing cost Azure SQL Database has the option of creating a Pool of resources, and then associating databases with this pool of resources and Azure SQL Database can have the resources from this pool as and when they require it. This pool of resources is called Elastic Pool.

The costing and Service Tiers are similar in terms of there names but differ with the minimum and maximum resources. For example a Basic single Azure SQL Database can have only 5 DTUs and maximum 2 GB size but a Basic Elastic Pool can have upto 1600 DTUs and maximum 156 GB size.

This is for Single Azure SQL Database

When creating elastic pool it looks like below.

This is for Elastic Pool

Under “Databases” you can add all the databases you want to be part of the pool.

Resources:

https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview#creating-a-new-sql-database-elastic-pool-using-the-azure-portal