Posted on Leave a comment

Pause automatically Dedicated SQL pool (formerly SQL DW) – REST api

I was struggling to make a dedicated sql pool pause automatically through a pipeline on non working hours. Although there are some options that you can use with az cli, it seemed that they could not work with an error that indicated resource could not be found.

az synapse sql pool | Microsoft Docs

Searching further I found that this action can be performed through the azure management API an example of which is shown below.

https://management.azure.com/subscriptions/a23eef11-f200-4722-866c-248ca45142f6/resourceGroups/sql-pool/providers/Microsoft.Sql/servers/geralexgr-sql/databases/geralexgr-sql/pause?api-version=2020-11-01-preview

In order to stop a dedicated sql pool (formerly SQL DW) you can follow the below guide that uses az cli to get an access token.

az account get-access-token

Add this token on Authorization tab with Bearer type and make your request.

By checking the result you can see that pause is completed sucesfully.

You can also automate the procedure using an Azure DevOps pipeline.

– task: AzureCLI@2
displayName: az cli stop command
inputs:
azureSubscription: 'YOURSUB'
scriptType: 'pscore'
scriptLocation: 'inlineScript'
inlineScript: |
$token = az account get-access-token | ConvertFrom-Json
$mytoken = $token.accesstoken
$headers = @{
Authorization = "Bearer $mytoken"
}
Invoke-RestMethod -Method Post -Uri "https://management.azure.com/subscriptions/xxxxx-xxxxx-xxxxx/resourceGroups/resource-group/providers/Microsoft.Sql/servers/sql-server/databases/database/pause?api-version=2020-11-01-preview" -Headers $headers -UseBasicParsing -ContentType 'application/json'

https://docs.microsoft.com/en-us/rest/api/sql/2021-02-01-preview/servers