Creating an Azure Policy to automatically give any SQL Managed Instance Databases a LTR instead of manually assigning them to ea

admin管理员组

文章数量:1315332

I wanted to create a policy that automatically gives databases a long term retention instead of doing it manually every time so I created this code but for some reason the database does not get any retention automatically.

There are no errors when creating it and I have assigned it to the resource group where the SQL MI resides. It is not a role issue either. Could anyone explain why it isn't working? I have created a new database and waited a few hours for it to fully deploy but it was not given the weekly, monthly, or yearly retention.

{
  "mode": "All",
  "policyRule": {
    "if": {
      "allOf": [
        {
          "field": "type",
          "equals": "Microsoft.Sql/managedInstances/databases"
        }
      ]
    },
    "then": {
      "effect": "deployIfNotExists",
      "details": {
        "type": "Microsoft.Sql/managedInstances/databases/backupLongTermRetentionPolicies",
        "existenceCondition": {
          "anyOf": [
            {
              "field": "Microsoft.Sql/managedInstances/databases/backupLongTermRetentionPolicies/weeklyRetention",
              "notEquals": "P12W"
            },
            {
              "field": "Microsoft.Sql/managedInstances/databases/backupLongTermRetentionPolicies/monthlyRetention",
              "notEquals": "P12M"
            },
            {
              "field": "Microsoft.Sql/managedInstances/databases/backupLongTermRetentionPolicies/yearlyRetention",
              "notEquals": "P5Y"
            }
          ]
        },
        "roleDefinitionIds": [
          "/providers/Microsoft.Authorization/roleDefinitions/b24988ac-6180-42a0-ab88-20f7382dd24c"
        ],
        "deployment": {
          "properties": {
            "mode": "incremental",
            "template": {
              "$schema": ".json#",
              "contentVersion": "1.0.0.0",
              "resources": [
                {
                  "type": "Microsoft.Sql/managedInstances/databases/backupLongTermRetentionPolicies",
                  "apiVersion": "2021-02-01-preview",
                  "name": "[concat(parameters('fullDbName'), '/default')]",
                  "properties": {
                    "weeklyRetention": "P12W",
                    "monthlyRetention": "P12M",
                    "yearlyRetention": "P5Y",
                    "weekOfYear": 1
                  }
                }
              ]
            }
          },
          "databaseName": {
            "value": "[field('name')]"
          }
        }
      }
    }
  },
  "parameters": {}
}

I wanted to create a policy that automatically gives databases a long term retention instead of doing it manually every time so I created this code but for some reason the database does not get any retention automatically.

There are no errors when creating it and I have assigned it to the resource group where the SQL MI resides. It is not a role issue either. Could anyone explain why it isn't working? I have created a new database and waited a few hours for it to fully deploy but it was not given the weekly, monthly, or yearly retention.

{
  "mode": "All",
  "policyRule": {
    "if": {
      "allOf": [
        {
          "field": "type",
          "equals": "Microsoft.Sql/managedInstances/databases"
        }
      ]
    },
    "then": {
      "effect": "deployIfNotExists",
      "details": {
        "type": "Microsoft.Sql/managedInstances/databases/backupLongTermRetentionPolicies",
        "existenceCondition": {
          "anyOf": [
            {
              "field": "Microsoft.Sql/managedInstances/databases/backupLongTermRetentionPolicies/weeklyRetention",
              "notEquals": "P12W"
            },
            {
              "field": "Microsoft.Sql/managedInstances/databases/backupLongTermRetentionPolicies/monthlyRetention",
              "notEquals": "P12M"
            },
            {
              "field": "Microsoft.Sql/managedInstances/databases/backupLongTermRetentionPolicies/yearlyRetention",
              "notEquals": "P5Y"
            }
          ]
        },
        "roleDefinitionIds": [
          "/providers/Microsoft.Authorization/roleDefinitions/b24988ac-6180-42a0-ab88-20f7382dd24c"
        ],
        "deployment": {
          "properties": {
            "mode": "incremental",
            "template": {
              "$schema": "https://schema.management.azure/schemas/2019-04-01/deploymentTemplate.json#",
              "contentVersion": "1.0.0.0",
              "resources": [
                {
                  "type": "Microsoft.Sql/managedInstances/databases/backupLongTermRetentionPolicies",
                  "apiVersion": "2021-02-01-preview",
                  "name": "[concat(parameters('fullDbName'), '/default')]",
                  "properties": {
                    "weeklyRetention": "P12W",
                    "monthlyRetention": "P12M",
                    "yearlyRetention": "P5Y",
                    "weekOfYear": 1
                  }
                }
              ]
            }
          },
          "databaseName": {
            "value": "[field('name')]"
          }
        }
      }
    }
  },
  "parameters": {}
}

• azure
• azure-sql-database
• azure-policy
• azure-sql-managed-instance

Share Improve this question Follow asked Jan 30 at 9:34 DanDan 1

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

When you say the policy is triggered but not applied on the database Look at the 'Activity Logs' on the corresponding Azure SQL managed instance Server.

• Go to your Azure SQL managed instance Server >> Activity Log.
• Look for deployIfNotExists policy on your Azure SQL Managed Instance or database server.
• The error message will provide details on why the policy failed.

Other possible reasons are as follows:

• Incorrect Name Reference in Deployment might cause the issue while applying the Policy on database, or there may be parameters you created are incorrect.
• make sure you applied policy on correct scope either on resource group level or database server level.
• parameter values provided in the policy definition match the expected data types and formats. For example, LTRWeekOfYear is defined as an integer, so ensure that the value provided is a valid integer

本文标签：