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.
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.
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)