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.
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.
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.
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
It creates Azure Run As account in your automation account. It is useful for authentication to different Azure services.
Click on Create, and it configures the automation account in the Azure portal.
Click on Go to resource to view automation account details.
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.
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.
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.
It starts the module import process. It takes 2-3 minutes for importing a module.
Once the module is installed and available, you can view its details such as modified date, version, size, cmdlets, and description.
Similarly, import the module Az.AnalysisServices in azure automation.
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
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.
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.
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
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.
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.
It adds the cmdlet in the runbook editor. Modify the Connect-AzAccount cmdlet with the following parameters.
|
1 2 3 4 5 |
Connect-AzAccount ` -ServicePrincipal ` -TenantId $servicePrincipalConnection.TenantId ` -ApplicationId $servicePrincipalConnection.ApplicationId ` -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint |
At this point, the azure automation PowerShell runbook should be able to connect with the Azure resources.
Now, we add another cmdlet Get-AzAnalysisServicesServer in the runbook editor using Add to Canvas option.
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.
|
1 2 3 |
$myazuressas= Get-AzAnalysisServicesServer ` -Name "azureanalysisservicedemo" ` -ResourceGroupName "azuressas" |
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.
|
1 2 3 4 5 6 7 8 |
if($myazuressas.state -eq "paused") { Write-host "Analysis service is paused state, Starting it" Resume-AzAnalysisServicesServer ` -Name "azureanalysisservicedemo" ` -ResourceGroupName "azuressas" } |
Before we test the runbook script, check the status of the azure analysis service. As shown below, it is in the paused state.
It’s time for testing the runbook execution. Click on Test Pane. It opens another window, click on Start to begin script execution.
Once the script finishes, it shows Completed status.
Refresh your console for the Azure analysis service. As shown below, the status changed from Paused to Active.
Once we have tested the runbook behavior, click on Publish.
It publishes the runbook with the scripts inside it. Click Yes to proceed.
You get the following notification once an azure runbook is published.
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.
It opens another window to schedule the runbook.
In the schedule, if you have an existing schedule satisfying your requirement, choose it or click on 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.
Click on Create, and it displays the schedule name in the below window.
If you have multiple schedules, you can 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].
In this runbook, paste the script from the previous runbook we configured for pausing the analysis service.
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
123456789if($myazuressas.state -eq "Succeeded"){Write-host "Analysis service is running, Pausing it"Suspend-AzAnalysisServicesServer `-Name "azureanalysisservicedemo" `-ResourceGroupName "azuressas"}
Save and publish the azure automation runbook
-
The initial state of azure analysis service:
Execute the runbook using Test Pane and verify the state of the azure analysis service
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
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023