This article is contributed. See the original author and article here.
As Synapse engineer or Synapse Support Engineer you may need to start and test some Pools, and you want this to be the most cost efficient possible. Leaving some Synapse with a lot of DWU left turned on during the weekend because you forget to pause the DW after you shutdown your computers is not a good approach and we can quickly resolve this by using Powershell + Automation accounts.
Before we get into the procedure on the automation, just want to show you some details.
We currently have two flavors of Synapse
- Dedicated SQL pools (formerly SQL DW)
- Old mode where the SQL DW lives in an Azure SQL DB Server that can be shared with regular Azure SQL databases.
- Internally is a resource of type (Microsoft.SQL)
- Sample: /subscriptions/xxxxxx/resourceGroups/yyyyyyy/providers/Microsoft.Sql/servers/yyyyyyyy/databases/olddwpool
- Azure Synapse Analytics – Dedicated SQL pool
- SQL DW database inside a workspace
- Internally is a resource of type (Microsoft.Synapse)
- Sample: /subscriptions/xxxxxx/resourceGroups/yyyyyyy/providers/Microsoft.Synapse/workspaces/yyyyyyyy/sqlPools/dwpool
This is important to know because we are going to use 2 different scripts to pause the pool
- Suspend-AzSynapseSqlPool (Az.Synapse)
- Suspend-AzSqlDatabase (Az.SQL)
There are 2 versions of the script
- Powershell to run from your machine
- Using Azure Automation Account
1. Powershell to run from your machine
This script you can run from your machine and you can get last version of script at ScriptCollection/Synapse – Pause all DWs.ps1 at master · FonsecaSergio/ScriptCollection · GitHub
You are going to need the modules installed
- Az.Accounts
- Az.Sql
- Az.Synapse
- Introducing the Azure Az PowerShell module | Microsoft Docs
- PowerShell Gallery | Az
- PowerShell Gallery | Az.Synapse
- At this point in time, the Az Module (5.3.0) does not include Az.Synapse by default. You need to install both
For below script I’m considering that your user is same that you use to admin your Azure Subscription
Find a sample below
Context exists
Current credential is sefonsec@microsoft.com
Current subscription is SEFONSEC Microsoft Azure Internal Consumption
—————————————————————————————————
Get SQL / Synapse RESOURCES
—————————————————————————————————
—————————————————————————————————
Loop through all Synapse Workspaces
—————————————————————————————————
Checking Azure Synapse Workspace [xxxxxxxxxxx_synapse] for Synapse SQL Pools
-> Synapse SQL Pool [dwpool] found with status [Online]
-> Pausing Synapse SQL Pool [dwpool]
-> Synapse SQL Pool [dwpool] paused in 0 hours, 2 minutes and 32 seconds. Current status [Paused]
—————————————————————————————————
Loop through all SQL Servers (former SQLDW)
—————————————————————————————————
Checking SQL Server [xxxxxxxxxxx-eastus] in Resource Group [CSSAzureDB_OtherRegions] for Synapse SQL Pools
Checking SQL Server [xxxxxxxxxxx-eastus2] in Resource Group [CSSAzureDB_OtherRegions] for Synapse SQL Pools
Checking SQL Server [xxxxxxxxxxx-northeu] in Resource Group [CSSAzureDB_OtherRegions] for Synapse SQL Pools
Checking SQL Server [xxxxxxxxxxx-southcentralus] in Resource Group [CSSAzureDB_OtherRegions] for Synapse SQL Pools
Checking SQL Server [xxxxxxxxxxx-uksouth] in Resource Group [CSSAzureDB_OtherRegions] for Synapse SQL Pools
Checking SQL Server [xxxxxxxxxxx-ukwest] in Resource Group [CSSAzureDB_OtherRegions] for Synapse SQL Pools
Checking SQL Server [xxxxxxxxxxx] in Resource Group [CSSAzureDB] for Synapse SQL Pools
-> Synapse SQL Pool [SQLDW] found with status [Paused]
Checking SQL Server [xxxxxxxxxxx-byok] in Resource Group [CSSAzureDB_OtherRegions] for Synapse SQL Pools
Checking SQL Server [xxxxxxxxxxx-demo] in Resource Group [CSSAzureDB_OtherRegions] for Synapse SQL Pools
Checking SQL Server [xxxxxxxxxxx_synapse] in Resource Group [synapseworkspace-managedrg-5da694c3-ae72-4f25-9cc6-626adcf858e6] for Synapse SQL Pools
-> This DB is part of Synapse Workspace – Ignore here Should be done above using Az.Synapse Module
Checking SQL Server [xxxxxxxxxxx-westeu] in Resource Group [CSSAzureDB_OtherRegions] for Synapse SQL Pools
Checking SQL Server [xxxxxxxxxxx-westus] in Resource Group [CSSAzureDB_OtherRegions] for Synapse SQL Pools
A sample with error
Checking Azure Synapse Workspace [xxxxxxxxxxx_synapse] for Synapse SQL Pools
Write-Error: -> Checking Synapse SQL Pool [dwpool] found with status [Resuming]
2. Using Azure Automation Account
Now we want this to be automated, like as a sample to shutdown every day at 11PM and send me alerts if error happens
You can find last version at: ScriptCollection/Synapse – Pause all DWs – Automation Acount.ps1 at master · FonsecaSergio/ScriptCollection · GitHub
1 – Lets first create the Automation Account
2 – Make sure to create a Run As Account
3 – By default it already got the contributor permission in the subscription level. You can change that if needed. Or add the any required permissions.
4 – You need to install the Az modules. Just go to modules and look for them in the gallery.
5 – Install first the Az.Account because it’s a prerequisite for others
Wait for it to complete
6 – Do same for 3 modules
- Az.Accounts
- Az.Sql
- Az.Synapse
7 – Now go to runbooks and create a new Powershell runbook
8 – Add code from ScriptCollection/Synapse – Pause all DWs – Automation Acount.ps1 at master · FonsecaSergio/ScriptCollection · GitHub
8.1 – Save and Publish. And click on Start to test it
9 – Check output tab
10 – You can now go to schedules and add a new schedule
11 – You may also want also to be alerted if schedule fail. Just to back to automation account and click on Alerts
12 – You need to add a condition when alert will fire and what actions will be taken. This action could be email to you or run some process or run another script
13 – First configure the condition. You are going to use the metric Total Jobs (Like total jobs failed)
14 – Define
– runbook name
– status (Add custom status “Failed“)
– Threshold > 0
– Run every hour
15 – Add now the action group. In this case, send email to me
You are now set. So your DW will be stopped at 11PM if some error happen you will be alerted like as a sample when a DW is in a state that could not be paused.
Another last tip is to use the Azure Budget control I got from my colleague @Gonçalo Ventura
Create a budget and alert
The budget puts a maximum limit on the cost of the subscription, in case some service is left running the subscription will automatically suspend when the budget is reached.
To avoid reaching the max limit and let the subscription go into suspended mode, it is possible to create an alert when the cost reaches a percentage of the budget.
To configure a budget, go to “Subscriptions” or “Cost Management + Billing”, then click on Budgets and fill in the parameters for your budget:
Click next and set an alert:
Because you do not have only Synapse in your subscription, you may have VMs, SQL DBs, etc.
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments