Protect SQL Server in Azure VMs using Azure Backup

 

In this post, I will show you how to use a new preview feature in Azure Backup to protect SQL Server databases that are hosted in Azure virtual machines.
 

 

Background

When I cover protection of Azure virtual machines in my training, I warn people that it’s great to protect the machine. You also need to perform maintenance on SQL Server. DBAs expect granular database backup, not just recovery of disks or files, and transaction log truncations must also take place to stop data disks from filling up.
To date, we’ve had a few ways to accomplish this including:

• Enable a maintenance plan in SQL Server to back up to a disk device that is stored on a data disk. This backup file would be captured as a part of the disk backup.
• Configure a SQL Server maintenance plan to back up to an Azure storage account (blob storage). This can be done in SQL Server Management Studio or when you deploy a SQL Server machine image from the Marketplace.

Ideally, you’ll only use one backup solution. If Azure Backup is to be used for machine protection, wouldn’t it make sense that it could reach into a virtual machine and perform a backup job in SQL Server? Microsoft agreed. It recently launched a preview of SQL Server protection by Azure Backup, which was introduced at Microsoft Ignite in September of last year.

What It Does

Most DBAs that I have met don’t like external backup solutions for their databases. The good news with Azure Backup is that it’s doing “SQL Server things” to protect the database:

• Full backups
• Differential backups
• Transaction log backups

There’s no re-inventing the wheel – just regular SQL Server protection that a DBA can like. And because it’s Azure Backup, it’s native to the system, easy, and relatively affordable to use (instance + blob storage costs).

Requirements

There are a number of requirements during the early days of the preview. Most Azure regions are included, but not all.
Linux is not supported. The virtual machines must be running one of the following:

• Windows Server 2012 (WS2012)
• Windows Server 2012 R2 (WS2012 R2)
• Windows Server 2016 (WS2016)

The installed version/edition of SQL Server must be one of the following:

• SQL 2012 Enterprise, Standard, Web, Developer, Express
• SQL 2014 Enterprise, Standard, Web, Developer, Express
• SQL 2016 Enterprise, Standard, Web, Developer, Express
• SQL 2017 Enterprise, Standard, Web, Developer, Express

In addition:

• The backup management/target recovery services vault must be in the same region as the virtual machines being protected.
• Permissions must be valid inside of the virtual machine. These are in Marketplace SQL Server images but manual/migrated installations will require work.
• The SQL Server virtual machine must have the necessary outbound connectivity to Azure.
• You can protect up to 2,000 SQL Server instances (databases) to a single recovery services vault.
• SQL Server failover clusters are not supported. DAGs have limitations too.
• The only management at this time is via the Azure Portal.

Protecting a SQL Server

Once you have deployed a recovery services vault, you can configure Azure Backup to protect databases as shown here. Start by opening the recovery services vault in the Azure Portal and browsing to Backup under Getting Started. Then:

1. Set the workload to Azure
2. Select SQL Server in Azure VM (Preview) as the thing you want to back up
3. Click Start Discovery

 
The discovery process can take a little while to scan your subscription/region for virtual machines. Next, you will select the machines that:

• Have SQL Server installed
• And that you want to protect using Azure Backup

 
Then you will click Discover DBs to find databases that can be protected. A job is created and runs. This job can also take a little while to run. You can see how it is going by clicking View Details in the original Getting Started blade.
 
There’s a little bug in the experience at the moment. Once the discovery is complete, you will have to exit the Getting Started blade and return to it for the Configure Backup button to be enabled. Click Configure Backup to create a backup policy for some/all of the discovered databases.
Select the databases that you want to protect in Items To Backup.
 
In Backup Policy, you will specify:

• What and when full, differential, and transaction log backups are done.
• Configure retention.
• Enable or leave disabled SQL Backup compression.

Full backups, enabled to be daily by default, offer the full complement of daily, weekly, monthly and yearly retention policies with a default retention of 10 years.

 
Differential backups are disabled by default but can be enabled for selected days of the week with retention for between 7-180 days.
Log (Transaction) backup is disabled by default but skips databases in the Simple Recovery Model. The frequency is every 2 hours by default but it can be between every 15 minutes and every 24 hours. Transaction log backups can be retained for between 7 and 35 days with 15 as the default.
SQL Backup Compression is disabled by default but you can reduce storage costs by enabling it, albeit with other less obvious costs.
Click OK to save any custom policy or select the default policy:

• Daily full backups at 21:00 UTC with retention for 30 days
• Log backups every hour with retention for 30 days

Click Enable Backup to deploy the policy to the virtual machine(s). A job is submitted to Azure Resource Manager with a task for each database that will be protected.

Backup Items

Protection is deployed very quickly to the virtual machines. You can manage the protection in the recovery services vault in Backup Items under Protected Items. Each protected database is listed under SQL in Azure VM. Here you can perform manual backups, recovery databases, stop backups, and delete the instance’s backup data.

 
When you do a recovery, you can:

• Overwrite an existing database or create a new one.
• Restore a full/differential recovery point or to a point in time (log backup).
• Opt to restore with NORECOVERY.

Opinion

Adding protection of SQL Server databases will add an additional cost to protecting the virtual machine. The benefit will be a SQL Server-supported protection with granular restore, including point-in-time recovery with peace of mind that an essential maintenance task (log truncation) is being done.