Azure Elastic Pool Databases and ARM Templates

Recently, I've been working with dynamically creating elastic pool databases based on customer need/demand.  The goal of this post is to discuss some of the aspects of this process.

What are elastic pools?

Essentially, elastic pool databases are an effective way of managing many databases that have varying usage/demand profiles.  Instead of thick-provisioning compute capacity on a per database perspective, you can create a "pool" of resources and then deploy many databases on top of this pool.  For more information, here is a link to the documentation.

What is the architecture of elastic pools?

Logically, Azure has the concept of a "sql server" that can then host many elastic pools and/or regular sql databases.  At the server level, authentication/backup/audit are configured.  Each logical resource deployed on that then inherit those properties.  At the pool level, performance targets are set (in eDTUs), diagnostics can be configured, and IAM policies can be set.  If you are deploying a pool, you can then deploy several databases on top of this.  These databases are the logical contains for data, handling authentication/access concerns at that level.

Elastic pools suffer from the noisy neighbours problem, and due care in the architecture should be considered.

Okay, lets build the server

For my deployment, I decided to create a separate sql server ARM template to handle that concern.  I am big into powershell orchestration, and my scripts are already using this as the deployment technology.  As such, it is easy for me to separate these concerns while keeping a clean process for the end user.

Here is the ARM template that I have used.

    {
      "comments": "The sql server",
      "type": "Microsoft.Sql/servers",
      "name": "[parameters('sqlServerName')]",
      "location": "[resourceGroup().location]",
      "apiVersion": "2015-05-01-preview",
      "dependsOn": [],
      "tags": {
        "displayName": "shared sql"
      },
      "properties": {
        "administratorLogin": "[parameters('sqlServerLogin')]",
        "administratorLoginPassword": "[parameters('sqlServerPassword')]",
        "version": "12.0"
      },
      "identity": {
        "type": "SystemAssigned"
      },
      "resources": [
        {
          "name": "AllowAllWindowsAzureIps",
          "type": "firewallRules",
          "location": "[resourceGroup().location]",
          "apiVersion": "2014-04-01",
          "dependsOn": [
            "[parameters('sqlServerName')]"
          ],
          "properties": {
            "startIpAddress": "0.0.0.0",
            "endIpAddress": "0.0.0.0"
          }
        }
      ]
    }

At the server level, there are essentially two things to configure.  The first is the default username/password, and the second is the sql firewall.  For the password, I chose to pass in the password via a securestring parameter.  As this script is designed to dynamically build servers and pools, I need somewhere to handle the password concern without the need for human intervention.  Here is a powershell snip that shows the code to integrate with Azure KeyVault.

function GetTempPassword([int]$length) {
 $ascii=$NULL;For ($a=33;$a –le 126;$a++) {$ascii+=,[char][byte]$a }
 for ($loop=1; $loop –le $length; $loop++) {
            $TempPassword+=($ascii | GET-RANDOM)
    }
 return $TempPassword
}

function StorePasswordIn($keyVaultName,$secretName,$password){
    "Converting to secure string"
    $securePassword = ConvertTo-SecureString -String $password -AsPlainText -Force
    "Setting in keyvault $secretName"
    Set-AzureKeyVaultSecret -VaultName $keyVaultName `
                            -Name $secretName `
                            -SecretValue $securePassword 
}

function GetStoredPassword($keyVaultName,$secretName){
 $secrets = Get-AzureKeyVaultSecret -VaultName $keyVaultName
 if ($secrets.Name -notcontains $secretName){
  $unsecuredPassword = (GetTempPassword -length 30)
  StorePasswordIn -keyVaultName $keyVaultName -secretName $secretName -password $unsecuredPassword
 }
 return (Get-AzureKeyVaultSecret -VaultName $keyVaultName -Name $secretName).SecretValue
}

Basically, I needed to handle the case where the script was being run against an already created database.  This is important as I want to be able to incrementally add elastic pools to my existing server as required.

I feel like a better approach to passing the password via securestring parameter is to use the keyvault integration in ARM.  See this link.  A planned upgrade for sure!

Now for the elastic pools

Here is my template for an elastic pool:

    {
      "comments": "Resource for each elastic pool",
      "name": "[concat(parameters('sqlServerName'),'/',parameters('elasticPoolNames')[copyIndex()])]",
      "type": "Microsoft.Sql/servers/elasticPools",
      "location": "[resourceGroup().location]",
      "apiVersion": "2014-04-01",
      "tags": {
        "displayName": "elastic pools"
      },
      "properties": {
        "edition": "[parameters('editions')[copyIndex()]]",
        "dtu": "[parameters('dtus')[copyIndex()]]"
      },
      "copy": {
        "name": "elasticPoolCopy",
        "count": "[length(parameters('elasticPoolNames'))]"
      },
      "dependsOn": [
      ]
    },

The interesting part here is being able to dynamically add pools.  Essentially, I pass in 3 arrays into this script that contain the required information.

    "elasticPoolNames": {
      "type": "array",
      "metadata": {
        "description": "The names of the pools to create"
      }
    },
    "editions": {
      "type": "array",
      "metadata": {
        "description": "The edition of the pools"
      }
    },
    "dtus": {
      "type": "array",
      "metadata": {
        "description": "The DTUs for the pools"
      }
    },

This way, my script can be used to either (a) scale existing pools as required or (b) create new pools as requirements dictate.

Add some alerts for good measure

Essentially, we are creating our own "shared infrastructure" for the various applications/clients that are running on the given pool.  Decisions need to be made at a certain point as to if the solution should be vertically or horizontally scaled.  In order to support reactive scaling, I've created alerts on the following metrics:
1) dtu_consumption_percentage
2) storage_percentage
3) sessions_percentage

The first two in the list are directly linked to the size of the elastic pool and could trigger a scale vertical/horizontal decision.  Sessions, on the other hand, are fixed for all sizes.  Reaching this limit could trigger a horizontal scale decision.

Here is the ARM template for that:

   {
      "comments": "Adding Session Alerts",
      "name": "[concat(parameters('elasticPoolNames')[copyIndex('elasticPool')],'_','sessions_percent')]",
      "type": "Microsoft.Insights/alertrules",
      "location": "[resourceGroup().location]",
      "apiVersion": "2016-03-01",
      "tags": {
        "displayName": "session alert"
      },
      "properties": {
        "name": "[concat(parameters('elasticPoolNames')[copyIndex('elasticPool')],'_','sessions_percent')]",
        "description": "an alert rule",
        "isEnabled": true,
        "condition": {
          "odata.type": "Microsoft.Azure.Management.Insights.Models.ThresholdRuleCondition",
          "dataSource": {
            "odata.type": "Microsoft.Azure.Management.Insights.Models.RuleMetricDataSource",
            "resourceUri": "[concat(resourceId('Microsoft.Sql/servers', parameters('sqlServerName')), '/elasticPools/',parameters('elasticPoolNames')[copyIndex()])]",
            "metricName": "sessions_percent"
          },
          "threshold": 90,
          "windowSize": "PT10M"
        }
      },
      "dependsOn": [
        "[concat(resourceId('Microsoft.Sql/servers', parameters('sqlServerName')), '/elasticPools/',parameters('elasticPoolNames')[copyIndex()])]"
      ],
      "copy": {
        "name": "elasticPool",
        "count": "[length(parameters('elasticPoolNames'))]"
      }
    },
    {
      "comments": "Adding DTU Alerts",
      "name": "[concat(parameters('elasticPoolNames')[copyIndex('elasticPool')],'_','dtu_consumption_percent')]",
      "type": "Microsoft.Insights/alertrules",
      "location": "[resourceGroup().location]",
      "apiVersion": "2016-03-01",
      "tags": {
        "displayName": "dtu alert"
      },
      "properties": {
        "name": "[concat(parameters('elasticPoolNames')[copyIndex('elasticPool')],'_','dtu_consumption_percent')]",
        "description": "an alert rule",
        "isEnabled": true,
        "condition": {
          "odata.type": "Microsoft.Azure.Management.Insights.Models.ThresholdRuleCondition",
          "dataSource": {
            "odata.type": "Microsoft.Azure.Management.Insights.Models.RuleMetricDataSource",
            "resourceUri": "[concat(resourceId('Microsoft.Sql/servers', parameters('sqlServerName')), '/elasticPools/',parameters('elasticPoolNames')[copyIndex()])]",
            "metricName": "dtu_consumption_percent"
          },
          "threshold": 90,
          "windowSize": "PT10M"
        }
      },
      "dependsOn": [
        "[concat(resourceId('Microsoft.Sql/servers', parameters('sqlServerName')), '/elasticPools/',parameters('elasticPoolNames')[copyIndex()])]"
      ],
      "copy": {
        "name": "elasticPool",
        "count": "[length(parameters('elasticPoolNames'))]"
      }
    },
    {
      "comments": "Adding Storage Alerts",
      "name": "[concat(parameters('elasticPoolNames')[copyIndex('elasticPool')],'_','storage_percent')]",
      "type": "Microsoft.Insights/alertrules",
      "location": "[resourceGroup().location]",
      "apiVersion": "2016-03-01",
      "tags": {
        "displayName": "storage alert"
      },
      "properties": {
        "name": "[concat(parameters('elasticPoolNames')[copyIndex('elasticPool')],'_','storage_percent')]",
        "description": "an alert rule",
        "isEnabled": true,
        "condition": {
          "odata.type": "Microsoft.Azure.Management.Insights.Models.ThresholdRuleCondition",
          "dataSource": {
            "odata.type": "Microsoft.Azure.Management.Insights.Models.RuleMetricDataSource",
            "resourceUri": "[concat(resourceId('Microsoft.Sql/servers', parameters('sqlServerName')), '/elasticPools/',parameters('elasticPoolNames')[copyIndex()])]",
            "metricName": "storage_percent"
          },
          "threshold": 90,
          "windowSize": "PT10M"
        }
      },
      "dependsOn": [
        "[concat(resourceId('Microsoft.Sql/servers', parameters('sqlServerName')), '/elasticPools/',parameters('elasticPoolNames')[copyIndex()])]"
      ],
      "copy": {
        "name": "elasticPool",
        "count": "[length(parameters('elasticPoolNames'))]"
      }
    }

In conclusion, we talked about some considerations for elastic pool architecture and shared some ARM templates for the dynamic creation of both sql servers and elastic pools. Lastly, we talked a bit about alerts that could be used to support vertical/horizontal scale decisions.