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.
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)Â
1 Comment. Leave new
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