Blog Post

Azure Synapse Analytics Blog
5 MIN READ

Automatic pause all Synapse Pools and keeping your subscription costs under control

FonsecaSergio's avatar
FonsecaSergio
Icon for Microsoft rankMicrosoft
Jan 16, 2021
* Update 2022-11-16: Updating scripts / procedures to use MSI, instead of Automation Run As Account https://learn.microsoft.com/en-us/azure/automation/manage-run-as-account Azure Automation Run A...
Updated Jul 12, 2024
Version 6.0
Synapse Administration
synapse devops
JordanJohnson's avatar
JordanJohnson
Copper Contributor
Feb 26, 2021

Lee_Schuenemeyer, I basically wrote the inverse of his script commands. So I used the command "$SynapseSqlPool | Resume-AzSynapseSqlPool" rather than the command to pause the pool. Here's the script for the foreach loop where I iterate through all the sql pools for the subscription.

foreach ($SynapseSqlPool in $SynapseSqlPools) {
        
        ##########################################################################################################################################################
        if ($SynapseSqlPool.Status -eq "Online") {
            Write-Output "  -> Synapse SQL Pool [$($SynapseSqlPool.SqlPoolName)] found with status [Online]"
        }
        ##########################################################################################################################################################
        elseif ($SynapseSqlPool.Status -eq "Paused") {
            Write-Output "  -> Synapse SQL Pool [$($SynapseSqlPool.SqlPoolName)] found with status [Paused]"
            # Start Synapse SQL Pool
            $startTimeResume = Get-Date
            Write-Output "  -> Resuming Synapse SQL Pool [$($SynapseSqlPool.SqlPoolName)]"
            
            if (!$debug) {
                $resultsynapseSqlPool = $SynapseSqlPool | Resume-AzSynapseSqlPool
            }
            
            # Show that the Synapse SQL Pool has been resumed and how long it took
            $endTimeResume = Get-Date
            $durationResume = NEW-TIMESPAN –Start $startTimeResume –End $endTimeResume

            if ($resultsynapseSqlPool.Status -eq "Online") {
                Write-Output "  -> Synapse SQL Pool [$($resultsynapseSqlPool.SqlPoolName)] resumed in $($durationResume.Hours) hours, $($durationResume.Minutes) minutes and $($durationResume.Seconds) seconds. Current status [$($resultsynapseSqlPool.Status)]"
            }
            else {
                if (!$debug) {
                    $iErrorCount += 1;
                    Write-Error "  -> (resultsynapseSqlPool.Status -ne ""Online"") - Synapse SQL Pool [$($resultsynapseSqlPool.SqlPoolName)] resumed in $($durationResume.Hours) hours, $($durationResume.Minutes) minutes and $($durationResume.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)]"
        }
        ##########################################################################################################################################################
    }    
}