Azure Backup: Does Azure Backup use SQL AlwaysOn AG Backup Preferences?

Question

Following the setup process outlined here: https://learn.microsoft.com/en-us/azure/backup/backup-sql-server-database-azure-vms.

I am using a three server Azure SQL VM AlwaysOn AG, with a backup policy applied to the AlwaysOn AG. The AG backup preferences are set to Primary. The backup policy is set to back up logs every 15 minutes, and do a full backup daily. When the full backup is scheduled by Azure Backup, it is scheduled on all three servers when only one server is the primary, so the backup policy appears to be ignoring the AG Backup preferences. The secondary backups all fail immediately due to backups not being allowed by SQL on a secondary server, but they really shouldn't be running in the first place if Azure Backup is paying attention to the AG backup preferences.

I confirmed that all servers, databases and service accounts for the backup service registered successfully and show as healthy.

This post indicates that the Azure Backup is supposed to use backup preferences for AGs: https://azure.microsoft.com/en-in/blog/azure-backup-for-sql-server-on-azure-vm-public-preview/

Quote: Azure Backup protects databases in an Availability Group such that data protection continues seamlessly even post failover while honoring the Availability Group backup preference.

The matrix for Azure SQL backup solutions also indicates that Azure Backup is the only solution that is AG Aware.

With SQL Backup, we could script awareness of the AG preference into the job, but there doesn't seem to be a powershell way to do that with Azure Backup.

When I trigger a manual backup on the AG database in Azure Backup using 'Backup now', the backup job shows in the Workload Name as ONLY the AG name, so manually triggering a backup seems to be aware of the AG.

Answer 1

Hello @JW , Apologies for delay in getting back to your query!

Azure backup honors the backup preference for both Scheduled and Ad-hoc backups.
Our schedules resides within the VM, and we trigger backup checks on all the nodes, that’s why you are seeing the backup jobs. However, only 1 (preferred node) will take the backup, rest all jobs would fail.
This is the internal logic that the Azure Backup uses in case of AG, backup is triggered on all nodes, but honored only for the node as mentioned in the backup preference. I believe you were able to catch this logic because of the ‘additional’ jobs that have started to appear under Backup jobs recently. This ‘bug’ was introduced as part of the larger effort of listing schedule full and differential jobs that were not shown earlier. We are taking a fix to mute this jobs such that they won’t be visible to the customer.

------------------------------------------------------------------------------------------------------------------------------

Please do not forget to "Accept the answer" and "Up-Vote" wherever the information provided helps you to help others in the community.