Azure SQL database and pool usage. When to use database pool

2
votes

Hello I have 14 Databases for Azure SQL with DTU SO, S1 and S4 (prod)

So I am paying for some unused or not frequently used databases. 10 databases for Dev and test. 2 for production. So I saw one post for Azure elastic pool. It was mentioned with Azure elastic pool. Can somebody suggest which kind database should I put in elastic pool and tips for cost saving.

Also I have Azure storage account (classic). How should I take its backup weekly. Is it possible.

Help and tips will be appreciated. Thanks

Regards

KP

2
azureazure-sql-databaseazure-sql-server
When / how to use elastic pool docs.microsoft.com/en-us/azure/sql-database/… – Nick.McDermaid
SQL Azure backups docs.microsoft.com/en-us/azure/sql-database/… – Nick.McDermaid
Please read these and a specific question if you don't understand something – Nick.McDermaid

2 Answers

0
votes

To keep it simple, Elastic pool will give you number of dtu's which can be used/distributed around number of databases you have as per their need.

So currently if you have 14 databases in S1 tier then you are have 14*50 =700 dtu's , if some databases are not in use, it's possible the dtu's are greatly underutilized.

In this case if You opt for Elastic pool with 50 dtu's then it will distribute among 14 databases , and as per need they will be used. which means you will save more and balance resources.

I have not verified all the numbers I have mentioned, but that's the principle idea.

0
votes

I will just add to others answers and comments. For backups take in consideration you have Azure automated backups that provides backups with 7-35 days of retention period. Additionally you can use Azure Long-Term Backup Retention which can store backups with a retention period of 10 years.

About choosing the correct pool size to save money one of the documents shared by Nick above states the following: "SQL Database automatically evaluates the historical resource usage of databases in an existing SQL Database server and recommends the appropriate pool configuration in the Azure portal. In addition to the recommendations, a built-in experience estimates the eDTU usage for a custom group of databases on the server. This enables you to do a "what-if" analysis by interactively adding databases to the pool and removing them to get resource usage analysis and sizing advice before committing your changes".

Additionally, "After adding databases to the pool, recommendations are dynamically generated based on the historical usage of the databases you have selected. These recommendations are shown in the eDTU and GB usage chart and in a recommendation banner at the top of the Configure pool page. These recommendations are intended to assist you in creating an elastic pool optimized for your specific databases".