I ran into a problem with Availability Group backups the other day that had me scratching my head for a couple of hours before I finally discovered what was going on. When you set up an Availability Group, one of the settings you can configure is where you want the backups to be made. The configuration screen looks like this:
I think the options are fairly self-explanatory (perhaps with the exception of the Backup Priority value for the last option, but that’s not important to this story). The default, and the way my AG was configured, was Prefer Secondary. As the image shows, this means backups will be made on the secondary, unless the secondary is unavailable, in which case, they will be made on the primary.
There are a couple of things to note when you use this setting:
- Full backups made on the secondary are Copy Only backups. This means they won’t reset the differential bitmap and your differentials will continue to increase in size until a full backup is made on the primary.
- Differential backups cannot be made on the secondary.
- Transaction log backups can be made on the secondary and they do clear the log, so your log file will not continue to grow.
One morning we had a situation where our differential backups were failing and we were trying to track down the cause. We have a centralized job server that runs jobs on several of our SQL Servers. This is accomplished by supplying the server name to a PowerShell script, which then executes the command against the specified server. The PowerShell backup script used the AG listener name when executing the command, so the command would always be sent to the primary replica.
This is where we started to get confused. According to the way the AG backup preferences were configured, SQL should run the backup on the secondary. But the backup command called for a differential, which can’t be run on a secondary. Well, the job was failing, so that made sense to me. What didn’t make sense was the fact that the job had run successfully for months in the past. Also, our full backups were resetting the differential bitmap because I could see any differential taken after a full backup decreased in size from the one taken before the full backup. How was that possible?
I went back and looked in the msdb backupset table for the last couple of months. This table has two columns – server_name and machine_name – that give the name of the machine the backup was made on. In my case, I found the name was always the name of the primary replica.
What’s going on? It looked like SQL was ignoring my AG backup preference setting. After much head-scratching and testing, I finally discovered something: those settings don’t mean crap! Well, I mean, they do, but it’s up to you to make use of them. In the Books Online documentation, I found this little gem:
There is no enforcement of the automated backup preference setting. The interpretation of this preference depends on the logic, if any, that you script into backup jobs for the databases in a given availability group. The automated backup preference setting has no impact on ad-hoc backups. (emphasis mine)
Great. Way to make that clear in the UI.
Way down at the bottom of that BOL page, it tells you that YOU are responsible to determine where your backup should be run and you do this by checking the sys.fn_hadr_backup_is_preferred_replica function. So basically, that configuration page where you select the Backup Preferences only tells SQL Server what value that function should return. It doesn’t actually make the backup happen on a certain replica.
Because our backup job did not check that function, backups were always being made on the primary.
The more you know…