When I decided to start this Blog, I had an idea to post how to deploy resources to the Azure Cloud in 3 different ways. I chose to use the Azure SQL Database as the first resource to deploy. Part 1 of this series shows the steps on how to deploy the SQL Database using the portal. In part 2 , I covered deploying the SQL Database using only PowerShell commands.
Now for part 3, I will cover using ARM (Azure Resource Manager) templates to deploy an Azure SQL Database.
To get started on learning how to use ARM Templates, I found that Microsoft had a bunch of quick start templates you can download and try yourself. I found these to be a good starting point to working with the templates and getting you to where you can build on to them. You can also find them at GitHub.
ARM Templates are basically JSON files with a basic schema as below. In the template I have for an Azure SQL Database, I have a section for parameters, variables, resources and outputs. I will have to discuss functions in another post.
{
"$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "",
"parameters": { },
"variables": { },
"functions": [ ],
"resources": [ ],
"outputs": { }
}So, let’s get started.
Parameters
This section is optional, you do want to add parameters so you can enter names for resource groups, storage accounts or the SQL Database name. As you can see below, my Parameters section is pretty long, but I do want to provide a template that I can use to create a variety of sizes. For each parameter, you define a “type” and optionally you can define a default value, and even a description of the parameter.
"parameters": {
"sqladministratorLogin": {
"type": "string",
"defaultValue": "sysdbadmin",
"metadata": {
"description": "This is the SQL Administrator Login (sa)"
}
},
"sqlPassword": {
"type": "securestring"
},
"databaseName": {
"type": "string",
"metadata": {
"description": "Name of database, format SQLDB---<01(P)(N)>"
}
},
"serverName": {
"type": "string",
"metadata": {
"description": "Name of logical SQL Server, format SQL<01(P)(N)>"
}
},
"serverLocation": {
"type": "string",
"defaultValue": "North Central US",
"metadata": {
"description": "Azure Data Center Location"
}
},
"collation": {
"type": "string"
},
"tier": {
"type": "string"
},
"skuName": {
"type": "string"
},
"zoneRedundant": {
"type": "bool"
},
"emailAddresses": {
"type": "string",
"defaultValue": "dba@dbainthecloud.com"
},
"firewallRuleName": {
"type": "array",
"defaultValue": [
{
"IPRangeName": "IPRange1",
"IPRangeStart": "192.168.1.0",
"IPRangeEnd": "192.168.1.10"
},
{
"IPRangeName": "IPRange2",
"IPRangeStart": "0.0.0.0",
"IPRangeEnd": "0.0.0.0"
}
]
}
},
Variables
When I started working with an ARM Template, I didn’t quite understand what the difference is between the parameters and variables section. However, when I was at Microsoft Ignite this year (September 2018), the one thing they did mention in all the sessions on DevOps was to use the variables to build/format your resource names.
For example, you could get some basic information from a user such as the resource group name or application name and use the Variables section to build the SQL Database Name or deploy a storage account.
"variables": {
"databaseServerName": "[toLower(parameters('serverName'))]",
"databaseName": "[parameters('databaseName')]",
"databaseServerLocation": "[parameters('ServerLocation')]",
"databaseServerAdminLogin": "[parameters('sqladministratorLogin')]",
"databaseServerAdminLoginPassword": "[parameters('sqlPassword')]",
"emailAddresses": "[parameters('emailAddresses')]"
},
Resources
The heart of the ARM Template. Here is where you can define all the configuration items of the SQL Database you want to deploy. In this template, I am setting up the logical server name, auditing settings which includes a storage account, firewall rules and then finally the SQL database.
I try to breakdown type of resource into it’s own section. It is also easier to think of it as a top down approach. For example, when creating a database, you first need a server (albeit a logical server, then all the resources that you want to configure at the server level and finally the last resource I have is for the database.
In this snippet I am giving it a type of Microsoft.Sql/servers, giving it the location to deploy the server, a name for the server along with properties for the server. In this case I am giving it an administrator login and password.
"resources": [
{
"apiVersion": "2017-10-01-preview",
"type": "Microsoft.Sql/servers",
"location": "[parameters('serverLocation')]",
"name": "[parameters('serverName')]",
"properties": {
"administratorLogin": "[parameters('sqladministratorLogin')]",
"administratorLoginPassword": "[parameters('sqlPassword')]"
}
},
The next section, I create the firewall rules. The firewall rules are at the server level, so, I am putting that under the server section. Since it is possible to setup several firewall rules along with enabling the Allow Access to Azure services, I am setting up a loop to go through each set of IP Ranges that were identified in the parameters. I also added the “dependsOn” label to tell the Resource Manager that the Firewall rules depend on the ServerName just created.
If you look back up at the Firewall Rule parameter, I setup an array and defined 2 different IP Ranges. With the array I can then use the “copy” section to count the number of parameters in the array to give me a loop count. I can then reference the parameter names for the Start and End IP addresses for the firewall rules.
{
"type": "Microsoft.Sql/servers/firewallrules",
"name": "[concat(variables('databaseServerName'), '/', parameters('firewallRuleName')[copyIndex()].IPRangeName)]",
"apiVersion": "2014-04-01-preview",
"dependsOn": [
"[variables('databaseServerName')]"
],
"location": "[parameters('serverLocation')]",
"properties": {
"startIpAddress": "[parameters('firewallRuleName')[copyIndex()].IPRangeStart]",
"endIpAddress": "[parameters('firewallRuleName')[copyIndex()].IPRangeEnd]"
},
"copy": {
"name": "firewallloop",
"count": "[length(parameters('firewallRuleName'))]"
}
},
Finally, for the last part of the Resources section, I give the properties to create the Azure SQL Database. For the properties, I can tell it which location to deploy the database, SKU information of the level of SQL Database I wish to deploy. There are many other properties you can set at this site.
{
"type": "Microsoft.Sql/servers/databases",
"location": "[parameters('serverLocation')]",
"apiVersion": "2017-10-01-preview",
"dependsOn": [
"[concat('Microsoft.Sql/servers/', parameters('serverName'))]"
],
"name": "[concat(parameters('serverName'),'/', parameters('databaseName'))]",
"properties": {
"collation": "[parameters('collation')]",
"zoneRedundant": "[parameters('zoneRedundant')]"
},
"sku": {
"name": "[parameters('skuName')]",
"tier": "[parameters('tier')]"
}
}And, finally, the outputs section of the ARM Template.
"outputs": {
"ServerObject": {
"value": "[reference(variables('databaseServerName'))]",
"type": "object"
},
"DBObject": {
"value": "[reference(variables('databaseName'))]",
"type": "object"
}
}Now that the ARM Template is done, you can reference the template in a Powershell script using the command, New-AzureRmResourceGroupDeployment . Here is how I setup the parameters and execute the command in Powershell.
$Parameters = @{
sqladministratorLogin = 'dbadmin'
databaseName= 'sqldb-dba-tmplt-01n'
servername= 'sqldbatmplt01n'
collation = 'SQL_Latin1_General_CP1_CI_AS'
serverLocation = 'northcentralus'
tier= 'Standard'
skuName= 'Standard'
zoneRedundant = $false
}
$secpasswd = ConvertTo-SecureString "" -AsPlainText -Force
New-AzureRmResourceGroupDeployment -sqlPassword $secpasswd -ResourceGroupName 'RG-DBGRL93-01P' -TemplateFile 'C:\DBAInTheCloud2\ARMTemplates\DBA_ITC_ARMTmplt_SQLDB.json' -TemplateParameterObject $Parameters
It may take a few minutes to deploy, but you can check the progress of the deployment in the portal, or in Powershell you can run
Get-AzureRmResourceGroupDeployment -ResourceGroupName ”
Hopefully this helps you through your journey on learning Cloud technologies and scripting.