Welcome back to part 2 of “Working with ARM templates”! In this post we’ll be taking a look at deploying a Recovery Services Vault (RSV) with a custom SQL backup policy. If you haven’t read it yet, please see part one, “Deploying a Recovery Services Vault with VM Backup Policy”, located here: https://blogs.stratum-tech.com/2019/03/working-with-arm-templates-part-1-deploying-a-recovery-services-vault-with-daily-vm-backup-policy/

The difficulty I ran into with this template was that Azure VM SQL Backup has not been a feature for long. In fact, it just hit General Availability on March 18th, 2019. That being said, there are very few, if any usable templates out there to “borrow” from. What I actually ended up doing was creating the RSV, and then creating the policy manually. After that, I went and took a look at the Azure Activity Log event where I created the policy. I then pulled the JSON located within. Obviously, I still needed to implement this code into my policy, but at the very least I got a good starting point on the policy.

Let’s not waste any more time and dive right into the reason you’re probably here: The code.

First, take a look at the parameters file.

{
"$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentParameters.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"vaultName": {
"value": "stmvault4"
},
"vaultRG": {
"value": "rg-jgnew2"
},
"skuTier": {
"value": "Standard"
},
"sqlBackupPolicyName": {
"value": "WeeklyFullDailyDiffFifteenMinLogBackup"
},
"backupManagementType": {
"value": "AzureWorkload"
},
"workloadType": {
"value": "SQLDataBase"
},
"subPolicyList": {
"value": [
{
"policyType": "Full",
"schedulePolicy": {
"scheduleRunFrequency": "Weekly",
"scheduleRunDays": [
"Sunday"
],
"scheduleRunTimes": [
"2019-03-18T23:00:00.000Z"
],
"schedulePolicyType": "SimpleSchedulePolicy"
},
"retentionPolicy": {
"dailySchedule": null,
"weeklySchedule": {
"daysOfTheWeek": [
"Sunday"
],
"retentionTimes": [
"2019-03-18T23:00:00.000Z"
],
"retentionDuration": {
"count": 4,
"durationType": "Weeks"
}
},
"monthlySchedule": null,
"yearlySchedule": null,
"retentionPolicyType": "LongTermRetentionPolicy"
}
},
{
"policyType": "Differential",
"schedulePolicy": {
"scheduleRunFrequency": "Weekly",
"scheduleRunDays": [
"Monday",
"Tuesday",
"Wednesday",
"Saturday",
"Friday",
"Thursday"
],
"scheduleRunTimes": [
"2019-03-18T23:00:00.000Z"
],
"scheduleWeeklyFrequency": 0,
"schedulePolicyType": "SimpleSchedulePolicy"
},
"retentionPolicy": {
"retentionDuration": {
"count": 28,
"durationType": "Days"
},
"retentionPolicyType": "SimpleRetentionPolicy"
}
},
{
"policyType": "Log",
"schedulePolicy": {
"scheduleFrequencyInMins": 15,
"schedulePolicyType": "LogSchedulePolicy"
},
"retentionPolicy": {
"retentionDuration": {
"count": 7,
"durationType": "Days"
},
"retentionPolicyType": "SimpleRetentionPolicy"
}
}
]
},
"policySettingsKey": {
"value": {
"timeZone": "UTC",
"isSqlCompression": true,
"isCompression": true
}
}
}
}

We can see that significantly more code is required to create the SQL backup policy than the VM backup policy. The “subPolicyList” parameter is pretty lengthy, but this is where we are identifying everything about the policy. We can see that the we’re calling out weekly “full” backups, daily “differential” backups, and “log” backups every 15 minutes. We are keeping full and differential backups for 4 weeks, and log backups for 7 days. The “policySettingsKey” sets the “timeZone” value and enables SQL backup compression.

 Let’s move along and explore the parameters piece of the template file.

{
"$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"vaultName": {
"type": "string",
"metadata": {
"description": "Name of the Vault"
}
},
"vaultRG": {
"type": "string",
"metadata": {
"description": "Name of the Resource Group"
}
},
"skuTier": {
"type": "string",
"defaultValue": "Standard",
"allowedValues": [
"Standard"
],
"metadata": {
"description": "SKU tier for the vault"
}
},
"location": {
"type": "string",
"defaultValue": "[resourceGroup().location]",
"metadata": {
"description": "Location for all resources."
}
},
"sqlBackupPolicyName": {
"type": "string",
"defaultValue": "HourlyLogBackup",
"metadata": {
"description": "Name of the SQL VM Backup Policy"
}
},
"backupManagementType": {
"type": "string"
},
"workloadType": {
"type": "string"
},
"subPolicyList": {
"type": "array"
},
"policySettingsKey": {
"type": "object"
}
},

Let’s continue to the “Resources” section of the template.

"resources": [
{
"type": "Microsoft.RecoveryServices/vaults",
"apiVersion": "2018-01-10",
"name": "[parameters('vaultName')]",
"location": "[parameters('location')]",
"sku": {
"name": "RS0",
"tier": "[parameters('skuTier')]"
},
"properties": {
},
"dependsOn": [
]
},
{
"name": "[concat(parameters('vaultName'), '/vaultstorageconfig')]",
"type": "Microsoft.RecoveryServices/vaults/backupstorageconfig",
"apiVersion": "2016-12-01",
"location": "[parameters('location')]",
"properties": {
"storageModelType": "LocallyRedundant",
"storageType": "LocallyRedundant",
"storageTypeState": "Unlocked"
},
"dependsOn": [
"[resourceId(parameters('vaultRG'), 'Microsoft.RecoveryServices/vaults', parameters('vaultName'))]"
]
},
{
"type": "Microsoft.RecoveryServices/vaults/backupPolicies",
"dependsOn": [
"[concat('Microsoft.RecoveryServices/vaults/', parameters('vaultName'))]"
],
"name": "[concat(parameters('vaultName'), '/', parameters('sqlBackupPolicyName'))]",
"apiVersion": "2016-06-01",
"properties": {
"backupManagementType": "[parameters('backupManagementType')]",
"workloadType": "[parameters('workloadType')]",
"settings": "[parameters('policySettingsKey')]",
"subProtectionPolicy": "[parameters('subPolicyList')]"
}
}
]
}

We can see we’re still using the “LocallyRedundant” storage type in this template that depends on the creation of the vault. This mirrors the configuration in the RSV build of Part 1. The last bit of the template is building out the VM SQL backup policy using the parameter values set.

Let’s run it and see what we get.

We can see that everything checks out. Weekly full backups on Sundays, Daily differential backups on every day but Sunday, and log backups every 15 minutes. In addition, we can see that SQL backup compression is also enabled.

Hopefully this helps in your infrastructure as a code quest. Thanks for stopping by!

Working with ARM Templates Part 2: Deploying a Recovery Services Vault with Custom SQL Backup Policy

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.