At the end of the day, the cloud needs automating. I like to use Azure Automation Accounts . They are a bit clunky, but Microsoft has improved the QOL for authenticating and deploying workbooks. Automation runbooks have a great set of uses:

Scaling Resources during peak hours
Reacting to Alerts to restart services
Scheduling long-running migrations to run in off-hours
Clean up orphaned resources
Cycling secrets and credentials
Automating manual procedures

In this blog, we will set up a new automation account, create a runbook, and hook it up to an Azure monitor alert.

I’ll be using Azure CLI and the Az PowerShell module.

Example Problem

Let’s say we have a database with a predictable load profile. It simmers at a low usage most of the time except at Show Time, whene everyone needs something from the database.

Situation A: Absolute garbage

Spending way too much money on DTUs we are not using and only looks good in the aggregate.

Situation B: Curbing the spike

Since we know when game time is, we can schedule a workbook to scale up the DB to catch the spike preemptively and scale back when things cool down.

Situation C: Healthy Utilization

We can create a simple auto-scaling system using alerts to scale the server down if utilization isn’t at a healthy percentage.

Automation Account

Let’s setup the automation account

$Subscription = "00000000-0000-0000-0000-000000000000"
$ResourceGroupName  = "db-group"
$AutomationAccountName ="db-automation"
$Location = "WestEurope"

# The resource group
az group create -n $ResourceGroupName -l $Location
# The Automation account
az automation account create -n $AutomationAccountName -g $ResourceGroupName --sku Free
#NOTE: As of writing, az automation might prompt you to install an automation module for Azure CLI.

# Now, we must assign the automation account a Managed (System-assigned) Identity. 
# Unfortunately, we can't do that through the Azure CLI natively so we will use az rest for that

$ApiVersion = "?api-version=2019-06-01"
$ManagementUrl = "https://management.azure.com"
$BaseUrl = "$ManagementUrl/subscriptions/$Subscription/resourceGroups/$ResourceGroupName"
$AutomationUrl = "$BaseUrl/providers/Microsoft.Automation/automationAccounts"

$ManagedIdentity = az rest `
    --method put `
    --uri "$AutomationUr/$AutomationAccountName?$ApiVersion" `
    --body '{"identity": {"type": "SystemAssigned"}}'
    --query identity.principalId
    --o tsv

# Finally we need to grant the automation account some rights to the subscription.
# For this example, we will make it a Contributor of it's own resoure group 
$ResourceId="/subscriptions/$Subscription/resourceGroups/$ResourceGroupName"

az role assignment create --assignee $ManagedIdentity --role Contributor --scope $ResourceId

Now, any workbooks on that automation account are authorized to execute almost anything in the scope of that resource group.

Runbook

Now we can create a runbook that will run some PowerShell

#... <previous script>

$RunBookName = "ScaleTheDataBase"

$RunBookId = az automation runbook create `
    --automation-account-name $AutomationAccountName `
    -n $RunBookName `
    -g $ResourceGroupName `
    --type Powershell ` # There are many handy options here, but PS has the best integration
    --description  "Scales up the database for Show Time"
    --query id
    -o tsv

Publish some Content

The runbook will host and execute a PowerShell script. That’s the content we need to write.

#ScaleDb.ps1
param ( # The runbook will detect these params and let you set them at runtime!
    [string]$ResourceGroupName=<ResourceGroupName>,
    [string]$ServerName=<ServerName>,
    [string]$DBName=<DBName>,
    [string]$Edition,
    [string]$TargetTier,
)

# This magic nugget authenticates the current run with the identity we created earlier
Connect-AzAccount -Identity

# Now we can use any AzPowershell modules
Set-AzSqlDatabase `
    -ResourceGroupName $ResourceGroupName `
    -ServerName $ServerName `
    -DatabaseName $DBName `
    -Edition $Edition `
    -RequestedServiceObjectiveName $TargetTier `

Now we can upload our content

az automation runbook replace-content --ids $RunBookId --content 'ScaleDb.ps1'

The content we just uploaded is just the draft, we still need to publish the draft to be able to run it

az automation runbook publish --ids $RunBookId

Now we have a hot runbook ready to be run

Run it!

az automation runbook start --ids $RunBookId

This will create a job. An automation job is an instance of a runbook running. We can play with it using

az automation job -h

Here, we can check our jobs, stop them, debug them, and even suspend them to resume them later.

Schedule it

Aside from workbooks and jobs, automation accounts contain schedules. Schedules can let the automation account execute something in the future. Schedules can be one-time or recurring.

Schedules are separate entities from alerts with a many-to-many relationship. This means that Schedules need to be explicitly created and linked to a workbook before they are active.

erDiagram RUNBOOK }|--|{ SCHEDULE : "linked (registerred)"

Unfortunately, as of this writing, neither the Azure CLI nor the REST API support linking schedules to workbooks. You can use the portal to do this manually. Alternatively, you can set it up through bicep/arm templates or the Az PowerShell module.

Create the schedules

Let’s set up situation B from the problem. We want to create two schedules. One to scale up the database before the spike and one to scale it down after it.

$StartTime = (Get-Date "23:00").AddMonths(1).AddDays(-1) # Set the start time to the first day of next month
$TimeZone = ([System.TimeZoneInfo]::Local).Id # Set the timezone to the local timezone

New-AzAutomationSchedule `
    -Name "ScaleupSchedule" `
    -StartTime ((Get-Date "23:00").AddMonths(1).AddDays(-1)) `
    -MonthInterval 1 `
    -DaysOfMonth "LastDay" #intera
    -ResourceGroupName $ResourceGroupName `
    -AutomationAccountName $AutomationAccountName `
    -TimeZone (([System.TimeZoneInfo]::Local).Id)

Now let’s create another schedule to go 2 days later

New-AzAutomationSchedule `
    -Name "ScaledownSchedule" `
    -StartTime ((Get-Date "23:00").AddMonths(1).AddDays(2)) `
    -MonthInterval 1 `
    -DaysOfMonth "Two" #intera
    -ResourceGroupName $ResourceGroupName `
    -AutomationAccountName $AutomationAccountName `
    -TimeZone (([System.TimeZoneInfo]::Local).Id)

Register workbook with schedules

Because schedules, and runbooks have a many-to-many relationship, we can create links and reuse the same runbook at different schedules, passing different parameters.

# The Scale-up schedule will scale the DB to P1 size
Register-AzAutomationScheduledRunbook
        -RunbookName $RunBookName 
        -ScheduleName "ScaleupSchedule" `
        -Parameters "Edition=Premium TargetTier=P1"
        -ResourceGroupName $ResourceGroupName `
        -AutomationAccountName $AutomationAccountName `

# The Scale-down schedule will scale the DB to S2 size
Register-AzAutomationScheduledRunbook
        -RunbookName $RunBookName 
        -ScheduleName "ScaledownSchedule" `
        -Parameters "Edition=Standard TargetTier=S1"
        -ResourceGroupName $ResourceGroupName `
        -AutomationAccountName $AutomationAccountName `

Done!

We have situation B complete!

Alert

Let’s reuse the runbook one last time by having it scale up if the database gets a bit hot

Create Alert Condition

$DBResourceId = az sql db show -n $DatabaseName -s $SqlServerName -g $ResourceGroupName --query id

$Alert = az monitor metrics alert create `
    -n DatabaseIsGetttingHot `
    -g $ResourceGroupName `
    --scopes $DBResourceId `
    --description "Database getting hot" `
    --condition "avg dtu_consumption_percent < 20" `
    --window-size 5m ` #Condition bucketed over 5 minutes
    --evaluation-frequency 5m #evaluated every 5 minutes

This will trigger if the DTU utilization falls below 20%

Create Action Group

Webook

So, what’s confusing is that the action itself can’t pass parameters to the runbook. The only way to do it is to register a webhook to the runbook that, in turn can be triggered by the

$Params = @{"Edition"="Standard";"TargetTier"="S1"}
$Expiration = (Get-Date).AddYears(1).ToString("dd/MM/yyyy") 
$WebHookName = "AlertWebhook"

$Webhook = New-AzAutomationWebhook `
    -Name $WebHookName `
    -Parameters $Params `
    -IsEnabled $True `
    -ExpiryTime $Expiration `
    -RunbookName $RunbookName `
    -ResourceGroupName $ResourceGroupName `
    -AutomationAccountName $AutomationAccountName `
    -Force

Action group

To react to an alert, we need to create an action group and point it not only to the runbook but the runbook’s webhook URI property

$WebhookId = "$AutomationAccountName/webhooks/$WebHookName"

$Action = az monitor action-group create `
    --name MyActionGroup `
    --resource-group MyResourceGroup `
    --action automationrunbook ScaleRunbook $AutomationAccountName $RunBookName $WebhookId $Webhook.WebhookURI

Finally, we link the action group to the alert and we are golden!


az monitor metrics alert update --ids $Alert.Id --add-action $Action.Id

Done!

Now we have an active alert that triggers an action group that calls an automation runbook webhook that passes parameters to a PowerShell script that scales down a database

Finally

Okay, that took a lot longer than expected, but we covered pretty much all the basic functionality of Azure Automation Accounts. If you set this up properly, you can automate an entire environment and even simulate many self-healing and auto-scaling features of premium resources!

Cheers!

Automating Azure with Azure Automation (Accounts)

Example Problem

Situation A: Absolute garbage

Situation B: Curbing the spike

Situation C: Healthy Utilization

Automation Account

Runbook

Publish some Content

Run it!

Schedule it

Create the schedules

Register workbook with schedules

Done!

Alert

Create Alert Condition

Create Action Group

Webook

Action group

Done!

Finally