SQL Server Always On Availability Groups – Issue with preferred replica for log backups

Over the last couple of days I’ve been trying to customize Ola Hallegren’s script in order to perform backups on an Always On Availability Group running on Azure (blog to follow soon)[1]. I managed to get everything working apart from the Log Backups and the culprit was fairly easy to find. In Ola’s DatabaseBackup stored proc, there is a section of code that checks whether the specified backup type (full, diff, log) can run on the current server as per below:

IF @Version >= 11 AND @Cluster IS NOT NULL
	SELECT @CurrentAvailabilityGroup = availability_groups.name,
	@CurrentAvailabilityGroupRole = 	dm_hadr_availability_replica_states.role_desc,
	@CurrentAvailabilityGroupBackupPreference = UPPER(availability_groups.automated_backup_preference_desc)
	FROM sys.databases databases
	INNER JOIN sys.availability_databases_cluster availability_databases_cluster ON databases.group_database_id = availability_databases_cluster.group_database_id
	INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id
	INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON availability_groups.group_id = dm_hadr_availability_replica_states.group_id AND databases.replica_id = dm_hadr_availability_replica_states.replica_id
	WHERE databases.name = @CurrentDatabaseName

IF @Version >= 11 AND @Cluster IS NOT NULL AND @CurrentAvailabilityGroup IS NOT NULL
	SET @CurrentIsPreferredBackupReplica = sys.fn_hadr_backup_is_preferred_replica(@CurrentDatabaseName) 

The code is pretty straightforward and I would have expected that the following line would return the right result:

  • 0: for the primary replica
  • 1: for the secondary replica(s) with priorities configured accordingly

However, when I run the following command manually I found to my dismay that both servers returned 0 even though the AG Backup settings had been configured properly:

SELECT sys.fn_hadr_backup_is_preferred_replica('<AnyDBName>')

As it turns out, there is a bug with this function when the server name is defined in lowercase in the AG group. Yes, that’s right! If the servers are defined with lower case letters, the function will always return 0.
My only workaround, not ideal though, was to check for the function result and if that was 0, to check whether the server role is secondary. If that’s true, then I go ahead and execute the backup as per my code below:

IF @Version >= 11 AND @Cluster IS NOT NULL AND @CurrentAvailabilityGroup IS NOT NULL
	SELECT @CurrentIsPreferredBackupReplica = sys.fn_hadr_backup_is_preferred_replica(@CurrentDatabaseName)
-- This is required for fixing a bug with sys.fn_hadr_backup_is_preferred_replica() returning always 0 if the server is defined as lowercase in the AG group
	IF  @CurrentIsPreferredBackupReplica = 0 AND @CurrentAvailabilityGroupRole = 'SECONDARY'
		SET @CurrentIsPreferredBackupReplica = 1

I hope this helps you if you ever come across this issue.

Happy coding...

[1] - If you are NOT using Ola's script for SQL Backups and database maintenance, you are seriously missing out! Get it here

  • Share this post on