Azure Analysis Services scheduled autoscaling and pausing

Posted by

One of the major benefits of Azure is scalability, the cloud is built to be elastic, to be as cost-effective as possible. Azure solutions can be scaled in ways that would never be possible with your own hardware. You are able to scale up and out when the demand arises, and more importantly you can scale down and in, or even pause, when you don’t need all the resources anymore, thereby saving a lot of money.

Autoscaling based on a schedule allows you to scale your solution according to predictable resource demand. For example you could require the highest capacity on Monday during peak hours, while the rest of the week the traffic is decreased allowing you to scale down and minimizing costs. Outside business hours and during weekends you could then pause the service, after which no charges will be applied at all.

I recently blogged about Azure SQL Database scheduled autoscaling and in this post I will apply the same solution to Azure Analysis Services. Scaling Azure AS works similar to Azure SQL, but the amount of money you can save is higher because in general Azure AS is more expensive, and you have the possibility to pause the instance.

Azure AS currently offers three service tiers: Developer, Basic and Standard. Each instance can contain one or multiple tabular models. You can change between instance tiers and performance levels easily and without downtime.
Azure Analysis Services Tiers

Scheduled autoscaling with Azure Automation

To automate scaling of Azure Analysis Services based on a schedule I have created an Azure Automation PowerShell Runbook called AzureAnalysisServicesScheduledAutoscaling. You can easily import this runbook into your Azure Automation account and then execute it against your instance by entering values for a few parameters that specify your database details, your Automaton Run As Account and an instance-specific scaling schedule. A step-by-step guide can be found at the bottom of this post.

Scheduling of the runbook itself can be done by creating a recurring hourly Automation Schedule. A single runbook can have multiple jobs running at one time. This means it is possible to create multiple schedules for the AzureAnalysisServicesScheduledAutoscaling runbook, enabling scaling of multiple instances simultaneously.

Scaling of the instance to a specified tier/performance level will be performed in case the runbook execution moment matches a day/time slot specified in the scaling schedule parameter. If the execution moment does not match a day/time slot the service will be paused. If the runbook executes and the instance is in the desired tier/performance level already, no work will be done. Don’t worry about the costs of Azure Automation, the first 500 run minutes are free and after that you pay €0.002 / minute (as per september 2017).

Azure Analysis Services Vertical Scaling using Azure Automation

When you start using this solution, keep the following in mind:

  • The length of time for a switch of tier/performance level varies, but is generally under a couple of minutes.
  • Behind the scenes, changing the tier/performance level creates a replica of the original instance at the new tier/performance level, and then switches connections over to the replica. No data is lost during this process. During the switch over to the replica, connections to the database are disabled which can cause in flight queries to fail but they should be retried.
  • Scaling from a lower tier to a higher tier is supported, but NOT vice-versa. For example, you can move from Basic to Standard tier or from Developer to Basic tier, but you can’t move from Standard to Basic tier or from Basic to Developer tier.
  • To scale an instance down, the total size of the models must be smaller than the maximum allowed size of the target performance level.
  • The Standard tier supports scale out instances. Additional instances can be added to a primary instance for faster data and query processing. In this PowerShell solution only vertical scaling is supported.

The Runbook PowerShell script

The PowerShell script below can be used in an Azure Automation Runbook with type “PowerShell”.

The script contains the following logic:

  1. Authenticate with Azure Automation Run As account.
  2. Get the current day of week and current time, and convert it to the time zone that is specified.
  3. Check which of the specified ‘slots’ in the scaling schedule is currently active.
  4. Scale instance to tier/performance level that is specified in the scaling schedule.
    If no schedule for the current day/time is found, pause instance.
    If instance is already in target edition/tier, do nothing.

Vertically scale up and down or pause/resume an Azure Analysis
Services server according to a schedule using Azure Automation.

This Azure Automation runbook enables vertically scaling or
pausing of an Azure Analysis Services server 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. S1) on monday
during peak hours, while the rest of the week the traffic is
decreased allowing you to scale down (e.g. S0). Outside business
hours and during weekends you could then pause the server 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 server is in the
desired state already and no work needs to be done. The script
is time zone aware.

.PARAMETER environmentName
Name of Azure Cloud environment. Default is AzureCloud, only change
when on Azure Government Cloud, for example AzureUSGovernment.

.PARAMETER resourceGroupName
Name of the resource group to which the server is assigned.

.PARAMETER azureRunAsConnectionName
Azure Automation Run As account name. Needs to be able to access
the $serverName.

.PARAMETER serverName
Azure Analysis Services server name.

.PARAMETER scalingSchedule
Server 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 slots
that you defined, the server will be paused.

.PARAMETER scalingScheduleTimeZone
Time Zone of time slots in $scalingSchedule.
Available time zones: [System.TimeZoneInfo]::GetSystemTimeZones().

-environmentName AzureCloud
-resourceGroupName myResourceGroup
-azureRunAsConnectionName AzureRunAsConnection
-serverName myserver
-scalingSchedule [{WeekDays:[1], StartTime:”06:59:59″, StopTime:”17:59:59″, Sku: “B2″}, {WeekDays:[2,3,4,5], StartTime:”06:59:59″, StopTime:”17:59:59”, Sku: “B1”}]
-scalingScheduleTimeZone W. Europe Standard Time

Author: Jorg Klein
Last Update: Nov 2018

[string] $environmentName = "AzureCloud",

[string] $resourceGroupName,

[string] $azureRunAsConnectionName = "AzureRunAsConnection",

[string] $serverName,

[string] $scalingSchedule,

[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
$environment = Get-AzureRmEnvironment -Name $environmentName
Add-AzureRmAccount -Environment $environment -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.ToString("yyyy:MM:dd")+”:”+$_.StartTime),"yyyy:MM:dd:HH:mm:ss", [System.Globalization.CultureInfo]::InvariantCulture)}}, `
@{Name="StopTime"; Expression = {[datetime]::ParseExact(($startTime.ToString("yyyy:MM:dd")+”:”+$_.StopTime),"yyyy:MM:dd:HH:mm:ss", [System.Globalization.CultureInfo]::InvariantCulture)}}

# Get the server object
$asSrv = Get-AzureRmAnalysisServicesServer -ResourceGroupName $resourceGroupName -Name $serverName
Write-Output "AAS server name: $($asSrv.Name)" | timestamp
Write-Output "Current server status: $($asSrv.State), sku: $($asSrv.Sku.Name)" | 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 server is paused and if current sku is matching..." | timestamp
        if($asSrv.State -eq "Paused")
            Write-Output "Server was paused. Resuming!" | timestamp
            $asSrv | Resume-AzureRmAnalysisServicesServer
            Write-Output "Server resumed." | timestamp
        if($asSrv.Sku.Name -ne $matchingObject.Sku)
            Write-Output "Server is not in the sku of the scaling schedule. Changing!" | timestamp
            $asSrv = Set-AzureRmAnalysisServicesServer -Name $asSrv.Name -ResourceGroupName $resourceGroupName -Sku $matchingObject.Sku
            Write-Output "Change to edition/tier as specified in scaling schedule initiated..." | timestamp
            $asSrv = Get-AzureRmAnalysisServicesServer -ResourceGroupName $resourceGroupName -Name $serverName
            Write-Output "Current server state: $($asSrv.State), sku: $($asSrv.Sku.Name)" | timestamp
            Write-Output "Current server sku 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 server is paused..." | timestamp
        if($asSrv.State -ne "Paused")
            Write-Output "Server not paused. Pausing!" | timestamp
            $asSrv | Suspend-AzureRmAnalysisServicesServer
            Write-Output "Server paused." | timestamp
            $asSrv = Get-AzureRmAnalysisServicesServer -ResourceGroupName $resourceGroupName -Name $serverName
            Write-Output "Current server sate: $($asSrv.State), sku: $($asSrv.Sku.Name)" | timestamp
            Write-Output "Server 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 server is paused..." | timestamp
    if($asSrv.State -ne "Paused")
        Write-Output "Server not paused. Pausing!" | timestamp
        $asSrv | Suspend-AzureRmAnalysisServicesServer
        Write-Output "Server paused." | timestamp
        $asSrv = Get-AzureRmAnalysisServicesServer -ResourceGroupName $resourceGroupName -Name $serverName
        Write-Output "Current server state: $($asSrv.State), sku: $($asSrv.Sku.Name)" | timestamp
        Write-Output "Server paused already. Exiting..." | timestamp

Write-Output "Script finished." | timestamp

Step-by-step setup guide

Navigate to the Azure portal and create an Azure Automation account in the same Azure Subscription as where the Azure Analysis Services instance that you want to scale resides. Make sure the Run As account has been created. When you create a new Automation account in the Azure portal, it automatically creates a new service principal (Run As account) and assigns it the Contributor role-based access control (RBAC) role in the subscription by default. These rights are sufficient to scale Azure SQL Databases.

A pre-requisite to using my script is to import the AzureRM.Profile and AzureRM.AnalysisServices PowerShell modules. Navigate to your Automation Account and click the “Modules Gallery” menu item. Search for “AzureRM.Profile” first since the Azure AS module depends on it. Open the item from the gallery and click Import.
Azure Automation Import Module

Next, import the “AzureRM.AnalysisServices” module.
Azure Automation Import Module AnalysisServices

I have uploaded my PowerShell script to TechNet Script Center so you can easily import it as Runbook. Navigate to your Automation Account if required and click the “Runbooks Gallery” menu item. Make sure you filter the Gallery Source on “Script Center” and search for “Azure Analysis Services – Scale and Pause or Resume using scheduled autoscaling”. Open the item from the gallery and click Import.
Azure Automation Import Runbook

Before you can use the runbook you have to publish it first. Click on “Overview” if you are not already on that page and then click “Edit”.
Edit Runbook

Now click “Publish”.
Publish Runbook

The runbook is now ready to use. Next step is to schedule it to run hourly, of course with your desired parameters. Click “Schedules” and then “Add a schedule”.
Schedule Runbook

Give your schedule a name, set the start time on a whole hour, the Recurrence on “Recurring”, Recur every “1 Hour” and Set expiration to “No”.
New Runbook Schedule

Now configure the parameters. Explaination can be found under “The Runbook PowerShell script” earlier in this article.
Schedule Runbook Parameters

When you now navigate to “Jobs” you get an overview of all executions.
Runbook Jobs

When you open a job and look at the Output, you will see all details of that particular run. For example, I can see my server was resumed on friday at 07:00.
Job Output


  1. Jorg – this is an excellent contribution, thank you!

    Having followed a lot of your work related to Azure AS and automation, I was wondering if you could comment (here or perhaps a separate blog post) on the pros/cons of going w/ Azure Automation over Azure Functions for this type of stuff.


    1. Hi Bill, thanks a lot, appreciate it!

      I see Azure Automation more as an easy and accessible (for not hardcore devs) service for running Admin PowerShell scripts while Azure Functions offers a more advanced and language neutral option as a lot of programming languages are supported. Also for example the ease of importing modules and runbooks from the gallery is something that is just easier with Automation. Also don’t forget the tight integration with OMS!

      Liked by 1 person

  2. Hello, Thanks for providing this
    Can you help me with a error Invalid JSON primitive: . (Invalid JSON primitive:
    I use this as [{WeekDays:[1], StartTime:”06:59:59″, StopTime:”17:59:59″, Sku: “S0″}, {WeekDays:[2,3,4,5], StartTime:”06:59:59″, StopTime:”17:59:59”, Sku: “S0”}]
    I don’t see where its going wrong can you give me a clue


    1. Hi Erwin, I think the quotation marks are invalid. Also make sure there is no CRLF (enter) in the string. Usually I copy/paste it to Notepad first to check if it looks valid and contains no CRLFs. Can you try this: [{WeekDays:[1], StartTime:”06:59:59″, StopTime:”17:59:59″, Sku:”B2″}, {WeekDays:[2,3,4,5], StartTime:”06:59:59″, StopTime:”17:59:59″, Sku: “B1”}]


      1. Thanks for the help; I ended up to type it in and then it worked, had a issue with the servername but that I have solved by only use the last part and not the copied servername from the analysis server
        thanks again

        Liked by 1 person

  3. Hi Jorg, congrats on another great article.
    I was wondering if you tried something similar for scale-out. I’ve been doing some tests but can’t seem ot find out which parameters to set.


    1. Hi Rui,
      I have investigated this scenario but I think we have to wait for new functionality before we can automate scaling out.

      In the following blog post is mentioned that scaling out using PowerShell will be possible in the future. Currently the only way to automate this would be via ARM by changing the capacity of the server.

      Synchronizing data to scale out replica’s can be done using the Azure AS REST API:
      I tried to communicate with the REST API from Azure Automation but it didn’t work because of missing ADAL modules that are required to authenticate. Fortunately it’s easy to use Logic Apps to communicate with the API as I have shown in my latest blog post:


  4. Thanks for the detailed article and the fantastic script. I hope you don’t mind assisting with an issue that I have. I have configured my instance to start at 8am and finish at 6pm weekdays, so my JSON looks like this: “[{WeekDays:[1,2,3,4,5], StartTime:””08:00:00″”, StopTime:””18:00:00″”, Sku:””D1″”}]” (I have double quotes because it’s hard coded in the script). I have the script scheduled to run every hour. It correctly stops at 6pm. However for the 8am and 9am runs I get “No matching scaling schedule time slot for this time found” and the instance does not start. Then at the 10am run it does find a match and starts. It seems like the ‘Where-Object { ($startTime -ge $_.StartTime)’ part is not evaluating correctly. I don’t think it’s a timezone issue because it stops at the correct time. It seems like maybe a it’s comparing 8 and not 08 or there is some datatype issue. Perhaps I will try putting it in as a parameter instead. Trouble is I only get a couple of hours to test it!


    1. Hi Nick, sorry for the late reply, I was on a holiday. Have you been able to fix the issue within the couple of hours that you had left? Unfortunately it doesn’t really make sense to me and I don’t know what’s wrong with your script. Maybe try to just import the identical runbook and use it without hardcoding parameters. It works for many others this way without issues this way!


  5. Hi Jorg… Similar to Nick McDermaid’s problem except we are having an issue with the Stop Time. We are using the parameters and the schedule we set up is “[{WeekDays:[1,2,3,4,5], StartTime:\”06:00:00\”, StopTime:\”17:59:59\”, Sku:\”S0\”}]” and it runs hourly on the top of the hour (8:00, 9:00, 10:00, etc.) and we are on the Pacific Standard Time timezone. For some reason, it starts at the correct time (6:00 AM) but for some reason, the server is paused at 17:00 instead of 18:00. I was wondering, in this part of the code “($startTime -lt $_.StopTime)”, should it be referencing the $startTime or should it be $newTime? Should I change the StopTime to 18:01:00? Should it run at 8:01, 9:01, 10:01, etc? Any light you can shed on this will be greatly appreciated! (we’ve tried also changing the StopTime to 18:59:59 but it still wants to pause at 17:00!).


    1. This is a bit belated but I had the same issue as Nick McDermaid and Ruby M. I believe the issue is that the Azure Automation account interprets these lines in UTC time:

      @{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)}}

      This can cause time zone issues at the day rollover for scheduling (in my case I tried to spin down AAS at midnight and it was shutting down at 7pm EST.) I fixed the issue by getting the day from the $startTime variable and combining it with the start/stop schedule time. I updated the code to use the following:

      @{Name=”StartTime”; Expression = {[datetime]::ParseExact(($startTime.ToString(“yyyy:MM:dd”)+”:”+$_.StartTime),”yyyy:MM:dd:HH:mm:ss”, [System.Globalization.CultureInfo]::InvariantCulture)}}, `
      @{Name=”StopTime”; Expression = {[datetime]::ParseExact(($startTime.ToString(“yyyy:MM:dd”)+”:”+$_.StopTime),”yyyy:MM:dd:HH:mm:ss”, [System.Globalization.CultureInfo]::InvariantCulture)}}

      Liked by 1 person

  6. Hi Jorg. I downloaded the runbook froom the runbook gallery and have specified the parameters as you have specified. However, i keep getting this error –
    At line:112 char:93
    + … = {[datetime]::ParseExact(($startTime.ToString(“yyyy:MM:dd”)+”:”+ …
    + ~ You must provide a value expression following the ‘+’ operator.

    At line:112 char:93
    + … Exact(($startTime.ToString(“yyyy:MM:dd”)+”:”+$_.StartTime),”yyyy: …
    + ~~~~~~~~~~~~~~~~~~~~ Unexpected token ‘”:”+$_.StartTime’ in expression or statement.

    At line:112 char:93
    + … = {[datetime]::ParseExact(($startTime.ToString(“yyyy:MM:dd”)+”:”+ …
    + ~ Missing closing ‘)’ in expression.

    At line:112 char:93
    + … = {[datetime]::ParseExact(($startTime.ToString(“yyyy:MM:dd”)+”:”+ …
    + ~ Missing ‘)’ in method call.

    At line:112 char:34
    + @{Name=”StartTime”; Expression = {[datetime]::ParseExact(($startTime. …
    + ~ Missing closing ‘}’ in statement block or type definition.

    At line:112 char:113
    + … Exact(($startTime.ToString(“yyyy:MM:dd”)+”:”+$_.StartTime),”yyyy: …
    + ~ The hash literal was incomplete.

    At line:112 char:113
    + … xact(($startTime.ToString(“yyyy:MM:dd”)+”:”+$_.StartTime),”yyyy:M …
    + ~ Unexpected token ‘)’ in expression

    Here is my scalingSchedule parameter –
    [{WeekDays:[1,2,3,4,5], StartTime:”07:59:59″, StopTime:”16:59:59″, Sku:”S4″}]
    I am trying to leave it on S4 from Monday to friday 8-5.

    Please let me know if i am missing anything here.

    Thank you so much for your help..!


    1. I tried different permutations but it keeps giving me the same error over and over again for some reason. Can you please help.
      I tried editing it in notepad as well –
      [{WeekDays:[1,2,3,4,5], StartTime:”07:59:59″, StopTime:”16:59:59″, Sku:”S4″}]


      1. Hey Surya, it looks like it might be an issue with copying the ” character. I would delete those in both the script and your schedule and then try again (specifically fix the quotes on lines 54 and 55 for the script.)

        Liked by 2 people

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s