I realize the title of this post is overly dramatic. And I’m happy to explain why creating the ARM template below earned the title. Still, on the off-chance you couldn’t care less what I think 🙂 and just want the code here’s what the Azure SQL Database ARM template and parameters files below do:
- Define a primary SQL logical server in the vCore purchasing model
- Define a secondary server in a different Azure region
- Define any number of databases
- Those databases are automatically placed in an elastic pool
- The elastic pool is placed in a failover group that automatically replicates to the secondary region
- Add an Azure Active Directory SQL Database administrator
- Add a range of public IP addresses to be permitted access (useful if you have an Express Route connection that is, essentially, a VPN over MPLS. Believe me, I’d never willingly expose a SQL database to the internet)
- Add a subnet of a virtual network that will be permitted access
- Define the Azure SQL logical server administrators
- Retrieve a key from a Key Vault as the logical server administrator’s password.
But first…some whinging, as the Brits say. There are techniques in this SQL Database template that, AFAIK, are not documented anywhere. That’s an existential issue for enterprises that want to use code to deploy infrastructure (IaaC). And it’s all Microsoft’s fault.
If Microsoft really wants people to use the Azure Resource Manager (ARM) to manage complex Azure infrastructure it really has to do a better job of documenting how things work. Today, even the most basic template documentation is poorly organized and, at best, thin in knowledge. The doc doesn’t even begin to address the deployment intricacies of the resources. An example: Microsoft provides no guideposts on how one should sequence resource deployment for a database environment like the one I needed to deploy. Even if you code a syntactically correct template, it’s still a guessing game as to what the underlying resource expects. With only declarative dependencies (dependsOn:[]) and no logic in ARM templates, it’s crucial to know in advance how resources must be deployed, especially for high-end deployments. In a universe of limitless ARM coding options and approaches, it’s Microsoft’s responsibility to provide at least some minimal roadsigns to prevent endless trial and error.
The samples on GitHub are most emphatically not what’s needed as I am sure many — if not all — of you would agree. The samples are contrived, poorly documented in their own right, stylistically inconsistent and because they presume you already know what you do not, they do nothing to tell an IaaC architect how a resource needs to be created.
Let’s be specific: if your task was to deploy an Azure SQL Database environment with failover groups and elastic pools like the one documented here, you’d have a choice of deploying the failover group first or deploying the elastic pools first. Which would you choose to do first? Why? Where would you go to answer this question? Where’s the doc that tells you what the right order is and what the cross-region resource naming requirements might be?
Well, there isn’t any. At least none I could find. Starting out blind as a bat, I found a sample template on GitHub that deploys Azure SQL Database failover groups. Stupid me to think that creating failover groups and then putting elastic pools into them makes sense. It turns out that that order is a fatal mistake if you also happen to want to deploy databases in an elastic pool in the vCore purchasing model.
Why fatal? Because you’ll spend days tearing your hair out trying to get the databases into the elastic pool after you create the failover group. Turns out that there are three things you have to know about creating Azure SQL Database failover groups and elastic pools in a single template. But the only apparent way to know these things is to either be one of Microsoft’s own Azure SQL developers or, as was my unfortunate lot, to deploy a template dozens and dozens of times until I accidentally discovered the secrets.
Number one, you must create the elastic pools on all servers destined to be in the failover group before you add the databases and before you create the failover groups. Second, you must name the elastic pool identically on all servers destined to be in the failover group. Third, you must, in this order, create the databases (using a root resource since you cannot use copy:{} in a child resource!), add them to the primary server’s elastic pool and then, and only then, create the failover group but just on the primary server.
Note that I said “create the” (singular) failover group. You cannot create a failover group on any of the other secondary (partner) servers. If you do, and give it a different name, there’s no way to connect secondary failover groups to the primary server’s failover group. OTOH, if you define the secondary failover groups on secondary servers with the same name as the primary, Azure won’t be able to sync them up. So, in the template you almost have perfect symmetry in deployed resources — each resource on all servers — except for databases and failover groups. If you just let the Resource Manager do it, Azure SQL will replicate the failover group to the partner servers automatically. This is crazy — and impossible to know beforehand. Someone, please, send me a link where this is documented — where it actually says, “Do not create failover groups in your ARM template on secondary Azure SQL Database logical servers. Instead, let Azure do it.”
I couldn’t find anything, anywhere like that in the doc. And for that, I blame Microsoft. Microsoft’s Azure architects can’t have it both ways. They can’t recommend using ARM deployments for complex, production infrastructure if coding even a modestly complex deployment is an exercise in frustration. Today, the DevOps coder has to start with an infinite number of possibilities and get one that works — by trial and error. Yes, you feel like a master of the universe when you hit on the right thing. ( You may think it’s a POS, but I’m proud of the template I posted here.) But why should one have to have the determination of an Everest climber to define one stinkin’ little Azure SQL Database multi-region environment?
It’s Microsoft’s responsibility, IMO, to make clear that one has to do strange things like name the elastic pools identically across servers and that pools must be built and databases added before failover groups are created. It seems obvious once you already know it. But it’s Microsoft’s failure that it hasn’t bothered to document what “everyone should already know” before they start hacking away at Azure infrastructure.
I realize we are a long way from a resolution to this quandary. Microsoft, for all the brilliant work they produce — Azure is astonishing — doesn’t seem to give a damn about documentation and support.
In the meantime, if you need to deploy a high-availability Azure SQL Database environment with failover groups and elastic pools, I hope this template helps you.
Azure SQL Database sample template file
{
"$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"metadata": {
"author": "Alex Neihaus",
"license": "Licensed under the MIT license. See https://yobyot.com/wp-content/uploads/licenses/mit2020.html"
},
"parameters": {
"licenseType": {
"type": "string",
"metadata": {
"description": "BasePrice is Azure Hybrid Benefit; LicenseIncluded is no AHB"
}
},
"vnetResourceGroupNamePrimary": {
"type": "string",
"metadata": {
"description": "The name of the resource group containing the Vnet that should be permitted to connect to this database server."
}
},
"vnetNamePrimary": {
"type": "string",
"metadata": {
"description": "The name of the Vnet in the resource group above."
}
},
"vnetSubnetNamePrimary": {
"type": "string",
"metadata": {
"description": "The name of the subnet containing the JIRA server in the Vnet."
}
},
"subscriptionSecondary": {
"type": "string",
"metadata": {
"description": "GUID of the subscription for the secondary Vnet for failover group."
}
},
"vnetResourceGroupSecondary": {
"type": "string",
"metadata": {
"description": "Name of the resource group containing the secondary Vnet."
}
},
"vnetNameSecondary": {
"type": "string",
"metadata": {
"description": "Name of the the secondary Vnet."
}
},
"vnetSubnetNameSecondary": {
"type": "string",
"metadata": {
"description": "Name of the the subnet in the secondary Vnet."
}
},
"sqlDatabaseNames": {
"type": "array",
"metadata": {
"description": "The names of the SQL databases to be created and assigned to an elastic pool."
}
},
"sqlDatabaseServiceObjective": {
"type": "string",
"metadata": {
"description": "The name of the configured service level objective of the database."
}
},
"elasticPoolvCoreLimit": {
"type": "string",
"metadata": {
"description": "Limit of vCores in the elastic pool. Must be available in the service objective selected by sqlDatabaseServiceObjective"
}
},
"sqlDatabaseEdition": {
"type": "string",
"metadata": {
"description": "The edition of the database."
}
},
"sqlServerPrimaryName": {
"type": "string",
"metadata": {
"description": "The name of the primary SQL Server."
}
},
"elasticPoolPrimaryName": {
"type": "string",
"metadata": {
"description": "The name of the primary elastic pool."
}
},
"elasticPoolStorageSize": {
"type": "int",
"metadata": {
"description": "The size, in bytes, of the elastic pool storage allocated."
}
},
"elasticPoolPerDatabasePerformanceMin": {
"type": "string",
"metadata": {
"description": "Minimum vCores guaranteed to each database in an elastic pool. Can be fractional."
}
},
"elasticPoolPerDatabasePerformanceMax": {
"type": "string",
"metadata": {
"description": "Maximum (partial) vCores guaranteed to each database in an elastic pool. Can be factional cannot be larger than number of vCores specified by sqlDatabaseServiceObjective."
}
},
"sqlServerPrimaryAdminUsername": {
"type": "string",
"metadata": {
"description": "The administrator username of the primary SQL Server."
}
},
"sqlServerPrimaryAdminPassword": {
"type": "securestring",
"metadata": {
"description": "The administrator password of the primary SQL Server."
}
},
"sqlServerSecondaryName": {
"type": "string",
"metadata": {
"description": "The name of the secondary SQL Server."
}
},
"sqlServerSecondaryRegion": {
"type": "string",
"metadata": {
"description": "The location of the secondary SQL Server."
}
},
"sqlServerSecondaryAdminUsername": {
"type": "string",
"metadata": {
"description": "The administrator username of the secondary SQL Server."
}
},
"sqlServerSecondaryAdminPassword": {
"type": "securestring",
"metadata": {
"description": "The administrator password of the secondary SQL Server."
}
},
"sqlFailoverGroupName": {
"type": "string",
"metadata": {
"description": "The name of the failover group."
}
},
"location": {
"type": "string",
"defaultValue": "[resourceGroup().location]",
"metadata": {
"description": "Location for all resources."
}
},
"login": {
"type": "string",
"metadata": {
"description": "UPN of the user to be assigned as Active Directory admin."
}
},
"sid": {
"type": "string",
"metadata": {
"description": "objectID of AAD user to be assigned as Active Directory admin."
}
},
"tenantId": {
"type": "string",
"metadata": {
"description": "Tenant ID of the AAD tenant containing the sid and login."
}
},
"zoneRedundant": {
"type": "bool",
"metadata": {
"description": "Should elastic pool storage be zone redundant? Set to false as we are using failover groups."
}
}
},
"variables": {
"vnetSubnetResourceId": "[resourceId(parameters('vnetResourceGroupNamePrimary'), 'Microsoft.Network/virtualNetworks/subnets', parameters('vnetNamePrimary'), parameters('vnetSubnetNamePrimary'))]",
"vnetSubnetSecondaryResourceId": "[resourceId( parameters('subscriptionSecondary'), parameters('vnetResourceGroupSecondary'), 'Microsoft.Network/virtualNetworks/subnets', parameters('vnetNameSecondary'), parameters('vnetSubnetNameSecondary'))]",
"elasticPoolPrimaryResourceId": "[resourceId('Microsoft.Sql/servers/elasticPools', parameters('sqlServerPrimaryName') , parameters('elasticPoolPrimaryName'))]",
"copy": [
{
"name": "fgDatabases",
"count": "[length(parameters('sqlDatabaseNames'))]",
"input": "[resourceId('Microsoft.Sql/servers/databases', parameters('sqlServerPrimaryName'),parameters('sqlDatabaseNames')[copyIndex('fgDatabases')].dbName )]"
}
]
},
"resources": [
{
"type": "Microsoft.Sql/servers",
"kind": "v12.0",
"name": "[parameters('sqlServerPrimaryName')]",
"apiVersion": "2014-04-01-preview",
"location": "[parameters('location')]",
"properties": {
"administratorLogin": "[parameters('sqlServerPrimaryAdminUsername')]",
"administratorLoginPassword": "[parameters('sqlServerPrimaryAdminPassword')]",
"version": "12.0"
},
"resources": [
{
"apiVersion": "2014-04-01",
"type": "administrators",
"name": "activeDirectory",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('sqlServerPrimaryName'))]"
],
"properties": {
"administratorType": "ActiveDirectory",
"login": "[parameters('login')]",
"sid": "[parameters('sid')]",
"tenantId": "[parameters('tenantId')]"
}
},
{
"type": "elasticpools",
"apiVersion": "2017-10-01-preview",
"name": "[parameters('elasticPoolPrimaryName')]",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers',parameters('sqlServerPrimaryName'))]"
],
"location": "[parameters('location')]",
"sku": {
"name": "[parameters('sqlDatabaseServiceObjective')]",
"tier": "[parameters('sqlDatabaseEdition')]",
"capacity": "[parameters('elasticPoolvCoreLimit')]"
},
"properties": {
"perDatabaseSettings": {
"minCapacity": "[parameters('elasticPoolPerDatabasePerformanceMin')]",
"maxCapacity": "[parameters('elasticPoolPerDatabasePerformanceMax')]"
},
"maxSizeBytes": "[parameters('elasticPoolStorageSize')]",
"zoneRedundant": "[parameters('zoneRedundant')]",
"licenseType": "[parameters('licenseType')]"
}
},
{
"apiVersion": "2015-05-01-preview",
"type": "failoverGroups",
"name": "[parameters('sqlFailoverGroupName')]",
"properties": {
"serverName": "[parameters('sqlServerPrimaryName')]",
"partnerServers": [
{
"id": "[resourceId('Microsoft.Sql/servers/', parameters('sqlServerSecondaryName'))]"
}
],
"readWriteEndpoint": {
"failoverPolicy": "Automatic",
"failoverWithDataLossGracePeriodMinutes": 60
},
"readOnlyEndpoint": {
"failoverPolicy": "Enabled"
},
"databases": "[variables('fgDatabases')]"
},
"dependsOn": [
"[parameters('sqlServerPrimaryName')]",
"[parameters('elasticPoolPrimaryName')]",
"databaseCopy"
]
},
{
"type": "firewallRules",
"name": "AllowAzureServicesPrimary",
"apiVersion": "2014-04-01-preview",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers',parameters('sqlServerPrimaryName'))]"
],
"properties": {
"startIpAddress": "0.0.0.0",
"endIpAddress": "0.0.0.0"
}
},
{
"type": "firewallRules",
"name": "AllowExpressRouteIPsPrimary",
"apiVersion": "2014-04-01-preview",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers',parameters('sqlServerPrimaryName'))]"
],
"properties": {
"startIpAddress": "100.60.70.141",
"endIpAddress": "100.60.70.145"
}
},
{
"type": "virtualNetworkRules",
"name": "[parameters('vnetSubnetNamePrimary')]",
"apiVersion": "2015-05-01-preview",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers',parameters('sqlServerPrimaryName'))]"
],
"properties": {
"virtualNetworkSubnetId": "[variables('vnetSubnetResourceId')]",
"ignoreMissingVnetServiceEndpoint": false
}
}
]
},
{
"type": "Microsoft.Sql/servers/databases",
"name": "[concat(parameters('sqlServerPrimaryName'),'/',parameters('sqlDatabaseNames')[copyIndex('databaseCopy')].dbName)]",
"copy": {
"name": "databaseCopy",
"count": "[length(parameters('sqlDatabaseNames'))]"
},
"apiVersion": "2017-10-01-preview",
"location": "[parameters('location')]",
"dependsOn": [
"[parameters('sqlServerPrimaryName')]",
"[variables('elasticPoolPrimaryResourceId')]"
],
"properties": {
"licenseType": "[parameters('licenseType')]",
"edition": "[parameters('sqlDatabaseEdition')]",
"collation": "SQL_Latin1_General_CP1_CI_AI",
"requestedServiceObjectiveName": "[parameters('sqlDatabaseServiceObjective')]",
"elasticPoolId": "[variables('elasticPoolPrimaryResourceId')]"
}
},
{
"type": "Microsoft.Sql/servers",
"kind": "v12.0",
"name": "[parameters('sqlServerSecondaryName')]",
"apiVersion": "2014-04-01-preview",
"location": "[parameters('sqlServerSecondaryRegion')]",
"properties": {
"administratorLogin": "[parameters('sqlServerSecondaryAdminUsername')]",
"administratorLoginPassword": "[parameters('sqlServerSecondaryAdminPassword')]",
"version": "12.0"
},
"resources": [
{
"type": "elasticpools",
"apiVersion": "2017-10-01-preview",
"name": "[parameters('elasticPoolPrimaryName')]",
"location": "[parameters('sqlServerSecondaryRegion')]",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('sqlServerSecondaryName'))]"
],
"sku": {
"name": "[parameters('sqlDatabaseServiceObjective')]",
"tier": "[parameters('sqlDatabaseEdition')]",
"capacity": "[parameters('elasticPoolvCoreLimit')]"
},
"properties": {
"perDatabaseSettings": {
"minCapacity": "[parameters('elasticPoolPerDatabasePerformanceMin')]",
"maxCapacity": "[parameters('elasticPoolPerDatabasePerformanceMax')]"
},
"maxSizeBytes": "[parameters('elasticPoolStorageSize')]",
"zoneRedundant": "[parameters('zoneRedundant')]",
"licenseType": "[parameters('licenseType')]"
}
},
{
"apiVersion": "2014-04-01",
"type": "administrators",
"name": "activeDirectory",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('sqlServerSecondaryName'))]"
],
"properties": {
"administratorType": "ActiveDirectory",
"login": "[parameters('login')]",
"sid": "[parameters('sid')]",
"tenantId": "[parameters('tenantId')]"
}
},
{
"type": "firewallRules",
"name": "AllowAzureServicesSecondary",
"apiVersion": "2014-04-01-preview",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers',parameters('sqlServerSecondaryName'))]"
],
"properties": {
"startIpAddress": "0.0.0.0",
"endIpAddress": "0.0.0.0"
}
},
{
"type": "firewallRules",
"name": "AllowExpressRouteIPsSecondary",
"apiVersion": "2014-04-01-preview",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers',parameters('sqlServerSecondaryName'))]"
],
"properties": {
"startIpAddress": "32.65.74.141",
"endIpAddress": "32.65.74.145"
}
},
{
"type": "virtualNetworkRules",
"name": "[parameters('vnetSubnetNameSecondary')]",
"apiVersion": "2015-05-01-preview",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers',parameters('sqlServerSecondaryName'))]"
],
"properties": {
"virtualNetworkSubnetId": "[variables('vnetSubnetSecondaryResourceId')]",
"ignoreMissingVnetServiceEndpoint": false
}
}
]
}
],
"outputs": {
"failoverGroupsDatabases": {
"type": "array",
"value": "[variables('fgDatabases')]"
},
"elasticPoolPrimaryResourceId": {
"type": "string",
"value": "[variables('elasticPoolPrimaryResourceId')]"
}
}
}
Azure SQL Database sample parameters file (resourceIds are “greeked” to protect the innocent)
{
"$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentParameters.json",
"contentVersion": "1.0.0.0",
"parameters": {
"licenseType": {
"value": "LicenseIncluded"
},
"vnetNamePrimary": {
"value": "VnetNamePrimary"
},
"vnetSubnetNamePrimary": {
"value": "vnetSubnetNamePrimary"
},
"vnetResourceGroupNamePrimary": {
"value": "vnetResourceGroupNamePrimary"
},
"subscriptionSecondary": {
"value": "9999999-0000-4986-90f2-d7777b4ff168"
},
"vnetResourceGroupSecondary": {
"value": "vnetResourceGroupSecondary"
},
"vnetNameSecondary": {
"value": "vnetNameSecondary"
},
"vnetSubnetNameSecondary": {
"value": "vnetSubnetNameSecondary"
},
"sqlDatabaseNames": {
"value": [
{
"dbName": "Database1"
},
{
"dbName": "Database2"
},
{
"dbName": "Database3"
}
]
},
"sqlDatabaseServiceObjective": {
"value": "GP_Gen4"
},
"elasticPoolvCoreLimit":{
"value": "2"
},
"sqlDatabaseEdition": {
"value": "GeneralPurpose"
},
"sqlServerPrimaryName": {
"value": "sqlServerPrimaryName"
},
"elasticPoolPrimaryName": {
"value": "elasticPoolPrimaryName"
},
"elasticPoolStorageSize": {
"value": 68719476736
},
"elasticPoolPerDatabasePerformanceMin":{
"value": ".5"
},
"elasticPoolPerDatabasePerformanceMax":{
"value": "2"
},
"sqlServerPrimaryAdminUsername": {
"value": "AdminUser"
},
"sqlServerPrimaryAdminPassword": {
"reference": {
"keyVault": {
"id": "/subscriptions/44444444-0000-2222-1111-abcf69c8311/resourceGroups/RgKeyVaultAzureInfrastructure01B/providers/Microsoft.KeyVault/vaults/KVAzureInfrastructure"
},
"secretName": "secretname"
}
},
"sqlServerSecondaryName": {
"value": "sqlServerSecondaryName"
},
"sqlServerSecondaryRegion": {
"value": "centralus"
},
"sqlServerSecondaryAdminUsername": {
"value": "AdminUser"
},
"sqlServerSecondaryAdminPassword": {
"reference": {
"keyVault": {
"id": "/subscriptions/44444444-0000-2222-1111-abcf69c8311/resourceGroups/RgKeyVaultAzureInfrastructure01B/providers/Microsoft.KeyVault/vaults/KVAzureInfrastructure"
},
"secretName": "secretname"
}
},
"sqlFailoverGroupName": {
"value": "sqlFailoverGroupName"
},
"login": {
"value": "user@yourtenant.onmicrosoft.com"
},
"sid": {
"value": "8888888-5555-0000-0000-c345bbaacc"
},
"tenantId": {
"value": "4dacb0000-5555-7777-2222-5612acceda90"
},
"zoneRedundant": {
"value": false
}
}
}
Leave a Reply Cancel reply