SQL Server high availability fn_hadr_backup_is_preferred_replica returns 0 zero on secondary

I'm running the Ola Hallengren Maintenance Solution and noticed the transaction logs weren't being backed up on the secondary, hence not being truncated, despite the AG backup preference being set to Prefer Secondary. If I run:

SELECT availability_groups.name,
   dm_hadr_availability_replica_states.role_desc,
   UPPER(availability_groups.automated_backup_preference_desc) as 'preference'
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 = 'EXT_CONFIG_DB'

on the secondary, I get:

name            role_desc   preference
SP-EXT-CONFIG   SECONDARY   SECONDARY

This is as expected. However if I then run:

SELECT sys.fn_hadr_backup_is_preferred_replica('EXT_Config_DB') as 'is_preferred_replica'

I get:

is_preferred_replica
0

This is wrong and should be set to 1. As the fn_hadr_backup_is_preferred_replica bit is set to 0 the Ola job doesn't backup or hence truncate the log. Searches seem to suggest lowercase instance names may be the issue, but all my instance names are upper case.

Can anyone suggest a resolution to fn_hadr_backup_is_preferred_replica returning 0 instead of 1?

This is wrong and should be set to 1.

If you want it set to 1, then you should configure your backup preferences properly.

The reason it is not returning a 1 is because it shouldn't be based on your current preferences. Right now, all of your replicas have the exact same backup priority which means we're going to next look at replica names. Since UK-DR-SQL001\WSS sorts first, that should be the secondary (given that UK-SB-SQL01\WSS is your primary) that the function returns a 1 on.

If you want the backups to happen on a different server, then set the priority appropriately.

Here is an extremely simple repro showing that given your current settings, UK-DR-SQL001\WSS should be the replica which a 1 is returned.

CREATE TABLE #Replicas
(
    ReplicaName VARCHAR(40) NOT NULL,
    BackupPriority INT NOT NULL
)
GO

INSERT INTO #Replicas(ReplicaName, BackupPriority) 
Values('UK-DR-SQL001\WSS', 50)
,('UK-SB-SQL01\WSS',50)
,('UK-SB-SQL02\WSS',50)
GO

-- current case UK-SB-SQL01\WSS is the PRIMARY
-- prefer secondary is set
-- default collation assumed

SELECT ReplicaName, BackupPriority
FROM #Replicas
WHERE ReplicaName <> 'UK-SB-SQL01\WSS'
ORDER BY BackupPriority DESC --most important first
, ReplicaName ASC

I'm running the Ola Hallengren Maintenance Solution and noticed the transaction logs weren't being backed up on the secondary, hence not being truncated, despite the AG backup preference being set to Prefer Secondary

It seems to be that this isn't setup or running on the DR server, or a backup would at least be attempted.

StackExchange Database Administrators Q#168924, answer score: 4