Pausing and resuming Dedicated SQL Pools from ADF

Case
Is there a solution to pause and resume an Azure Dedicated SQL Pool from the Azure Data Factory pipeline without scripting? I know there are PowerShell solutions, but I rather use a no-code solution. What are my options?

Pause and resume Azure Dedicated SQL Pools

Solution
Luckily you can now use the Rest API's of Azure Dedicated SQL Pools (formerly known as Azure SQL Data Warehouse and for a short period as Azure Synapse Analytics) to pause or resume the compute.

1) Give ADF Access to SQL Pool
To call the Rest API we need to give ADF access to the SQL Pool or more specific to the SQL Server hosting that SQL Pool. We need a role that can only change the database settings, but nothing security related: Contributor, SQL Server Contributor or  SQL DB Contributor. Choose the role with just enough permissions to perform the task and avoid the Owner role.

• Go to the Azure SQL Server of the SQL Pool that you want to pause or resume with ADF
• In the left menu click on Access control (IAM)
• Click on Add, Add role assignment
• In the 'Role' drop down select 'SQL DB Contributor'
• In the 'Assign access to' drop down select Data Factory
• Search for your Data Factory, select it and click on Save

Grant data factory SQL DB Contributor role to SQL Server

If you forget this step then you will receive an authorization error while executing your ADF pipeline.

2108 Authorization Failed

 

{"error":
{"code":"AuthorizationFailed"
,"message":"The client 'xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' with object id 'xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' does not have authorization to perform action 'Microsoft.Sql/servers/databases/resume/action' over scope '/subscriptions/xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/RG_bitools/providers/Microsoft.Sql/servers/SQL_bitools/databases/bitools' or the scope is invalid. If access was recently granted, please refresh your credentials."}
}

2) Determine URL

Now it is almost time to edit your ADF pipeline. The first step will be adding a Web activity to call the Rest API, but before we can do that we need to determine the URL of this API which you can find here.

Pause compute

https://management.azure.com/subscriptions/{subscription-id}/resourceGroups/{resource-group-name}/providers/Microsoft.Sql/servers/{server-name}/databases/{database-name}/pause?api-version=2014-04-01-preview

Resume compute

https://management.azure.com/subscriptions/{subscription-id}/resourceGroups/{resource-group-name}/providers/Microsoft.Sql/servers/{server-name}/databases/{database-name}/resume?api-version=2014-04-01-preview

Within these URL's you need to replace all parts that start and end with a curly bracket: {subscription-id}, {resource-group-name}, {server-name} and {database-name} (including the brackets themselves). Don't use a URL (bitools.database.windows.net) for the database server name, but use only the name: bitools.

Example URL

https://management.azure.com/subscriptions/aaaa-bbbb-1234-cccc/resourceGroups/RG_Bitools/providers/Microsoft.Sql/servers/bitools2/databases/bitools/resume?api-version=2014-04-01-preview

Example URL

3) Add Web Activity
To call the Rest API we will use the Web Activity in the ADF pipeline. The actual Rest API call is synchronous, which means it waits for it to finish the pause or resume action and then it will return a message. This also means that you don't have to build any checks to make sure it is already online.

• Add a Web activity to your pipeline and give it a suitable name
• Go to the Settings tab and use the URL from the previous step in the URL property
• Choose POST as method
• Fill in {} as body (we don't need it, but it is required)
• Choose MSI as authentication method
• As the last step enter this URL https://management.azure.com/ as Resource

Use a Web activity to call the Rest API

You could also first check the current status via the Check database state Rest API and then use an expression like @activity('Get Status').output.properties.status to retrieve the current state.

Summary

In this post you learned how to pause and resume your Dedicated SQL Pool to save some money on your Azure bill without writing any code. Note that you only pause the compute and that you still have to pay for the storage of the SQL Pool.

Also note that ADF pipelines slightly differ from Azure Synapse Analytics pipelines. So if you consider switching to Synapse workspaces because you apparently already use Dedicated SQL Pools then you have to make some small adjustments to this specific task which will be described in a next post. In an other post we will also show how to scale your Azure SQL Pools from within ADF.