This post tells you how to optimize you Azure SQL performance to make quick ROI.
Perhaps you have deployed an Azure SQL DB and had some performance issues, so that you were forced to select a premium tier to get the right experience.
But your system only uses this power once or twice a day, at a predictive pattern. If this is the case you need to continue reading this post because it will save you a lot of money.
As you can see in the example, our DB is just using 40% once every day somewhere around 02pm, so we need to crank the performance up before the peak and then bring it back down after the job is done.
What is the right service tier for you?
|2 GB||√ (D)||√||√|
|250 GB||√ (D)||√|
|500 GB||√ (D)|
As you can see in the table above, we can change from P1 as long as the DB is under or 250GB and if we have a DB equal or less than 2GB we can go even lower.
Let’s make a quick calculation, what will the Annual Total be if only pay for what we need when we need it?
|P1 Price per hour||$ 0,625|
|S0 Price per hour||$ 0,020|
|Basic Price per hour||$ 0,007|
|Active hours per day||
|Cold hours per day||
|P1 Price per year||$ 5 475,00|
|P1 & S0 Price per year||$ 397,704||
|P1 & Basic Price per year||$ 284,372||
How to set it up?
What we need
- Azure SQL DB
- Azure SQL Server Credentials
- Azure Automation Account
- Runbook script
- Create an Azure Automation Account in your Azure subscription by simple searching for Azure Automation in the marketplace
- Create a new runbook empty runbook
- Add this code to your runbook
- Save and publish the runbook
- Go back to your Azure Automation account and create an PS Credential Asset
*Remember the name of the asset. You’ll need it later on.
- Navigate back to your runbook and click Schedules.
Create two schedules one for scaling up and one for scaling down.
You are done!
A couple of things to remember. The DB Editions differs in other things like Point-in-time-restore so this might not always be the best solution. If you have multiple Azure SQL databases an elastic pool is a far better option.