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.
Say Goodbye to Traditional PC Lifecycle Management
Traditional IT tools, including Microsoft SCCM, Ghost Solution Suite, and KACE, often require considerable custom configurations by T3 technicians (an expensive and often elusive IT resource) to enable management of a hybrid onsite + remote workforce. In many cases, even with the best resources, organizations are finding that these on-premise tools simply cannot support remote endpoints consistently and reliably due to infrastructure limitations.
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).
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
- 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:
- Set the workload to Azure
- Select SQL Server in Azure VM (Preview) as the thing you want to back up
- 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.
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.
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.