Automate overnight and weekend pause and Resume in Azure Analysis Service

We have an Analysis Service on our Training Subscription and resource group and obviously its only being used within office hours as its only a training AS

the last thing we want is to have someone remembering to pause the service every night. As soon as you forget to do this you end up paying for the service when you don’t need it.

Clearly, this needs automating. There are lots of how to’s on line for this so lets see how easy this is to set up

What you need

• Azure account
• Azure Analysis Services Model

Create an Automation Account

You need an Azure Automation account to run an Azure runbook with powershell code

Search for Automation Accounts

Make sure Create Azure Run as Account is Yes

Set up Credentials

Runbook credentials are very similar to Azure SQL Server credentials. in your new Automation Account click on Credentials

Ive set up a new account for azure_trainingmantenance with the same user name and and a password that I have saved for later.

Connections

to see all your information like tenant ID and Subscription ID simply click on Connections

these fields can be added to the Powershell code later

Add Modules

All the Analysis Services methods (cmdlets) are in Powershell modules and will need to be installed if they are not already.

Go to modules and check AzureRM.Profile is available

If not then we need to add it. Go to Browse Modules Gallery

it takes a while for this to import. You need AzureRM.Profile for the next on AzureRM.AnalysisServices. repeat the process. In modules search. then go to Modules gallery, Search and Import

You now have the cmdlet for Analysis Services within the new Automation Account

Create a RunBook

click on Runbooks and add new RunBook

Now its time to add the Script to set up the Automation

I have used the code from GitHub https://github.com/DevScope/ssas-powershell-modules/blob/master/AzureAS.AutoScheduler.ps1

Paste the code into the RunBook Window

These are the following parameters to set

• param( [string] $resourceGroupName = “RGName ” ,    
• [string] $serverName = ” awas “,     (Note asazure://uksouth.asazure.windows.net/ from the full Analysis Services server path needs removing)
• [string] $azureProfilePath  = “”,    
• [string] $azureRunAsConnectionName = “AzureRunAsConnection”,    
• [string] $configStr = ”

You can get your Servername by going to Analysis Services in Azure and copying the Server name (This RunBook will pause and unpause this specific Analysis Service)

Another important part of the code is sorting out weekdays and weekends according to the SKU (Stock Keeping Unit)

Our training Analysis Service is in SKU S0 (Standard 40 query processing units) and we only need it running in the week

{ 

WeekDays:[1,2,3,4,5]            ,StartTime: “”08:30:00″” ,

StopTime: “”17:59:59″”        ,Sku: “”S0″”    

}  

  

Note, you could also use the code to scale your Analysis Services up and down to keep down costs. S1 has more processing units for peak hours and is more expensive. S0 with less Processing units could be for off peak hours. In this case you could set the code to not only set times to Pause and Un pause but to add and remove processing units

{ 

WeekDays:[1,2,3,4,5]            ,StartTime: “”08:00:00″” ,

StopTime: “”17:59:59″”        ,Sku: “”S1″”    

}  ,    

{

WeekDays:[1,2,3,4,5]            ,StartTime: “”18:00:00″” ,

StopTime: “”23:59:59″”        ,Sku: “”S0″”    

} ,    

{

WeekDays:[6, 0]         ,StartTime: “”08:00:00″” ,

StopTime: “”23:59:59″”        ,Sku: “”S0″” 

}    

Test Runbook

Whilst in Edit mode you can also test your Runbook

If there are any issues, your test will flag these up. My first test brought up the fact that I had entered the wrong Resource group name

Go back to your Edit PowerShell Runbook using the breadcrumb trail (You don’t need to save the test)

Save and Publish your RunBook

Create a schedule

Azure Automation is enabled to schedule a run book one time or a reocurring schedule

I believe that in this instance we just need the scheduler to kick off our Runbook which contains all the code required to pause and restart the Analysis Service

Starts today

the schedule is now linked to the runBook

Lets see it in Action

the RunBook is now ready to go. Currently the training Analysis Services is Off, Once the Schedule kicks in will this turn everything back on again?

The Run book is Successful.

After 6 pm the Analysis Service is paused and when I get back into the office at 8.30 the server has restarted.

Success