Unable to create multiple databases with the same name on different SQL Servers in Azure via ARM templates

0
votes

I am using an ARM template to create Azure resources. I've run the template once to create a SQL Server and database in a resource group. It worked fine. When I run it a second time with different parameters, it successfully creates the SQL Server but fails with a vague message for the database.

Excerpt from my ARM template:

{
  "type": "Microsoft.Sql/servers",
  "kind": "v12.0",
  "name": "[variables('sqlServer_name')]",
  "apiVersion": "2015-05-01-preview",
  "location": "[parameters('resourceLocation')]",
  "tags": "[variables('environmentTags')]",
  "properties": {
    "administratorLogin": "[parameters('sqlServerAdminAccountName')]",
    "administratorLoginPassword": "[parameters('sqlServerAdminAccountPassword')]",
    "version": "12.0"
  },
  "resources": [
    {
      "name": "EnableAzureServicesFirewallRule",
      "type": "firewallRules",
      "apiVersion": "2015-05-01-preview",
      "properties": {
        "startIpAddress": "0.0.0.0",
        "endIpAddress": "0.0.0.0"
      },
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', variables('sqlServer_name'))]"
      ]
    }
  ]
},
{
  "type": "Microsoft.Sql/servers/databases",
  "sku": "[variables('databaseSizeSkuMap')[parameters('sqlServerDatabaseSize')]]",
  "kind": "v12.0,user",
  "name": "[concat(variables('sqlServer_name'), '/', variables('sqlServerDatabase_name'))]",
  "apiVersion": "2017-03-01-preview",
  "location": "[parameters('resourceLocation')]",
  "tags": "[variables('environmentTags')]",
  "dependsOn": [
    "[resourceId('Microsoft.Sql/servers', variables('sqlServer_name'))]"
  ]
}

The sku is { "name": "Free", "tier": "Free" }.

Running the template results in two separate resource groups each with a SQL Server instance:

Resource Group Dev

  • Server: sql-myappname-dev
  • Database: sql-myappname-dev/DbName

Resource Group Test

  • Server: sql-myappname-test
  • Database: sql-myappname-test/DbName <--- Fails to create

Error message:

New-AzureRmResourceGroupDeployment : 6:21:23 PM - Resource Microsoft.Sql/servers/databases 'sql-myappname-test/DbName' failed with message '{
  "status": "Failed",
  "error": {
    "code": "ResourceDeploymentFailure",
    "message": "The resource operation completed with terminal provisioning state 'Failed'.",
    "details": [
      {
        "code": "InternalServerError",
        "message": "An unexpected error occured while processing the request. Tracking ID: '438f9ade-e84f-4627-acec-1156ea54aa65'"
      }
    ]
  }
}'

What is the issue here? I've been able to create databases with the same name on separate servers through the Azure Portal. Also, if I delete Resource Group Dev, then running the template for Resource Group Test succeeds in creating the database.

1
azureazure-sql-databaseazure-resource-manager
retry it? also, you cannot create another sql with the same name – 4c74356b41
When I retry, it still fails.There is no problem creating this in the Azure Portal, only when running an ARM template. – John Mills

1 Answers

0
votes

Azure only supports one free tier SQL database per region & subscription.

Limitations

This process will allow you to get up to one free Azure SQL database per region.

Source

Changing the pricing tier for one of the ARM template parameters files from "Free" to "Basic" allows both databases to be created.

To create a second free tier database, it needs to be created in a different region or subscription.