Auto Scale - Azure SQL elastic pools

[Sabrin_Alsahsah]

We have received a few inquiries and service requests regarding having an automatic scale for Azure SQL elastic pools and this blog was written based on the previous great blog article by our colleague Julio Calderón (How-to auto-scale Azure SQL Databases - Microsoft Community Hub) which explained how to perform auto scale for Azure SQL database. This blog article will be a continuation of the previous one to cover the Azure SQL elastic pool.

Our customers would like to automate this job to react automatically during high workloads and make sure that the elastic pool has enough resources. Therefore, in our example, we will be using an Azure automation account that is triggered by an alert set on the elastic pool – for example, the alert will be triggered when the elastic pool reaches 90% and will run the job to scale the Azure SQL elastic pool to the next service tier.

Environment

• Azure SQL elastic pool: Manage multiple databases with elastic pools - Azure SQL Database | Microsoft Learn.
• Automation account: Azure Automation overview | Microsoft Learn

To configure the Azure Automation account, please follow the below steps:

1) Create a new Azure automation account, you can check this link for the required steps: Quickstart - Create an Azure Automation account using the portal | Microsoft Learn.

2) Since the “Run as Account” has been retried within the automation account, we will be using a managed identity. Once the Azure account is created > access the identity blade >  set the system-assigned identity to on and make sure to assign the required permissions.

For more information: Using a system-assigned managed identity for an Azure Automation account | Microsoft Learn

3) Make sure you have the module (Az.Sql) imported. You can check this by accessing Modules blade under the Azure automation account.

4) Then, create a new runbook by accessing runbook blade and choose the runbook type as (PowerShell).

5) Next step would be adding the below PowerShell script within your Azure Runbook:

Notes:

•  This code was written by our colleague Julio Calderón on this blog, it was just altered for elastic pool.
•  The parameter $TargetDtu needs to be updated depending on your Azure SQL elastic pool value, and you can check this either by accessing configure blade on the elastic pool or can refer to these documents:

1. For DTU: DTU resource limits elastic pools - Azure SQL Database | Microsoft Learn
2. For vCore: Elastic pool vCore resource limits - Azure SQL Database | Microsoft Learn.

• Adjust the max value according to your elastic service tier in the condition (if($currentDatabaseDetails.DTU -eq "3000")

param (
    [Parameter (Mandatory=$false)]
    [object]$WebhookData
)
# Import the Az module
Import-Module Az.Sql

# If there is webhook data coming from an Azure Alert, go into the workflow.
if ($WebhookData){
    # Get the data object from WebhookData
    $WebhookBody = (ConvertFrom-Json -InputObject $WebhookData.RequestBody)
    # Get the info needed to identify the SQL database (depends on the payload schema)
    $schemaId = $WebhookBody.schemaId
    Write-Verbose "schemaId: $schemaId" -Verbose
    if ($schemaId -eq "azureMonitorCommonAlertSchema") {
        # This is the common Metric Alert schema (released March 2019)
        $Essentials = [object] ($WebhookBody.data).essentials
        Write-Output $Essentials
        # Get the first target only as this script doesn't handle multiple
        $alertTargetIdArray = (($Essentials.alertTargetIds)[0]).Split("/")
        $SubId = ($alertTargetIdArray)[2]
        $ResourceGroupName = ($alertTargetIdArray)[4]
        $ResourceType = ($alertTargetIdArray)[6] + "/" + ($alertTargetIdArray)[7]
        $ServerName = ($alertTargetIdArray)[8]
        $ElasticPoolName = ($alertTargetIdArray)[-1]
        $status = $Essentials.monitorCondition

    }
    else{
        # Schema not supported
        Write-Error "The alert data schema - $schemaId - is not supported."
    }}
    if (($status -eq "Activated") -or ($status -eq "Fired"))
    {
        Write-Output "resourceType: $ResourceType"
        Write-Output "serverName: $ServerName"
        Write-Output "resourceGroupName: $ResourceGroupName"
        Write-Output "subscriptionId: $SubId"

# Import the Az module
Import-Module Az.Sql

# Authenticate to Azure using Managed Identity
Connect-AzAccount -Identity 

 # Because Azure SQL tiers cannot be obtained programatically, we need to hardcode them as below.
 # Check the values for your Azure SQL elastic pool and replace them within the below parameter $TargetDtu 

$TargetDtu = @(50,100,200,300,400,800,1200,1600,2000,2500,3000)

$currentDatabaseDetails = Get-AzSqlElasticPool -ResourceGroupName $ResourceGroupName -ServerName $ServerName -ElasticPoolName $ElasticPoolName

#Replace the highest value with the values from your Azure elastic pool service tier
 if ($currentDatabaseDetails.DTU -eq "3000") {
      Write-Output "DTU database is already at highest tier (3000)"
         } else {
                for ($i=0; $i -lt $TargetDtu.length; $i++) {
                      if ($TargetDtu[$i].equals($currentDatabaseDetails.DTU)) {
                      Set-AzSqlElasticPool -ResourceGroupName $ResourceGroupName -ServerName $ServerName -ElasticPoolName $ElasticPoolName -Dtu $TargetDtu[$i+1]  
                      break                 
                    }
                }}}

Configure the alert

Now the runbook is ready, and next, we need to create an alert that will trigger this runbook:

1) From Azure portal, access your elastic pool > alerts blade.

2) Create a new Alert Rule.

3) Within the condition section, you will set the metrics that will be triggering this alert. In our example, it will be triggered if the CPU reaches more than 90%.

4) Next Actions, choose to create a new action group and within the notification section, we will be using email notification and make sure to enable the common alert schema as below:

5) Next, on the actions blade > choose the action type as (Automation account) > then select the user automation account, which was created with the PowerShell script, in this step make sure to enable the common alert schema as below:

After these steps, whenever the alert is triggered, you will receive an email that an alert has been fired and the automation account will be triggered as well.

You can view the job status on Azure automation account under the overview blade and you can have more details when you click on the triggered job.

Disclaimer
Please note that products and options presented in this article are subject to change. This article reflects the auto scale for Azure SQL elastic pool in December 2023.

I hope this article was helpful for you, please feel free to share your feedback in the comments section.