How to Deploy A SQL Server Virtual Machine in Azure – Part 3

For part 3 on deploying a SQL Server virtual machine, I am focusing on using ARM (Azure Resource Manager) Templates to build a virtual machine with SQL Server 2016 installed on it.  I used this Microsoft article as a reference.

ARM templates use the JSon (Java Script Object Notation) format.  There are a total of 3 files, 2 JSon files and a PowerShell script file.  There is a JSon file for the parameters and another file for the information on what to deploy.  The PowerShell script references the ARM files and executes the command New-AzureRmResourceGroupDeployment.

I go into some detail on the sections of the ARM Template in my article “How to Deploy an Azure SQL Database – Part 3” .  To deploy a VM, you would still create the template with the same sections, just specifying different resources.

The first section contains the Parameters. This is a fairly large list of parameters, but to create the VM with SQL Server you will need to specify things like the SQL connectivity type, SQL Server port number, disk for the database files and information on setting up the database backups for example. In the ARM Template you define the parameters and the type, in the parameters file, you specify the actual values. And that would look like

“location”: {
“value”: “northcentralus”
},

"parameters": {
        "location": {
            "type": "string"
        },
        "virtualMachineName": {
            "type": "string"
        },
        "virtualMachineSize": {
            "type": "string"
        },
        "adminUsername": {
            "type": "string"
        },
        "virtualNetworkName": {
            "type": "string"
        },
        "networkInterfaceName": {
            "type": "string"
        },
        "networkSecurityGroupName": {
            "type": "string"
        },
        "adminPassword": {
            "type": "securestring"
        },
        "diagnosticsStorageAccountName": {
            "type": "string"
        },
        "diagnosticsStorageAccountId": {
            "type": "string"
        },
        "diagnosticsStorageAccountType": {
            "type": "string"
        },
        "diagnosticsStorageAccountKind": {
            "type": "string"
        },
        "subnetName": {
            "type": "string"
        },
        "msiExtensionName": {
            "type": "string"
        },
        "sqlConnectivityType": {
            "type": "string"
        },
        "sqlPortNumber": {
            "type": "int"
        },
        "sqlStorageDisksCount": {
            "type": "int"
        },
        "sqlStorageWorkloadType": {
            "type": "string"
        },
        "sqlStorageDisksConfigurationType": {
            "type": "string"
        },
        "sqlStorageStartingDeviceId": {
            "type": "int"
        },
        "sqlStorageDeploymentToken": {
            "type": "int"
        },
        "sqlAutopatchingDayOfWeek": {
            "type": "string"
        },
        "sqlAutopatchingStartHour": {
            "type": "string"
        },
        "sqlAutopatchingWindowDuration": {
            "type": "string"
        },
        "sqlAutobackupRetentionPeriod": {
            "type": "string"
        },
        "sqlAutobackupStorageAccountName": {
            "type": "string"
        },
        "sqlAutobackupStorageAccountType": {
            "type": "string"
        },
        "backupSystemDbs": {
            "type": "string"
        },
        "backupScheduleType": {
            "type": "string"
        },
        "fullBackupFrequency": {
            "type": "string"
        },
        "fullBackupStartTime": {
            "type": "string"
        },
        "backupTimeWindow": {
            "type": "string"
        },
        "logBackupFrequency": {
            "type": "string"
        },
        "sqlAuthenticationLogin": {
            "type": "string"
        },
        "sqlAuthenticationPassword": {
            "type": "securestring"
        },
        "rServicesEnabled": {
            "type": "string"
        }
    },

The next section, Variables, is used more to build values such as the VM name or storage accounts. In my file I am getting the VirtualNet ID and the SubNet reference.

"variables": {
        "vnetId": "[resourceId('RG-DBGRL93-01P','Microsoft.Network/virtualNetworks', parameters('virtualNetworkName'))]",
        "subnetRef": "[concat(variables('vnetId'), '/subnets/', parameters('subnetName'))]"
    },

Next is the heart of the ARM Template, the resources. The first part, I define the properties for the virtual machine. You will need to define the name of the VM, then the type, Microsoft.Compute/virtualMachines. Moving down the file, you specify the hardware profile along with the storage profile. The storage profile is where you specify the SQL Server Image you want to deploy. Then on to the OS disk and data disks. For SQL Server, make sure you will want to setup Managed Premium disks to get better performance on the database(s).

    "resources": [
        {
            "name": "[parameters('virtualMachineName')]",
            "type": "Microsoft.Compute/virtualMachines",
            "apiVersion": "2018-06-01",
            "location": "[parameters('location')]",
            "dependsOn": [
                "[concat('Microsoft.Network/networkInterfaces/', parameters('networkInterfaceName'))]",
                "[concat('Microsoft.Storage/storageAccounts/', parameters('diagnosticsStorageAccountName'))]"
            ],
            "properties": {
                "osProfile": {
                    "computerName": "[parameters('virtualMachineName')]",
                    "adminUsername": "[parameters('adminUsername')]",
                    "adminPassword": "[parameters('adminPassword')]",
                    "windowsConfiguration": {
                        "provisionVmAgent": "true"
                    }
                },
                "hardwareProfile": {
                    "vmSize": "[parameters('virtualMachineSize')]"
                },
                "storageProfile": {
                    "imageReference": {
                        "publisher": "MicrosoftSQLServer",
                        "offer": "SQL2016SP1-WS2016",
                        "sku": "Standard",
                        "version": "latest"
                    },
                    "osDisk": {
                        "createOption": "fromImage",
                        "managedDisk": {
                            "storageAccountType": "Premium_LRS"
                        }
                    },
                    "dataDisks": [
                        {
                            "createOption": "empty",
                            "lun": 0,
                            "diskSizeGB": "1023",
                            "caching": "ReadOnly",
                            "managedDisk": {
                                "storageAccountType": "Premium_LRS"
                            }
                        }
                    ]
                },

For a SQL Server deployment, you will also need to setup and configure the SQLIaaS Agent extension. This extension provides the Azure portal interface for SQL Server. You will also need it for the managed database backups along with the automated patching settings.
In this section, I am defining the Auto Patch settings along with the Auto Backup settings.  With the backups, we can set the backup retention settings, how often to run log backups and the schedule type (manual or automated).  I like to include these settings in the template, so that the server has everything configured and it is ready to go.

{
            "apiVersion": "2015-06-15",
            "type": "Microsoft.Compute/virtualMachines/extensions",
            "name": "[concat(parameters('virtualMachineName'), '/SqlIaasExtension')]",
            "location": "[parameters('location')]",
            "dependsOn": [
                "[concat('Microsoft.Compute/virtualMachines/', parameters('virtualMachineName'))]",
                "[concat('Microsoft.Compute/virtualMachines/',parameters('virtualMachineName'),'/extensions/', parameters('msiExtensionName'))]"
            ],
            "properties": {
                "type": "SqlIaaSAgent",
                "publisher": "Microsoft.SqlServer.Management",
                "typeHandlerVersion": "2.0",
                "autoUpgradeMinorVersion": "true",
                "settings": {
                    "AutoTelemetrySettings": {
                        "Region": "[parameters('location')]"
                    },
                    "AutoPatchingSettings": {
                        "PatchCategory": "WindowsMandatoryUpdates",
                        "Enable": true,
                        "DayOfWeek": "[parameters('sqlAutopatchingDayOfWeek')]",
                        "MaintenanceWindowStartingHour": "[parameters('sqlAutopatchingStartHour')]",
                        "MaintenanceWindowDuration": "[parameters('sqlAutopatchingWindowDuration')]"
                    },
                    "AutoBackupSettings": {
                        "Enable": true,
                        "RetentionPeriod": "[parameters('sqlAutobackupRetentionPeriod')]",
                        "EnableEncryption": false,
                        "BackupSystemDbs": "[parameters('backupSystemDbs')]",
                        "BackupScheduleType": "[parameters('backupScheduleType')]",
                        "FullBackupFrequency": "[parameters('fullBackupFrequency')]",
                        "FullBackupStartTime": "[parameters('fullBackupStartTime')]",
                        "FullBackupWindowHours": "[parameters('backupTimeWindow')]",
                        "LogBackupFrequency": "[parameters('logBackupFrequency')]"
                    },

When both the template and parameter JSON files are ready, you can go on to create a PowerShell script. I first setup the password for the admin account. Then call the New-AzureRmResouceDeployment command. For this command, you just need to specify the Resource Group to deploy to and then tell it where the Template file is and the Parameter file. Save the script and you are ready to run it.

$secpasswd = ConvertTo-SecureString "ch@ng3MeQ" -AsPlainText -Force

New-AzureRmResourceGroupDeployment -adminPassword $secpasswd -ResourceGroupName 'RG-DBGRL93-01P' -TemplateFile 'C:\DBAInTheCloud\Code\DBAInTheCloud2\ARMTemplates\DBA_ARMTmplt_SQLVM.json' -TemplateParameterFile 'C:\DBAInTheCloud\Code\DBAInTheCloud2\ARMTemplates\DBA_ARMParams_SQLVM.json'

It will take a few minutes to deploy the VM. Once it is deployed, you are ready to connect to the SQL Server and setup your databases.

That’s it for this series. The next set of scripts will focus on using Terraform scripts to deploy to Azure.

Happy scripting!!!!
DBA Work Matters

How to Deploy an Azure SQL Database with an ARM Template – Part 3

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.