Weird issue with sys.fn_hadr_backup_is_preferred_replica() function

A couple of days ago, I faced a weird backup issue with SQL Server AlwaysOn and availability groups at one of my customer (thank to him to point out this issue ). After installing our DMK tool (Database Management Kit) about database maintenance for AlwaysOn, my customer noticed that their databases have not backed up. Ouch … what’s going on? I never ran into this issue before… Do the problem comes from our tool?

In fact, our DMK uses the useful DMF sys.fn_hadr_backup_is_preferred_replica() to know which databases are candidate for backup operations on replicas at a given time and this is where our issue starts. Indeed, in a specific situation that includes both a case sensitive server collation and entering the name of replicas in lower case, we found that the result of this function is inconsistent. Let me show with an example.

In my customer’s context, the replica names have been filled out from a PowerShell script form in lower case as follows:

Let’s take a look at the system view to check the availability group configuration:

SELECT

replica_server_name,

availability_mode_desc,

failover_mode_desc

FROMsys.availability_replicas

Let’s verify that the collation of the SQL Server instance is case sensitive …

SELECTSERVERPROPERTY(‘Collation’) AS ServerCollation;

… and the backup preference policy is configured to “primary”

SELECT

name AS group_name,

automated_backup_preference_desc as backup_preference

FROMsys.availability_groups

Finally, let’s verify the database inside the availability group:

SELECT

g.name AS group_name,

r.replica_server_name AS replica_name,

dcs.database_name,

drs.database_.state_desc AS db_state

FROMsys.dm_hadr_database_replica_states AS drs

JOINsys.availability_replicas AS r

ON drs.replica_id = r.replica_id

JOINsys.availability_groups AS g

ON g.group_id = drs.group_id

JOINsys.dm_hadr_database_replica_cluster_states AS dcs

ON dcs.group_database_id = drs.group_database_id

AND dcs.replica_id = drs.replica_id

Ok now let’s take a look at the result of the DMF sys.fn_hadr_backup_is_preferred_replica() in this context. I put here a simplified sample portion of the TSQL code used in our DMK:

USEmaster;

GO

DECLARE @db_name SYSNAME;

SELECT @db_name = name

FROMsys.databases WHERE name = N’DUMMY’;

SELECT

@@SERVERNAME AS server_name,

@db_name AS database_name,

sys.fn_hadr_backup_is_preferred_replica(@db_name) AS fn_result;

Concerning the primary:

Concerning the secondary :

If you perform the same by configuring this time the replica names in upper case, you will notice that the issue will disappear. When I think about this issue, it’s true that in almost cases customers prefer to use the assistant wizard to configure availability groups and in this case do you notice that the replica names are always switched in upper case?

There also exists a Microsoft connect item about this problem but unfortunately it seems that it will not be solved by Microsoft … so be careful when you implement availability groups by script.