Azure SQL Database scheduled autoscaling

Posted by

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, Premium, and Premium RS. Databases are isolated from each other and portable. You can change between tiers easily and without downtime.
Azure SQL database tiers and performance levels


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 september 2017).

Azure Automation Runbook Architecture

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 or Premium RS database that contains clustered columnstore indexes to Standard or Basic, the tables will be inaccessible until you scale back to Premium (RS) 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 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 wil be used outside the slots
specified in the scalingSchedule paramater value.
Example values: Basic, Standard, PremiumRS, Premium.
For more information on editions/tiers levels,
http://msdn.microsoft.com/en-us/library/azure/dn741336.aspx

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

.EXAMPLE
-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: Oct 2017

param(
[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
Add-AzureRmAccount -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,"HH:mm:ss", [System.Globalization.CultureInfo]::InvariantCulture)}}, `
@{Name="StopTime"; Expression = {[datetime]::ParseExact($_.StopTime,"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.

You need a recent version of the AzureRM.Sql PowerShell module to be able to switch to the PremiumRS edition. This module is imported automatically when you create a new Azure Automation account. When you have an existing account you might run into the following error:
Cannot bind parameter ‘Edition’. Cannot convert value “Premium RS” to type “Microsoft.Azure.Commands.Sql.Database.Model.DatabaseEdition”. Error: “Unable to match the identifier name Premium RS to a valid enumerator name. Specify one of the following enumerator names and try again: None, Premium, Basic, Standard, DataWarehouse, Free”
You can solve this by updating your modules. Navigate to your Azure Automation account, choose “Modules” and click on “Update Azure Modules”. The version in my screenshot (1.0.3) is not sufficient, when you are on version 3.3.1 or higher you are good to go.
Update Azure Modules

After updating my version is 3.4.0. Please note: Azure modules have been updated; for runbooks that use these modules and have a linked schedule you will need to unlink and re-link the schedule so that the updated modules will be used by the runbook.
Azure Modules Updated

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.
Runbooks Gallery

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”.
Edit Runbook

Now click “Publish”.
Publish Runbook

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”.
Schedule Runbook

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”.
Recurring Schedule Runbook

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

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

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.
Job Output

5 comments

  1. Jorge,

    Great post, but I found out that the Price Tier Premium RS is not working. You should use the Edition NONE. Very strange, but true. Maybe it has something todo with the Public Preview
    See below the output of the script
    Current DB status: Online, edition: None, tier: PRS1

    Liked by 1 person

    1. Hi Erwin (and Jorg),

      For Premium RS edition you can use the parameter value “PremiumRS”.
      Tiers can have values “PRS1” until “PRS6”.

      Cheers,
      Dave

      Liked by 1 person

      1. Thanks Dave! If you still run into the error you need to update your Azure Automation Modules (AzureRM.Sql). I have added this to the step-by-step instructions.

        Like

  2. Hi Dave. I thinlk that the hourly scheduling is not accurate. Maybe yo can schudule Once and while waiting for time to scaling.

    while($true) {
    ######## YOUR PROCESS
    $d = Get-Date
    Write-Output $d
    Start-Sleep -Milliseconds 60000
    }

    Like

    1. Hi Esteban, it can take a minute before the script actually starts when the runbook is scheduled, so it’s not accurate on the second. But keep in mind it can (in theory) take hours before a scaling operation finishes, so why worry about the runbook schedule accuracy? Or do you mean something else?

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s