dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted March 27, 2019

Configuring Azure SQL Databases Using Azure Resource Manager Templates

By Marcin Policht

In a recent article (Using Azure Resource Manager Templates To Provision Azure SQL Databases) published on this site, a sample Azure Resource Manager template was presented that provisions a new logical server hosting a single Azure SQL Database instance. Template-based provisioning simplifies deployment and promotes principles of DevOps and Infrastructure as Code, making it the recommended method for implementing cloud-based services. However, its benefits extend beyond initial implementation, since you can apply it to configuring and maintaining existing Azure SQL Database instances. 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. Other uses of templates will be illustrated in upcoming articles published on DatabaseJournal.

    See All Articles by Marcin Policht



MS SQL Archives




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM