Get in touch with us!

Automated Azure SQL scaling

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?

MAXSIZEBasicStandardPremium
100 MB
500 MB
1 GB
2 GB√ (D)
5 GB 
10 GB 
20 GB 
30 GB 
40 GB 
50 GB 
100 GB 
150 GB 
200 GB 
250 GB √ (D)
300 GB  
400 GB  
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

1

Cold hours per day

23

P1 Price per year $ 5 475,00
P1 & S0 Price per year $ 397,704

1377%

P1 & Basic Price per year $ 284,372

1925%

How to set it up?

What we need

  • Azure SQL DB
  • Azure SQL Server Credentials
  • Azure Automation Account
  • Runbook script

Steps

  1. Create an Azure Automation Account in your Azure subscription by simple searching for Azure Automation in the marketplace
  2. Create a new runbook empty runbook
  3. Add this code to your runbook
    https://raw.githubusercontent.com/Altitude365/Altitude-Public/master/Tools/Azure%20SQL%20Resize/Azure%20SQL%20Resize.ps1
  4. Save and publish the runbook
  5. Go back to your Azure Automation account and create an PS Credential Asset

  6. *Remember the name of the asset. You’ll need it later on.
  7. Navigate back to your runbook and click Schedules.

  8. Create two schedules one for scaling up and one for scaling down.
  9. 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.

2 Comments

  1. Aran

    Can the script be modified to work with the new Premium RS tiers (PRS1-PRS6)?

    Reply
    • Jon Jander

      Hi Aran I have updated the script and added support for Premium RS.

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *