Today, I’m going to show you how to schedule the restart/pause/start of your Azure Synapse Analytics database. You will use an Azure Automation Account and Runbooks.
In my previous blog post, you were able to use a PowerShell to restart Azure Synapse Analytics, but today we’ll use Azure Automation Accounts and Runbooks to schedule this activity.
Create an Azure Automation Account
To begin, automation accounts allow you to automate maintenance activities within the Azure environment. Hardcoding credentials within external PowerShell scripts aren’t necessary.
First, let’s create one.
Next, once you find the service, select “Add” to create a new one.
The configuration isn’t very complex, but you want to select the option for the service to create the Run As account for you as well. This we you don’t need to do it manually.
Once the creation has finished, the service will show different options.
Next up, create a Runbook.
Configure Automation Account
For your PowerShell script, use the new Az modules to manage the Azure environment. You need 2 different modules:
You can also install “Az” because it includes all the modules, but I recommend only installing what is required. In this way, you can learn about the different modules without adding too much overhead to the Runbook.
Install Az.Accounts module
You can easily find the module in the gallery.
Then, import the Az.Accounts module.
Importing the module takes a few minutes.
Now, repeat the same process for the Az.Sql module.
Install Az.Sql module
Do you remember the option that you selected to create the Run-As-Account automatically? You can see it in the following sections.
It also includes giving Contributor at the Subscription level. If you want to further lock down access , you can create the account manually and give access to different services.
Runbooks are the activities that you can execute as part of a routine. In this example, it’s Restarting Azure Synapse Analytics SQL Pools.
By default, your Azure Automation Account includes some Runbooks.
Select “Create a runbook”
Then, include a name and description.
Now you are ready to include the logic in the script.
Include Script and Schedule Restart
You can download the PowerShell script that you need to copy from:
You will need to paste the scrip in the editor.
Uncomment the following lines.
So it looks like the following picture.
By using the Test Pane, it’s possible to debug the script before publishing it.
Next, publish the script.
Now, let’s run it manually.
Include the parameter values.
A new window will open with the execution status.
You can see the output here.
As you can see, the Synapse Analytics has restarted (pause/start).
You can attach your runbook to a schedule by using the following option.
You can create one by following the next options.
Defining the time zone is one of the options within the schedule.
And you can finally define the parameters. The same script will allow you to use the operations Pause and Start separately.
Now, you can save the schedule
Verifying the schedule.
And you are all set!
The next schedule will kick off the restart operation.
In summary, by following the steps above, you can schedule the restart of your Azure Synapse Analytics SQL Pool on a different time basis using Automation Accounts and Runbooks.
Finally, Azure Automation Accounts and Runbooks are useful to schedule routines for maintenance activities. Not only can you manage Azure Synapse Analytics, but it’s also possible to manage all your Azure services.
In upcoming blog posts, we’ll continue to explore some of the features within Azure Services.
Please follow Tech Talk Corner on Twitter for blog updates, virtual presentations, and more!
As always, please leave any comments or questions below.
angry_dba30 . 11 . 2022
Have you been able to get this work for the “new” version of Synapse where the resources are in a Microsoft managed RG? There are explicit DENY permissions on this Microsoft managed RG (where the SQL pool and logical SQL server are located) that prevent a runbook from performing any actions on it.
DenyAssignmentAuthorizationFailed: The client ‘4a6ac1a9-2f2a-471b-830a-783e5db81de3’ with object id ‘4a6ac1a9-2f2a-471b-830a-783e5db81de3’ has permission to perform action ‘Microsoft.Sql/servers/databases/pause/action’ on scope ‘/subscriptions//resourceGroups/-rg/providers/Microsoft.Sql/servers//databases/synapsesqlpool’;
however, the access is denied because of the deny assignment with name ’15e94f5e-5096-4475-9201-c5d33f8d72be’ and Id ’15e94f5e509644759201c5d33f8d72be’ at scope ‘/subscriptions//resourceGroups/’.