How To Pause And Resume SQL Pool From ADF

Image credit — https://www.keyivr.com/us/is-pause-and-resume-really-protecting-your-customers-data/

This article demonstrates how ADF can be used to pause and resume a Dedicated SQL pool (formerly SQL DW).

What do you do when your team does not need its dedicated SQL Pool? Do you keep it running? If yes, I will show you an interesting feature in Azure where you can pause the SQL Pool from your ADF Pipeline…..and then resume it whenever you need.

Use Case:
To be able to pause the SQL pool from an ADF pipeline and then resume it.

Requirement:
Quite often the ADF pipelines scheduled for the night push data into the Synapse but that data is not consumed until the morning. The ADF pipeline, thus, should be able to pause the SQL Pool for the night and resume it again in the morning.

Step 1: Add ADF to SQL Contributor Role

The ADF needs to have the required role to be able to pause or resume the SQL Pool. In order to do so, follow the steps below:

1. In the Azure portal, navigate to the SQL Server and click Access Control (IAM).

(Access Control- SQL Pool)

2. Click Add from the top and select Add role assignment.