How do I pause and resume a Synapse dedicated SQL pool automatically?

  • 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

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • 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