Setting SQL Backup Redundancy with DeployIfNotExists - Possible?

Yannick Janssens 21 Reputation points
2022-01-14T09:21:47.3+00:00

I am attempting to use Policy in order to enforce a specific Backup Redundancy setting (Local, Zone or Geo) on Azure SQL Databases based on a tag that is set on that Database.

The problem I'm seeing is that I don't believe it's possible to use DeployIfNotExists since the BackupRedundancy setting does not reside in a 'related' resource to 'Microsoft.Sql/servers/databases'.

The property is: 

"field": "Microsoft.Sql/servers/databases/currentBackupStorageRedundancy"

I would like to use a template deployment that looks like this: 

                "template": {
                  "$schema": "http://schema.management.azure.com/schemas/2014-04-01-preview/deploymentTemplate.json#",
                  "contentVersion": "1.0.0.0",
                  "parameters": {
                    "DB": {
                      "type": "String"
                    }
                  },
                  "variables": {},
                  "resources": [
                    {
                      "type": "Microsoft.Sql/servers/databases",
                      "apiVersion": "2021-02-01-preview",
                      "name": "[parameters('DB')]",
                      "location": "westeurope",
                      "properties": {
                        "requestedBackupStorageRedundancy": "Geo"
                      }
                    }
                  ]
                }

Is there a way to achieve this?

Azure SQL Database
Azure Policy
Azure Policy
An Azure service that is used to implement corporate governance and standards at scale for Azure resources.
797 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Martin Cairney 2,241 Reputation points
    2022-01-17T11:53:52.76+00:00

    My understanding is that DeployIfNotExists is used to include missing properties - whereas if you already have a deployment then you would want a MODIFY policy that looks for your tag and sets the property accordingly.

    0 comments
  2. Yannick Janssens 21 Reputation points
    2022-01-17T19:11:48.69+00:00

    Apparently you can use the Modify effect to change existing properties if the alias supports it. But it seems in my case it doesn't:

    Creating policy definition 'xxx' in 'Microsoft Azure' failed. The policy definition 'xxx' has operations referring to aliases that are not modifiable: 'Microsoft.Sql/servers/databases/requestedBackupStorageRedundancy'.

    I did find this sub-property that I can probably change with the DeployIfNotExists effect but that doesn't cover the Zone-redundant option... 

        {
        "type": "Microsoft.Sql/servers/databases/geoBackupPolicies",
        "apiVersion": "2014-04-01",
        "name": "xxx/xxx/Default')]",
        "location": "West Europe",
        "dependsOn": [
            "[resourceId('Microsoft.Sql/servers/databases', 'xxx', 'xxx')]"
        ],
        "properties": {
            **"state": "Enabled"**
        }
    },
    0 comments