Auto scale Azure SQL Database

         Auto scale Azure SQL Database with the help of Monitoring and Automation. Before getting into the auto scale let’s understand that how scaling works in Azure SQL.

How scaling works in Azure SQL?

          There are 2 type in scaling and that would be decided by Azure.

Type 1:

• The new compute instance will be created with the specified size.
•  A Replica of that database will be created in the above space, here latency is based on the size of the data base i.e. maximum of 1 GB /minute for coping. During this time the connections will be handled by the previous instance.
• Then all the connections will be redirected to the new instance.

Type 2:

• The new compute instance will be created with the specified size.
• The database will be detached and attached to the above.
• This cause the database unavailability for the maximum of 30 seconds.

Enable Auto Scale

Here I took S0 tier DB as an example and scale up to S2 and scale down to S0 based on DTU.

Azure Automation

         Here we create 2 runbooks in azure automation for scale up and down

1. Search for azure automation
• Name : AutomateSQLScaling
• Subscription, resource group , location same as SQL DB
• Create Azure Run As account  : Yes
• Click create
2. Click on runbooks on the left navigation
3. Create a runbook for scale up
• Name : ScaleSQLToS2
• Runbook type         : PowerShell
• Description : PS SQL Scale Up script
• Click create 
4. Once created 
• Update the script with your values and use  : Set-AzureRmSqlDatabase -ResourceGroupName "datamanagement" -DatabaseName "scaledb" -ServerName "collabspaceinteg" -Edition "Standard" -RequestedServiceObjectiveName "S2"
5. Repeat step 3 and paste the below code for scale down
• Set-AzureRmSqlDatabase -ResourceGroupName "ResourceGroup01" -DatabaseName "Database01" -ServerName "Server01" -Edition "Standard" -RequestedServiceObjectiveName "S0"

Monitoring Alert

1. Select the SQL DB for which you want to configure the auto scale
2. Click on alerts on the left navigation
3. Create new alert rule 
• Select condition
• Select signal DTU percentage, drag down
• Alert logic 
• Threshold : Static
• Operator         : Greater than 
• Aggregation Type : Average
• Threshold percentage : 60 
• Aggregation         : 5 minutes
• Frequency of evaluation : 5 minutes
• Click done
• Select action group
• Create action group
• Name : Auto scale up Azure SQL
• Short Name : AutoScaleUpAzureSQL
• Resource group : <resource group of your db>
• Action Name : ScaleUpAZAutoRunbook
• Action Type : Automation Runbook 
• Under runbook
• Run book : enabled
• Source : user
• Select subscription
• Select the run book we created before for scale up “ScaleSQLToS2”
• File alert rule name, description, Severity and Enable : true
• Click create alert rule
4. Repeat step 4 with below changes
• Select condition
• Alert logic 
• Operator         : Less than
• Threshold percentage : 25 
• Aggregation         : 1 hour
• Frequency of evaluation : 15 minutes
• Click done
• Select action group 
• Create action group
• Name : Auto scale down Azure SQL
• Short Name : AutoScaleDownAzureSQL
• Resource group : <resource group of your db>
• Action Name : ScaleDownAZAutoRunbook
• Action Type : Automation Runbook 
• Under runbook
• Select the run book we create before for scale down “ScaleSQLToS0”

I will update the generic power shell script here for scale up and down in a week based on the pricing tiers available now.