SQL SERVER – AlwaysOn Availability Group Backup fn_hadr_backup_is_preferred_replica Not Working Correctly

SQL
1 Comment

One of my clients has configured AlwaysOn availability group and wanted to automate backup to secondary replica. They debugged and found that the function sys.fn_hadr_backup_is_preferred_replica returns 0 no matter what the backup preference is set to. Since function was returning zero, the maintenance plan was ignoring the server to take backup.

Here is the output of the function for both the nodes.

SQL SERVER - AlwaysOn Availability Group Backup fn_hadr_backup_is_preferred_replica Not Working Correctly fn_hadr-01

As we can see, the value is zero on both nodes. I verified settings via catalog views and DMV and all were looking perfectly fine.

WORKAROUND/SOLUTION

After studying the code of fn_hadr_backup_is_preferred_replica I was able to debug and figure out that this was because of the fact that SELECT @@SERVERNAME was returning incorrect values. After digging from the client, I would that their Wintel team configure this VM from an image that had SQL installed.

Here is my own blog which I followed to fix server name.

SQL SERVER – Fix @@ServerName Property Value When Incorrect

After putting the correct name and restarting the SQL Server service, the function started working as expected.

Let me know if you have faced ever such error in SQL Server where you have to apply such fix for your SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com) 

AlwaysOn, SQL Error Messages, SQL High Availability, SQL Server
Previous Post
SQL SERVER – TCP Provider – An Operation On a Socket Could Not be Performed Because the System Lacked Sufficient Buffer Space or Because a Queue was Full
Next Post
SQL SERVER – Msg 1105 – Could Not Allocate Space for Object Name in Database ‘DB’ Because the ‘PRIMARY’ Filegroup is Full

Related Posts

1 Comment. Leave new

  • Ali Ben Tahar
    June 30, 2020 2:51 am

    Hi Dave,
    Thank you for this post!
    I had the same problem but for a different cause, I wanted to share it with you!
    In short, the problem was due to a configuration in the availability group, the replica was excluded in the backup preferences of that AG.
    It is therefore something else to check when the sys.fn_hadr_backup_is_preferred_replica system function unexpectedly returns 0.

    Thanks

    Reply

Leave a Reply