Zahid's blog on Data Engineering and BI

Azure Analysis Service Replicas

Azure Analysis Service lets you scale out your queries very easily through replicas. Replicas are nothing but multiple copies of your Azure Analysis Service (AAS) database. In critical times, queries can run in replicas and reduce the load on Query Processing Unit (QPU). You can read more about Analysis Service replicas here.

If you want to configure replicas, follow the screenshots given below:

Step 1:

Select analysis services from Azure services in Azure portal

Step 2:

Select your analysis service instance:

Step 3:

Then select ‘Replicas’ and you will be able to configure replicas and sync them manually by clicking on ‘sync’ button.

Sync Replicas with Azure Automation

If you have replicas, you have to sync them every time you process your database. This post is mainly about automating this sync process with Azure Automation and PowerShell Runbooks. Following are the steps you need to follow to create a runbook that will sync Analysis Service replicas through PowerShell scripts:

STEP 1:

Create a Service Principal in Azure Active Directory. In case you are wondering what is a ‘Service Principal’, a service principal is a system/service account (not a personal one) that usually has elevated permissions or roles than the account used for automation activities like triggering jobs, executing scripts etc. You can follow this article to create a Service Principal in Azure Active Directory.

STEP 2:

Now you need to add the service principal created in first step to Analysis Service Administrator role. Easiest way to do is to use SSMS (SQL server Management Studio). Connect to your AS through SSMS. Right click on server name in server explorer tab, select Properties->Security. Then you will be probably asked to login to Azure Portal. Once you log in, you will see a search box where you can search your service principal by its name. Unfortunately, it is very unlikely you will get to see the service principal in search box. Don’t worry. You can still add it manually in ‘Manual Entry’ text box at the bottom:

In that text box, you will need to enter service principal name in following format:

app:service-principal-client-id@azure-ad-tenant-id
If you still struggle, pay close attention to the naming format shown in above diagram.

STEP 3:

Create a ‘Automation Account’ after clicking on ‘Automation Accounts’ service in Azure Portal. My automation account’s name is AASAutomation.

Click on the newly created account and get inside

Once you are inside of your automation account, click on a resource called ‘Modules’ and install the following four modules:

a)Az.Accounts
b)Az.AnalysisServices
c)Az.Resources
d)SQlServer

Click on ‘Credentials’ resource and create a new credential called ‘TestUser’

This new credential called TestUser will impersonate the Service Principal created before. Enter Application Id of the Service Principal into User Name* field and enter the value of the ‘secret’ of the service principal into Password * field (secret has two attributes: Secret Id and Secret Value. Use ‘value’ as password).

4. Now, come back to your Automation Account management UI and click on ‘Runbooks‘ in Process Automation section. Then create a Power Shell runbook

Once the Runbook (let’s call it ‘test’) is created, you will see a window like the following. Click on ‘Edit’ button:

Now, write down the following code snippet in ‘Edit’ mode:

Let me explain the code. First, we are declaring parameters (DB Name and Analysis Server full name with URL). Then, the program reads credentials from our credential called “TestUser” and stores user name and password (service principal app id and secret, remember?) found in TestUser credential in $_Credential variable.

Add-AzAnalysisServicesAccount command adds the credential into environment variable. Finally, Sync-AzAnalysisServicesInstance command sends command to sync replicas. Credentials saved in environment are used internally behind-the-scene.

Now, you SAVE and PUBLISH the runbook. And then you can run it by clicking ‘Start‘. You will be asked to enter parameters first.

You can schedule the runbook using ‘Schedule’ resource (highlighted in image). You will be able to save parameters when you schedule the runbook.

Successful runs will show you an output like the following screenshot:

You could also use personal account instead of a Service Principal. In that case, you need to use the user name and the password of the personal account inside the credential called TestUser. And you need to remove the -ServicePrincipal parameter from Add-AzAnalysisServicesAccount command.

Have a nice day.. 🙂