Rajendra Gupta
Policy compliance details

Custom Azure Policy definitions for Azure SQL Databases backup retention periods compliance

September 22, 2021 by

This article uses Azure policies to check compliance of Azure SQL Databases against the backup retention period.

Introduction

Azure SQL Database offers DTU and vCores based pricing models. These service tiers primarily depend on the business continuity, storage, and performance requirements. In a managed database service, Azure automatically manages the database backups for point-in-time recovery. These database backups have different maximum retention periods based on their service tier.

  • Basic Service tier: 7 days
  • Standard and Premium Service tier: 35 days
  • Note: The default backup retention is seven days for all service tiers of the Azure SQL Database

For example, in my lab environment, the Azure database is in the Standard S0 pricing tier. It is configured with default backup retention. To verify the configured backup retention, go to Azure server configuration and click on Backups.

As shown below, PITR ( Point-In-Time-Recovery) is for 7 days.

Azure SQL DB PITR

To modify this configuration, select the database and configured policies. You can move the slider to increase the PITR value to 35 days. Similarly, you can reduce the PITR to a minimum of 1 day if you do not want to restore backups for the environment, such as the development database.

PITR configuration up to 35 days

Suppose you have a large Azure database infrastructure. You also have given permissions to specific users for database deployment. As a database administrator, you require the PITR value should be 35 days instead of the default 7 days period.

In the article, Identify and remediate non-compliant Azure SQL Databases for auditing on SQL Server, we explored the default Azure policy for Auditing on SQL server. This article guides you through creating a custom policy, implementing it, and remediating issues so that the database is fully compliant against the business rule or configuration.

To configure the custom policy definition, search for Policy in the Azure portal and navigate to Definitions.

Search Azure Policy definition

Click on Policy definition to define a custom policy. In the new policy definition, enter the following details:

  • Definition Location: In the definition location, click on eclipse and select the Azure subscription
  • Name: Enter the custom policy definition display name
  • Description: It is an optional field. You can use this field to describe the policy definition and requirement
  • Category: It is the Azure policy category that helps you to group policies together. For example, you can find all related policies in the SQL category belongs to Azure SQL Database. You can choose to create a new category or an existing category

Policy definition configuration

  • Policy Rule: In the policy rule, we define the JSON policy document to evaluate Azure resources.

For this article, we use the following JSON script. You can note the specified retention period is 35 days in this policy script. Suppose you want to configure a policy with a default backup retention of 7 days, modify the value from 35 days to 7 days.

Script Reference: Azure Database Support Blog

{
    “properties”: {
      “displayName”: “Azure PITR configration for 35 days”,
      “policyType”: “Custom”,
      “mode”: “Indexed”,
      “description”: “This policy checks Azure SQL Databases for 35 days backup retention period. “,
      “metadata”: {
        “version”: “1.1.0”,
        “category”: “SQL”,
        “createdBy”: “5623789b-3072-41a7-8b9b-74d388241482”,
        “createdOn”: “2021-05-19T11:57:08.9261396Z”,
        “updatedBy”: null,
        “updatedOn”: null
      },
      “parameters”: {
        “effect”: {
          “type”: “String”,
          “metadata”: {
            “displayName”: “Effect”,
            “description”: “Enable or disable the execution of the policy.”
          },
          “allowedValues”: [
            “DeployIfNotExists”,
            “Disabled”
          ],
          “defaultValue”: “DeployIfNotExists”
        },
        “retentionDays”: {
          “type”: “String”,
          “metadata”: {
            “displayName”: “Retention Days”,
            “description”: “Set the number of Backup Retention Days.”
          },
          “defaultValue”: “35”
        }
      },
      “policyRule”: {
        “if”: {
          “field”: “type”,
          “equals”: “Microsoft.Sql/servers/databases”
        },
        “then”: {
          “effect”: “[parameters(‘effect’)]”,
          “details”: {
            “type”: “Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies”,
            “name”: “default”,
            “roleDefinitionIds”: [
              “/providers/Microsoft.Authorization/roleDefinitions/9b7fa17d-e63e-47b0-bb0a-15c516ac86ec”
            ],
            “existenceCondition”: {
              “field”: “Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies/retentionDays”,
              “equals”: “[parameters(‘retentionDays’)]”
            },
            “deployment”: {
              “properties”: {
                “mode”: “incremental”,
                “template”: {
                  “$schema”: “https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#”,
                  “contentVersion”: “1.0.0.0”,
                  “parameters”: {
                    “serverName”: {
                      “type”: “string”
                    },
                    “shortTermRetention”: {
                      “type”: “string”
                    }
                  },
                  “resources”: [
                    {
                      “name”: “[concat(parameters(‘serverName’),’/default’)]”,
                      “type”: “Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies”,
                      “apiVersion”: “2017-10-01-preview”,
                      “properties”: {
                        “retentionDays”: “[parameters(‘shortTermRetention’)]”
                      }
                    }
                  ]
                },
                “parameters”: {
                  “serverName”: {
                    “value”: “[field(‘fullname’)]”
                  },
                  “shortTermRetention”: {
                    “value”: “[parameters(‘retentionDays’)]”
                  }
                }
              }
            }
          }
        }
      }
    },
    “id”: “/subscriptions/c6eb5552-7748-4d57-82bb-4c52c1c8f87e/providers/Microsoft.Authorization/policyDefinitions/f5d5c854-5a1c-4521-ab9d-ec2d82ccbc1d”,
    “type”: “Microsoft.Authorization/policyDefinitions”,
    “name”: “f5d5c854-5a1c-4521-ab9d-ec2d82ccbc1d”
  }

Policy JSON document

Save the policy definition, and it has the following properties:

  • Policy Name: Azure PITR configuration for 35 days
  • Policy type: Custom
  • Definition Type: Policy
  • Category: SQL

View newly created policy

Assign Azure custom policy for Azure SQL Database

In the previous section, we implemented an Azure policy for validation PITR configuration of 35 days for the Azure SQL database. After the implementation, we need to assign this policy to Azure resources to check its compliance.

In the Azure policy dashboard, click on Assign policy.

Assign Azure custom policy for Azure SQL Database

On the Assign policy page, enter the following information on the basic page.

  • Scope: Select the Azure subscription
  • Exclusion: If you wish to exclude any resources from the policy assignment, you can choose those resources. Suppose you have an Azure SQL Database that requires 7 days PITR due to specific reasons. Therefore, exclude that resource so that you are compliant with the remaining Azure databases
  • Policy definition: In this policy definition, we use the custom policy we defined earlier. You can select the custom type to find out the policy quickly

    Available definitions

  • Policy enforcement: Enabled
  • Assigned by: You can enter the policy assignee or owner name

    Policy enforcements

  • Parameters: The parameter page defines the policy effect and retention days. Here, we have specified 35 days as a retention period

    Effect and Retention days

  • Remediation: Azure remediation task can update the Azure resources to comply with the deployed policy. For example, if Azure SQL Database does not have 35 days retention period, the remediation task modifies the database configuration for compliance. Click on – Create a remediation task

    Create a remediation task

  • It uses the SQL DB contributor role based on the role definition in the policy. The role manages the Azure SQL Databases

    • Note: The SQL DB role cannot modify the configuration for SQL Server (instance) level. You can refer to Azure built-in roles for more details

    SQL DB contributor role

  • Non-Compliant message: Enter the message that the user gets if the policy outcome is Non-Compliant

    Non-Compliant message

In the last section, review all policy assignments carefully.

review policy assignment

Click on Create. During the policy assignment to Azure SQL Database, you get the following messages in the notification area.

Notifications

Check Policy compliance for Azure PITR configuration

To check the policy compliance for Azure PITR configuration, navigate to the Azure policy dashboard. As shown below, my lab environment database is non-compliant.

Check Policy compliance for Azure PITR configuration

Click on the policy name, and you get detailed information such as resource name, scope, parent resource, compliance state and non-compliance message.

Check Non-compliant message

We have configured remediation tasks as well during the policy assignment. It takes a few minutes for the remediation task to make the configuration changes. As shown below, its status is Evaluating.

Remediation task status - Evaluating

The status changes to In Progress after the evaluation is finished.

Remediation task status - In Progress

Open the remediation task properties to get the details. As shown below, policy remediation is successful for Azure SQL Database. However, it is failed for the master database.

Check status for each Azure DB

Click on deployment ID for the master database to know the failure reason. As shown below, modifying the feature is not available for the selected ( Master) database because you cannot make changes to it for the Azure database.

Deployment status

You can validate the Azure database backup PITR value. As shown below, it is modified to 35 days.

Validate Azure PITR value

In the Azure policy dashboard, the policy is showing Compliant after the remediation task is successful.

Policy status changes to compliant

Click on the Azure policy to check individual resource compliance. If you have multiple Azure databases, you can check the status of individual resources.

Policy compliance details

Conclusion

This article explored the custom Azure policy for validating the PITR configuration of Azure SQL Databases. You can use it for checking the compliance status of Azure databases against the policy configuration. You can also configure the remediation task to change Azure DB configuration and change their status from Non-Compliant to Compliant. It also makes sure you have a sufficient PITR configuration that protects you against accidental data losses or issues.

Rajendra Gupta
Azure, SQL Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

260 Views