preferred_replica returns 0 in each node and backup is not running

Question

_{Thursday, June 23, 2016 4:39 AM}

HI Team,

Always on 2014 is configured and having 2 nodes. When I am running the below command it is returned 0 in both the always on nodes. due to this Backup is not running.

SELECT d.database_name, 

  sys.fn_hadr_backup_is_preferred_replica (d.database_name) AS IsPreferredBackupReplicaNow
FROM sys.availability_databases_cluster d

Out put of IsPreferredBackupReplicaNow = 0.

Due to this automated backup is nt running, Could you please help me on this.

Thanks you

Subrat

+91 9008233332

All replies (2)

_{Thursday, June 23, 2016 6:00 AM ✅Answered | 1 vote}

Hi Subrat,

Based on my research, if the @@SERVERNAME doesn't match the server names in the availability group catalog views that are used in sys.fn_hadr_backup_is_preferred_replica , or you have created the availability groups using scripts with lower case server names but your collation is case-sensitive, the above issue will occur.

Please check your server name and make sure your replica names are in upper case when creating availability group if your collation is case-sensitive.

There are a similar thread and connect item for your reference.
http://stackoverflow.com/questions/21565671/fn-hadr-backup-is-preferred-replica-always-returning-zero

https://connect.microsoft.com/SQLServer/feedback/details/765317/sys-fn-hadr-backup-is-preferred-replica-always-return-zero-for-availabilty-database

Thanks,
Lydia Zhang

Lydia Zhang
TechNet Community Support

---

_{Thursday, June 23, 2016 9:05 AM ✅Answered}

Thank you Lydia,

Yes you pointed the correct solution, when I checked the Host and server name details by using the below command, it shows that both of the names are different. We took the AMI from AWS Market place, while changing the hostname only changed the Host and it dint change the server name.

SELECT HOST_NAME()
SELECT @@SERVERNAME

By using the below command I changed the servername. ( Beforing this please stop the cluster service if it is part of SQL  )

sp_dropserver <old_name>;  
GO  
sp_addserver <new_name>, local;  
GO  

Then checked the Prefered role, and output of of IsPreferredBackupReplicaNow = 1

SELECT d.database_name, 
  sys.fn_hadr_backup_is_preferred_replica (d.database_name) AS IsPreferredBackupReplicaNow
FROM sys.availability_databases_cluster d

Out put of IsPreferredBackupReplicaNow = 1

Thank you so much for highlighting this.

Subrat

+91 9008233332