Running SQL Server in Azure VMs offers a number of benefits directly associated with the very nature of the Infrastructure-as-a-Service (IaaS) platform, such as simplified scalability, enhanced agility, and the operational cost model. In addition, the use of Azure as the cloud platform presents some additional opportunities that simplify traditional maintenance tasks such as patching or backups. In this article, you will learn backup techniques available exclusively when using Azure VMs to host SQL Server-based workloads.
The overview will cover three different backup methods, which are either capable of or specifically intended for protecting SQL Server databases:
- Azure IaaS VM Backup
- Automated Backup v2 for Azure Virtual Machines
- SQL Server Backup in Azure VMs
Azure IaaS VM Backup implements a VM-level backup, which automatically captures content of its persistent disks. Its functionality relies on a VM agent extension (VMSnapshot for Windows VMs and VMSnapshotLinux for Linux VMs), which automatically integrates with Azure Backup. On Azure VMs running Windows operating system, the extension takes advantage of the Volume Shadow Copy Service (VSS) in order to generate application-consistent snapshots (which includes support for SQL Server VSS Writer).
While very straightforward to set up, Azure IaaS VM Backup is rarely considered a SQL Server backup solution (at least in production scenarios) due to some of its shortcomings:
- The scheduled backup frequency is limited to a single backup per day.
- Backup is snapshot-based and does not facilitate transaction log backups.
- The restore process is cumbersome and requires either a VM-level restore or mounting snapshots representing backed up disks and copying individual items from the mounted disks.
Automated Backup v2 for Azure Virtual Machines is a successor of Automated Backup v1 (compatible with SQL Server 2014). It implements managed backup of Azure VMs running Windows Server 2012 R2 (or newer) with Standard, Enterprise, or Developer edition of SQL Server 2016 (or newer). The backup mechanism leverages SQL Server IaaS Agent Extension (SqlIaasExtension), which purpose is to automate the traditional database management tasks, including patching and backups. The agent also facilitates integration with Azure Key Vault. If you deploy any of the SQL Server Azure Marketplace images, the agent will be preinstalled. Otherwise, you will need to install it manually (for installation instructions, refer to Microsoft Docs).
There are a few considerations applicable to Automated Backup v2 for Azure Virtual Machines:
- User databases must be configured with the full recovery model.
- You have the option of enabling backups of system databases. This will result in full backups of the master database. For backups of msdb and model databases, you need to ensure that they are configured in the full recovery model.
- All databases must be hosted either on the default SQL Server instance or a named instance (as long as there is only a single named instance present on the server and the default instance has been uninstalled).
By default, the frequency of transaction log backups is dependent on the growth rate of the logs, however, you have the ability to define the backup frequency and time window. Full backups are always deterministic, based on the settings that you specify. The resulting files are stored as blobs in a designated container of an Azure Storage account that you designate and retained for 30 days. The blobs are named based on the combination of the GUID of the database being backed up and the timestamp of the backup event (this allows you to identify the appropriate backup instance during restore operations). To protect blobs at rest, you have the option of either relying on the built-in support for encryption available in Azure Storage or encrypting backups directly by employing Automated Backup v2 for Azure Virtual Machines-specific encryption mechanism.
SQL Server Backup in Azure VMs integrates directly with Azure Backup. To implement it, you can invoke discovery of SQL Server instances running in Azure VMs either from the Recovery Vault interface in the Azure portal or via Azure PowerShell. The discovery process will perform several tasks, including:
- registering the target Azure VMs with the vault
- installing the AzureBackupWindowsWorkload VM extension in the target Azure VMs
- creating a service account (NT ServiceAzureWLBackupPluginSvc) that provides security context for backup operations. The account must have the SQL Server sysadmin permissions.
The extension handles backup and restore operations, including control of data flow. The frequency of backups and data retention are determined by the corresponding backup policy. The backup process employs Virtual Backup Device Interface (VDI) to transfer database dump directly to the target Site Recovery vault, without having to rely on a staging location. It is worth noting that SQL Server Backup in Azure VMs should not be used in combination with other backup solutions.
While SQL Server Backup in Azure VMs does not support SQL Server Failover Cluster Instance (FCI) or SQL Server Always On FCI, it allows for backups of databases hosted by SQL Server Always On Availability Groups (which constitute overwhelming majority of highly available SQL Server installations on Azure). In such cases, there are some additional considerations to consider:
- The backup must target a cluster node in the same Azure region as the primary cluster node.
- The backup must target a cluster node in the Azure region hosting the Recovery Services vault.
- Full and differential backups are always performed from the primary cluster node.
- The selection of the cluster node used to perform transaction log and copy-only full backups depends on the backup preference setting. In particular, with the backup preference set to primary, these backups run on the primary node, and with backup preference set to secondary only, they run from the secondary node.
This concludes the overview of three Azure-specific methods of backing up SQL Server instances hosted in Azure VMs. It is important to note that you also have a variety of other backup options when running SQL Server workloads in Azure VMs, including both Microsoft offerings (such as Azure Backup Server or System Center Data Protection Manager) and third-party products (including services available from Azure Marketplace and locally installed software products).
# # #
