Change the Elastic Pool Storage Size using Azure Monitor and Azure Automation
Published Sep 01 2020 10:41 PM 4,969 Views

Introduction to Elastic Pool:

In this article, we will setup an Azure Monitor Alert to Scale up the Storage limit of an SQL Elastic Pool on Azure. Please read more about Elastic Pool in the above article.

We will divide this into three parts

i.   Setting up an Automation Runbook and Webhook
ii.  Setting up an Alert Action Group
iii. Setting up an Alert under Azure Monitor.

We will not talk much about Azure Automation or Azure Monitor as they are off the topic, we will only cover the steps for setting up of this Auto scale of storage. Here are some of the articles that should bring you up to Speed.

Create Azure Automation Account [ In this case, we would need to use RunAsAccount]

Using Az modules in Azure Automation Account

Azure Monitor

Azure Monitor Overview

Monitoring Azure Service


Setting Up Automation Modules

By default, you cannot run both Az and Rm Modules on the Automation and this is explained here:

So, we will import the Az Modules to the Automation Account and not Rm Modules. By default, when you create an Automation Account, there is a bunch of modules imported and we will not touch them as we will use the AZ modules.

Here is what you need to do.


  1. Go to Azure Automation Account.
  2. Click on Modules under Shared resource.


  3. Click on Browse gallery.


  4. Search for Az.Accounts and Click on Import.


  5. Likewise, search of Az.sql and import it too [ once the Az.Accounts import is complete. Otherwise, it may fail].
  6. Let the modules get imported.
  7. Once the modules are imported, you would see the status as available..


  8. Further, you don’t need to add any modules as we will use only SQL related Cmdlets unless you are using this Automation Account for other purposes.
  9. Next, we will need to setup an Automation Account Runbook, for that Navigate to Runbooks under Process Automation under Automation Account.


  10. Click on Create Runbook and Provide the details as below, Click Ok


  11. In the Runbook Edit section, copy paste the following script:

    #Author: Shashanka Haritsa
    #Date: 19th March 2020
    <#WARNING: The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind.
    Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no
    event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages. #>

    #Read Webhook data
    [Parameter (Mandatory = $false)]
    [object] $WebhookData
    # If runbook was called from Webhook, WebhookData will not be null.
    if ($WebhookData) {
    #Authenticate to Azure First using an RunAsAccount
    $getrunasaccount= Get-AutomationConnection -Name 'AzureRunAsConnection'
    Add-AzAccount -ServicePrincipal -ApplicationId $getrunasaccount.ApplicationId -CertificateThumbprint
    $getrunasaccount.CertificateThumbprint -Tenant $getrunasaccount.TenantId
    # Authentication Complete
    $Converteddata = $WebhookData.Requestbody | ConvertFrom-Json
    $resourcegroupname = $
    $resourceName = $
    $getservername=(($ -split('/'))[8]
    #Read ElasticPools Current storage and double it
    $GetElasticPoolStorage=(Get-AzSqlElasticPool -ElasticPoolName $resourceName -ResourceGroupName
    $resourcegroupname -ServerName $getservername).StorageMB
    $NewStorage = ($GetElasticPoolStorage *2) #I am just Increasing my storage to 100% more for my Standard
    Plan so I am multiplying the storage by 2, you may need to change this according to your requirement
    #Set the new storare limit
    Set-AzSqlElasticPool -ElasticPoolName $resourceName -ResourceGroupName $resourcegroupname -
    StorageMB $NewStorage -ServerName $getservername
    Write-output "No Webhookdata found. Exiting"
  12. Click on save and Click on Publish.
  13. Now, we will need to create a Webhook. Under the same Runbook, Click on Webhooks


  14. Click on Add Webhook.


  15. Under Create Webhook, give it a name and copy the URL to a safe place from where you can retrieve it in future. [ NOTE: This URL cannot be retrieved after creation, so please keep it safe] Click ok and Click on Create.


  16. Once the Webhook is created, you will see that under the Webhooks section.


This Completes the first part where we have created the Automation Runbook, setup modules and a Webhook.


Setting up an Alert Action Group


In this section, we will create an Action Group that we will use with an Alert.

Please follow the steps below to create an Action Group

  1. Login into Azure Portal [ If you haven’t already]
  2. Navigate to Azure Monitor →Alerts and Click on Manage actions


  3. Next, click on Add action group and fill in the information as needed.
  4. Under the Action Name, provide a name as desired and under Action Type, select Webhook


  5. A Webhook URI screen pops up on the right-hand side, please use the Webhook URL we had copied during the Webhook creation under the Automation Account and click ok.


  6. Click OK again on the Add action group screen. This will create an action group.

This completes the creation of Action Group.


Setting up an Alert under Azure Monitor

In this part, we will create an Alert that will trigger our Runbook whenever the used space is greater than some value. Please follow the steps below.


  1. Navigate to Azure Monitor
  2. Click on Alerts and Click on New alert rule
  3. Under the resource, click on Select



  4. Filter the Subscription and Resource type as SQL elastic pools and location, select the Elastic Pool of Interest. This should populate the resource as below.



  5. Now, click on add under condition. Select Signal type as Metrics and Monitor Service as Platform
  6. Select the Signal name of interest, in this case we will select Data space used percent



  7. Once you select the Metric, you will now need to add alert logic, lets say that you would like to trigger an alert when the Percentage used space is 70 [ Average] for last 1 hour, we will set it up as below:



    What does it mean? We are checking the Average Data space used Percentage for last one hour and we will evaluate this condition every 5 minutes as a part of Alert.

  8. Click on done and now click on Add under ACTIONS GROUPS and select the one you created during the action group creation.
  9. Now provide Alert details and a Description. Select Severity of Interest. Once you are happy with the details provided, click Create alert rule

That covers all the three configurations involved. Whenever the data space used percentage on the Elastic Pool increases over 70%, an alert will be triggered, and the Runbook invoked through Webhook will resize the storage on the Elastic Pool.



  • This above sample document is for reference purpose only and is provided AS IS without warranty of any kind.
  • The author is not responsible for any damage or impact on the production, the entire risk arising out of the use or performance of the above sample document remains with you
  • For the Script section, under Automation Runbook setup, we have taken Standard plan[ Elastic Pool] in account and have only doubled the storage based on our requirement, if your requirement is different, you should evaluate the logic for increasing the storage and then amend the script as necessary.



1 Comment
Version history
Last update:
‎Sep 01 2020 10:36 PM
Updated by: