none
sys.fn_hadr_backup_is_preferred_replica returns 0 on both nodes of cluster

    Question

  • I have setup a Windows 2012, SQL 20102, 2-node, non-shared storage Availability Group cluster. I am trying to backup the transaction logs and databases but the sys.fn_hadr_backup_is_preferred_replica function returns 0 on both nodes. I have changed backup preference settings multiple ways, rebuilt the availability group from scratch, rebooted both nodes and there is no change. If I remove the database from the availability group, it will backup no problem(using Ola Hallengren's scripts). We have a case sensitive collation(Latin1_General_BIN). Not sure If that may be an issue.

    SELECT sys.fn_hadr_backup_is_preferred_replica('<db_name>') always returns 0 for a database in the availability group no matter what backup preference you set for the availability group.

    Thanks, Mike

    Wednesday, April 24, 2013 10:29 PM

Answers

  • Solved.

    It was not an issue with the function but with us changing a configuration setting.

    During the course of our setup for our hosted applications we made a change to a configuration setting.

    On a traditional cluster(previously, before sql2012) the @@servername would retrieve the local ‘cluster’ name from sys.sysservers.

    Some of the CLR assemblies use this cluster name(from the @@servername) in their procedures.

    In an Availability Group the local servername is the name of the actual node and not the cluster name(listener name in sql 2012).

    We fudged this for the application team by changing the local servername to the listener name in sys.sysservers(in SQL 2008R2 it is the cluster name).

    i.e. So in 2012, If we have nodes XXXAV, XXXBV and a listener name of XXXS, we wanted the @@servername to retrieve the XXXS.

          This would match the @@servername in our SQL2008R2 traditional clusters.

    It did not break anything in testing(that we were aware of) and all our jobs ran fine until we tested the sys.fn_hadr_backup_is_preferred_replica  function.

    It obviously checks the sys.servers view/table.

    Once I changed the local servername back to the node names the sys.fn_hadr_backup_is_preferred_replica  started working(after I recycled sql server).

    lesson learned !

    Thursday, April 25, 2013 2:28 PM

All replies

  • Solved.

    It was not an issue with the function but with us changing a configuration setting.

    During the course of our setup for our hosted applications we made a change to a configuration setting.

    On a traditional cluster(previously, before sql2012) the @@servername would retrieve the local ‘cluster’ name from sys.sysservers.

    Some of the CLR assemblies use this cluster name(from the @@servername) in their procedures.

    In an Availability Group the local servername is the name of the actual node and not the cluster name(listener name in sql 2012).

    We fudged this for the application team by changing the local servername to the listener name in sys.sysservers(in SQL 2008R2 it is the cluster name).

    i.e. So in 2012, If we have nodes XXXAV, XXXBV and a listener name of XXXS, we wanted the @@servername to retrieve the XXXS.

          This would match the @@servername in our SQL2008R2 traditional clusters.

    It did not break anything in testing(that we were aware of) and all our jobs ran fine until we tested the sys.fn_hadr_backup_is_preferred_replica  function.

    It obviously checks the sys.servers view/table.

    Once I changed the local servername back to the node names the sys.fn_hadr_backup_is_preferred_replica  started working(after I recycled sql server).

    lesson learned !

    Thursday, April 25, 2013 2:28 PM
  • You should never change the system tables, this will cause problems down the road, as you discovered.


    Thursday, April 25, 2013 8:50 PM
    Moderator