Pausing and Resuming Synapse Dedicated SQL Pool with Data Factory

Costs are crucial aspects of every cloud project. One of the basic savings options available in Synapse Analytics is pausing and resuming. This activity can be done manually using a graphical user interface but also programmatically. In this article, I would like to show you how to do it via Data Factory/Integrated Pipelines and REST API.

First of all, let’s check what we can achieve from a graphical interface. You must find Synapse Analytics then go to SQL Pools:

On this screen, you can see all your Serverless and Dedicated SQL Pools (DSP) with their current status. DSP can have the following statuses:

Pausing – the pool is currently pausing – we cannot send pause or resume requests,
Resuming – the pool is currently resuming – we cannot send pause or resume requests,
Scaling – the pool is currently scaling up or down – we cannot send pause or resume requests,
Paused – the pool is currently paused – we cannot send pause but we can send resume request,
Online – the pool is currently running – we cannot send resume but we can send pause requests.

We can change status by choosing a specific pool:

On the above screen, I have the option to Resume (because my pool is paused), scale, and restore. If my pool will be Online then I will see here Pause. It is pretty simple but it is not what we are looking for. We want to have a solution that will automatically pause and resume. For that purpose, we will use Integrated Pipelines available in Synapse Workspace but this process will look exactly the same in Azure Data Factory. Our scenario is pretty simple: we want to perform pause if Dedicated SQL Pool is running or perform resume when DSP is paused. This scenario is simplified but in my opinion it will give you nice overview how it works.

To have the possibility to perform any activities on Synapse we have to add its own Managed Identity to IAM:

First of all we have to add pipeline that will perform action. We can do it on Integrate tab and click “+ -> Pipeline” as you can see on the below screenshoot:

The first task that we have to perform is to add a name of a pipeline – in my case it is PauseResumeSynapseDWH:

After that we can start building our pipeline – we will start by defining parameters:

subscriptionid – identifier of subscription,
resourcegroup – name of a resource group,
synapsedwhname – name of dedicated sql pool,
synapseworkspacename – name of synapse workspace.

Those values are needed to send proper REST API request and they are defined as parameters so it gives us possibility to pause and resume every Synapse instance. Additionally this information is present in every request so instead of hard-coding them it is good practice to use parameters.

Next to the parameters we have few variables that will help us sending requests:

__varStatusURL – REST API url that will check current DSP status,
__varResumeURL – REST API url that will resume DSP,
__varPauseURL – REST API url that will pause DSP,
__varOperationURL – __varResumeURL or __varPauseURL depends on which action to do,
__varStatus – status returned by REST API status that checked it.

Those variables will be used to build specifc request urls and hold some specific information like current DSP status. After that we can add three “Set variable” tasks. Those three tasks will assign to variables proper URLs:

Prepare Check Status task will assign the following value to the __varStatusURL variable:

@concat('https://management.azure.com/subscriptions/'
,pipeline().parameters.subscriptionid
,'/resourceGroups/'
,pipeline().parameters.resourcegroup
,'/providers/Microsoft.Synapse/workspaces/'
,pipeline().parameters.synapseworkspacename
,'/sqlPools/'
,pipeline().parameters.synapsedwhname
,'/?api-version=2021-06-01')

as you can see this URL has a pretty simple construction and every REST API request that we will use today will be built in a similar manner.

Prepare Resume URL task will assign the following value to the __varResumeURL variable:

@concat('https://management.azure.com/subscriptions/'
,pipeline().parameters.subscriptionid
,'/resourceGroups/'
,pipeline().parameters.resourcegroup
,'/providers/Microsoft.Synapse/workspaces/'
,pipeline().parameters.synapseworkspacename
,'/sqlPools/'
,pipeline().parameters.synapsedwhname
,'/resume?api-version=2021-06-01')

Prepare Pause URL task will assign value to the __varPauseUrl variable:

@concat('https://management.azure.com/subscriptions/'
,pipeline().parameters.subscriptionid
,'/resourceGroups/'
,pipeline().parameters.resourcegroup
,'/providers/Microsoft.Synapse/workspaces/'
,pipeline().parameters.synapseworkspacename
,'/sqlPools/'
,pipeline().parameters.synapsedwhname
,'/pause?api-version=2021-06-01')

So now we have assignments to variables. Next, we have to check the current status of the target Dedicated SQL Pool to know if we should pause or resume. The standard way to do it is to use Web activity so I added it as you can see below:

The configuration of this task is pretty straightforward – the only thing we have to set is:

URL – here we use __varStatusUrl,
Method – in this case it will be GET,
Authentication – System Assigned Managed Identity
Resource – https://management.azure.com/

The output of this task will provide many interesting things but the most important one is under node properties and is called status:

{
“properties”: {
“status”: “Paused”,
“maxSizeBytes”: 263882790666240,
“collation”: “SQL_Latin1_General_CP1_CI_AS”,
“creationDate”: “2022-06-19T14:46:10.85Z”,
“storageAccountType”: “GRS”,
“provisioningState”: “Succeeded”
},

Now when we know current state of DSP so we can assign this operation to __varOperationURL – we will do it with Set variable activity. Assignment expression looks like this:

@if(equals(activity('Check Synapse DWH Status').output.properties.status,'Paused')
,variables('__varResumeURL')
,variables('__varPauseURL')
)

Then we have another Web Activity that will send proper resume or pause request:

Inside this task we are using __varOperationURL assigned in previous step, method is POST for Authentication and Resource we are using same values as previously.

The last step is Until loop that will check if pausing/resuming action is already performed:

Configuration of this task looks like following:

@or(equals(variables('__varStatus'),'Paused')
,equals(variables('__varStatus'),'Online'))

Inside the loop we check the status every 10 seconds and assign this to variable used in the Until loop:

Checking and assigning status to the loop looks exactly the same like I showed above.

Below you can see the result how much time resume process took. Interesting is that Perform Operation Web Activty took 2:22, I don’t have answer but it looks that this request was send in a synchronous way!

Below you can see Pause operation log:

As you can see entire process is pretty simple to implement. Additional thing that I would propose to you to do is to add additional checks of current status that will include other statuses and to avoid problems with DSP that is already resuming or pausing. That is all for now, thanks!

Links: