Azure SQL Database scheduled autoscaling

One of the major benefits of Azure is scalability, the cloud has been built to be elastic to be as cost-effective as possible. Azure solutions can be scaled in ways that would never be possible with your own hardware. You are able to scale up and out when the demand arises, and, more importantly, you can scale down and in when you don’t need all the resources anymore, thereby saving a lot of money.

Autoscaling based on a schedule allows you to scale your solution according to predictable resource demand. For example you could require the highest capacity on Monday during peak hours, while the rest of the week the traffic is decreased allowing you to scale down. Outside business hours and during weekends you could then scale down further to the absolute minimum.

Azure SQL Database currently offers four service tiers: Basic, Standard and Premium. Databases are isolated from each other and portable. You can change between tiers easily and without downtime.

Scheduled autoscaling with Azure Automation

To automate scaling of Azure SQL Databases based on a schedule I have created an Azure Automation PowerShell Runbook called AzureSQLDatabaseScheduledAutoScaling. You can easily import this runbook into your Azure Automation account and then execute it against your database by entering values for a few parameters that specify your database details, your Automaton Run As Account and a database-specific scaling schedule. A step-by-step guide can be found at the bottom of this post.

Scheduling of the runbook itself can be done by creating a recurring hourly Automation Schedule. A single runbook can have multiple jobs running at one time. This means it is possible to create multiple schedules for the AzureSQLDatabaseScheduledAutoScaling runbook, enabling scaling of multiple databases simultaneously.

Scaling of the database will only be performed in case the runbook execution moment matches a day/time slot specified in the scaling schedule parameter. If the runbook executes and the database is in the desired edition/tier already, no work will be done. Don’t worry about the costs of Azure Automation, the first 500 run minutes are free and after that you pay €0.002 / minute (as per November 2018).

When you start using this solution, keep the following in mind:

• The length of time for a switch of edition/tier varies, but is generally under 4 seconds, and under 30 seconds in 99% of the time. The duration of the entire scale process depends on both the size and service tier of the database before and after the change. For example a 250 GB database that is changing to, from, or within a Standard service tier, should complete within 6 hours. For a database of the same size that is changing performance levels within the Premium service tier, it should complete within 3 hours.
• Behind the scenes, changing the edition/tier of a database creates a replica of the original database at the new edition/tier, and then switches connections over to the replica. No data is lost during this process. During the switch over to the replica, connections to the database are disabled, so some transactions in flight may be rolled back. If there are large numbers of transactions in flight at the moment connections are disabled, the length of time for the switch over may be longer.
• To scale a database down, the database must be smaller than the maximum allowed size of the target service tier.
• Objects that are not supported in target tiers can become inaccessible. For example when you scale down a Premium database that contains clustered columnstore indexes to Standard or Basic, the tables will be inaccessible until you scale back to Premium again.

The Runbook PowerShell script

The PowerShell script below can be used in an Azure Automation Runbook with type “PowerShell”.

The script contains the following logic:

1. Authenticate with Azure Automation Run As account.
2. Get the current day of week and current time, and convert it to the time zone that is specified.
3. Check which of the specified ‘slots’ in the scaling schedule is currently active.
4. Scale database to edition/tier that is specified in the scaling schedule.
   If no schedule for the current day/time is found, scale to default edition/tier.
   If database is already in target edition/tier, do nothing.

.SYNOPSIS
Vertically scale an Azure SQL Database up or down according to a
schedule using Azure Automation.

.DESCRIPTION
This Azure Automation runbook enables vertically scaling of
an Azure SQL Database according to a schedule. Autoscaling based
on a schedule allows you to scale your solution according to
predictable resource demand. For example you could require a
high capacity (e.g. P2) on Monday during peak hours, while the rest
of the week the traffic is decreased allowing you to scale down
(e.g. P1). Outside business hours and during weekends you could then
scale down further to a minimum (e.g. S0). This runbook
can be scheduled to run hourly. The code checks the
scalingSchedule parameter to decide if scaling needs to be
executed, or if the database is in the desired state already and
no work needs to be done. The script is Timezone aware.

.PARAMETER environmentName
Name of Azure Cloud environment. Default is AzureCloud, only change
when on Azure Government Cloud, for example AzureUSGovernment.

.PARAMETER resourceGroupName
Name of the resource group to which the database server is
assigned.

.PARAMETER azureRunAsConnectionName
Azure Automation Run As account name. Needs to be able to access
the $serverName.

.PARAMETER serverName
Azure SQL Database server name.

.PARAMETER databaseName
Azure SQL Database name (case sensitive).

.PARAMETER scalingSchedule
Database Scaling Schedule. It is possible to enter multiple
comma separated schedules: [{},{}]
Weekdays start at 0 (sunday) and end at 6 (saturday).
If the script is executed outside the scaling schedule time slots
that you defined, the defaut edition/tier (see below) will be
configured.

.PARAMETER scalingScheduleTimeZone
Time Zone of time slots in $scalingSchedule.
Available time zones: [System.TimeZoneInfo]::GetSystemTimeZones().

.PARAMETER defaultEdition
Azure SQL Database Edition that will be used outside the slots
specified in the scalingSchedule paramater value.
Available values: Basic, Standard, Premium.

.PARAMETER defaultTier
Azure SQL Database Tier that will be used outside the slots
specified in the scalingSchedule paramater value.
Example values: Basic, S0, S1, S2, S3, P1, P2, P4, P6, P11, P15.

.EXAMPLE
-environmentName AzureCloud
-resourceGroupName myResourceGroup
-azureRunAsConnectionName AzureRunAsConnection
-serverName myserver
-databaseName myDatabase
-scalingSchedule [{WeekDays:[1], StartTime:”06:59:59″, StopTime:”17:59:59″, Edition: “Premium”, Tier: “P2″}, {WeekDays:[2,3,4,5], StartTime:”06:59:59″, StopTime:”17:59:59”, Edition: “Premium”, Tier: “P1”}]
-scalingScheduleTimeZone W. Europe Standard Time
-defaultEdition Standard
-defaultTier S0

.NOTES
Author: Jorg Klein
Last Update: Nov 2018

param(
[parameter(Mandatory=$false)]
[string] $environmentName = "AzureCloud",   

[parameter(Mandatory=$true)]
[string] $resourceGroupName,

[parameter(Mandatory=$false)]
[string] $azureRunAsConnectionName = "AzureRunAsConnection",

[parameter(Mandatory=$true)]
[string] $serverName,

[parameter(Mandatory=$true)]
[string] $databaseName,

[parameter(Mandatory=$true)]
[string] $scalingSchedule,

[parameter(Mandatory=$false)]
[string] $scalingScheduleTimeZone = "W. Europe Standard Time",

[parameter(Mandatory=$false)]
[string] $defaultEdition = "Standard",

[parameter(Mandatory=$false)]
[string] $defaultTier = "S0"
)

filter timestamp {"[$(Get-Date -Format G)]: $_"}

Write-Output "Script started." | timestamp

$VerbosePreference = "Continue"
$ErrorActionPreference = "Stop"

#Authenticate with Azure Automation Run As account (service principal)
$runAsConnectionProfile = Get-AutomationConnection -Name $azureRunAsConnectionName
$environment = Get-AzureRmEnvironment -Name $environmentName
Add-AzureRmAccount -Environment $environment -ServicePrincipal `
-TenantId $runAsConnectionProfile.TenantId `
-ApplicationId $runAsConnectionProfile.ApplicationId `
-CertificateThumbprint ` $runAsConnectionProfile.CertificateThumbprint | Out-Null
Write-Output "Authenticated with Automation Run As Account."  | timestamp

#Get current date/time and convert to $scalingScheduleTimeZone
$stateConfig = $scalingSchedule | ConvertFrom-Json
$startTime = Get-Date
Write-Output "Azure Automation local time: $startTime." | timestamp
$toTimeZone = [System.TimeZoneInfo]::FindSystemTimeZoneById($scalingScheduleTimeZone)
Write-Output "Time zone to convert to: $toTimeZone." | timestamp
$newTime = [System.TimeZoneInfo]::ConvertTime($startTime, $toTimeZone)
Write-Output "Converted time: $newTime." | timestamp
$startTime = $newTime

#Get current day of week, based on converted start time
$currentDayOfWeek = [Int]($startTime).DayOfWeek
Write-Output "Current day of week: $currentDayOfWeek." | timestamp

# Get the scaling schedule for the current day of week
$dayObjects = $stateConfig | Where-Object {$_.WeekDays -contains $currentDayOfWeek } `
|Select-Object Edition, Tier, `
@{Name="StartTime"; Expression = {[datetime]::ParseExact(($startTime.ToString("yyyy:MM:dd")+”:”+$_.StartTime),"yyyy:MM:dd:HH:mm:ss", [System.Globalization.CultureInfo]::InvariantCulture)}}, `
@{Name="StopTime"; Expression = {[datetime]::ParseExact(($startTime.ToString("yyyy:MM:dd")+”:”+$_.StopTime),"yyyy:MM:dd:HH:mm:ss", [System.Globalization.CultureInfo]::InvariantCulture)}}

# Get the database object
$sqlDB = Get-AzureRmSqlDatabase `
-ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-DatabaseName $databaseName
Write-Output "DB name: $($sqlDB.DatabaseName)" | timestamp
Write-Output "Current DB status: $($sqlDB.Status), edition: $($sqlDB.Edition), tier: $($sqlDB.CurrentServiceObjectiveName)" | timestamp

if($dayObjects -ne $null) { # Scaling schedule found for this day
    # Get the scaling schedule for the current time. If there is more than one available, pick the first
    $matchingObject = $dayObjects | Where-Object { ($startTime -ge $_.StartTime) -and ($startTime -lt $_.StopTime) } | Select-Object -First 1
    if($matchingObject -ne $null)
    {
        Write-Output "Scaling schedule found. Check if current edition/tier is matching..." | timestamp
        if($sqlDB.CurrentServiceObjectiveName -ne $matchingObject.Tier -or $sqlDB.Edition -ne $matchingObject.Edition)
        {
            Write-Output "DB is not in the edition and/or tier of the scaling schedule. Changing!" | timestamp
            $sqlDB | Set-AzureRmSqlDatabase -Edition $matchingObject.Edition -RequestedServiceObjectiveName $matchingObject.Tier | out-null
            Write-Output "Change to edition/tier as specified in scaling schedule initiated..." | timestamp
            $sqlDB = Get-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName
            Write-Output "Current DB status: $($sqlDB.Status), edition: $($sqlDB.Edition), tier: $($sqlDB.CurrentServiceObjectiveName)" | timestamp
        }
        else
        {
            Write-Output "Current DB tier and edition matches the scaling schedule already. Exiting..." | timestamp
        }
    }
    else { # Scaling schedule not found for current time
        Write-Output "No matching scaling schedule time slot for this time found. Check if current edition/tier matches the default..." | timestamp
        if($sqlDB.CurrentServiceObjectiveName -ne $defaultTier -or $sqlDB.Edition -ne $defaultEdition)
        {
            Write-Output "DB is not in the default edition and/or tier. Changing!" | timestamp
            $sqlDB | Set-AzureRmSqlDatabase -Edition $defaultEdition -RequestedServiceObjectiveName $defaultTier | out-null
            Write-Output "Change to default edition/tier initiated." | timestamp
            $sqlDB = Get-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName
            Write-Output "Current DB status: $($sqlDB.Status), edition: $($sqlDB.Edition), tier: $($sqlDB.CurrentServiceObjectiveName)" | timestamp
        }
        else
        {
            Write-Output "Current DB tier and edition matches the default already. Exiting..." | timestamp
        }
    }
}
else # Scaling schedule not found for this day
{
    Write-Output "No matching scaling schedule for this day found. Check if current edition/tier matches the default..." | timestamp
    if($sqlDB.CurrentServiceObjectiveName -ne $defaultTier -or $sqlDB.Edition -ne $defaultEdition)
    {
        Write-Output "DB is not in the default edition and/or tier. Changing!" | timestamp
        $sqlDB | Set-AzureRmSqlDatabase -Edition $defaultEdition -RequestedServiceObjectiveName $defaultTier | out-null
        Write-Output "Change to default edition/tier initiated." | timestamp
        $sqlDB = Get-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName
        Write-Output "Current DB status: $($sqlDB.Status), edition: $($sqlDB.Edition), tier: $($sqlDB.CurrentServiceObjectiveName)" | timestamp
    }
    else
    {
        Write-Output "Current DB tier and edition matches the default already. Exiting..." | timestamp
    }
}

Write-Output "Script finished." | timestamp

Step-by-step setup guide

Navigate to the Azure portal and create an Azure Automation account in the same Azure Subscription as where the Azure SQL Database that you want to scale resides. Make sure the Run As account has been created. When you create a new Automation account in the Azure portal, it automatically creates a new service principal(Run As account) and assigns it the Contributor role-based access control (RBAC) role in the subscription by default. These rights are sufficient to scale Azure SQL Databases.

I have uploaded my PowerShell script to TechNet Script Center so you can easily import it as Runbook. Navigate to your Automation Account and click the “Runbooks Gallery” menu item. Make sure you filter the Gallery Source on “Script Center” and search for “Azure SQL Database – Scale using scheduled autoscaling”. Open the item from the gallery and click Import.

Before you can use the runbook you have to publish it first. Click on “Overview” if you are not already on that page and then click “Edit”.

Now click “Publish”.

The runbook is now ready to use. Next step is to schedule it to run hourly, of course with your desired parameters. Click “Schedules” and then “Add a schedule”.

Give your schedule a name, set the start time on a whole hour, the Recurrence on “Recurring”, Recur every “1 Hour” and Set expiration to “No”.

Now configure the parameters. Explanation can be found under “The Runbook PowerShell script” earlier in this article.

When you now navigate to “Jobs” you get an overview of all executions.

When you open a job and look at the Output, you will see all details of that particular run. For example, I can see my database was scaled up from S0 to S1 on Friday at 07:00.