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:

param (
    [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



Powershell Runbook Editing


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:

  • Parameters Block
  • Function Block(s)
  • Run the Functions


  • 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:

    Get-AzSqlServerServiceObjective -Location EastUS2 | where-object {$_.Enabled -eq "True"}

    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:

    ServiceObjectiveName SkuName       Edition          Family Capacity CapacityUnit Enabled
    -------------------- -------       -------          ------ -------- ------------ -------
    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:

  • Setting the ScaleUP and ScaleDOWN times to the same value
  • Setting the target ScaleUP size to a SMALLER value than the target ScaleDOWN size
  • Typos in the target ScaleUP/ScaleDown size parameters
  • Selecting target ScaleUP/ScaleDown sizes that don’t exist and/or don’t match the database edition you are performing scale operations against. (i.e. trying to set an Standard DTU DB to a Hyper-Scale 4vCore size)
  • Unexpected scaling operations firing off at unplanned times…


  • 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!

    This post has no comments. Be the first to leave one!

    Join the discussion

    Your email address will not be published. Required fields are marked *