azureazure-resource-managerazure-rm-template

Provide a dynamic list of databases to ARM template FailOverGroup


I am trying to create a FailOverGroup for a SQL Server via an ARM template. In an ideal world, I'd like to be able to pass an array of database names into my template and use that to generate an array of database resources, however I haven't been able to make that work. The next attempt was to add all databases in an Elastic Pool to the FOG, but again, I can't find a way of pulling the databases in a pool via an ARM template. The following is my current example with 'hard coded' database names;

{
    "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "origin_sql_server_name": {
            "type": "string"
        },
        "web-database-name": {
            "type": "string"
        },
        "core-database-name": {
            "type": "string"
        },
        "failover-database-server-name": {
            "type": "string"
        },
        "dr-resource-group-name": {
            "type": "string"
        }
    },
    "variables": {
        "fog-name": "[concat(parameters('origin_sql_server_name'),'/', parameters('origin_sql_server_name'),'-fog')]"
    },
    "resources": [
        {
            "type": "Microsoft.Sql/servers/failoverGroups",
            "apiVersion": "2015-05-01-preview",
            "name": "[variables('fog-name')]",
            "properties": {
                "readWriteEndpoint": {
                    "failoverPolicy": "Automatic",
                    "failoverWithDataLossGracePeriodMinutes": 60
                },
                "readOnlyEndpoint": {
                    "failoverPolicy": "Disabled"
                },
                "partnerServers": [
                    {
                        "id": "[resourceId(parameters('dr-resource-group-name'),'Microsoft.Sql/servers', parameters('failover-database-server-name'))]"
                    }
                ],
                "databases": [
                    "[resourceId('Microsoft.Sql/servers/databases', parameters('origin_sql_server_name'), parameters('web-database-name'))]",
                    "[resourceId('Microsoft.Sql/servers/databases', parameters('origin_sql_server_name'), parameters('core-database-name'))]"
                ]
            }
        }
    ],
    "outputs":{
        "fogName": {
            "type": "string",
            "value": "[concat(parameters('origin_sql_server_name'),'-fog')]"
        }
    },
    "functions": [
    ]
}

The purpose of this approach is to have a single template that can be used across a few different sites that have similar, but slightly different database requirements. If this isn't possible, then I can have separate templates, but I'm trying to avoid that for maintainability purposes Thanks!


Solution

  • You can use array copy on the database property of the failoverGroup - replace your database property with:

        "copy": [
            {
                "name": "databases",
                "count": "[length(parameters('failOverDbs'))]",
                "input": "['Microsoft.Sql/servers/databases', parameters('origin_sql_server_name'), parameters('failOVerDbs')[copyIndex('databases')])]"
            }
        ]
    

    And then use an array parameter for your db list. That help?