Azure automation PowerShell runbook for maintenance of Sitecore databases

SqlDbMaintenanceRunbook.ps1

# Getting values of automation variables
$resourceGroupName = Get-AutomationVariable -Name 'ResourceGroupName'
$sqlServerName = Get-AutomationVariable -Name 'SqlServerName'
$keyVaultName = Get-AutomationVariable -Name 'KeyVaultName'
$kvSqlAdminUserNameKey = Get-AutomationVariable -Name 'kvSqlAdminUserNameKey'
$kvSqlAdminPasswordKey = Get-AutomationVariable -Name 'kvSqlAdminPasswordKey'
$AzureSQLServerFQDN = $sqlServerName + ".database.windows.net"

$connection = Get-AutomationConnection -Name 'AzureRunAsConnection'
# Login to Azure context using service principal
Connect-AzAccount -ServicePrincipal -CertificateThumbprint $connection.CertificateThumbprint -ApplicationId $connection.ApplicationID -Tenant $connection.TenantID

$sqlAdminUserName = (Get-AzKeyVaultSecret -VaultName $keyVaultName -Name $kvSqlAdminUserNameKey).SecretValueText
$sqlAdminPassword = (Get-AzKeyVaultSecret -VaultName $keyVaultName -Name $kvSqlAdminPasswordKey).SecretValueText

# Get all databases within resource group and SQL server
$databases = Get-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $sqlServerName

foreach($db in $databases)
{
    if($db.DatabaseName -eq "master")
    {
        # do not do a maintenance of system master DB
        continue
    }

    Write-Output "Starting maintenance of "$db.DatabaseName
    $SQLOutput = $(Invoke-Sqlcmd -ServerInstance $AzureSQLServerFQDN -Username $sqlAdminUserName -Password $sqlAdminPassword -Database $db.DatabaseName -Query "exec [dbo].[AzureSQLMaintenance] @Operation='all' ,@LogToTable=1" -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1 
    Write-Output "################# SQL CMD Output #################"​
    Write-Output $SQLOutput​
    Write-Output "##################################################"​
    Write-Output "Finished maintenance of "$db.DatabaseName
}