Rajendra Gupta

Azure Automation: Automate Pause and Resume of Azure Analysis Services

April 20, 2021 by

This article explores Azure Automation for the change state of Azure Analysis Services.

Introduction

Azure Analysis Services (AAS) is a PaaS cloud infrastructure solution for implementing tabular data models similar to an on-premises SQL Server analysis service. In the article, Azure Analysis Services and Power BI Live connections, we implemented an Azure SSAS instance and data models.

  • Sample [adventureworks] model from the Azure portal
  • We created a data model in Visual Studio 2019

Azure uses an hourly rate for analysis services. Its cost depends upon service tier, performance level and hours of usage. Therefore, you should pause the analysis service if it is not in use. For example, if you do not use the analysis service on weekends or night hours, you can pause it and resume it before using it again. As an Azure administrator, it becomes tedious to perform this task manually. You might also forget to do it, and it might incur costs for you.

You can refer to Azure Analysis Services pricing for more details.

Azure Analysis Services pricing

We can quickly automate this using the Microsoft Azure Automation Service and Runbooks. It allows you to automate repetitive tasks. You can do process automation, configuration management, deployment and decommission of azure resources.

In the below image, we get an overview of the Azure Automation process.

Azure Automation process

In this tip, we will do the following tasks:

  • Configure Azure Automation account
  • Import Modules
  • Create runbooks for automatic start and stop azure analysis service
  • Schedule runbooks

Pre-requisite

In this article, I assume

  • You have an active Azure Analysis Service instance
  • You have administrative permissions on the Azure portal

Configure Azure Automation account

In the Azure portal, search for automation accounts. It launches an automation dashboard. Right now, we don’t have any accounts configured. Therefore, it does not display any information.

Automation account

Click on Create automation account. In the next screen, we specify the following user inputs.

  • Azure automation account name
  • Select your subscription
  • Create a new Azure resource group or select the existing group
  • Location

Create Automation account.

It creates Azure Run As account in your automation account. It is useful for authentication to different Azure services.

Azure Run As account

Click on Create, and it configures the automation account in the Azure portal.

configures the automation account

Click on Go to resource to view automation account details.

Go to resource

Import PowerShell modules

By default, Azure installs a few PowerShell modules for automation. We can import additional modules from the Browse gallery option. Navigate to Shared resources from the left-hand menu and view the currently installed modules in Azure.

Import PowerShell modules

We require two additional modules for working with automation scripts in this article.

  • Az.Accounts: It contains a PowerShell cmdlet for Azure account credentials management
  • Az.AnalysisServices: It has a PowerShell cmdlet for working with AAS

To import a module, click on Browse Gallery and search for a specific module.

Browse Gallery

Open the module to view its brief description, the hyperlink for useful resources and a list of cmdlets. Click on Import to install it in your environment.

hyperlink for useful resources

It starts the module import process. It takes 2-3 minutes for importing a module.

import process

Once the module is installed and available, you can view its details such as modified date, version, size, cmdlets, and description.

modified date, version

Similarly, import the module Az.AnalysisServices in azure automation.

import the module Az.AnalysisServices

Module is in available state

Create an Azure Automation runbook to resume AAS service

Azure automation runbook defines step-by-step procedures for performing administrative tasks. We can include tasks such as resource deployment, management, server patching, database maintenance. Click on the Runbooks in the left-hand side menu, and it shows you the following runbooks:

  • Graphical Runbook
  • Python 2 Runbook
  • PowerShell Runbook

runbook to resume AAS service

In this article, we will work with the PowerShell cmdlet. Therefore, we require PowerShell Runbook. Click on PowerShell Runbook, and it gives you a sample automation script as shown below.

View script

Copy the PowerShell script and paste it into the Visual Studio editor. We will modify the copied script for resource deployments. Now, click on Create a runbook.

Create a runbook

In the new runbook window, enter the following details:

  • Azure automation runbook name
  • Runbook type: Select PowerShell for this tutorial
  • Description: Enter the description to define the purpose of this runbook

new runbook window

It creates the [azuressasrume] automation runbook. Edit the runbook and paste the PowerShell code we copied from the sample runbook. On the left-hand side, you can view PowerShell modules and cmdlets inside it. Delete the highlighted scripts from the runbook.

Delete the highlighted scripts

In the sample script, we have cmdlet Add-AzureRmAccount. We will replace it with the Connect-AzAccount cmdlet. Expand the Connect-AzAccount cmdlet, click on three dots and Add to the canvas.

 cmdlet Add-AzureRmAccount

It adds the cmdlet in the runbook editor. Modify the Connect-AzAccount cmdlet with the following parameters.

At this point, the azure automation PowerShell runbook should be able to connect with the Azure resources.

Modify the Connect-AzAccount cmdlet

Now, we add another cmdlet Get-AzAnalysisServicesServer in the runbook editor using Add to Canvas option.

add another cmdlet Get-AzAnalysisServicesServer

Remove the default arguments for the Get-AzAnalysisServicesServer cmdlet and replace it with the following:

  • Name: It is the AAS instance name
  • ResourceGroupName: It is the resource group name in which the azure ssas exists

We capture the result of Get-AzAnalysisServicesServer into the variable $myazuressas.

Remove the default arguments

View script

We need to write a logic for resuming the Azure analysis service if it is in the Paused state. We can write the logic quickly using the if condition. In the below PowerShell script, we do the following:

  • Check if the AAS state is paused
  • If the above condition is true, the script uses another Resume-AzAnalysisServicesServer cmdlet for resuming the azure ssas.

logic for resuming Azure Analysis

Before we test the runbook script, check the status of the azure analysis service. As shown below, it is in the paused state.

test the runbook script

It’s time for testing the runbook execution. Click on Test Pane. It opens another window, click on Start to begin script execution.

Click on Test Pane

Once the script finishes, it shows Completed status.

Completed status

Refresh your console for the Azure analysis service. As shown below, the status changed from Paused to Active.

Refresh your console

Once we have tested the runbook behavior, click on Publish.

click on Publish

It publishes the runbook with the scripts inside it. Click Yes to proceed.

Click Yes to proceed

You get the following notification once an azure runbook is published.

notification

Schedule the runbook

Once an azure runbook is published, we can link it to an existing schedule or create a new schedule to run on a specified schedule automatically. Click on Link to Schedule.

Link to Schedule

It opens another window to schedule the runbook.

Schedule Runbook

In the schedule, if you have an existing schedule satisfying your requirement, choose it or click on Create a new schedule.

Create a new schedule

In the new schedule configuration, enter the name, description, and specify the frequency, time, time zone, end date etc. In the below screenshot, we set the runbook to run every Monday at 10 AM IST. It means that every Monday, 10 AM IST analysis service will be resumed if it is in the paused state.

set the runbook schedule

Click on Create, and it displays the schedule name in the below window.

displays the schedule name

If you have multiple schedules, you can click on Schedules and manage it.

click on Schedules and manage it

Create an Azure Automation Runbook to pause azure analysis service

Create another azure runbook to pause the analysis service on its specific schedule. Here, we create another runbook [PauseAzureAnalysisService].

Automation Runbook to pause azure analysis

In this runbook, paste the script from the previous runbook we configured for pausing the analysis service.

copy the script

In the runbook, replace the IF block with the following script.

  • It first checks the azure analysis service status. If it is succeeded, it goes inside the if block
  • The script uses the Suspend-AzAnalysisServicesServer cmdlet and suspends the analysis service

    Edit PowerShell

    Save and publish the azure automation runbook

  • The initial state of azure analysis service:

    View status

  • Execute the runbook using Test Pane and verify the state of the azure analysis service

    Execute the runbook using Test Pane

You can schedule this runbook as per your requirement.

Conclusion

In this article, we explored the azure runbooks for automating the process of pause and resume azure analysis service. It is an excellent way to save cost and minimize manual administrative work.

Table of contents

Azure Automation: Export Azure SQL Database to Blob Storage in a BACPAC file
Azure Automation: Create database copies of Azure SQL Database
Azure Automation: Automate Azure SQL Database indexes and statistics maintenance
Azure Automation: Automate Pause and Resume of Azure Analysis Services
Azure Automation: Automate data loading from email attachments using Azure Logic Apps
Azure Automation: Building approval-based automated workflows using Azure Logic Apps
Azure Automation: Auto-scaling Azure SQL database with Azure Logic Apps
Azure Automation: Analyzing Twitter sentiments using Azure Logic Apps
Azure Automation: Use Azure Logic Apps to import data into Azure SQL Database from Azure Blob Storage
Azure Automation: Publish LinkedIn posts and tweets automatically using Azure Logic Apps
Azure Automation: Export Azure SQL database data to CSV files using Azure Logic Apps
Azure Automation: Translate documents into different languages using Cognitive Services with Azure Logic Apps
Azure Automation: Azure Logic Apps for face recognition and insert its data into Azure SQL Database
Azure Automation: Automatic scaling Azure SQL databases based on CPU usage threshold

Rajendra Gupta
Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

2,317 Views