Infrastructure at your Service

David Barbarin

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:


FROM sys.availability_replicas




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


SELECT SERVERPROPERTY(‘Collation’) AS ServerCollation;




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


       name AS group_name,
       automated_backup_preference_desc as backup_preference
FROM sys.availability_groups




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


SELECT AS group_name,
       r.replica_server_name AS replica_name,
       drs.database_.state_desc AS db_state
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS r
       ON drs.replica_id = r.replica_id
JOIN sys.availability_groups AS g
       ON g.group_id = drs.group_id
JOIN sys.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:


USE master;
SELECT @db_name = name
FROM sys.databases WHERE name = N’DUMMY’;
       @@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.

See you on the next availability group ventury!





Leave a Reply

seven − = 2

David Barbarin
David Barbarin

Senior Consultant & Microsoft Technology Leader