Skip to content

Instantly share code, notes, and snippets.

@DaveRuijter
Last active August 9, 2023 20:09
Show Gist options
  • Star 3 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • Save DaveRuijter/0e9253507723c01fe16fa673173bee26 to your computer and use it in GitHub Desktop.
Save DaveRuijter/0e9253507723c01fe16fa673173bee26 to your computer and use it in GitHub Desktop.
PowerShell script to vertically scale up and down or pause/resume an Azure Power BI Embedded Capacity according to a schedule. You can use this in an Azure Automation Runbook.
<#
.SYNOPSIS
Vertically scale up and down or pause/resume an Azure Power BI Embedded Capacity according to a schedule using Azure Automation.
.DESCRIPTION
This Azure Automation runbook enables vertically scaling or pausing of an Azure Power BI Embedded Capacity 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. A5) on monday during peak hours, while the rest of the week the traffic is decreased, allowing you to scale down (e.g. A1). Outside business hours and during weekends you could then suspend the capacity so no charges will be applied. 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 capacity is in the desired state already and no work needs to be done. The script is time zone aware.
.PARAMETER resourceGroupName
Name of the resource group to which the capacity is assigned.
.PARAMETER azureRunAsConnectionName
Azure Automation Run As account name. Needs to be able to access
the $capacityName.
.PARAMETER serverName
Azure Power BI Embedded Capacity name.
.PARAMETER scalingSchedule
PBI Capacity 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 slotsthat you defined, the server will be paused.
.PARAMETER scalingScheduleTimeZone
Timezone of time slots in $scalingSchedule.
Available time zones: [System.TimeZoneInfo]::GetSystemTimeZones().
.EXAMPLE
-resourceGroupName myResourceGroup
-azureRunAsConnectionName AzureRunAsConnection
-serverName myserver
-scalingSchedule [{WeekDays:[1], StartTime:"06:59:59", StopTime:"17:59:59", Sku: "A4"}, {WeekDays:[2,3,4,5], StartTime:"06:59:59", StopTime:"17:59:59", Sku: "A2"}]
-scalingScheduleTimeZone W. Europe Standard Time
.NOTES
Author: Dave Ruijter
Last Updated: Oct 2018
#>
param(
[parameter(Mandatory=$true)]
[string] $resourceGroupName,
[parameter(Mandatory=$false)]
[string] $azureRunAsConnectionName = "AzureRunAsConnection",
[parameter(Mandatory=$true)]
[string] $capacityName,
[parameter(Mandatory=$true)]
[string] $scalingSchedule,
[parameter(Mandatory=$false)]
[string] $scalingScheduleTimeZone = "W. Europe Standard Time"
)
filter timestamp {"[$(Get-Date -Format G)]: $_"}
Write-Output "Script started." | timestamp
$VerbosePreference = "Continue"
$ErrorActionPreference = "Stop"
#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." | timestamp
#Get current date/time and convert to $scalingScheduleTimeZone
$stateConfig = $scalingSchedule | ConvertFrom-Json
$startTime = Get-Date
Write-Output "Azure Automation local time: $startTime." | timestamp
$toTimeZone = [System.TimeZoneInfo]::FindSystemTimeZoneById($scalingScheduleTimeZone)
Write-Output "Time zone to convert to: $toTimeZone." | timestamp
$newTime = [System.TimeZoneInfo]::ConvertTime($startTime, $toTimeZone)
Write-Output "Converted time: $newTime." | timestamp
$startTime = $newTime
#Get current day of week based on converted start time
$currentDayOfWeek = [Int]($startTime).DayOfWeek
Write-Output "Current day of week: $currentDayOfWeek." | timestamp
# Get the scaling schedule for the current day of week
$dayObjects = $stateConfig | Where-Object {$_.WeekDays -contains $currentDayOfWeek } |Select-Object Sku, @{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)}}
# Get the PBI Capacity object
$pbiEmbCap = Get-AzureRmPowerBIEmbeddedCapacity -ResourceGroupName $resourceGroupName -Name $capacityName
Write-Output "PBI Capacity name found: $($pbiEmbCap.Name)" | timestamp
Write-Output "Current PBI Capacity status: $($pbiEmbCap.State), pricing tier: $($pbiEmbCap.Sku)" | timestamp
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 { ($startTime -ge $_.StartTime) -and ($startTime -lt $_.StopTime) } | Select-Object -First 1
if($matchingObject -ne $null)
{
Write-Output "Scaling schedule found. Check if PBI Capacity is paused and if current pricing tier is matching..." | timestamp
if($pbiEmbCap.State -eq "Paused")
{
Write-Output "PBI Capacity was paused. Resuming!" | timestamp
$pbiEmbCap | Resume-AzureRmPowerBIEmbeddedCapacity
Write-Output "PBI Capacity resumed." | timestamp
}
if($pbiEmbCap.Sku.Name -ne $matchingObject.Sku)
{
Write-Output "PBI Capacity is not in the pricing tier of the scaling schedule. Changing!" | timestamp
$pbiEmbCap = Update-AzureRmPowerBIEmbeddedCapacity -Name $pbiEmbCap.Name -ResourceGroupName $resourceGroupName -Sku $matchingObject.Sku
Write-Output "Change to edition/tier as specified in scaling schedule initiated..." | timestamp
$pbiEmbCap = Get-AzureRmPowerBIEmbeddedCapacity -ResourceGroupName $resourceGroupName -Name $capacityName
Write-Output "Current PBI Capacity state: $($pbiEmbCap.State), pricing tier: $($pbiEmbCap.Sku.Name)" | timestamp
}
else
{
Write-Output "Current PBI Capacity pricing tier matches the scaling schedule already. Exiting..." | timestamp
}
}
else { # Scaling schedule not found for current time
Write-Output "No matching scaling schedule time slot for this time found. Check if the PBI Capacity is paused..." | timestamp
if($pbiEmbCap.State -ne "Paused")
{
Write-Output "PBI Capacity not paused. Pausing!" | timestamp
$pbiEmbCap | Suspend-AzureRmPowerBIEmbeddedCapacity
Write-Output "PBI Capacity paused." | timestamp
$pbiEmbCap = Get-AzureRmPowerBIEmbeddedCapacity -ResourceGroupName $resourceGroupName -Name $capacityName
Write-Output "Current PBI Capacity sate: $($pbiEmbCap.State), pricing tier: $($pbiEmbCap.Sku.Name)" | timestamp
}
else
{
Write-Output "PBI Capacity paused already. Exiting..." | timestamp
}
}
}
else # Scaling schedule not found for this day
{
Write-Output "No matching scaling schedule for this day found. Check if the PBI Capacity is paused..." | timestamp
if($pbiEmbCap.State -ne "Paused")
{
Write-Output "PBI Capacity not paused. Pausing!" | timestamp
$pbiEmbCap | Suspend-AzureRmPowerBIEmbeddedCapacity
Write-Output "PBI Capacity paused." | timestamp
$pbiEmbCap = Get-AzureRmPowerBIEmbeddedCapacity -ResourceGroupName $resourceGroupName -Name $capacityName
Write-Output "Current PBI Capacity state: $($pbiEmbCap.State), pricing tier: $($pbiEmbCap.Sku.Name)" | timestamp
}
else
{
Write-Output "PBI Capacity paused already. Exiting..." | timestamp
}
}
Write-Output "Script finished." | timestamp
@luu-at-pharm3r
Copy link

If you run this script and get this error, "The term 'Get-AzureRmPowerBIEmbeddedCapacity' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. (The term 'Get-AzureRmPowerBIEmbeddedCapacity' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.)" go and update to the newer powershell from AzureRM to Az https://docs.microsoft.com/en-us/powershell/azure/migrate-from-azurerm-to-az?view=azps-5.4.0

@DaveRuijter
Copy link
Author

Thanks for helping out and sharing this tip!

@maxwiks
Copy link

maxwiks commented Jul 2, 2021

First of all. Thank you for the script! Been using it for a while and it's awesome.

However I think I've found a small bug.

There's an issue in the script when comparing $startTime with StartTime and StopTime on line 91-92 and at the same time using a different timezone then UTC (Azure). Somehow it seems like it's comparing not only time but also dates coming from $dayObjects. (The issue could be earlier in the script).

I think what happens is that if UTC time is e.g. 11 PM and your converted time zone is 1 AM next day = No match because different days. It actually causes the capacity to pause. I've had this issue for a while and it has been pausing the capacity on every night for around 2 hours (Schedule runs every hour) until it scales back up again. And schedule is fine. We have covered all days and hours.

I'm looking for a quick fix on our side so it doesn't compare dats. But perhaps this is something you could look into for future use of this?

@bt101
Copy link

bt101 commented Mar 20, 2022

@DaveRuijter Thank you for this script. It is greatly appreciated. I couldn't seem to get this to work until I changed the following IF statement to remove the ".Name" from the end of "$pbiEmbCap.Sku.Name".

Original: if($pbiEmbCap.Sku.Name -ne $matchingObject.Sku)
New: if($pbiEmbCap.Sku -ne $matchingObject.Sku)

I'm not sure if I messed something up but the IF statement was resolving to TRUE even when the current PBI Sku was the same as the scheduled PBI Sku.

After adding additional write-Output's to list the Current Sku: $pbiEmbCap.Sku and Scheduled Sku: $matchingObject.Sku, I saw they were identical, so I dropped the .Name in the IF statement. I'm not sure what the $pbiEmbCap.Sku.Name resolved to. I'm just happy it seems to be working now.

@bt101
Copy link

bt101 commented Mar 21, 2022

First of all. Thank you for the script! Been using it for a while and it's awesome.

However I think I've found a small bug.

There's an issue in the script when comparing $startTime with StartTime and StopTime on line 91-92 and at the same time using a different timezone then UTC (Azure). Somehow it seems like it's comparing not only time but also dates coming from $dayObjects. (The issue could be earlier in the script).

I think what happens is that if UTC time is e.g. 11 PM and your converted time zone is 1 AM next day = No match because different days. It actually causes the capacity to pause. I've had this issue for a while and it has been pausing the capacity on every night for around 2 hours (Schedule runs every hour) until it scales back up again. And schedule is fine. We have covered all days and hours.

I'm looking for a quick fix on our side so it doesn't compare dats. But perhaps this is something you could look into for future use of this?

I had the same issue. The $dayObjects .StartTime and .StopTime use the current (local/UTC) date when it's not specified in the ParseExact. So I referenced another PowerShell script (Azure SQL Database Vertical Scaling) to correct the issue. This is the new logic that I used for $dayObjects.

$dayObjects = $stateConfig | Where-Object {$.WeekDays -contains $currentDayOfWeek } |Select-Object Sku, @{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)}}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment