Recently we have been building a synapse data platform. One of the things you will often want to do is to pause your dedicated SQL pool in your non production environments as there are some significant potential cost savings.
How are you implementing this?
We decided that as we are using devops pipelines to do all of the deployments then using a devops pipeline to run a scheduled pipeline run to pause the sql pool at the end of the day was a very simple way to implement this. Also the service principal we have for deployment already has the necessary permissions to perform the actions.
To do this we created a pipeline which runs on a scheduled trigger at the end of the day. We then use an Azure CLI task in the pipeline like shown below.
We can then add the below powershell script in the step which will pause the dedicated SQL pool.
$sqlPoolName = '[Dedicated SQL Pool Name goes here]'
$resourceGroup = '[Resource Group Name goes here]'
$synapseWorkspaceName = '[Synapse Workspace Name goes here]'
#Pause the dedicated SQL pool
$status = az synapse sql pool show --query "status" --name $sqlPoolName --workspace-name $synapseWorkspaceName --resource-group $resourceGroup
write-Host 'Current Status: ' $status
if($status -eq '"Paused"'){
Write-Host 'SQL Pool is paused so skipping'
}
else{
Write-Host 'SQL Pool is running so pausing it'
az synapse sql pool pause --name $sqlPoolName --workspace-name $synapseWorkspaceName --resource-group $resourceGroup
}This was a quick 5 minute implementation to solve this problem and benefit from the cost savings of pausing your SQL pool when its not being used.
