Invoking a Runbook based on Azure alerts

Goal: My goal is to configure the Azure SQL elastic pool based on the DTU usage.

Preface: Let me walk you through our setup. Our cloud solution requires a database per tenant. We have decided to use the Azure SQL elastic pool to host tenant databases. We also have multiple environments like Development, QA, Performance etc. all sharing the same elastic pool. Since each environment is used by geographically dispersed teams, the load on the elastic pool varies.

We first started with a fixed number of eDTU’s (Elastic Data Throughput Units) for the pool. However the DTU usage was variable and rarely excessive. Hence, I started exploring the possibility of elastically changing the eDTU’s based on demand.


Azure Metrics: Azure provides lot of metrics for each resource type. One such metric for the SQL elastic pool is the amount of eDTUs used.

Alerts: Alerts will allow you to monitor based on metrics and notifies proactively when the condition is met. When an alert is triggered, Azure allows for the invoking of a web hook.

Runbooks: An Azure run book allows you to run power shell commands. An Azure automation run book allows you to create a web hook.

The Solution: Putting these three features together will create a smarter solution.

Configuring Alerts: Let’s say you would like to ensure that the eDTU’s for your pool will be between 100–300 depending on the load. Here are some alerts you can create.

SQL Elastic pool alerts

Here is a sample of these alerts configurations:

Alert configuration

Metric: Chose the metric on which you would like the alert to be triggered.

Condition: Is the alert condition

Threshold: Depending on the type of metric, you can set the threshold at which the alert should be triggered.

Period: The duration you would like to check the threshold value condition.

Webhook: This is the URI that will be called when the alert is triggered. Get the Webhook URI of your runbook and paste here.


Runbook: This is where the magic happens.

When an alert is triggered, Azure sends the details about the alert as a payload to the URI. Here is a sample payload.

{
“status”: “Activated”,
“context”: {
“timestamp”: “2015–08–14T22:26:41.9975398Z”,
“id”: “/subscriptions/s1/resourceGroups/useast/providers/microsoft.insights/alertrules/ruleName1”,
“name”: “ruleName1”,
“description”: “some description”,
“conditionType”: “Metric”,
“condition”: {
“metricName”: “Requests”,
“metricUnit”: “Count”,
“metricValue”: “10”,
threshold”: “10”,
“windowSize”: “15”,
“timeAggregation”: “Average”,
“operator”: “GreaterThanOrEqual”
},
“subscriptionId”: “s1”,
“resourceGroupName”: “useast”,
“resourceName”: “mysite1”,
“resourceType”: “microsoft.foo/sites”,
“resourceId”: “/subscriptions/s1/resourceGroups/useast/providers/microsoft.foo/sites/mysite1”,
“resourceRegion”: “centralus”,
“portalLink”: “https://portal.azure.com/#resource/subscriptions/s1/resourceGroups/useast/providers/microsoft.foo/sites/mysite1
},
“properties”: {
“key1”: “value1”,
“key2”: “value2”
}
}

The payload contains the details of the alert. One of the interesting properties is the threshold. Notice below how threshold is being used.

Runbook Implementation: The runbook expects the payload as input. Once the payload is parsed, we can read the properties. The payload is a PSObject in JSON format. The below runbook will increase the eDTU’s based on the load.

Similarly another runbook can be created to reduce the eDTU’s.


Few things to remember:

  1. Don’t update the pool configuration frequently. The changes to the elastic pool takes time depending on number and size of the databases. Read the impacts here.
  2. If ColumnStore Indexes are enabled on your database, you need to set the eDTU’s to a minimum of 100. Otherwise the schema changes will fail.
  3. Based on your usage, find a happy medium for the eDTU’s. This way you don’t have to raise alerts and change configuration frequently.