While testing backups in SQL Server 2014 Availability Groups I hit a bug in a system function that is used to determine which replica is the most appropriate to be backed up.
You can set the desired backup pattern by RIGHT clicking on the availability group to bring up the properties:
I’ve decided I want my backups to happen on my PRIMARY replica, leaving my READ ONLY reporting replica available for client reporting.
Then I setup a backup maintenance plan using the out-of-the-box Maintenance Plan builder to setup the FULL, DIFF and LOG backups.
The script used in the maintenance plan looks something like this:
[sourcecode language='sql' padlinenumbers='true'] DECLARE @preferredReplica int SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica('myDB')) IF (@preferredReplica = 1) BEGIN BACKUP LOG [myDB] TO DISK = ... <The rest of the backup command> END [/sourcecode]
The bug was the function sys.fn_hadr_backup_is_preferred_replica always returned zero. This resulted in SQL Agent jobs that complete successfully, but the backup never ran.
In this particular case the server was created from a VM template. The servername did not match the instance name, which caused the function to always return zero.
The fix… Rename the @@servername value for all instances in the AG. Something like this did the trick:
[sourcecode language='sql' ] SELECT @@servername; -- Returned CHANGETHIS sp_dropserver 'CHANGETHIS'; sp_addserver 'HSVSQLAG02D', local; [/sourcecode]
A quick restart of the instances and voila, the log backups actually worked.
Back to testing AG backup strategies.
Stay curious; keep learning…