Actionable Data Analytics
Join Our Email List for Data News Sent to Your Inbox

Azure Automation Account and Runbooks to Restart Synapse Analytics


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. 

Create an automation account

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. 

Create Azure Run As account

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: 

  • Az.Accounts 
  • Az.Sql 

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. 

Install Az.Accounts module

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 

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. 

Azure Run As Connection

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. 

Create Runbook  

Runbooks are the activities that you can execute as part of a routine. In this example, it’s Restarting Azure Synapse Analytics SQL Pools. 

Create Runbook

By default, your Azure Automation Account includes some Runbooks.  

Select “Create a runbook” 

Then, include a name and description. 

Page Break 

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: 

Get it from here

You will need to paste the scrip in the editor.

PowerShell Runbook script

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. 

Include parameter values in Runbook

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). 

Schedule Runbook 

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. 

Final Thoughts 

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. 

What’s Next? 

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.   

Check out my other blog posts

comment [ 1 ]
No tags 0
1 Response
  • angry_dba
    30 . 11 . 2022

    Hi David,

    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.

    Example error:
    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/’.

Do you want to leave a comment?

Your email address will not be published. Required fields are marked *