Azure Resource Manager (ARM) and Azure SQL Data Warehouse

Having worked with many customers doing ARM deployments, I've found it is not always clear how to create a good template when deploying an Azure SQL Data Warehouse.

Here is an example implementation created for testing:

{ "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#", "contentVersion": "1.0.0.0", "parameters": { "serverName": { "type": "string", "minLength": 1 }, "serverAdminLogin": { "type": "string", "minLength": 1 }, "serverAdminLoginPassword": { "type": "securestring" }, "databaseName": { "type": "string", "minLength": 1 }, "databaseCollation": { "type": "string", "minLength": 1, "defaultValue": "SQL_Latin1_General_CP1_CI_AS" }, "databaseEdition": { "type": "string", "defaultValue": "DataWarehouse", "allowedValues": [ "DataWarehouse" ] }, "RequestedServiceObjectiveName": { "type": "string", "defaultValue": "DW100", "allowedValues": [ "DW100" ], "metadata": { "description": "Describes the performance level for Edition" } } }, "variables": { "dwserverName": "[concat(parameters('serverName'), uniqueString(resourceGroup().id))]" }, "resources": [ { "name": "[variables('dwserverName')]", "type": "Microsoft.Sql/servers", "location": "[resourceGroup().location]", "apiVersion": "2014-04-01-preview", "dependsOn": [ ], "properties": { "administratorLogin": "[parameters('serverAdminLogin')]", "administratorLoginPassword": "[parameters('serverAdminLoginPassword')]" }, "resources": [ { "name": "AllowAllWindowsAzureIps", "type": "firewallrules", "location": "[resourceGroup().location]", "apiVersion": "2014-04-01-preview", "dependsOn": [ "[resourceId('Microsoft.Sql/servers', variables('dwserverName'))]" ], "properties": { "startIpAddress": "0.0.0.0", "endIpAddress": "0.0.0.0" } }, { "name": "[parameters('databaseName')]", "type": "databases", "location": "[resourceGroup().location]", "apiVersion": "2014-04-01-preview", "dependsOn": [ "[resourceId('Microsoft.Sql/servers', variables('dwserverName'))]" ], "properties": { "collation": "[parameters('databaseCollation')]", "edition": "[parameters('databaseEdition')]", "requestedServiceObjectiveName": "[parameters('RequestedServiceObjectiveName')]" } } ] }], "outputs": {} }

Editing a Template
I have to recommend you either use Visual Studio Code or Visual Studio 2017. You can install Azure Resource Manager Tools in VSCode while Visual Studio with Azure Tools installed has the Azure Resource Group project type with a JSON Outline that helps when editing the template.

When modifying the template, it is important to know the Microsoft.Sql/Servers settings and the database settings. The audit settings that can be specified with an Azure SQL DB are not supported on Azure SQL Data Warehouse. The settings are not supported because they are for Blob auditing and at the time of this writing, the Azure SQL Data Warehouse only supports table auditing.

The template will almost always be accompanied with a parameters JSON file that will store the standard values for your deployment. If you have more than one environment, you can have more than one parameter file and specify which one you want depending on where you are deploying. An easy way to think about this is Development, Test, and Production will each have a different parameters file that would detail the server, Resource Group and Data Warehouse for that environment. Below is an example parameter file.

 

{ "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentParameters.json#", "contentVersion": "1.0.0.0", "parameters": { "databaseCollation": { "value": "SQL_Latin1_General_CP1_CI_AS" }, "databaseEdition": { "value": "DataWarehouse" }, "databaseName": { "value": "dwarmtestdb" }, "RequestedServiceObjectiveName": { "value": "DW100" }, "serverAdminLogin": { "value": "dwadmin" } } }

 

 

Troubleshooting Your Template Deployment

When things go wrong with your template deployment, it can be a very unsettling experience. If you used PowerShell New-AzureRMResourceGroupDeployment as your deployment method, the error is provided as part of the result. One of the best things I have found to help in working with the templates is in the Azure Portal by navigating to the Resource Group in question and looking at the Deployments blade. In the Deployments blade, you can see the successful and failed deployments with details as with the screenshot below.

You can see that using deploying a Data Warehouse, the max size property does not appear to be supported or will need match a MAXSIZE of the CREATE DATABASE statement, though I was not able to get it to work with any values. I recommend if you need to create an Azure SQL Data Warehouse of a specific size, use the CREATE DATABASE statement.

Have fun deploying!