Running SQL Server In Azure VM – What Backup Solution Should I Use? @AzureBackup

Different ways to back up your SQL Database in Azure VM.

In this article, we’ll discuss what backup solution to use for SQL Server running in Azure VM.

Introduction

In June 2018, Microsoft announced the public preview of SQL Server backup in Azure VM, and finally, in March 2019, this service becomes generally available (GA). You can read about the recent announcement here.

With the GA release of Azure Backup for SQL Server, customers now have three solutions for backing up SQL Server running in Azure VM:

1) Automated SQL Backup v2 for Azure VMs. Check also this article on how to monitor the automated SQL Backup v2.

2) Azure Backup for IaaS VM to back up the entire VM.

3) Back up SQL Server databases in Azure VMs.

It’s also worth mentioning that you can implement Azure SQL Database and Azure SQL Managed Instance, for these two implementation types, backup is automatically managed by Azure internally.

A significant number of customers choose to lift and shift SQL Server from on-premises physical or virtual environments to Azure VM to reduce friction and lower migration costs or risks.

I’ve been discussing lately with many customers around the three backup solutions when it comes to backing up SQL Server in Azure VM.

In this article, we will share the difference between these backup options, so that you can choose the right solution based on your requirements.

Backup options for SQL Server in Azure VMs

The Automated Backup option provides automatic backup service for SQL Server Standard and Enterprise editions running in a Windows VM in Azure. This service is provided by the SQL Server IaaS Agent Extension, which is automatically installed on SQL Server Windows virtual machine images in Azure. For more information about automated SQL Backup for Azure VMs, please check the following article.

With the automated backup solution, all databases are backed up to an Azure storage account that you configure. Backups can be encrypted and retained for up to a maximum of 30 days only. To restore a database, you must locate the required backup file(s) in the storage account and perform a restore on your SQL VM using SQL Server Management Studio (SSMS) or Transact-SQL commands.

On the other hand, with the Azure Backup for IaaS VM, you can protect the entire Azure VM where SQL Server is installed, if you choose that option, Microsoft recommends changing the VSS setting so that Azure Backup takes VSS copy backup instead of the default VSS full backup, you can do this by setting the following registry key from a command prompt inside the VM:

REG ADD "HKLM\SOFTWARE\Microsoft\BcdrAgent" /v USEVSSCOPYBACKUP /t REG_SZ /d TRUE /f

As a side note, here is the difference between VSS full backup and VSS copy backup:

• When you do a VSS full backup, you create a backup of all the files – but after that, the backup application may truncate logs on the file system. If the third-party backup application relies on those logs to take incremental backups, it would take full backups all the time.
• On the other hand, when you do a VSS copy backup, all files are backed up and you preserve all the applications files including log files on the live system.

With the VSS copy backup enabled, Azure Backup will take a VM snapshot as a copy-only backup, as well as SQL backup at the same time.

Additionally, when you back up the entire VM, you won’t get advanced features like:

• VM backup works better for simple scenarios like single instance only, you cannot back up complex deployments like SQL AlwaysOn AG.
• With VM backup, you can have a 1-day recovery point instead of an RPO of 15 minutes.

While the last option, is Azure Backup for SQL VMs, you don’t need to take care of VSS copy backup and VSS full backup. Microsoft takes care of it in the back-end – it’s always VSS copy backup. Besides the integration and native APIs support, there are several advantages that this solution provides, especially for Enterprises such as:

• Zero-infrastructure backup.
• Easy to set up and manage.
• Scale: Protect many SQL VMs and thousands of databases.
• Policy-driven backup and retention: Create standard backup policies for regular backups. Establish retention policies to maintain backups for years.
• Support for SQL AlwaysOn AG.
• 15-minute Recovery Point Objective (RPO).

So if you are looking for an enterprise backup solution for SQL Server running in Azure VM, Azure Backup for SQL VMs option is the optimum way to go. For more information about backing up SQL Server in Azure VMs, please check the following step-by-step guide.

Please note that you can’t use Automated Backup V2 option along with Azure Backup for SQL VMs, because both options rely on SQL Server transaction logs technology, therefore you can use one OR the other.

Backup options for SQL AlwaysOn AG

When protecting the SQL AlwaysOn availability group, Microsoft recommends that the backup is configured on only one node of an availability group (AG). Always configure backup in the same region as the primary node. In other words, you always need the primary node to be present in the region where you’re configuring the backup. If all the nodes of the AG are in the same region where the backup is configured, there isn’t any concern.

And even in the case of AG, you can also use Azure VM backup along with Azure Backup for SQL VMs. It works much the same way as in the case of a standalone SQL server.

Additionally, you could also consider using selective disk backup; with this, you can back up a subset of data disks with Azure VM backup and use SQL backup for the databases on other disks. This is a great approach. For more information, about selective disk backup, please check the following documentation from Microsoft.

Cross-region SQL AG

Regardless of the backup preference, backups will only run from the nodes that are in the same Azure region where the backup is configured. This is because cross-region backups aren’t supported. If you have only two nodes and the secondary node is in the other region, the backups will continue to run from the primary node (unless your backup preference is ‘secondary only’). If a node fails over to a region different than the one where the backup is configured, backups will fail on the nodes in the failed-over region.

Depending on the backup preference and backup types (full/differential/log/copy-only full), backups are taken from a particular node (primary/secondary).

Summary

To summarize the three backup options, I have created a small table that provides a quick overview in the form of a feature matrix.

I hope this article gives you direction to choose the right solution based on your requirements.

__
Thank you for reading my blog.

If you have any questions or feedback, please leave a comment.

-Charbel Nemnom-