I want to show you how I went from having multiple single SQL databases in Azure to a database elastic pool within a new dedicated SQL Server. Once setup I create and use elastic jobs. This post is long but I am sure you will find it useful.
I sometimes forget that Microsoft watches out for us by proactively looking at the behaviour of our SQL databases in Azure.
SQL database has built-in intelligence and with that intelligence it may recommend an elastic pool setup if it’s more cost-efficient for you based on the past usage telemetry of your databases within a server.
If your environment will benefit from using a pool then you will clearly be told this fact.
Just to recap, an elastic database pool provides elastic database transaction units (eDTUs) and storage (GBs) that are shared by multiple databases. Read more here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool-create-portal or https://blobeater.blog/2016/12/02/i-use-elastic-database-pools-for-now/
Within the overview section of your SQL Server of interest you may notice the following message:
All you need to do is click it to view the details.
The recommendation is to setup a standard 50 EDTU pool. I am convinced that this pool is a new pricing tier. Even though the cost saving is small it is still clever that it suggests this. I assume the analysis done in the background really does understand my utilization patterns as we know that the patterns are absolutely crucial for when using elastic pools so it is something to definitely consider.
Within a click of a button the portal will create it for you.
All you need to do it hit that OK button.
As mentioned in previous blog posts, I love the power available to you at your finger tips when using the Azure portal.
When you have many SQL databases that are required to run your environments and they show signs of specific usage patterns and some of the databases requires different performance levels then how can you ensure that the all the databases get the performance resource that they need within a specified budget?
Elastic database pools could be the answer because databases in an elastic pool consume but they do not exceed the limits of the pool, so both your cost and performance levels remain predictable.
As shown by the screen shot below there are three tiers to select from. They are basic, standard and premium tiers. Each tier imposes limits on the pool and the individual databases within the pool.
This is probably the right time to discuss the meaning behind some key terms.
- DTU Stands for Database Transaction Unit. As quoted from https://azure.microsoft.com/en-gb/documentation/articles/sql-database-service-tiers/#understanding-dtus this is “a unit of measure in SQL Database that represents the relative power of databases based on a real-world measure: the database transaction”, the higher the DTU means higher the throughput capability.
- eDTU Elastic Database Transaction Unit has a similar concept to the above but applies to elastic database pools only. From looking at the above diagram you may notice this unit is used at the pool level (eDTUs / pool) and database level (min/max eDTUs).
- eDTU MIN (database level setting). The minimum number of eDTUs from the pool that a database in the pool is guaranteed at any time.
- eDTU MAX (database level setting). The maximum number of eDTUs that any single database in the pool may use – this is NOT a resource guarantee but a ceiling limit.
- POOL eDTU- The Max eDTUs available and shared by all databases within the pool.
Elastic pools work for me because of the way I am using them. I use them to build environments. For example, I need to build 11 environments – each needs just 1 SQL database meaning I need 11 databases; I could go for the 11 single approach or consider using 1 pool with 11 databases within it.
This is the cost for standard single databases.
Cost for Standard elastic pool.
Based on the environment requirements and the amount of work going through it and testing I decided that 2 key environments will need to be S2 edition and the other 9 will be S0.
The databases are not going to be big; we are talking about 6.5GB per database which means 71.5GB worth of disk space.
With these facts in mind let’s crunch some numbers.
Based on https://azure.microsoft.com/en-gb/pricing/details/sql-database/ and using GBP this is the cost comparison of the elastic pool versus purchasing individual SQL databases.
Standard 100 elastic pool = £136/month = £1632/YEAR
Single database cost for 9 S0 databases and 2 S2 databases = (9 x £9.1811) + (2 x £45.81) = £82.63 + £91.62 = £174.52/month = £2094/YEAR
Potentially the more databases you can add to a pool the greater your savings (assuming that you are still within you selected pool‘s limit), but you will need to work out yourself whether or not elastic pools makes financial sense for you.
Database utilisation patterns
The key to using elastic database pools is that you must understand the characteristics of the databases involved and their utilisation patterns, if you do not understand this then the idea of using an elastic database pool may cause problems.
The maximum amount my pool has is 100 eDTUs, I know for a fact that the S2 databases will not be used at the same time, the other S0 databases might be used at the same time at the most 3 of them at the same time. Basically what I am saying here is that I know that when the databases concurrently peak I know that it will not go beyond the 100 eDTU limit.
If for example my S2 database and 8 S0 databases peak at the same time then I will go well beyond my limits – but I know this won’t happen.
Another important consideration is the maximum storage allowed for a Standard 100 pool, this is currently set to 100GB and my solution is well within that limit. If the databases do grow then I would need to upgrade to standard 200 pool which will cost me £272 / month. So at the moment it makes financial sense for me.