Identify Azure SQL elastic pools without members

I’ve spent a fair share of time working on cost optimization for Microsoft Azure over the past months. After working a lot with the cost management features in Azure I’ve created some scripts that I thought I would share.

This first PowerShell script is designed to identify elastic pools that do not have any databases associated with them. Unless you’re planning on adding databases to the elastic pool in the near future I can’t think of any reason to keep these resources online, as of today this cost optimization is not identified by the Azure Advisor.

#Make sure you are connected
#Connect-AzAccount 

#Empty array to store output
$Out = @()

#Get all subscriptions
$Subscriptions = Get-AzSubscription

#Loop through subscriptions
foreach ($Subscription in $Subscriptions){
Write-Host "Processing $($Subscription.Name)..."

$ElasticPools = @()
Select-AzSubscription $Subscription.Id | Out-Null

#Get Azure SQL servers in subscription
$Servers = Get-AzSqlServer
Write-Host "    Processing $($Servers.count) SQL server(s)..."


#Get Azure SQL elastic pools associated with servers
foreach ($Server in $Servers) {
$ElasticPools += Get-AzSqlElasticPool -ResourceGroupName $Server.ResourceGroupName -ServerName $Server.Servername
}
Write-Host "    Processing $($ElasticPools.count) SQL elastic pool(s)..."

#Get databases in elastic pools
foreach ($ElasticPool in $ElasticPools) {

#Count the databases
$DBCount = ($ElasticPool | Get-AzSqlElasticPoolDatabase).Count

#Add the count of databases to the elastic pool properties
$ElasticPool | Add-Member -MemberType NoteProperty -Name DBCount -Value $DBCount

#Add to output variable
$Out += $ElasticPool
}
}

#Select empty elastic pools and display the results on screen
$out | where {$_.DBCount -eq 0} | select ResourceGroupName, ServerName, ElasticPoolName

#Select empty elastic pools and display the results on screen and write to CSV file
#$out | where {$_.DBCount -eq 0} | Export-Csv C:\temp\EmptyElasticPools.csv -Delimiter ";" -NoTypeInformation

You may also want to use the output of the above script to output elastic pools with only a single database. Running a single database in an elastic pool is likely to be more costly than running it as a stand alone database.

#Select elastic pools with a single database and display the results on screen
$out | where {$_.DBCount -eq 1} | select ResourceGroupName, ServerName, ElasticPoolName

Leave a Reply

Your email address will not be published.