Azure SQL Elastic Pool Automatic Storage Scaling
Tags: powershell, sql, automation, azure
Published: 2024-06-18 08:18:50

---
title: Azure SQL Elastic Pool Automatic Storage Scaling
published: true
date: 2021-11-24 08:01:55 UTC
tags: powershell,sql,automation,azure
canonical_url: https://andyr8939.medium.com/azure-sql-elastic-pool-automatic-storage-scaling-46871324bbc3
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ylnfchbz38jxtd40xlql.jpg
---

Azure SQL Elastic Pools offer some great benefits for SQL Databases such as DTU based Performance and on-demand scaling up and down of compute/memory/storage via the Azure Portal. However, one thing that is not native out of the box, is scaling of storage on an elastic pool, in response to storage pressure. Recently I was exposed to this where an elastic pool reached its storage limit due to large amounts of data load happening, which caused the pool to run out of space and which would then quickly cause an outage as databases couldn’t allocate anymore space.

So, lets fix that and create our own….

To do this, we are going to leverage a couple of Azure features

- Azure Monitor Alerts
- Azure Automation Accounts & Runbooks

### How do we do this?

What we want to happen is that when an Elastic Pool hits a certain limit of **Data Space Allocated Percentage** that an Azure Monitor Alert is triggered, which executes an Azure Automation Runbook, which runs some powershell to scale the elastic pool. How I have structured this is the following.

1. At 80% Data Space Allocated, send me an email but don’t scale (so I get chance to validate it)
2. At 90% Data Space Allocated, send me an email but this time scale the pool.
3. At 95% Data Space Allocated, repeat the scale as before. This is a safety net in case the pool grows very quickly.

And part of this is also to have a hard limit of elastic pool size, so that I don’t get a situation where runaway storage growth needs me to take out a loan to pay the bill. All of this is easily doable with Azure Runbooks.

### Lets Test

You’ll need some pre-reqs for this:

1. Azure SQL Server
2. Azure SQL Elastic Pool
3. Azure Automation Account

If you don’t have these you can create some quickly with the below powershell, replacing the vars with what you want.

```
_# Install Pre-Req Modules_

Install-Module Az.Resources, Az.Sql, Az.ManagedServiceIdentity, Az.Automation

_# Define our variables_

$location = "australiaeast"

$rsgName = "myResourceGroup"

$sqlServerName = "mySQLServer"

$sqlServeElasticPoolName = "myElasticPool"

$sqlServerUser = "mysSQLAdminName"

$sqlServerPass = "SuperSecretPassword123!"

$automationAccountName = "myAutomationAccount"

_# Create Resource Group_

New-AzResourceGroup -Name $rsgName -Location $location

_# Create our Credential Object_

[securestring]$secStringPassword = ConvertTo-SecureString $sqlServerPass -AsPlainText -Force

[pscredential]$sqlCreds = New-Object System.Management.Automation.PSCredential ($sqlServerUser, $secStringPassword)

_# Create SQL Server and Elastic Pool_

$sqlServer = New-AzSQLServer -ServerName $sqlServerName -ResourceGroupName $rsgName -Location $location -ServerVersion "12.0" -SqlAdministratorCredentials $sqlCreds

$sqlElasticPool = New-AzSqlElasticPool -ServerName $sqlServerName -ResourceGroupName $rsgName -ElasticPoolName $sqlServeElasticPoolName -Edition "Standard" -Dtu 50 -DatabaseDtuMin 10 -DatabaseDtuMax 50 -StorageMB 51200

_# Create Automation Account_

New-AzAutomationAccount -Name $automationAccountName -ResourceGroupName $rsgName -Location $location -AssignSystemIdentity
```

### Create your Azure Automation Runbook

Go to your Azure Automation Account, either an existing one or the one you created above, and create a Runbook.

![](https://cdn-images-1.medium.com/max/400/0*vklC02ESy3RlwqNT.png)

Name it and ensure its running **Powershell** and  **5.1**

![](https://cdn-images-1.medium.com/max/600/0*Td2AEw5GN_nzZTgQ.png)

Once its created, open it and click **Edit** at the top, and then paste in the following Powershell Code. You can also get this from my [GitHub Repo here](https://github.com/andyr8939/azure/blob/main/scale-sql-elasticpool-storage.ps1). Once pasted in click **Save** and then **Publish**. If you don’t click Publish then the Runbook won’t execute.

![](https://cdn-images-1.medium.com/max/800/0*HHLkoZ88a_CdOZ9C.png)

```
_<#_

_.SYNOPSIS_

_Automatically grow the allocated storage of an Azure SQL Elastic Pool from an Azure Runbook_

_.DESCRIPTION_

_When executed from an Azure Monitor Runbook Trigger, this script will expand the elastic pool by_

_its specified amount, up to a hard limit._

_It also won't scale if it doesn't meet a scaling threshold, initially defined as 85%_

_Created by Andy Roberts (andyr8939@gmail.com)_

_.PARAMETER WebhookData_

_Raw data passed to the runbook from Azure Monitor_

_.PARAMETER poolIncreaseMB_

_How much space to add to the pool on each scale event. Initially set at 50 GB._

_.PARAMETER poolIncreaseLimitMB_

_A hard limit of how big the pool can increase too. Initially set at 1 TB._

_#>_

param

(

[Parameter (_Mandatory_=$false)]

[object] $WebhookData,

[Parameter (_Mandatory_= $false)]

[String] $poolIncreaseMB = "51200", _# 50GB_

[Parameter (_Mandatory_= $false)]

[String] $poolIncreaseLimitMB = "1048576" _# 1TB_

)

$connectionName = "AzureRunAsConnection"

try

{

_# Get the connection "AzureRunAsConnection "_

$servicePrincipalConnection=Get-AutomationConnection -Name $connectionName

"Logging in to Azure..."

Connect-AzAccount `

-ServicePrincipal `

-TenantId $servicePrincipalConnection.TenantId `

-ApplicationId $servicePrincipalConnection.ApplicationId `

-CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint

}

catch {

if (!$servicePrincipalConnection)

{

$ErrorMessage = "Connection $connectionName not found."

throw $ErrorMessage

} else{

Write-Error -Message $_.Exception

throw $_.Exception

}

}

Write-Output "This runbook was started from webhook $WebhookName."

_# Collect Webhook Data and convert to use with JSON_

$WebhookBody = (ConvertFrom-Json -InputObject $WebhookData.RequestBody)

_# Obtain the WebhookBody containing the AlertContext and any details we need_

$AlertContext = [object] ($WebhookBody.data).context

$SubId = $AlertContext.subscriptionId

$ResourceGroupName = $AlertContext.resourceGroupName

$ResourceType = $AlertContext.resourceType

$ResourceName = $AlertContext.resourceName

$status = ($WebhookBody.data).status

$threshold = $WebhookBody.data.context.condition.allOf.threshold

_# Write results to output for logging_

Write-output "Subscription - $SubId"

Write-output "RSG Name - $ResourceGroupName"

Write-output "Resource Type - $ResourceType"

Write-output "Resrouce Name - $ResourceName"

Write-output "Status - $status"

Write-output "Threshold - $threshold"

_# Only scale if over 85% allocated, to give chance to manually clean up on earlier 80% trigger with just email_

if ($threshold -le 85) {

Write-Output "Threshold is $threshold which does not require scaling. Email only sent."

Write-Output "Scaling Occurs once threshold is over 90%"

Exit

}

_# As its over 85% threshold, prepare to scale_

_# Get Resources for SQL and Elastic Pool_

$sqlServer = Get-AzSqlServer -ResourceGroupName $ResourceGroupName

$elasticPool = Get-AzSqlElasticPool -ElasticPoolName $ResourceName -ResourceGroupName $ResourceGroupName -ServerName $sqlserver.ServerName

  
_# Add 50GB to the current size_  

$newElasticPoolStorageGB = $elasticpool.StorageMB + $poolIncreaseMB _# Add 50GB_

_# Check new size and if it will exceed 1TB as a hard limit to prevent runaway scale_

if ($newElasticPoolStorageGB -ge $poolIncreaseLimitMB) {

$failError = "Elastic Pool Size for $ResourceName will exceed its allowed upper limit of $($poolIncreaseLimitMB /1024) GB so cannot scale"

Write-Output $failError

throw $failError

}

Write-Output "Current Elastic Pool Size for $ResourceName is $($elasticpool.StorageMB /1024) GB"

Write-Output "Scaling Elastic Pool $ResourceName to $($newElasticPoolStorageGB /1024) GB"

_# Scale Elastic Pool to new size_

$elasticpool | Set-AzSqlElasticPool -StorageMB $newElasticPoolStorageGB

Write-Output "New Elastic Pool Size for $ResourceName is $($newElasticPoolStorageGB /1024) GB"
```

Now you had added your Runbook, you need to ensure that the Automation Account knows about the AZ.Sql Module so it can actually run the powershell cmdlets in the runbook.

In your Automation Account, select **Modules** and **Add a Module**.

![](https://cdn-images-1.medium.com/max/773/0*57AVCZfxM4K9m_TL.png)

Select **Browse from Gallery** and then click to Browse the Gallery. A nice feature here is that you don’t need to know the module name, just type the Powershell cmd you want and it will tell you the module. So for example type in **Get-AzSQLServer** and it will show you the Az.Sql Module. Select it and then when it comes back to the Add a Module screen, select **5.1** as the Runtime Version and **Import**. This can take a few minutes but as we are finished here so you can move on.

![](https://cdn-images-1.medium.com/max/1024/0*xb01umndAmP3U7R1.png)

### Create your SQL Elastic Pool Alert

Now you have your Automation Runbook, its time to configure the Alert Rule. Go to your SQL Elastic Pool, Alerts and then **Create Alert Rule**.

![](https://cdn-images-1.medium.com/max/500/0*QGyTyr8TM3Ozy51n.png)

The metric you want to monitor is **Data Space Allocated Percent** , and the values you use are up to you, but I like to create an initial one at 80%. Also the period depends on your individual needs, but for a demo, I set it to 1 minute so it quickly notices the change.

![](https://cdn-images-1.medium.com/max/1024/0*yGnfN8qor6QPWyne.png)

Next you need to crete an **Action Group**. This is what you are going to action, surprisingly, so it’s executing a runbook, triggering an email etc. So give it a sample name.

![](https://cdn-images-1.medium.com/max/700/0*SQYM5YaFE6KUcwDz.png)

Under Action Type, select **Automation Runbook**.

![](https://cdn-images-1.medium.com/max/700/0*3hrFIDRv0-LUGMqO.png)

To find the Runbook you created earlier, change the **Runbook Source** to **User** and then find the Automation Account and Runbook you Created earlier. You don’t need to Configure Parameters at this stage, but if you wanted to add in custom policies like how many GB to scale and the upper limit, you can do this here.

![](https://cdn-images-1.medium.com/max/487/0*X2dfwJkTHJX0DquF.png)

I also like to add in additional actions here, such as sending myself an email when it happens, but this is totally up to you.

Repeat all of the above **Alert Rules** for as many levels as you want. I typically have rules trigger at 80%, 90% and 95%, but you will end up with a rule looking like this.

![](https://cdn-images-1.medium.com/max/1024/0*eUJfcNCw6LU9BwGt.png)

### Time to expand that pool

Its now time to put this all together and see how it functions. You’ll need to add some data to your SQL Elastic Pool to push it over the threshold you set. I like to use these [sample StackOverflow Databases](https://www.brentozar.com/archive/2015/10/how-to-download-the-stack-overflow-database-via-bittorrent/) for this purpose but you can use whatever suits.

#### 80% Allocated — Don’t Grow

On my first run, I pushed Data Space Allocated to 80%. The Alert Rule on the Elastic Pool triggered the Runbook (yey) but it didn’t grown the pool. This was because I had the threshold in the parameters set to only scale at 90%. This is perfect, as my action group sent me an email so I can investigate if I need to.

![](https://cdn-images-1.medium.com/max/1024/0*0dmFxLDNg4bF_30J.png)

#### 90% Allocated — Lets Grow

But for the demo perspective, lets assume it was valid and now it suddenly hits 90%. So the 90% Alert Rule triggers the runbook again.

![](https://cdn-images-1.medium.com/max/1024/0*9nC0XFG_Y6f9ipmh.png)

It works! We can see above that the runbook saw the threshold from the alert was 90%, which was over my 85% threshold. So because of that it knew it needed to scale and added 50 GB to the pool, meaning it was now 100 GB!

#### Protect with a limit

Like we put in earlier, we want a hard limit to stop run away growth of the pool. To illustrate this here is an example where I changed my thresholds so it would hit the limit in one go. This causes the runbook to throw an exception (as by default all runbooks will complete) and fail, highlighting the limit.

![](https://cdn-images-1.medium.com/max/1024/0*rUfy55s8XdWT5tIt.png)

That’s all there is to it. You can adjust and change that with thresholds to suit your needs really easily. It was a fun one for me to do as it solved a fairly common problem and got me more familar with Azure Automation Runbooks at the same time.

Andy

_Originally published at_ [_https://www.andyroberts.nz_](https://www.andyroberts.nz/posts/elastic-pool-scaling/) _on November 24, 2021._