Multiple Ways to Backup SQL Server in an Azure VM

This post will discuss several ways that you can back up SQL Server which is running in an Azure virtual machine.

SQL Server

One of the more common workloads that I encounter in Azure is SQL Server – often deployed in lift & shift scenarios into Azure. There are Azure SQL and Managed Instance alternatives which can reduce direct or operational costs, but sometimes SQL Server in a machine is the only choice. The databases contain valuable information so they must be backed up.
Note: Azure does not back up any virtual machine by default. You must choose to and pay for backups.

Azure Virtual Machine Backup

Azure allows us to protect running virtual machines. This service works very well, and recent enhancements have improved the performance for virtual machines with large amounts of disk capacity.
An extension is deployed from a Recovery Services Vault into the virtual machine, allowing a consistent backup of the machine to blob storage. A backup policy allows the machine to be backed up once per day. The costs of this solution are the Azure Backup instance charge plus the amount of general purpose v1 blob storage that is consumed to store the back ups.

There is no actual “SQL Server” backup. Instead, all of the virtual machine is backed up, including the databases. You can restore the virtual machine, or restore the files (database & log) of the virtual machine. Unfortunately, this means that you lose log truncation, which must be done or the log will fill the data disk, and you lose point-in-time restores which you get from a transaction log backup. But you do get centralized management, reporting, and alerting from the Recovery Services Vault.

SQL Server Backup

Many admins of SQL Server, even we accidental ones, have used the option to backup SQL Server to a disk device (.BAK file). In my experience, SQL Server admins prefer this method for backups because they know it, it’s in their toolset, and it’s what the SQL Server documentation covers. Many of the repair or recovery options for SQL Server are based on this backup/restore option that comes with SQL Server.
Since SQL Server 2012 SP1 CU2, we’ve had an alternative target using the same engine and SQL Server Management Studio wizard. Instead of backing up to a local .BAK file, we can back up SQL Server to an Azure storage account, storing backup data in the ultra-affordable blob storage.
The benefit of this method is that it’s very affordable requiring just blob storage charges and outbound data transfer charges. It’s also a familiar system for SQL Server admins and under their control in SQL Server Management Studio. Importantly, because it’s a SQL backup, granular (full, differential, transaction) and log truncations will take place.
On the downside, this method has distributed management and no centralized management, reporting, and alerting. Restoring large databases could take a long time because data must be restored over the network from the storage account.

Azure Backup for SQL Server

A preview was launched recently for a new Azure Backup service, enabling Azure Backup to reach into an Azure virtual machine and perform SQL Server backups for you. This solution offers the best of both worlds of the above solutions:

• SQL Server admins continue to get full, differential, and transaction log backups.
• You can do point-in-time restores.
• The transaction logs are truncated.
• Backups have centralized management, alerting, and reporting through the Recovery Services Vault.

On the downside:

• The instance charge is slightly higher for SQL Server than the standard Azure Backup instance charge.
• The solution is not natively managed via SQL Server Management Studio, but the above features might dissuade concerns here.

What Would I Do?

In an ideal world I would do two kinds of backup:

• Protect the running virtual machine, enabling me to restore a complete machine if it was lost.
• Backup up SQL Server using Azure Backup, for a true SQL Server backup with log protection/truncation.