How to ARM Template Azure SQL Failover Group?

Question

How can I create a Azure SQL Failover Group in a different deployment to the servers?

We use deployments within deployments to achieve concurrent deployments.

I'm trying to create 2 SQL Servers, one in UK West (primary) and one in UK South (secondary), and then create a Failover group from the Primary to Secondary.

The issue is that when creating the Failover group, I have to reference the primary server to create the FOG under. This is failing and saying that the SQL Server is not defined.

Deployment template validation failed: 'The resource 'Microsoft.Sql/servers/xxxxxx' is not defined in the template. Please see https://aka.ms/arm-template for usage details.'

Is it possible to keep the deployments separate, yet still create the FOG which references the SQL Servers? All examples I can find are using a single template/deployment which makes matters slightly more straightforward.

maindeployment.json

{
  "apiVersion": "2018-05-01",
  "name": "sqlServerTemplate",
  "type": "Microsoft.Resources/deployments",
  "properties": {
    "mode": "Incremental",
    "templateLink": {
      "uri": "[replace(variables('templateLinkUri'), '*', 'sql-server')]",
      "contentVersion": "1.0.0.0"
    },
    "parameters": {
      "name": {
        "value": "[variables('sqlServerName')]"
      },
      "location": {
        "value": "[parameters('location')]"
      },
      "adminUsername": {
        "value": "[variables('sqlServerAdminUsername')]"
      },
      "adminPassword": {
        "value": "[variables('sqlServerAdminPassword')]"
      }
    }
  }
},
{
  "apiVersion": "2018-05-01",
  "name": "dbTemplate",
  "type": "Microsoft.Resources/deployments",
  "properties": {
    "mode": "Incremental",
    "templateLink": {
      "uri": "[replace(variables('templateLinkUri'), '*', 'sql-database')]",
      "contentVersion": "1.0.0.0"
    },
    "parameters": {
      "dbName": {
        "value": "[variables('dbName')]"
      },
      "sqlServerName": {
        "value": "[variables('sqlServerName')]"
      },
      "location": {
        "value": "[parameters('location')]"
      },
      "skuName": {
        "value": "[parameters('dbSkuName')]"
      },
      "dbCapacity": {
        "value": "[parameters('dbCapacity')]"
      }
    }
  },
  "dependsOn": [
    "sqlServerTemplate"
  ]
},
{
  "apiVersion": "2018-05-01",
  "name": "failoverSqlServerTemplate",
  "type": "Microsoft.Resources/deployments",
  "properties": {
    "mode": "Incremental",
    "templateLink": {
      "uri": "[replace(variables('templateLinkUri'), '*', 'sql-server-failover')]",
      "contentVersion": "1.0.0.0"
    },
    "parameters": {
      "name": {
        "value": "[variables('failoverSqlServerName')]"
      },
      "location": {
        "value": "[parameters('failoverLocation')]"
      },
      "adminUsername": {
        "value": "[variables('sqlServerAdminUsername')]"
      },
      "adminPassword": {
        "value": "[variables('sqlServerAdminPassword')]"
      }
    }
  }
},
{
  "apiVersion": "2018-05-01",
  "name": "sqlFailoverGroupTemplate",
  "type": "Microsoft.Resources/deployments",
  "properties": {
    "mode": "Incremental",
    "templateLink": {
      "uri": "[replace(variables('templateLinkUri'), '*', 'sql-failovergroup')]",
      "contentVersion": "1.0.0.0"
    },
    "parameters": {
      "failoverGroupName": {
        "value": "[variables('failoverGroupName')]"
      },
      "sourceSqlServerName": {
        "value": "[reference('sqlServerTemplate').parameters.name.value]"
      },
      "targetSqlServerName": {
        "value": "[reference('failoverSqlServerTemplate').parameters.name.value]"
      },
      "sqlDatabaseNameToReplicate": {
        "value": "[reference('dbTemplate').parameters.dbName.value]"
      }
    }
  }
}

sql-failovergroup.json

{
"$schema": "https://schema.management.azure.com/schemas/2018-05-01/subscriptionDeploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "failoverGroupName": {
      "type": "string"
    },
    "sourceSqlServerName": {
      "type": "string"
    },
    "targetSqlServerName": {
      "type": "string"
    },
    "sqlDatabaseNameToReplicate": {
      "type": "string"
    }
  },
  "variables": {
    "TODO": "Figure out how to reference the SQL Server as the below method is failing with... Error: Code=InvalidTemplate; Message=Deployment template validation failed: 'The resource 'Microsoft.Sql/servers/xxxxx' is not defined in the template.",
    "sourceServerResourceId": "[resourceId('Microsoft.Sql/servers', parameters('sourceSqlServerName'))]",
    "targetServerResourceId": "[resourceId('Microsoft.Sql/servers', parameters('targetSqlServerName'))]",
    "databaseResourceId": "[concat(resourceGroup().id, '/providers/Microsoft.Sql/servers/', parameters('sourceSqlServerName'), '/databases/', parameters('sqlDatabaseNameToReplicate'))]"
  },
  "resources": [
    {
      "name": "[concat(parameters('sourceSqlServerName'), '/', parameters('failoverGroupName'))]",
      "type": "Microsoft.Sql/servers/failoverGroups",
      "apiVersion": "2015-05-01-preview",
      "properties": {
        "readWriteEndpoint": {
          "failoverPolicy": "Manual",
          "failoverWithDataLossGracePeriodMinutes": 60
        },
        "readOnlyEndpoint": {
          "failoverPolicy": "Disabled"
        },
        "partnerServers": [
          {
            "id": "[variables('targetServerResourceId')]"
          }
        ],
        "databases": [
          "[variables('databaseResourceId')]"
        ]
      },
      "dependsOn": [
        "[variables('sourceServerResourceId')]",
        "[variables('targetServerResourceId')]"
      ]
    }
  ]
}

sql-server.json

{
"$schema": "https://schema.management.azure.com/schemas/2018-05-01/subscriptionDeploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
  "name": {
    "type": "string"
  },
  "location": {
    "type": "string"
  },
  "adminUsername": {
    "type": "string"
  },
  "adminPassword": {
    "type": "string"
  },
  "whitelistStartIpAddress": {
    "type": "string"
  },
  "whitelistEndIpAddress": {
    "type": "string"
  }
},
"variables": {
   "azureStartIpAddress": "0.0.0.0",
   "azureEndIpAddress": "0.0.0.0"
},
"resources": [{
    "name": "[parameters('name')]",
    "type": "Microsoft.Sql/servers",
    "apiVersion": "2014-01-01",
    "location": "[parameters('location')]",
    "properties": {
      "administratorLogin": "[parameters('adminUsername')]",
      "administratorLoginPassword": "[parameters('adminPassword')]"
    }
  },
  {
    "name": "[concat(parameters('name'), '/WindowsAzureIps')]",
    "type": "Microsoft.Sql/servers/firewallRules",
    "apiVersion": "2014-04-01",
    "properties": {
      "startIpAddress": "[variables('azureStartIpAddress')]",
      "endIpAddress": "[variables('azureEndIpAddress')]"
    },
    "dependsOn": [
      "[resourceId('Microsoft.Sql/servers', parameters('name'))]"
    ]
  }
]

}

Don't use the resourceId() function, instead, try to create the resource id by using concat() like you have used for databaseResourceId — bit
I've tried that, this just pushes the issue further down to the Name property of the failover group which is a concat of the server plus the FOG name. "name": "[concat(parameters('sourceSqlServerName'), '/', parameters('failoverGroupName'))]", — David C
Get rid of the concat() just use "name": "[parameters('failoverGroupName')]" — bit
I will try it, but I would expect that it would only work if it was nested within the template for a SQL server. — David C
As expected, incorrect segment lengths for the name property. Microsoft.Sql/servers/failoverGroups' at line '24' and column '5' has incorrect segment lengths. Unfortunately this one has me baffled! — David C

Jason Jason · Accepted Answer · 2020-12-01T03:40:51

Just came across this same problem, and found the answer on this example:

https://github.com/Azure/azure-quickstart-templates/blob/master/101-sql-with-failover-group/azuredeploy.json

The bit you're missing is: "serverName": "[parameters('sourceSqlServerName')]",

So your full resource:

{
  "name": "[concat(parameters('sourceSqlServerName'), '/', parameters('failoverGroupName'))]",
  "type": "Microsoft.Sql/servers/failoverGroups",
  "apiVersion": "2015-05-01-preview",
  "properties": {
    "readWriteEndpoint": {
      "failoverPolicy": "Manual",
      "failoverWithDataLossGracePeriodMinutes": 60
    },
    "readOnlyEndpoint": {
      "failoverPolicy": "Disabled"
    },
    "serverName": "[parameters('sourceSqlServerName')]",
    "partnerServers": [
      {
        "id": "[variables('targetServerResourceId')]"
      }
    ],
    "databases": [
          "[variables('databaseResourceId')]"
    ]
  },
  "dependsOn": [
    "[variables('sourceServerResourceId')]",
    "[variables('targetServerResourceId')]"
  ]
}

How to ARM Template Azure SQL Failover Group?

1 Answers