I want to schedule a pause of my Azure Synapse SQL Pools to save some money on my Azure bills. Back in 2017 when Synapse was still called SQL Data Warehouse we wrote a post with the AzureRM modules which are now outdated. How does it work with AZ?
 |
| Pause and resume Synapse SQL Pools |
Solution
For this example we will have two scripts. The first to stop all Azure Synapse SQL Pools within an Azure subscription. This is especially handy for Development, Test or Acceptance environments where you only turn on the Synapse SQL Pools when you need to develop or test something. When you forget to pause them afterwards this script will pause them all on a scheduled moment. The second script is for pausing (or resuming) a specific Synapse SQL Pool. Probably more suitable for a production environment where you don't want to ruthlessly pause all Synapse SQL Pools.
Note: All basics to create your first Azure Automation Runbook can be found here. Combine that with the code below.
1) Modules
1) Modules
If you want to use this script in an Azure Automation Runbook you first need to add the module Az.Sql and that module first wants the module Az.Accounts to be installed. If you already added AzureRM modules to your Azure Automation account then that is no problem. As long as you only don't mix them up in your runbooks. On your own PC you cannot have both installed. You first need to remove all AzureRm modules before you can add Az modules.
- Go to your Azure Automation account in the Azure Portal
- Click on Modules in the left menu
- Click on Browse Gallery (not on Add a module)
- Search for Az.Sql and click on it
- Next click on Import and then on the Ok button
Note 1: If you haven't installed Az.Account then it will ask you to do that first.
Note 2: It takes a few minutes to import a module
 |
| Adding a module |
2) Login
If you want to run the script in PowerShell ISE then you first need to login and select your subscription if you have multiple subscriptions.# PowerShell code # Login to Azure (browser popup will appear) Connect-AzAccount -Confirm # Optional: select your subscription Set-AzContext -SubscriptionName "mysubscription"
 |
| Login to Azure with PowerShell ISE |
# PowerShell code
########################################################
# Log in to Azure with AZ (standard code)
########################################################
Write-Verbose -Message 'Connecting to Azure'
# Name of the Azure Run As connection
$ConnectionName = 'AzureRunAsConnection'
try
{
# Get the connection properties
$ServicePrincipalConnection = Get-AutomationConnection -Name $ConnectionName
'Log in to Azure...'
$null = Connect-AzAccount `
-ServicePrincipal `
-TenantId $ServicePrincipalConnection.TenantId `
-ApplicationId $ServicePrincipalConnection.ApplicationId `
-CertificateThumbprint $ServicePrincipalConnection.CertificateThumbprint
}
catch
{
if (!$ServicePrincipalConnection)
{
# You forgot to turn on 'Create Azure Run As account'
$ErrorMessage = "Connection $ConnectionName not found."
throw $ErrorMessage
}
else
{
# Something else went wrong
Write-Error -Message $_.Exception.Message
throw $_.Exception
}
}
########################################################
Below the above script we need to add the following script which first gets all Azure SQL Servers and then checks whether they also host a Synapse SQL Pool. When one is found it checks the status. If it is still Online (active) then it will pause it. This will take several minutes. Afterwards it will recheck the status and show how long it took to pause. Feel free to add more checks and let us know in the comments what you added.
# PowerShell code
# Get all SQL Servers to check whether they host a Synapse SQL Pool
$allSqlServers = Get-AzSqlServer
# Loop through all SQL Servers
foreach ($sqlServer in $allSqlServers)
{
# Log which SQL Servers are checked and in which resource group
Write-Output "Checking SQL Server [$($sqlServer.ServerName)] in Resource Group [$($sqlServer.ResourceGroupName)] for Synapse SQL Pools"
# Get all databases from a SQL Server, but filter on Edition = "DataWarehouse"
$allSynapseSqlPools = Get-AzSqlDatabase -ResourceGroupName $sqlServer.ResourceGroupName `
-ServerName $sqlServer.ServerName `
| Where-Object {$_.Edition -eq "DataWarehouse"}
# Loop through each found Synapse SQL Pool
foreach ($synapseSqlPool in $allSynapseSqlPools)
{
# Show status of found Synapse SQL Pool
# Available statuses: Online Paused Pausing Resuming
Write-Output "Synapse SQL Pool [$($synapseSqlPool.DatabaseName)] found with status [$($synapseSqlPool.Status)]"
# If status is online then pause Synapse SQL Pool
if ($synapseSqlPool.Status -eq "Online")
{
# Pause Synapse SQL Pool
$startTimePause = Get-Date
Write-Output "Pausing Synapse SQL Pool [$($synapseSqlPool.DatabaseName)]"
$resultsynapseSqlPool = $synapseSqlPool | Suspend-AzSqlDatabase
# Show that the Synapse SQL Pool has been pause and how long it took
$endTimePause = Get-Date
$durationPause = NEW-TIMESPAN –Start $startTimePause –End $endTimePause
$synapseSqlPool = Get-AzSqlDatabase -ResourceGroupName $sqlServer.ResourceGroupName `
-ServerName $sqlServer.ServerName `
-DatabaseName $synapseSqlPool.DatabaseName
Write-Output "Synapse SQL Pool [$($synapseSqlPool.DatabaseName)] paused in $($durationPause.Hours) hours, $($durationPause.Minutes) minutes and $($durationPause.Seconds) seconds. Current status [$($synapseSqlPool.Status)]"
}
}
}
In PowerShell ISE the result will look this. To see the result of your runbook you need to check the jobs and the the output. |
| Running the script in PowerShell ISE |
4) The pause one script - parameters
To only select one Synapse SQL Pool we need to provide three parameters: Resource Group name, SQL Server name and the name of the SQL Pool. For this we will add parameter code at the beginning of the complete script (above login). You can add parameter validations to make it more monkey proof.
# PowerShell code
<#
.SYNOPSIS
Pause an Azure Synapse SQL Pool
.DESCRIPTION
By providing the following parameters you can pause one
specific Azure Synapse SQL Pool. It will only pause when
the status is 'online'
.PARAMETER resourceGroupName
This is the Resource group where Azure Synapse Analytics
SQL Pool is located
.PARAMETER sqlServerName
This is the name of the Azure SQL Server hosting the Azure
Synapse Analytics SQL Pool
.PARAMETER SynapseSqlPoolName
This is the name of the Azure Synapse Analytics SQL Pool
#>
Param(
# This is the Resource group where Azure Synapse Analytics SQL Pool is located
[Parameter(Mandatory=$True)]
[String] $resourceGroupName
,
# This is the name of the Azure SQL Server hosting the Azure Synapse Analytics SQL Pool
[Parameter(Mandatory=$True)]
[String] $sqlServerName
,
# This is the name of the Azure Synapse Analytics SQL Pool
[Parameter(Mandatory=$True)]
[String] $SynapseSqlPoolName
)
5) The pause one script
Now the actual pause one script to pause only one SQL Pool. This replaces the script of step 3. If you also want a resume script you just have to replace Pause-AzSqlDatabase by Resume-AzSqlDatabase. and of course chancing some of the texts and if statement (Online => Paused). You could also merge the pause and resume script into one script by adding an extra parameter to indicate what you want to do.
# PowerShell code
# Get one specific Synapse SQL Pool
$synapseSqlPool = Get-AzSqlDatabase -ResourceGroupName $resourceGroupName `
-ServerName $sqlServerName `
-DatabaseName $SynapseSqlPoolName `
| Where-Object {$_.Edition -eq "DataWarehouse"}
# Check if the Synapse SQL Pool can be found with the provided parameters
if ($synapseSqlPool)
{
# Show status of found Synapse SQL Pool
# Available statuses: Online Paused Pausing Resuming
Write-Output "Synapse SQL Pool [$($synapseSqlPool.DatabaseName)] found with status [$($synapseSqlPool.Status)]"
# If status is online then pause Synapse SQL Pool
if ($synapseSqlPool.Status -eq "Online")
{
# Pause Synapse SQL Pool
$startTimePause = Get-Date
Write-Output "Pausing Synapse SQL Pool [$($synapseSqlPool.DatabaseName)]"
$resultsynapseSqlPool = $synapseSqlPool | Suspend-AzSqlDatabase
# Show that the Synapse SQL Pool has been pause and how long it took
$endTimePause = Get-Date
$durationPause = NEW-TIMESPAN –Start $startTimePause –End $endTimePause
$synapseSqlPool = Get-AzSqlDatabase -ResourceGroupName $resourceGroupName `
-ServerName $sqlServerName `
-DatabaseName $SynapseSqlPoolName
Write-Output "Synapse SQL Pool [$($synapseSqlPool.DatabaseName)] paused in $($durationPause.Hours) hours, $($durationPause.Minutes) minutes and $($durationPause.Seconds) seconds. Current status [$($synapseSqlPool.Status)]"
}
}
else
{
Throw "Synapse SQL Pool [$($SynapseSqlPoolName)] not found. Check parameter values."
}
 |
| Running the script in PowerShell ISE |
Summary
In this post you saw how to pause one or more Synapse SQL Pools to save some money on your Azure bill, but note that the storage costs will continue when you pause Synapse. Next step is to either schedule it within your Azure Automation account or to add a Webhook and execute it from an other service like Azure Data Factory
Besides scripting you can also use the Rest API of Synapse which is particularly easy if you want to pause or resume from within an ADF pipeline, but that will be explored in an other blogpost.
This script is not more working. It is giving error of deny assignments while pausing the SQL Pools.
ReplyDeleteThis is because its using out-of-date Powershell cmdlets. If you use the Az.Synapse module instead you won't get the deny assignments error. There's an example here: https://github.com/FonsecaSergio/ScriptCollection/blob/master/Powershell/Synapse%20-%20Pause%20all%20DWs%20-%20Automation%20Acount.ps1
Delete