Pausing Dedicated SQL Pools with Synapse Pipelines

Do you forget to pause Azure resources? If you answered yes, you’re not alone. Implementing the process below will save time and money. A Microsoft article on the same subject inspired me to write this.

I would highly recommend reviewing the original. What follows is an attempt to simplify the process and focus only on pausing the dedicated SQL pool.

Step 1: Create a New Pipeline in Synapse Studio

1. First, navigate to your workspace and open Synapse Studio.
2. Click the Integrate icon, then select the + sign to create a new pipeline.
3. Name your pipeline PausePool.

Step 2: Create Pipeline Parameters

1. Select the Parameters tab near the bottom of the pipeline screen.
2. After that, select +New to create each of the following parameters:

Name	Type	Default Values
ResourceGroup	string	Name of your resource group
SubscriptionID	string	Subscription Id of your resource group
WorkspaceName	string	Name of your workspace
SQLPoolName	string	Name of your dedicated SQL pool

Step 3: Check the Dedicated SQL Pool State

1. Under Activities > General drag a Web activity to the pipeline canvas as the first stage of your pipeline.
2. In the General tab, name this stage Check State.

3. Select the Settings tab then click in the URL entry space, then select Add dynamic content.

4. Copy and paste the GET request that has been parameterized using the @concat string function below into the dynamic content box. Select Finish. The following code is a simple GET request: 

@concat('https://management.azure.com/subscriptions/',pipeline().parameters.SubscriptionID,'/resourceGroups/',pipeline().parameters.ResourceGroup,'/providers/Microsoft.Synapse/workspaces/',pipeline().parameters.WorkspaceName,'/sqlPools/',pipeline().parameters.SQLPoolName,'?api-version=2021-03-01')

5. Select the drop-down for Method and select GET.
6. Select Advanced to expand the content. Select Managed Identity as the Authentication type. For Resource enter https://management.azure.com/

Step 4: Add an If Condition to Pause the SQL Pool

1. Continuing, under Activities > Iteration & conditionals drag an If condition activity to the pipeline canvas as the second stage of your pipeline.
2. In the General tab, name this stage Pause If Online.

3. Select the Activates tab then click in the Expression entry space, then select Add dynamic content.

4. Copy and paste the expression below into the dynamic content box. Select Finish. The following code is an expression which returns True if the status is Online: 

@equals(activity('Check State').output.properties.status,'Online')

5. On the Activities tab, select the edit icon for the True case.

6. Under Activities > General drag a Web activity to the True activities canvas.
7. In the General tab, name this stage Pause

8. Copy and paste the POST request that has been parameterized using the @concat string function below into the dynamic content box. Select Finish. The following code is a simple Get request:

@concat('https://management.azure.com/subscriptions/',pipeline().parameters.SubscriptionID,'/resourceGroups/',pipeline().parameters.ResourceGroup,'/providers/Microsoft.Synapse/workspaces/',pipeline().parameters.WorkspaceName,'/sqlPools/',pipeline().parameters.SQLPoolName,'/pause?api-version=2021-03-01')

9. Select the drop-down for Method and select POST.
10. In the Body section type Pause.
11. Select Advanced to expand the content. Select Managed Identity as the Authentication type. For Resource enter https://management.azure.com/

12. Finally, You can run your pipeline by selecting Debug mode. After your debug is successful, you can add a trigger to schedule the pipeline execution.

Closing

Above all, I hope you found this article helpful. Would you mind letting me know of any comments or questions you have below?

If you run into issues or need help, please let me know.