February 16, 2022 at 1:37 pm
Hi All,
How do I pause and resume a Synapse dedicated SQL pool automatically? If it is in ideal state can I capture that and pause synapse dedicated SQL Pool? Could you please suggest.
Thanks in Advance!!
Regards, Ashok Kumar
February 17, 2022 at 2:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 15, 2022 at 6:28 pm
You can do this through Azure Automation with a PowerShell run book. You will need to register the Synapse module in Azure automation before you create this as well as your "Run as" account. Here is some code that may help you that will connect to your subscription:
Firstly create a runbook called AzureConnectRunAsAccount
# AzureConnectRunAsAccount
# Publish This runbook first
#Connect to Azure using the Run As Account
Try{
$servicePrincipalConnection=Get-AutomationConnection -Name "AzureRunAsConnection"
Connect-AzAccount -ServicePrincipal -TenantId $servicePrincipalConnection.TenantId -ApplicationId $servicePrincipalConnection.ApplicationId -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint
Write-Output "Run as account connected"
}
Catch {
if (!$servicePrincipalConnection){
$ErrorMessage = "Connection $connectionName not found."
throw $ErrorMessage
} else{
Write-Output -Message $_.Exception
throw $_.Exception
}
}
#######################################################################
# Get Security and Subscription Context Info
#######################################################################
$Context = Get-AzContext
if ($Context -eq $null) {
Write-Output "Need to login"
Connect-AzAccount -Subscription $SubscriptionName
}
else {
Write-Output "Context exists"
Write-Output "Current credential is $($Context.Account.Id)"
Write-Output "Current subscription is $($Context.Subscription.Name)"
}
Once you publish this, then create another runbook called AzureSynapsePauseAllPools:
<#
.History
7/23/2021 - 1.0 - Initial release - Alan Eager
.Synopsis
Pauses all Synapse Analytics SQL Pools in a Specified Subscription
.Description
Loops through all Azure Synapse Servers and pauses the pools for each to save money while not used
#>
[CmdletBinding()]
param (
[Parameter(Mandatory=$false)]
[string]$SubscriptionName ="ingevity - data platform - co-managed"
)
. .\AzureConnectRunAsAccount.ps1
Write-Output "Connecting on $(Get-Date)"
#######################################################################
# If you are using an Automation Account, uncomment the following lines
#######################################################################
<#
#Connect to Azure using the Run As Account
Try{
$servicePrincipalConnection=Get-AutomationConnection -Name "AzureRunAsConnection"
Connect-AzAccount -ServicePrincipal -TenantId $servicePrincipalConnection.TenantId -ApplicationId $servicePrincipalConnection.ApplicationId -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint
}
Catch {
if (!$servicePrincipalConnection){
$ErrorMessage = "Connection $connectionName not found."
throw $ErrorMessage
} else{
Write-Output -Message $_.Exception
throw $_.Exception
}
}
#######################################################################
# Get Security and Subscription Context Info
#######################################################################
$Context = Get-AzContext
if ($Context -eq $null) {
Write-Output "Need to login"
Connect-AzAccount -Subscription $SubscriptionName
}
else {
Write-Output "Context exists"
Write-Output "Current credential is $($Context.Account.Id)"
if ($Context.Subscription.Name -ne $SubscriptionName) {
$Subscription = Get-AzSubscription -SubscriptionName $SubscriptionName -WarningAction Ignore
Select-AzSubscription -Subscription $Subscription.Id | Out-Null
Write-Output "Current subscription is $($Subscription.Name)"
}
else {
Write-Output "Current subscription is $($Context.Subscription.Name)"
}
}
#>
#######################################################################
# Get Azure Synapse Info and Look through all SQL Pools to Pause
#######################################################################
$AzureSynapseWorkspaces = @(Get-AzSynapseWorkspace -ErrorAction Stop)
Write-Output ""
Write-Output "---------------------------------------------------------------------------------------------------"
Write-Output "Get SQL / Synapse RESOURCES"
Write-Output "---------------------------------------------------------------------------------------------------"
for ($i = 0; $i -lt $AzureSynapseWorkspaces.Count; $i++) {
$AzureSynapseWorkspace = $AzureSynapseWorkspaces[$i]
Write-Output "***************************************************************************************"
Write-Output "Checking Azure Synapse Workspace [$($AzureSynapseWorkspace.Name)] for Synapse SQL Pools"
try {
$SynapseSqlPools = @($AzureSynapseWorkspace | Get-AzSynapseSqlPool -ErrorAction Stop)
Write-Output "SQL Pools:"$SynapseSqlPools
Write-Output "***************************************************************************************"
}
catch [Microsoft.Azure.Commands.Synapse.Models.Exceptions.SynapseException] {
if ($_.Exception.InnerException.Message -eq "Operation returned an invalid status code 'Conflict'") {
Write-Error " -> Operation returned an invalid status code 'Conflict'"
Write-Output " -> Removed ($($AzureSynapseWorkspace.Name)) from AzureSynapseWorkspaces"
$AzureSynapseWorkspaces.Remove($AzureSynapseWorkspace);
}
else {
$iErrorCount += 1;
Write-Error $_.Exception.Message
}
}
catch {
$iErrorCount += 1;
Write-Error $_.Exception.Message
}
foreach ($SynapseSqlPool in $SynapseSqlPools) {
##########################################################################################################################################################
if ($SynapseSqlPool.Status -eq "Paused") {
Write-Output " -> Synapse SQL Pool [$($SynapseSqlPool.SqlPoolName)] found with status [Paused]"
}
##########################################################################################################################################################
elseif ($SynapseSqlPool.Status -eq "Online") {
Write-Output " -> Synapse SQL Pool [$($SynapseSqlPool.SqlPoolName)] found with status [Online]"
# Pause Synapse SQL Pool
$startTimePause = Get-Date
Write-Output " -> Pausing Synapse SQL Pool [$($SynapseSqlPool.SqlPoolName)]"
if (!$debug) {
$resultsynapseSqlPool = $SynapseSqlPool | Suspend-AzSynapseSqlPool
}
# Show that the Synapse SQL Pool has been pause and how long it took
$endTimePause = Get-Date
$durationPause = NEW-TIMESPAN –Start $startTimePause –End $endTimePause
if ($resultsynapseSqlPool.Status -eq "Paused") {
Write-Output " -> Synapse SQL Pool [$($resultsynapseSqlPool.SqlPoolName)] paused in $($durationPause.Hours) hours, $($durationPause.Minutes) minutes and $($durationPause.Seconds) seconds. Current status [$($resultsynapseSqlPool.Status)]"
}
else {
if (!$debug) {
$iErrorCount += 1;
Write-Error " -> (resultsynapseSqlPool.Status -ne ""Paused"") - Synapse SQL Pool [$($resultsynapseSqlPool.SqlPoolName)] paused in $($durationPause.Hours) hours, $($durationPause.Minutes) minutes and $($durationPause.Seconds) seconds. Current status [$($resultsynapseSqlPool.Status)]"
}
else {
Write-Host "This is a debug session - Nothing was done" -ForegroundColor Yellow
}
}
}
##########################################################################################################################################################
else {
$iErrorCount += 1;
Write-Error " -> (SynapseSqlPool.Status -eq ""Online"") Checking Synapse SQL Pool [$($SynapseSqlPool.SqlPoolName)] found with status [$($SynapseSqlPool.Status)]"
}
##########################################################################################################################################################
}
} # for loop
# } # End
Publish these then setup a schedule that links the schedule to the runbookAzureSynapsePauseAllPools
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply