Azure SQL Elastic Pool – ARM Templates


coil-632650_640[1]In my last article, I covered Azure SQL Elastic Pool.  In this one I cover how to provision it using ARM templates.

As of today (December 2016), the documentation about Azure SQL Elastic Pool provisioning via ARM templates is…  not existing.

Searching for it I was able to gather hints via a few colleagues GitHub repos, but there are no examples in the ARM quickstart templates nor is the elastic pool resource schema documented.  Also, the Automation Script feature in the portal doesn’t reverse engineer an ARM template for the elastic pool.

So I hope this article fills that gap and is easy to search for & consume.

ARM Template

Here we’re going to provision a Server with two pools, Pool-A & Pool-B (yeah, sounds a bit like Thing 1 & Thing 2), each having a few (configurable number of) databases in them.

{
  "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "Server Name": {
      "defaultValue": "pooldemoserver",
      "type": "string",
      "metadata": {
        "description": "Name of the SQL:  needs to be unique among all servers in Azure"
      }
    },
    "Admin Login": {
      "defaultValue": "myadmin",
      "type": "string",
      "metadata": {
        "description": "SQL Server Admin login name"
      }
    },
    "Admin Password": {
      "type": "securestring",
      "metadata": {
        "description": "SQL Server Admin login password"
      }
    },
    "Pool A Edition": {
      "defaultValue": "Standard",
      "type": "string",
      "allowedValues": [
        "Basic",
        "Standard",
        "Premium"
      ],
      "metadata": {
        "description": "Pool A Edition"
      }
    },
    "Pool B Edition": {
      "defaultValue": "Standard",
      "type": "string",
      "allowedValues": [
        "Basic",
        "Standard",
        "Premium"
      ],
      "metadata": {
        "description": "Pool B Edition"
      }
    },
    "DB Max Size": {
      "defaultValue": "10737418240",
      "type": "string",
      "allowedValues": [
        "104857600",
        "524288000",
        "1073741824",
        "2147483648",
        "5368709120",
        "10737418240",
        "21474836480",
        "32212254720",
        "42949672960",
        "53687091200",
        "107374182400",
        "161061273600",
        "214748364800",
        "268435456000",
        "322122547200",
        "429496729600",
        "536870912000"
      ],
      "metadata": {
        "description": "DB Max Size, in bytes"
      }
    }
  },
  "variables": {
    "Pool A": "Pool-A",
    "Pool B": "Pool-B",
    "DB A Prefix": "Pool-A-Db",
    "DB B Prefix": "Pool-B-Db",
    "Count A": 2,
    "Count B": 4
  },
  "resources": [
    {
      "name": "[parameters('Server Name')]",
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2014-04-01-preview",
      "location": "[resourceGroup().location]",
      "dependsOn": [],
      "properties": {
        "administratorLogin": "[parameters('Admin Login')]",
        "administratorLoginPassword": "[parameters('Admin Password')]",
        "version": "12.0"
      },
      "resources": [
        {
          "type": "firewallRules",
          "kind": "v12.0",
          "name": "AllowAllAzureIps",
          "apiVersion": "2014-04-01-preview",
          "location": "[resourceGroup().location]",
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', parameters('Server Name'))]"
          ],
          "properties": {
            "startIpAddress": "0.0.0.0",
            "endIpAddress": "0.0.0.0"
          }
        },
        {
          "type": "elasticpools",
          "name": "[variables('Pool A')]",
          "apiVersion": "2014-04-01-preview",
          "location": "[resourceGroup().location]",
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', parameters('Server Name'))]"
          ],
          "properties": {
            "edition": "[parameters('Pool A Edition')]",
            "dtu": "200",
            "databaseDtuMin": "10",
            "databaseDtuMax": "50"
          }
        },
        {
          "type": "elasticpools",
          "name": "[variables('Pool B')]",
          "apiVersion": "2014-04-01-preview",
          "location": "[resourceGroup().location]",
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', parameters('Server Name'))]"
          ],
          "properties": {
            "edition": "[parameters('Pool B Edition')]",
            "dtu": "400",
            "databaseDtuMin": "0",
            "databaseDtuMax": null
          }
        }
      ]
    },
    {
      "type": "Microsoft.Sql/servers/databases",
      "copy": {
        "name": "DBs-A",
        "count": "[variables('Count A')]"
      },
      "name": "[concat(parameters('Server Name'), '/', variables('DB A Prefix'), copyIndex())]",
      "location": "[resourceGroup().location]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', parameters('Server Name'))]",
        "[resourceId('Microsoft.Sql/servers/elasticpools', parameters('Server Name'), variables('Pool A'))]"
      ],
      "tags": {
        "displayName": "Pool-A DBs"
      },
      "apiVersion": "2014-04-01-preview",
      "properties": {
        "collation": "SQL_Latin1_General_CP1_CI_AS",
        "maxSizeBytes": "[parameters('DB Max Size')]",
        "requestedServiceObjectiveName": "ElasticPool",
        "elasticPoolName": "[variables('Pool A')]"
      }
    },
    {
      "type": "Microsoft.Sql/servers/databases",
      "copy": {
        "name": "DBs-B",
        "count": "[variables('Count B')]"
      },
      "name": "[concat(parameters('Server Name'), '/', variables('DB B Prefix'), copyIndex())]",
      "location": "[resourceGroup().location]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', parameters('Server Name'))]",
        "[resourceId('Microsoft.Sql/servers/elasticpools', parameters('Server Name'), variables('Pool B'))]"
      ],
      "tags": {
        "displayName": "Pool-B DBs"
      },
      "apiVersion": "2014-04-01-preview",
      "properties": {
        "edition": "[parameters('Pool B Edition')]",
        "collation": "SQL_Latin1_General_CP1_CI_AS",
        "maxSizeBytes": "[parameters('DB Max Size')]",
        "requestedServiceObjectiveName": "ElasticPool",
        "elasticPoolName": "[variables('Pool B')]"
      }
    }
  ]
}

We can deploy the template as is.  We’ll need to enter at least an Admin password (for the Azure SQL server).

The “Server Name” parameter must be unique throughout Azure (not just your subscription).  So if it happens to be taken when you try to deploy the template (in which case you would receive an error message around Server ‘pooldemoserver’ is busy with another operation), try a new, more original name.

Each parameter is documented in the metadata description.

Results

Let’s look at the result.  Let’s first go in the resource group where we deployed the template.

In the resource list we should see the following:

image

We first have our server, with default name pooldemoserver, our two pools, Pool-A & Pool-B, and 6 databases.

Let’s select Pool-A.

image

We can see the pool is of Standard edition, has 200 eDTUs with a minimum of 10 eDTUs and maximum 50 per databases, which is faithful to its ARM definition (line 10-13).

        {
          "type": "elasticpools",
          "name": "[variables('Pool A')]",
          "apiVersion": "2014-04-01-preview",
          "location": "[resourceGroup().location]",
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', parameters('Server Name'))]"
          ],
          "properties": {
            "edition": "[parameters('Pool A Edition')]",
            "dtu": "200",
            "databaseDtuMin": "10",
            "databaseDtuMax": "50"
          }
        }

Similarly, Pool-B has a minimum of 0 and a maximum of 100.  The maximum was set to null in the template and hence is the maximum allowed for a standard pool of 400 DTUs.

Let’s select the databases in Pool-B.  Alternatively, we can select the Configure pool tool bar option.

image

The following pane shows us the eDTUs consumed in the last 14 days.  It also allows us to change the assigned eDTUs to the pool.

It is in this pane that we can add / remove databases from the pool.

image

In order to remove databases from the pool, they must first be selected in the lower right pane corner.  We will have to chose a standalone pricing tier for each DB and hit save.  As of today (December 2016), there are no way to move databases from one pool to another directly, i.e. they must first be converted as a stand alone.  It is possible to move databases from a pool to another using PowerShell as I’ll demonstrate in a future article though.

If we go back to the resource group and select any of the database, we have a link to its parent pool.

image

Summary

Despite the current lack (as of December 2016) of documentation around it, it is quite possible to create databases within an elastic pool using ARM templates as we’ve demonstrated here.


8 thoughts on “Azure SQL Elastic Pool – ARM Templates

  1. Thank you so much for sharing this. I am working on a solution that builds databases and then adds them to a pool, all via an ARM template. This is going to get me started on the right track.

  2. Also, a quick question for veritifaction. Are you automatically adding the databases you are creating into the elastic pools? The screen shots confused me a bit, as you show the databases already in the pools. I am struggling to have my template create the elastic pool and THEN automatically add the database to that new pool. I’ve got my resources structured out and using the appropriate depends on. The below lines of code (properties of databases) seem like they should assign the databaseto the elastic pool, but I’m having no luck.

        "requestedServiceObjectiveName": "ElasticPool",
        "elasticPoolName": "[variables('Pool B')]"
    
    1. Hi Vincent,

      I am trying to reverse engineer yours to make it fit for my scenario. I misspoke, yours does work properly and add the databases into the elastic pool – but even using your same logic, I am having trouble with mine. I even switched my template around to build the pools within the same resource as the database server, thinking it was something associated with that. Would you mind giving my template a quick glance and maybe provide any guidance? I’ve tried playing with the api versions, thinking that maybe the newer ones do not support the “requestedServiceObjectiveName”: “ElasticPool”, command the way the 2014-preview one does, but I am having no luck. Please note this is a nested template that is kicked off from a parent template. It does build out all of the resources, just is not automatically putting the database I am creating INTO the elastic pool I am creating. Thanks so much!

      {
      “$schema”: “https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#”,
      “contentVersion”: “1.0.0.0”,
      “parameters”: {
      “administratorLogin”: {
      “type”: “string”,
      “metadata”: {
      “description”: “The SQL Server administrator login”
      }
      },
      “administratorLoginPassword”: {
      “type”: “securestring”,
      “metadata”: {
      “description”: “The SQL Server administrator login password.”
      }
      },
      “collation”: {
      “type”: “string”,
      “defaultValue”: “SQL_Latin1_General_CP1_CI_AS”
      },
      “databaseName”: {
      “type”: “string”,
      “defaultValue”: “DBnameunique”
      },
      “elasticPool”: {
      “type”: “string”,
      “defaultValue”: “Yes”,
      “allowedValues”: [
      “Yes”,
      “No”
      ]
      },
      “elasticPoolName”: {
      “type”: “string”,
      “defaultValue”: “elasticPoolName”
      },
      “elasticPoolEdition”: {
      “type”: “string”,
      “defaultValue”: “Standard”
      },
      “poolDtu”: {
      “type”: “int”,
      “defaultValue”: 100
      },
      “databaseDtuMin”: {
      “type”: “int”,
      “defaultValue”: 0
      },
      “databaseDtuMax”: {
      “type”: “int”,
      “defaultValue”: 100
      },
      “tier”: {
      “type”: “string”,
      “defaultValue”: “Standard”
      },
      “dbSkuName”: {
      “type”: “string”,
      “defaultValue”: “S1”
      },
      “maxSizeBytes”: {
      “type”: “int”,
      “defaultValue”: 268435456000
      },
      “serverName”: {
      “type”: “string”,
      “defaultValue”: “testsqltest”
      },
      “sampleName”: {
      “type”: “string”,
      “defaultValue”: “”
      },
      “zoneRedundant”: {
      “type”: “bool”,
      “defaultValue”: false
      },
      “dbLicenseType”: {
      “type”: “string”,
      “defaultValue”: “”
      },
      “readScaleOut”: {
      “type”: “string”,
      “defaultValue”: “Disabled”
      },
      “numberOfReplicas”: {
      “type”: “int”,
      “defaultValue”: 0
      },
      “enableATP”: {
      “type”: “bool”,
      “defaultValue”: false
      },
      “allowAzureIps”: {
      “type”: “bool”,
      “defaultValue”: true
      }
      },
      “variables”: {
      “resourceGroupLocation”: “[resourceGroup().location]”
      },
      “resources”: [
      {
      “apiVersion”: “2014-04-01-preview”,
      “location”: “[variables(‘resourceGroupLocation’)]”,
      “name”: “[parameters(‘serverName’)]”,
      “properties”: {
      “administratorLogin”: “[parameters(‘administratorLogin’)]”,
      “administratorLoginPassword”: “[parameters(‘administratorLoginPassword’)]”,
      “version”: “12.0”
      },
      “resources”: [
      {
      “condition”: “[equals(parameters(‘elasticPool’), ‘Yes’)]”,
      “apiVersion”: “2014-04-01-preview”,
      “dependsOn”: [
      “[concat(‘Microsoft.Sql/servers/’, parameters(‘serverName’))]”
      ],
      “location”: “[variables(‘resourceGroupLocation’)]”,
      “name”: “[concat(parameters(‘serverName’), ‘/’, parameters(‘elasticPoolName’))]”,
      “properties”: {
      “edition”: “[parameters(‘elasticPoolEdition’)]”,
      “dtu”: “[parameters(‘poolDtu’)]”,
      “databaseDtuMin”: “[parameters(‘databaseDtuMin’)]”,
      “databaseDtuMax”: “[parameters(‘databaseDtuMax’)]”
      },
      “type”: “Microsoft.Sql/servers/elasticPools”
      },
      {
      “condition”: “[parameters(‘allowAzureIps’)]”,
      “apiVersion”: “2014-04-01-preview”,
      “dependsOn”: [
      “[concat(‘Microsoft.Sql/servers/’, parameters(‘serverName’))]”
      ],
      “location”: “[variables(‘resourceGroupLocation’)]”,
      “name”: “AllowAllWindowsAzureIps”,
      “properties”: {
      “endIpAddress”: “0.0.0.0”,
      “startIpAddress”: “0.0.0.0”
      },
      “type”: “firewallrules”
      },
      {
      “condition”: “[parameters(‘enableATP’)]”,
      “apiVersion”: “2017-03-01-preview”,
      “type”: “securityAlertPolicies”,
      “name”: “Default”,
      “dependsOn”: [
      “[concat(‘Microsoft.Sql/servers/’, parameters(‘serverName’))]”,
      “[concat(‘Microsoft.Sql/servers/’, parameters(‘serverName’), ‘/databases/’, parameters(‘databaseName’))]”
      ],
      “properties”: {
      “state”: “Enabled”,
      “disabledAlerts”: [],
      “emailAddresses”: [],
      “emailAccountAdmins”: true
      }
      }
      ],
      “type”: “Microsoft.Sql/servers”
      },
      {
      “apiVersion”: “2017-03-01-preview”,
      “dependsOn”: [
      “[concat(‘Microsoft.Sql/servers/’, parameters(‘serverName’))]”,
      “[resourceId(‘Microsoft.Sql/servers/elasticpools’, parameters(‘serverName’), parameters(‘elasticPoolName’))]”
      ],
      “location”: “[variables(‘resourceGroupLocation’)]”,
      “name”: “[concat (parameters(‘serverName’), ‘/’, parameters(‘databaseName’))]”,
      “properties”: {
      “collation”: “[parameters(‘collation’)]”,
      “maxSizeBytes”: “[parameters(‘maxSizeBytes’)]”,
      “sampleName”: “[parameters(‘sampleName’)]”,
      “zoneRedundant”: “[parameters(‘zoneRedundant’)]”,
      “licenseType”: “[parameters(‘DBlicenseType’)]”,
      “readScale”: “[parameters(‘readScaleOut’)]”,
      “readReplicaCount”: “[parameters(‘numberOfReplicas’)]”,
      “requestedServiceObjectiveName”: “ElasticPool”,
      “elasticPoolName”: “[concat (parameters(‘serverName’), ‘/’, parameters(‘elasticPoolName’))]”
      },
      “sku”: {
      “name”: “[parameters(‘DBskuName’)]”,
      “tier”: “[parameters(‘tier’)]”
      },
      “type”: “Microsoft.Sql/servers/databases”
      }
      ],
      “outputs”: {}
      }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s