How to Auto-Scale Azure Elastic Pool Based on CPU Threshold (5-Min Trigger)

Hi Hardiv Joshi (PredicAire),

Greetings!!

It looks like you want to set up auto-scaling for your Azure SQL Elastic Pool based on CPU usage. Azure currently doesn’t provide a built-in feature for automatic scaling of SQL Elastic Pools like it does for other Azure services, but you can manually set this up using PowerShell scripts along with Azure Monitor alerts. Here’s how you might approach it:

Steps to Automate Elastic Pool Scaling

1. Set Up Alerts:
   • Use Azure Monitor to create an alert based on the CPU usage metric of your Elastic Pool.
   • You can set the alert to check if the CPU usage goes above 90% over a specified duration (e.g., 5 minutes).
   • Here's how to set up alerts.
2. Create a Runbook in Azure Automation:
   • Create an Azure Automation Account and a Runbook that uses PowerShell.
   • The Runbook will contain scripts that scale the Elastic Pool up or down.
   • For example, you can use the following PowerShell example in the Runbook to scale the Elastic Pool

# Define parameters
$resourceGroupName = "YourResourceGroup"
$serverName = "YourSqlServer"
$elasticPoolName = "YourElasticPool"
# Connect to Azure
Connect-AzAccount
# Scale the elastic pool
Set-AzSqlElasticPool `
  -ResourceGroupName $resourceGroupName `
  -ServerName $serverName `
  -ElasticPoolName $elasticPoolName `
  -Edition "Standard" `
  -Dtu 100 # Adjust DTU based on scale-up or scale-down

For more information, please refer the document:

https://learn.microsoft.com/en-us/azure/azure-sql/database/scripts/monitor-and-scale-pool-powershell?view=azuresql

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.