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

3 min read

Introduction

Since 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 of 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 SQL Azure Database and SQL Managed Instance, for these two implementation types, backup are automatically managed by Azure internally. However, 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, I will discuss what is the difference between these backup options, so 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 maximum 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, 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 to change 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:

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 the 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 1 day recovery point instead of RPO of 15 minutes.

While the last option, 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.

Summary

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

 Automated SQL Backup V2Azure Backup for IaaS VMAzure Backup for SQL VMs
Manage multiple servers in one dashboardN/AYESYES
Point-in-time restoreYESYESYES
15-minute (RPO)YESN/AYES
Long-Term backup policy retentionN/AYESYES
Support for SQL Server AlwaysOn AGN/AN/AYES
Consolidated email alerts for failuresN/AYESYES
Restore databases with SSMS or Transact-SQL ScriptsYESN/AN/A
Easy to set up and manage for large deploymentN/AYESYES
Role-based access controlN/AYESYES
Central customizable backup reportsN/AYESYES
Monitor backup jobs with SSMS or Transact-SQL scriptsYESN/AN/A
Short-Term backup policy retentionYESYESYES

I hope this article give 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-

Advertisements
About Charbel Nemnom 448 Articles
Charbel Nemnom is a Cloud Solutions Architect and Microsoft Most Valuable Professional (MVP), totally fan of the latest's IT platform solutions, accomplished hands-on technical professional with over 17 years of broad IT Infrastructure experience serving on and guiding technical teams to optimize performance of mission-critical enterprise systems. Excellent communicator adept at identifying business needs and bridging the gap between functional groups and technology to foster targeted and innovative IT project development. Well respected by peers through demonstrating passion for technology and performance improvement. Extensive practical knowledge of complex systems builds, network design and virtualization.

Be the first to comment

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