Change the Elastic Pool Storage Size using Azure Monitor and Azure Automation

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Introduction to Elastic Pool:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-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]


https://docs.microsoft.com/en-us/azure/automation/automation-create-standalone-account


Using Az modules in Azure Automation Account


https://docs.microsoft.com/en-us/azure/automation/az-modules


Azure Monitor


https://docs.microsoft.com/en-us/azure/azure-monitor/


Azure Monitor Overview


https://docs.microsoft.com/en-us/azure/azure-monitor/overview


Monitoring Azure Service


https://docs.microsoft.com/en-us/azure/azure-monitor/insights/monitor-azure-resource

 

Setting Up Automation Modules

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


https://docs.microsoft.com/en-us/azure/automation/az-modules


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.

    ShashankaHaritsa_0-1599019457430.png 

  3. Click on Browse gallery.

    ShashankaHaritsa_1-1599019483844.png

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

    ShashankaHaritsa_2-1599019504166.png

  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..

    ShashankaHaritsa_5-1599019601058.png

  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.

    ShashankaHaritsa_6-1599019635338.png

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

    ShashankaHaritsa_7-1599019652236.png

  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
    param
    (
    [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
    $WebhookData.Requestbody
    $Converteddata = $WebhookData.Requestbody | ConvertFrom-Json
    $resourcegroupname = $converteddata.data.context.resourceGroupName
    $resourceName = $converteddata.data.context.resourceName
    $getservername=(($converteddata.data.context.resourceId) -split('/'))[8]
    #Read ElasticPools Current storage and double it
    $GetElasticPoolStorage=(Get-AzSqlElasticPool -ElasticPoolName $resourceName -ResourceGroupName
    $resourcegroupname -ServerName $getservername).StorageMB
    $GetElasticPoolStorage
    $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
    }
    Else{
    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

    ShashankaHaritsa_0-1599019752455.png

  14. Click on Add Webhook.

    ShashankaHaritsa_1-1599019814317.png

  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.

    ShashankaHaritsa_2-1599019883293.png

  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

    ShashankaHaritsa_3-1599020167604.png

  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

    ShashankaHaritsa_4-1599020227968.png

  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.

    ShashankaHaritsa_5-1599020260522.png

  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

    ShashankaHaritsa_6-1599020423921.png

     

  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.

    ShashankaHaritsa_8-1599020541692.png

     

  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

    ShashankaHaritsa_9-1599020575675.png

     

  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:

    ShashankaHaritsa_10-1599020611906.png

     


    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.

 

IMPORTANT NOTE:

  • 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.

 

 

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.