Introduction
This blog post introduces a straightforward pipeline that allows you to pause and resume an SQL Pool using Synapse API POST and GET requests. The pipeline is triggered based on a schedule.
Purpose
The primary purpose behind creating this pipeline was to reduce costs for a client. Keeping an SQL pool running continuously can be expensive, so it made sense to have it offline during the night when it was not used during development. If needed, you can manually turn it back on.
How it Works and How to Replicate It
The pipeline consists of two main parts: a GET request to check the state of the SQL Pool and a switch case to pause or resume the Pool.
The pipeline also requires a set of input parameters:
- Workspace: [string]
- SubscriptionID: [string]
- SQLPool: [string] – [name of SQL pool]
- ResourceGroup: [string]
- PauseOrResume: [string] – [‘Pause’ to Pause pool or ‘Resume’ to Resume pool]
The first four parameters are used to create your API connection string for your GET and POST requests, while the last one is used to pause or resume the pool via the switch case.
Phase 1
- Create a web activity and let us name it ‘Check State’.
- Under Settings – URL, paste the following connection string:
@concat(‘https://management.azure.com/subscriptions/’,pipeline().parameters.SubscriptionID,’/resourceGroups/’,pipeline().parameters.ResourceGroup, ‘/providers/Microsoft.Synapse/workspaces/’,pipeline().parameters.Workspace,’/sqlPools/’,pipeline().parameters.SQLPool,’?api-version=2019-06-01-preview’)
- Settings – Method, select ‘GET’.
- Settings – Authentication, select ‘System Assigned Managed Identity’.
- Settings – Resource, enter ‘https://management.azure.com/’.
- Settings – Advanced – Integration runtime, select your IR.
This ‘Check State’ GET request will return the current state of the SQL Pool, either ‘Paused’ or ‘Online’. This value is then used in part 2 to check if a POST request is required for the desired outcome.
Phase 2
- Create a Switch activity and name it, for example, ‘State-PauseOrResume’.
- For the expression, paste the following:
@concat(activity(‘Check State’).output.properties.status, ‘-‘,pipeline().parameters.PauseORResume)
- This string concatenates the status of the SQL Pool and the input value of ‘PauseORResume’ with a ‘-‘ in between.
- Check State output status value + ‘-‘ + PauseOrResume value, for example Online-Pause or Paused-Resume
Add two cases to the switch and leave Default blank/empty.
- Case 1 – ‘Paused-Resume’
- Case 2 – ‘Online-Pause’
The case names should match the expression result. The idea is that if the trigger was to pause the SQL pool and it was already paused, then it does not send another pause request, and the same applies to the Resume request.
For Case 1, create a web request. Let us call it ‘POST Resume Pool’.
- Use the same settings from the ‘Check State’ web activity, except change the Method to POST.
- Also, change the URL to:
@concat(‘https://management.azure.com/subscriptions/’,pipeline().parameters.SubscriptionID,’/resourceGroups/’,pipeline().parameters.ResourceGroup, ‘/providers/Microsoft.Synapse/workspaces/’,pipeline().parameters.Workspace,’/sqlPools/’,activity(‘Check State’).output.name,’/resume?api-version=2019-06-01-preview’)
This POST request sends an API request to resume the SQL pool.
For Case 2, create another web activity equivalent to Case 1. Change the URL to:
@concat(‘https://management.azure.com/subscriptions/’,pipeline().parameters.SubscriptionID,’/resourceGroups/’,pipeline().parameters.ResourceGroup, ‘/providers/Microsoft.Synapse/workspaces/’,pipeline().parameters.Workspace,’/sqlPools/’,activity(‘Check State’).output.name,’/pause?api-version=2019-06-01-preview’)
This POST request sends an API request to pause the SQL pool.
Setting Up Triggers
After setting up the pipeline, the next step is to create triggers for when you want to pause/resume the pipeline. For this example, we will create two triggers:
Trigger 1 – EOD_Trigger: This trigger runs at the end of the day to pause the SQL pool.
- Name: EOD_Trigger
- Description: Trigger to run at the end of the day to pause the SQL pool
- Type: ScheduleTrigger
- Recurrence: Every 1, Week(s)
- Run of these Days: Mon, Tue, Wed, Thu, Fri
- Execute at these times: 18:00
- Status: Started
Trigger 2 – SOD_Trigger: This trigger runs at the start of the day to resume the SQL pool.
- Name: SOD_Trigger
- Description: Trigger to run at the start of the day to pause the SQL pool
- Type: ScheduleTrigger
- Recurrence: Every 1, Week(s)
- Run of these Days: Mon, Tue, Wed, Thu, Fri
- Execute at these times: 08:30
- Status: Started
Ensure to include your input parameters in each trigger, setting the ‘PauseOrResume’ parameter to ‘Pause’ for the EOD_Trigger and ‘Resume’ for the SOD_Trigger.
Once you have set up, validated and debugged the pipeline. Once it is working as expected, publish it.
Potential Improvements
While the current pipeline serves its purpose, there are a few potential improvements that suggested:
- SQLPool Parameter: Change the SQL Pool input parameter to a list and add a loop to cycle through each DB in the SQL pool. This would allow the pipeline to handle multiple databases.
- Switch Case Naming: The naming of the switch cases (1 and 2) could be improved for clarity. While this might seem minor, it could reduce the runtime of the pipeline, as API requests can take a while, and any unnecessary request will increase the runtime.
- Scripting: Consider moving this to a script written in Python. While this would require more work and technical knowledge, it might be easier to use when moving from project to project.
Conclusion
This blog post has provided a step-by-step guide on creating a Synapse pipeline to pause and resume an SQL Pool. The solution can be cost-effective, especially during development when the SQL pool is not used. This pipeline could be even more efficient and adaptable to different projects with the suggested improvements.
Thank you for reading.
