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