Deploying a SQL Database with ARM templates

2020-02-18

Azure

ARM, Azure, AzureSQL

I am trying to build a new ARM template to deploy, among others, a SQL server along with a Database.

My starting point was to export the template from the Azure resource group where I had this deployed and start from there. However, when trying to deploy that template I always got a Conflict status with this cryptical error 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: '...'"
            }
        ]
    }
}

The error itself doesn’t say much, and internet didn’t help a lot either unfortunately. The resource group I am deploying into is clean, so the chance of a conflict was relatively low.

Let’s take a look at the problematic template:

{
    "type": "Microsoft.Sql/servers/databases",
    "apiVersion": "2019-06-01-preview",
    "name": "[concat(parameters('sqlServerName'), '/databaseName')]",
    "location": "[parameters('location')]",
    "kind": "v12.0,user",
    "dependsOn": [
 	    "[resourceId('Microsoft.Sql/servers', parameters('sqlServerName'))]"
    ],
    "properties": {
        "collation": "SQL_Latin1_General_CP1_CI_AS",
        "maxSizeBytes": 2147483648,
        "catalogCollation": "SQL_Latin1_General_CP1_CI_AS",
        "zoneRedundant": false,
        "readScale": "Disabled",
        "readReplicaCount": 0,
        "storageAccountType": "GRS"
    },
    "sku": {
        "name": "Basic",
        "tier": "Basic",
        "capacity": 5
    },
}

The template looks legit, so I tried finding some samples for deploying a database and I quickly found an azure quickstart template that promised to do just that.

Here’s the relevant portion of that template:

{
    "name": "[variables('databaseName')]",
    "type": "databases",
    "apiVersion": "2015-01-01",
    "location": "[parameters('location')]",
    "tags": {
        "displayName": "Database"
    },
    "properties": {
        "edition": "[variables('databaseEdition')]",
        "collation": "[variables('databaseCollation')]",
        "requestedServiceObjectiveName": "[variables('databaseServiceObjectiveName')]"
    },
    "dependsOn": [
        "[variables('sqlServerName')]"
    ]
}

The template, as is, fails for me due to an older apiVersion property - (2015-01-01) but it works fine if you update that to the latest value of 019-06-01-preview

Starting from here, with some experimentation, it was easy to find that the the culprit in the first template was this rogue setting under properties "storageAccountType": "GRS"

As soon as we remove that, and without any changes, the original template starts working.
The templates are slightly different (especially in how they define the type and name of the database) however they both work fine… with one small caveat.

Template1 The template that we downloaded from the portal,

{
    "type": "Microsoft.Sql/servers/databases",
    "apiVersion": "2019-06-01-preview",
    "name": "[concat(parameters('sqlServerName'), '/databaseName')]",
    "location": "[parameters('location')]",
    "kind": "v12.0,user",
    "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', parameters('sqlServerName'))]"
    ],
    "properties": {
        "collation": "SQL_Latin1_General_CP1_CI_AS",
        "maxSizeBytes": 2147483648,
        "catalogCollation": "SQL_Latin1_General_CP1_CI_AS",
        "zoneRedundant": false,
        "readScale": "Disabled",
        "readReplicaCount": 0,
     },
     "sku": {
         "name": "Basic",
         "tier": "Basic",
         "capacity": 5
     }
},

This will generate a database with a 5 DTU, Basic SKU - pretty much as expected.

Template2 (The template we find in the quickstart docs):

{
    "type": "databases",
    "apiVersion": "2019-06-01-preview",
    "name": "databaseName",
    "location": "[parameters('location')]",
    "dependsOn": [
        "[variables('sqlServerName')]"
    ],
    "properties": {
        "edition": "Basic",
	"requestedServiceObjectiveName": "Basic",
        "collation": "SQL_Latin1_General_CP1_CI_AS",
     }
},

After we update the apiVersion to make it work, will deploy a General Purpose: Gen5, 2 vCores database that is way more expensive than the Basic.
My assumption is that the edition and requestedServiceObjectiveName parameters we see under properties are deprecated in the new Api version and because the sku id missing Azure will deploy a Db with the default scale.

Goodluck deploying your Azure SQL servers and databases with ARM templates.