Setting SQL Backup Redundancy with DeployIfNotExists - Possible?

Question

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?

Answer 1

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"**
        }
    },

Answer 2

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.