Scaling an Azure SQL database using Azure Automation

With Azure SQL Database, you can easily change service tiers and performance levels dynamically without downtime.  Basic, Standard, and Premium are the 3 service tiers with multiple performance levels to handle different workloads.

Higher performance levels provide increasing resources designed to deliver increasingly higher throughput.

 Azure SQL database follows the hourly billing model for the resources assigned to a database.
So, if your business requires you to use a Premium service tier for high transactional workload for few business hours during the day only, it would not make sense to be paying for a premium tier for the nonbusiness hours when there are no users using the database.

To take advantage of the "Pay as go/use" you must upscale your database during Business hours and down scale database during off hours to optimize costs. However, this can become monotonous and tiresome. 

Don't Worry!! We have a solution for this. This can be achieved by Azure automation. Azure Automation brings a powerful, much needed PowerShell Workflow execution service to the Azure platform.

In this Blog, I will show you to how to achieve this by using Azure Automation. I am providing a Customer scenario where Customer wants to have his database at S2(50 DTU's) from 7A.M to 7 P.M and rest of the hours in Basic Edition (5 DTU's). Customer like to perform this activity daily.

 

Step 1: Create your Automation Account:

To do the Automation you should create an automation account. Add the details for the Automation account and proceed with the creation.

 

 

 

 

 

Step 2: Create a Runbook under the Automation account you created

Click on Add a Runbook Icon and create the run book with PowerShell work flow as run book type.

 

 

 

 

 

 

 

 

Step-3: Publish the Runbook

 

Copy the script from the below mentioned link and publish it in the runbook

https://gallery.technet.microsoft.com/scriptcenter/Azure-SQL-Database-e957354f

 

Step-4: Creation of credential for the runbook

create a credential with the server admin user and password like the one I mentioned in the below screenshot.

 

 

Step-5: Scheduling the run book

You can decide up on the time where you want to upscale and down scale your database. But you need to create separate run book for each of the options.

 

 

 

 

Step-6: Configure Parameter setting

Configure the parameter setting for the Runbook like the server name, database name, Edition, Perf Level and the credential

Edition: Basic, Standard, Premium
Perf Level: Basic, S0, S1, S2, P1, P2, P3

 

In my case I am downscaling my database from S1 to S0

 

 

 

 

 

 

 

 

 

 

 

 

 

 

It Successfully downgraded to S0. By this time, you would have figure out how easy is to auto scale your database by Azure Automation.

Hope this help!! J