Continuing on my journey up the Azure Automation mountain, I recently completed a simple AZ PowerShell script that takes several input parameters and scales UP or scales DOWN a given AzureSQL database instance depending on what time of day it is.
Before I go any further, if you are just getting started in Azure Automation, I wrote another article here which may help you avoid some of the headaches I ran into as part of my initial foray:
Azure Automation – Powershell – Getting it Working – Authentication the “Easy” Way -and- Ditching AzureRM
BACKGROUND
Azure has excellent auto-scaling parameters built-in for scaling App Services horizontally (i.e. “out” and “in”… adding or decreasing instance count). However for vertical scaling of AzureSQL services, you are pretty much left to your own devices.
I have an “okay” amount of experience using AZ Powershell to script and get things done in my Azure Subscriptions and with that in mind and the task set in front of me of auto-scaling our AzureSQL databases based on time of day (scale up before the busy hours, scale down when the quiet hours start) I turned to Azure Automation to get the work done.
Being an IT professional of several years, I did what we all do… I turned to Google and searched for scripts I could just steal, modify, and use (I am nodding at you right now…). However, if your want to use “AZ” Powershell instead of the deprecated “AzureRM”… I think the term is… “forgetaboutit.” The templates and examples that I found were either based on Powershell “Workflows” which I didn’t want to mess with and/or used the old AzureRM Powershell syntax. So I then turned to… Powershell ISE… and started writing.
After a bit of work, here is what I ended up with:
[Parameter (Mandatory = $true)]
[String] $DBname,
[Parameter (Mandatory = $true)]
[String] $ServerName,
[Parameter (Mandatory = $true)]
[String] $RGname,
[Parameter (Mandatory = $true)]
[String] $Region,
[Parameter (Mandatory = $true)]
[String] $UTCHourScaleUp,
[Parameter (Mandatory = $true)]
[String] $UTCHourScaleDown,
[Parameter (Mandatory = $true)]
[String] $DBsizeLarge,
[Parameter (Mandatory = $true)]
[String] $DBsizeSmall
)
Function Logon-AzureAutomationRunAsAccount {
Disable-AzContextAutosave -Scope Process
$connection = Get-AutomationConnection -Name AzureRunAsConnection
$logonAttempt = 0
while (!($connectionResult) -And ($logonAttempt -le 10)) {
$LogonAttempt++
# Logging in to Azure...
$connectionResult = Connect-AzAccount `
-ServicePrincipal `
-Tenant $connection.TenantID `
-ApplicationId $connection.ApplicationID `
-CertificateThumbprint $connection.CertificateThumbprint
Start-Sleep -Seconds 30
}
}
Function Scale-AzSQLDB ($DBname, $Servername, $RGname, $Region, $UTCHourScaleUp, $UTCHourScaleDown, $DBsizeLarge, $DBsizeSmall) {
$time = Get-Date
$DB = Get-AzSqlDatabase -DatabaseName $DBname -ServerName $ServerName -ResourceGroupName $RGname
$SkuTable = Get-AzSqlServerServiceObjective -Location $Region | where-object { $_.Enabled -eq "True" } | where-object { $_.Edition -eq $DB.Edition } | where-object { $_.SkuName -eq $DB.SkuName }
$CurrentSize = $SkuTable | where-object { $_.ServiceObjectiveName -eq $DB.CurrentServiceObjectiveName } | Select-Object -expandProperty Capacity
$TargetBIGSize = $SkuTable | where-object { $_.ServiceObjectiveName -eq $DBsizeLarge } | Select-Object -expandProperty Capacity
$TargetSMALLSize = $SkuTable | where-object { $_.ServiceObjectiveName -eq $DBsizeSmall } | Select-Object -expandProperty Capacity
If ($UTCHourScaleUp -eq $UTCHourScaleDown) {
Write-Output "Scale Up hour and Scale Down hour parameter values cannot match! Exiting"
Return
}
If ($TargetSMALLSize -ge $TargetBIGSize -or !$TargetSMALLSize -or !$TargetBIGSize) {
Write-Output "Ensure that DBSizeLARGE is a larger instance size than DBSizeSmall and that you are inputing values for these parameters that are within your current AzureSQL DB Edition (ex. S1, S2, HS_GEN5_4, HS_GEN5_12, etc.)"
Return
}
If ($time.hour -ne $UTCHourScaleUp -and $time.hour -ne $UTCHourScaleDown) {
Write-Output "Not time for a scaling operation, exiting"
Return
}
If ($time.hour -eq $UTCHourScaleUp -and $DB.RequestedServiceObjectiveName -eq $DB.CurrentServiceObjectiveName -and $TargetBIGSize -lt $CurrentSize) {
Write-Output "Requested Scale-UP size is less than the current database size, cancelling Scale-UP operation."
Return
}
If ($time.hour -eq $UTCHourScaleDown -and $DB.RequestedServiceObjectiveName -eq $DB.CurrentServiceObjectiveName -and $CurrentSize -lt $TargetSMALLSize) {
Write-Output "The current database size is less than the requested ScaleDown size, exiting Scale-Down operation."
Return
}
If ($time.hour -eq $UTCHourScaleUp -and $DB.RequestedServiceObjectiveName -eq $DB.CurrentServiceObjectiveName -and $TargetBIGSize -gt $CurrentSize) {
Set-AzSqlDatabase -DatabaseName $DBname -ServerName $ServerName -ResourceGroupName $RGname -RequestedServiceObjectiveName $DBsizeLarge
Write-Output "Scale up operation complete"
Return
}
If ($time.hour -eq $UTCHourScaleDown -and $DB.RequestedServiceObjectiveName -eq $DB.CurrentServiceObjectiveName -and $CurrentSize -gt $TargetSMALLSize) {
Set-AzSqlDatabase -DatabaseName $DBname -ServerName $ServerName -ResourceGroupName $RGname -RequestedServiceObjectiveName $DBsizeSmall
Write-Output "Scale down operation complete"
Return
}
Else {
Write-Output "Database is either currently scaling or already at the target size."
Return
}
}
Logon-AzureAutomationRunAsAccount
Scale-AzSQLDB $DBname $Servername $RGname $Region $UTCHourScaleUp $UTCHourScaleDown $DBsizeLarge $DBsizeSmall
Quick Walkthrough of the Script
First a side note… Not sure if it is “correct” but I have fallen into the habit of structuring my powershell scripts like so:
I find that for me at least, structuring my code this way keeps it clean, breaks it up into manageable chunks/units, and makes it much easier to maintain and enhance over time.
So with this script, several of the parameters are more-or-less self-explanatory however I will note some particulars… Because this is a scheduled run script that runs every hour and I need to get the date and time and then “do stuff” depending on the hour of the day, two of the parameters are for the “scale up” and “scale down” hour (in UTC 24-hour format 0 – 23). The two parameters below those are the database sizes being targeted for SCALE UP and SCALE DOWN at those given hours.
For a complete list of database sizes for a given edition of AzureSQL you can run something like this:
update: added a “where-object” filter so only enabled sizes for a given region are shown. Updated primary script as well.
Which outputs all of the given AzureSQL sizes in a given region which looks something like this:
-------------------- ------- ------- ------ -------- ------------ -------
S1 Standard Standard 20 DTU True
S2 Standard Standard 50 DTU True
S3 Standard Standard 100 DTU True
P6 Premium Premium 1000 DTU True
P11 Premium Premium 1750 DTU True
P15 Premium Premium 4000 DTU True
DW100c DataWarehouse DataWarehouse 900 DTU True
DS100 Stretch Stretch 750 DTU True
DS200 Stretch Stretch 1500 DTU True
DS300 Stretch Stretch 2250 DTU True
GP_Gen4_1 GP_Gen4 GeneralPurpose Gen4 1 VCores True
GP_Gen5_12 GP_Gen5 GeneralPurpose Gen5 12 VCores True
GP_S_Gen5_12 GP_S_Gen5 GeneralPurpose Gen5 12 VCores True
GP_Gen5_80 GP_Gen5 GeneralPurpose Gen5 80 VCores True
HS_Gen4_24 HS_Gen4 Hyperscale Gen4 24 VCores True
HS_Gen5_24 HS_Gen5 Hyperscale Gen5 24 VCores True
HS_Gen5_32 HS_Gen5 Hyperscale Gen5 32 VCores True
HS_Gen5_40 HS_Gen5 Hyperscale Gen5 40 VCores True
HS_Gen5_80 HS_Gen5 Hyperscale Gen5 80 VCores True
The “ServiceObjectiveName” column has the values that go in for the DBsizeLarge and DBsizeSmall parameters.
Beyond the parameters are two functions. One is for logging in under the Azure Automation Run-As account and is boiler-plate from Microsoft. I shared this in a previous article I mentioned at the outset if you need more details. The second function is the actual scaling function. I won’t explain it line by line but in short, there is logic built-in to hopefully protect from “stupid” things happening :).
Whilst stupidity is never lacking for imagination… here are the “oops, that was dumb” things I was able to conceive of and hopefully prevent from happening:
As for the basic logic… If the UTC hour matches the SCALE-UP hour AND the SCALE-UP size is LARGER-THAN the current size of the database, it will scale it up to the instance size specified by the parameters. Vice-versa for the SCALE-DOWN logic. The logic is setup such that the scheduled “SCALE UP” operations won’t actually scale down the database if it is already set to a larger size.
Once the script is saved as a runbook, we can then just schedule the runbook to fire-off hourly. Parameter values are input as part of linking the schedule to the runbook. It will run 24 times in a day on said hourly schedule and 22 of those times it will do nothing but print a message. But twice a day it will attempt to scale the instance to either the LARGE instance size or the SMALL instance size.
Anyhow, I wanted to post and share because there is a real absence of Powershell AZ scripts for Azure Automation usage. (or… maybe I was just being lazy and didn’t search long enough.)
This is great for basic scheduled scaling, the next thing I am working on is setting up Azure monitor to alert on database metrics (like DTU or CPU utilization) and then fire-off a runbook that will scale a database up or down by one instance size. This should allow for “granular” scale-up and scale-down during the intervening hours when this script isn’t doing anything.
As always, this is provided as-is, so TEST, TEST, and then TEST again before pushing to production!
I hope this is helpful!
Cheers!