DevOps with Sitecore on Azure Part 2 - Using SQL Elastic Pools with Sitecore

DevOps with Sitecore on Azure Part 2 - Using SQL Elastic Pools with Sitecore

This is the second of two posts with tips and tricks about running Sitecore on PaaS services in Azure. Today I will show how to use SQL Elastic Pools with Sitecore.

Introduction

Azure SQL Elastic Database Pools allow you to group your databases and use a shared pool of resources. Instead of assigning a fixed set of performance capabilities to a single database (measured in DTU), you assign the capabilities to the shared pool.

Elastic Pools are very useful with databases that have varying, unpredictable or spiky usage patterns. A database can burst in performance and take the resources it needs from the pool. This makes it very useful in a Sitecore implementation on Azure. In this blog post I will describe how you can modify your ARM templates to deploy Sitecore 9 in Azure using an Sql Elastic Pool.

When to use Elastic Pools?

Elastic Pools are very useful when you have a collection of databases with a relatively low average utilization, but that have occasional spikes in the capacity that they require.

The image below, taken from the Microsoft docs website, shows an example where you have an Elastic Pool that has a pool size of 100 DTU. The image shows that usage patterns differs per database, and that they each can take the capacity of the shared resources to burst.

Usage patterns in Elastic Pool

In my experience many Sitecore setups show these kind of usage patterns. When you follow the Sitecore guidelines for sizing your Azure resources, I found that you often run in the situation where the databases spike in utilization and become a bottleneck.

You could simply scale up your individual databases to a higher SKU without using an Elastic Pool. But then you might be paying for capacity that you only need occasionally. On the other hand, Elastic Pools are generally a bit more expensive. But I found that the additional costs are worth it. Especially in acceptance or production environments I often choose to implement an Elastic Pool.

When determining the pool size in the Elastic Pool, I usually just add up the DTU’s per database as listed in the Sitecore sizing recommendations to determine how many DTU’s I need for all the databases. Then I look at the Elastic Pool size that best matches the DTU’s that I need in total. By the way, I usually round the number down because some of the Sitecore databases require very little capacity and infrequently spike.

The ARM templates

ARM templates are used to deploy Sitecore on Azure. We will be using the ARM templates that are provided by Sitecore in their Github repository. We need to make changes to several ARM templates in order to create the Elastic Pool and to link the Sql databases to the it.

In this article I am deploying Sitecore 9 to Azure. And in order to make use of an Sql Elastic Pool you need to change the following templates, when deploying either XM Scaled, XM Single, XP Scaled or SX Single.

  • azuredeploy.parameters.json
  • azuredeploy.json
  • infrastructure.json

azuredeploy.parameters.json

I am defining two parameters in the azuredeploy.parameters.json file related to the Elastic Pool. Parameter “sqlElasticPoolName” provides the name of the Elastic Pool resource and “sqlElasticPoolLimit” determines the amount of DTU’s that you are assigning to the pool.

1"sqlElasticPoolName":{
2  "value": ""
3},
4"sqlElasticPoolLimit": {
5  "value": ""
6},

azuredeploy.json file

Step 1: Defining the Elastic Pool related parameters in the azuredeploy.son file. In my case, I have defined five parameters in the azuredeploy.json.

 1"sqlElasticPoolName": {
 2    "type": "string",
 3    "minLength": 1,
 4    "defaultValue": "[concat(parameters('deploymentId'), '-sqlpool')]"
 5},
 6"sqlElasticPoolskuName": {
 7    "type": "string",
 8    "minLength": 1,
 9    "defaultValue": "StandardPool"
10},
11"sqlElasticPoolTier": {
12    "type": "string",
13    "minLength": 1,
14    "defaultValue": "Standard"
15},
16"sqlElasticPoolLimit": {
17    "type": "string",
18    "defaultValue": "100"
19},
20"sqlElasticPoolStorageSize": {
21    "type": "int",
22    "minValue": 1000000000,
23    "defaultValue": 50000000000
24},

You might notice that I didn’t reference all of the parameters in the parameter file. This is because I have assigned default values that I will probably always remain unchanged.

Step 2: Add the parameter to the resource where the nested infrastructure.json template is referenced: “[concat(parameters(‘deploymentId’), ‘-infrastructure’)]”

 1"sqlElasticPoolName": {
 2    "value": "[parameters('sqlElasticPoolName')]"
 3},
 4"sqlElasticPoolskuName": {
 5    "value": "[parameters('sqlElasticPoolskuName')]"
 6},
 7"sqlElasticPoolTier": {
 8    "value": "[parameters('sqlElasticPoolTier')]"
 9},
10"sqlElasticPoolLimit": {
11    "value": "[parameters('sqlElasticPoolLimit')]"
12},
13"sqlElasticPoolStorageSize": {
14    "value": "[parameters('sqlElasticPoolStorageSize')]"
15},

infrastructure.json file

Step 1: Add the five parameters that we defined in the azuredeploy.json file in the infrastructure.json file. You can simply select and copy those parameters from the parameters section in azurdeploy.json to infrastructure.json.

Step 2: We need to add some json to actually create the Sql Elastic Pool. In the snippet below you can see that I created a (child) resouce.

 1    {
 2      "type": "Microsoft.Sql/servers",
 3      "apiVersion": "[variables('dbApiVersion')]",
 4      "properties": {
 5        "administratorLogin": "[parameters('sqlServerLogin')]",
 6        "administratorLoginPassword": "[parameters('sqlServerPassword')]",
 7        "version": "[parameters('sqlServerVersion')]"
 8      },
 9      "name": "[variables('sqlServerNameTidy')]",
10      "location": "[parameters('location')]",
11      "tags": {
12        "provider": "[variables('sitecoreTags').provider]"
13      },
14      "resources": [
15        {
16          "apiVersion": "2017-10-01-preview",
17          "dependsOn": [
18            "[resourceId('Microsoft.Sql/servers', variables('sqlServerNameTidy'))]"
19          ],
20          "location": "[parameters('location')]",
21          "name": "[concat(variables('sqlServerNameTidy'), '/', parameters('sqlElasticPoolName'))]",
22          "sku": {
23            "name": "[parameters('sqlElasticPoolskuName')]",
24            "tier": "[parameters('sqlElasticPoolTier')]",
25            "capacity": "[parameters('sqlElasticPoolLimit')]"
26          },
27          "properties": {
28            "licenseType": "",
29            "storageMB": "[parameters('sqlElasticPoolStorageSize')]"
30          },
31          "type": "Microsoft.Sql/servers/elasticpools"
32        },

Please note that you have the option to configure a minimum capacity all databases are guaranteed, and the maximum capacity any one database can consume. This can be relevant if you have a database that is using all the resources without leaving anything for other databases. The snippet above doesn’t use these options. For more information about limits, see this article, and this page for the ARM template reference.

More information

You can find the complete set of ARM templates that I created for this article in my Github repository. Also check out this Microsoft docs page for a detailed description about Sql Elastic Pools.