Yet another SQL Server enthusiast

2017-12-20

Azure SQL DB – Elastic pools

Filed under: Uncategorized — sa @ 1:57 PM

Problem

Working with Azure SQL DBs requires to allocate resources for databases. The unit measure for resources is DTU (link) and it takes into account: CPU, memory, IO reads and IO writes. Let’s assume that we have to manage 3 Azure SQL DBs having following service tiers associated (link with all STs):
DB1 – S1 (20 DTU)
DB2 – S1 (20 DTU)
DB3 – S2 (50 DTU)
Scenario: we assume that these database are used as follow:
⦁ In the morning (4 hours): DB1/DB2 need 15 x 2 DTU, DB3 needs 2 DTU
⦁ Afternoon (4 hours): DB1/DB3 need 2 x 2 DTU while DB3 needs 45 DTU
If the resources of these databases are manage separately then we have to provision 90 DTU x 8 hours.
The question that raises from this scenario is: how we can manage the scalability of these databases in a costly effective manner ?

Solution

The answer is to group these databases into a elastic pool which does allow resource sharing. In this case, we could create a single elastic pool with 50 e[elastic]DTU (service tier S1) and we could include these three databases in this elastic pool. In this case:
⦁ In the morning (4 hours): DB1/DB2 need 15 x 2 eDTU + DB3 needs 2 eDTU = 32 eDTU (resources needed for all databases included into elastic pool)
⦁ Afternoon (4 hours): DB1/DB3 need 2 x 2 eDTU + DB3 needs 45 eDTU = 49 eDTU (resources needed for all databases included into elastic pool)
The conclusion of this section is that instead of provisiong 90 DTU x 8 hours ($0.0404/hour x 8 hours + 0.0404/hour x 8 hours + $0.1009/hour x 8 hours = $1.4536), because of elastic pools we have to provision only 50 eDTU x 8 hours ($0.1511/hour x 8 = $1.2088). In terms of money, maybe the difference is not that big for one day but we could extrapolate this computation to one month/year, to many databases, to superior service tiers and the difference will be higher.

How

SQL elastic pools > Add

Azure.SQLDB.Elastic.Pool.Add

 

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: