Configuring Azure SQL Databases Using Azure Resource Manager Templates

In this article, you are provided with an example of this approach.

When considering the use of ARM templates for modifying the configuration to existing instances of an Azure SQL Database, it is important to note that, by default, template deployments are incremental in nature. As the result, the scope of changes is limited to resources and their properties, which are explicitly referenced in the template, while all others within the same resource group remain intact. This simplifies template authoring and minimizes duration of template deployment.

Consider a scenario in which Azure SQL Database auditing needs to be enabled on a logical server (which, by default, is inherited by all of its databases). This configuration is controlled by the properties of the auditingSettings resource type:

        {
          "name": "DefaultAuditingSettings",
          "type": "auditingSettings",
          "apiVersion": "2017-03-01-preview",
          "dependsOn": [
             "<server_resourceId>",
             "<storage_account_resourceId>"
          ],
          "properties": {
             "state": "Enabled",
             "storageEndpoint": "<storage_account_blob_endpoint_Uri>",
             "storageAccountAccessKey": "<storage_account_primary_key>",
             "storageAccountSubscriptionId": "<subscriptionId>",
             "retentionDays": 0,
             "auditActionsAndGroups": null,
             "isStorageSecondaryKeyInUse": false
          }
        }

As you can see, the configuration requires access to an existing Azure Storage account, established based on the combination of the account name and one of its keys. Not surprisingly, the auditing settings also require an existing Azure SQL Database logical server. To implement the resource, a template is used that will, additionally, automatically provision the storage account so that its only parameter will be the logical server name. There are a few more interesting aspects of the template:

◈ The name of the storage account must be globally unique. In order to generate it, rely on the uniqueString() function with the seed value derived from the resource group identifier (guaranteed to be globally unique). The resulting string can also be prepended with the 'st' prefix by using the concat() function in order to ensure that the storage account name starts with a letter.

◈ The storage account name is stored in a variable to facilitate multiple references to its value throughout the template

◈ The value of the first storage account key is retrieved by using the listKeys() function

◈ The target Azure region where the deployment takes place is determined based on the location of the resource group. Note that this presumes that the resource group location matches the location of the logical server.

{
  "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "serverName": {
      "type": "string"
    }
  },
  "variables": {
      "storageAccountName": "[concat('st', uniqueString(resourceGroup().id))]"
  },
  "resources": [
    {
      "type": "Microsoft.Storage/storageAccounts",
      "name": "[variables('storageAccountName')]",
      "apiVersion": "2018-07-01",
      "location": "[resourceGroup().location]",
        "sku": {
          "name": "Standard_LRS"
        },
      "kind": "Storage",
      "properties": {
      }
    },
    {
      "name": "[parameters('serverName')]",
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2015-05-01-preview",
      "location": "[resourceGroup().location]",
      "properties": {
        "version": "12.0"
      },
      "resources": [
        {
          "name": "DefaultAuditingSettings",
          "type": "auditingSettings",
          "apiVersion": "2017-03-01-preview",
          "dependsOn": [
             "[concat('Microsoft.Sql/servers/', parameters('serverName'))]",
             "[concat('Microsoft.Storage/storageAccounts/', variables('storageAccountName'))]"
          ],
          "properties": {
             "state": "Enabled",
             "storageEndpoint": "[concat('https://', variables('storageAccountName'), '.blob.core.windows.net/')]",
             "storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', variables('storageAccountName')), providers('Microsoft.Storage', 'storageAccounts').apiVersions[0]).keys[0].value]",
             "storageAccountSubscriptionId": "[subscription().subscriptionId]",
             "retentionDays": 0,
             "auditActionsAndGroups": null,
             "isStorageSecondaryKeyInUse": false
          }
        }
      ]
    }
  ],
  "outputs": {}
}

Deploying the template will automatically result in the logical server auditing enabled with unlimited retention and the storage account included in the deployment configured as the audit log destination. This concludes the sample, template-based configuration of an existing Azure SQL Database logical server.