Pause Dedicated SQL Pools With Azure Synapse Pipelines

Introduction

One of the main objectives of any business that is using cloud services is to optimize resources and lower the on-going costs. Most of the organizations do need access to the data warehouse layer round the clock and they will be using reporting dashboards to view the information. In such scenarios, it is best to pause the dedicated SQL pool and resume it when necessary.

Now we can automate Pausing and Resuming the synapse dedicated pools to save cloud costs. We will demo this with a real-time example and we are going to use pipelines in azure synapse to perform this.

For this automated pause/resume demo, we are going to take simple exercise with simple steps. In reality you cannot have one size fits all approach hence you have to modify based on your requirement.

Below are the steps we are going to follow,

Creating a pipeline.
Setting up parameters in the pipeline.
List out the dedicated pools from the synapse workspace
Iterate into each dedicated SQL pool and…
- Check the state of the dedicated SQL pool.
- Evaluate the state of the dedicated SQL pool.
- Pause or resume the dedicated SQL pool.

Steps

Open the azure synapse studio and create a new pipeline from Integrate tab. I have named it as Pause or Resume SQL Pool for easy understanding.

Create a parameter enabled pipeline in the next step. Parameterized pipeline will help us to create reusable and generic pipeline which can be used across multiple subscriptions, resource groups or even dedicated SQL pools. There will be ‘Parameters’ tab at the bottom of the canvas, go ahead and click ‘+’ to add each parameters. In this demo, I am using the following parameters as I had stated earlier it is one’s choice based on their requirement to modify them.

Name	Description
ResourceGroup	Name of the resource group for your dedicated SQL pools
SubscriptionID	Subscription ID for your resource group
WorkspaceName	Name of your workspace
SQLPoolName	Name of your dedicated SQL pool
PauseorResume	The state to be at the end of the pipeline run
WaitSec	Wait time in seconds for the pipeline to finish
WaitSecRetry	Wait time in seconds for the retry process

All the parameters are of ‘String’ type except the two wait time fields which are specified in seconds.

Until Activity

A synapse SQL pool can be in multiple states namely Pausing, Resuming, Scaling, Paused, Online. In order to change the status the SQL pool has to be either in Paused or Online state hence we need to use the Until activity to start the pipeline. The Until activity starts a set of activities together in a loop until the condition of the activity yields true and we can validate the status of the SQL Pool and wait until it is turned into Paused or Online state.

Web Activity

Inside the Until activity we have to create a Web activity which will then be used to call a custom REST API endpoint from synapse data pipeline.

URL

https://management.azure.com/subscriptions/{subscription-id}/resourceGroups/{resource-group-name}/providers/Microsoft.Synapse/workspaces/{workspace-name}/sqlPools?api-version=2019-06-01-preview

We have to replace the above URL with the pipeline parameters that we created earlier similar to the below URL.

https://management.azure.com/subscriptions/@{pipeline().parameters.SubscriptionID}/resourceGroups/@{pipeline().parameters.Synapse_ResourceGroup}/providers/Microsoft.Synapse/workspaces/@{pipeline().parameters.WorkspaceName}/sqlPools/@{pipeline().parameters.SQLPoolName}/?api-version=2019-06-01-preview

Method: GET

Resource: https://management.azure.com/

Now move on to Until activity which we created earlier. We need to add the below expression which will check if the status is either paused or online.

Expression:

@or(bool(startswith(activity('PoolStatusCheck').Output.Properties.Status,'Paused')), bool(startswith(activity('PoolStatusCheck').Output.Properties.Status, 'Online')))

Timeout: 0:00:20:00

Since the activity can only continue when the above Web activity is Paused or Online we are adding a wait activity so that the Web activity doesn’t need to executed every time.

Wait Activity

Wait activity as name specifies waits for a period of time before continuing with execution of subsequent activities. Inside the wait activity we need to add expression from the parameters for wait time in seconds.

@pipeline().parameters.WaitSecRetry

I have used the above parameter in add dynamic content, you can modify as per the parameter you have named in your exercise.

Now to check if the SQL Pool is paused or running, we should add an If condition activity and insert the following expression which will basically check if the SQL Pool is paused or not.

@bool(startswith(activity('PoolStatusCheck').Output.Properties.status,'Paused'))

In our demo we want to pause the SQL pool hence we have to add an web activity for pausing to False, if in case the SQL pool is already paused we don’t action anything (True).

Use the following URL settings for the web activity, as already said modify the parameters to suit your exercise. It will be similar to the one we saw earlier except we are adding ‘Action’ option in the parameters.

https://management.azure.com/subscriptions/{pipeline().parameters.SubscriptionID}/resourceGroups/{pipeline().parameters.ResourceGroup}/providers/Microsoft.Synapse/workspaces/{pipeline().parameters.WorkspaceName}/sqlPools/{pipeline().parameters.SQLPoolName}/@{pipeline().parameters.PauseorResume}?api-version=2019-06-01-preview

Use the following in the ‘settings’ tab of the web activity.

Method: Post

Header: {“Nothing”:”Nothing”}

Resource: https://management.azure.com/

Next step is to add wait activity with the ‘WaitSec’ parameter we created at the beginning. The main function of this activity is to set a wait period before we start the data activity to make sure the SQL pool is online.

@pipeline().parameters.WaitSec

Now we can go ahead and run the pipeline to pause the SQL pool. When you try to execute you will be seeing the following parameter screen through which you can change the values depending upon your requirement and reuse the same pipeline multiple times.

Resume Pipeline

Resuming the SQL pool is very simple compared to all the steps we prepared above. You have to just Clone the pipeline and change the ‘PauseorResume’ parameter to ‘Online’ and Inside the IF condition change both the web activity Pause SQL pool and Wait activity from false to true.

Summary

In this article, we saw real-time demo of how to pause a dedicated SQL pool in azure synapse and how these parameters and even the whole pipeline can be cloned and reused for various pools, resource groups and even different environments. Hope this would have given an example on how synapse pipelines can be utilized very quickly and efficiently.

References

Microsoft azure synapse analytics official documentation