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.
jorgklein.com for more information and a step-by-step setup guide
<#
.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, Premium RS, 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: 18/09/2017
#>
param(
[parameter(Mandatory=$true)]
[string] $resourceGroupName,
[parameter(Mandatory=$true)]
[string] $azureRunAsConnectionName,
[parameter(Mandatory=$true)]
[string] $serverName,
[parameter(Mandatory=$true)]
[string] $databaseName,
[parameter(Mandatory=$true)]
[string] $scalingSchedule,
[parameter(Mandatory=$true)]
[string] $scalingScheduleTimeZone,
[parameter(Mandatory=$true)]
[string] $defaultEdition,
[parameter(Mandatory=$true)]
[string] $defaultTier
)
Write-Output 'Script started.'
#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.'
#Get current date/time and convert to $scalingScheduleTimeZone
$stateConfig = $scalingSchedule | ConvertFrom-Json
$startTime = Get-Date
Write-Output 'Azure Automation local time: $startTime.'
$toTimeZone = [System.TimeZoneInfo]::FindSystemTimeZoneById($scalingScheduleTimeZone)
Write-Output 'Time zone to convert to: $toTimeZone.'
$newTime = [System.TimeZoneInfo]::ConvertTime($startTime, $toTimeZone)
Write-Output 'Converted time: $newTime.'
$startTime = $newTime
#Get current day of week, based on converted start time
$currentDayOfWeek = Int.DayOfWeek
Write-Output 'Current day of week: $currentDayOfWeek.'
$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)}}
$sqlDB = Get-AzureRmSqlDatabase `
-ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-DatabaseName $databaseName
Write-Output 'DB name: $($sqlDB.DatabaseName)'
Write-Output 'Current DB status: $(
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 { (
if($matchingObject -ne $null)
{
Write-Output 'Scaling schedule found. Check if current edition & tier is matching...'
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!'
$sqlDB | Set-AzureRmSqlDatabase -Edition $matchingObject.Edition -RequestedServiceObjectiveName $matchingObject.Tier | out-null
Write-Output 'Change to edition/tier as specified in scaling schedule initiated...'
$sqlDB = Get-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName
Write-Output 'Current DB status: $(
}
else
{
Write-Output 'Current DB tier and edition matches the scaling schedule already. Exiting...'
}
}
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...'
if($sqlDB.CurrentServiceObjectiveName -ne $defaultTier -or $sqlDB.Edition -ne $defaultEdition)
{
Write-Output 'DB is not in the default edition and/or tier. Changing!'
$sqlDB | Set-AzureRmSqlDatabase -Edition $defaultEdition -RequestedServiceObjectiveName $defaultTier | out-null
Write-Output 'Change to default edition/tier initiated.'
$sqlDB = Get-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName
Write-Output 'Current DB status: $(
}
else
{
Write-Output 'Current DB tier and edition matches the default already. Exiting...'
}
}
}
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...'
if($sqlDB.CurrentServiceObjectiveName -ne $defaultTier -or $sqlDB.Edition -ne $defaultEdition)
{
Write-Output 'DB is not in the default edition and/or tier. Changing!'
$sqlDB | Set-AzureRmSqlDatabase -Edition $defaultEdition -RequestedServiceObjectiveName $defaultTier | out-null
Write-Output 'Change to default edition/tier initiated.'
$sqlDB = Get-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName
Write-Output 'Current DB status: $(
}
else
{
Write-Output 'Current DB tier and edition matches the default already. Exiting...'
}
}
Write-Output 'Script finished.'
TechNet gallery is retiring! This script was migrated from TechNet script center to GitHub by Microsoft Azure Automation product group. All the Script Center fields like Rating, RatingCount and DownloadCount have been carried over to Github as-is for the migrated scripts only. Note : The Script Center fields will not be applicable for the new repositories created in Github & hence those fields will not show up for new Github repositories.