1

I currently have a 2 node SQL Server 2012 HA Availability Group setup. Currently we have a number of SharePoint databases that exist in this availability group. I'm attempting to ensure our backups are properly configured to run on the secondary node. I have set the backup priority on node 1 to 50 and node 2 to 100. Then I run the below query to check and see which databases will be backed up on which node:

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

Node 2 reports all databases have it set to the preferred backup replica. The problem is when I then run the same query on node 1. I see about 10 out of the 80+ databases also have node 1 set as the preferred node.

Node 2

database_name   IsPreferredBackupReplicaNow 
WSS_Projects_Content    1 
WSS_Teams_Content   1 
WSS_Teams2_Content  1 
WSS_Teams3_Content  1 
WSS_Teams4_Content  1 
WSS_Teams5_Content  1 
WSS_Teams6_Content  1 
WSS_Teams7_Content  1 
WSS_Mysites_Content 1 
WSS_Mysites10_Content   1 
WSS_Mysites11_Content   1 
WSS_Mysites12_Content   1 
WSS_Mysites13_Content   1 
WSS_Mysites14_Content   1 
WSS_Mysites15_Content   1 
WSS_Mysites2_Content    1 
WSS_Mysites3_Content    1 
WSS_Mysites4_Content    1 
WSS_Mysites5_Content    1 
WSS_Mysites6_Content    1 
WSS_Mysites7_Content    1 
WSS_Mysites8_Content    1 
WSS_Mysites9_Content    1 
WSS_Orgs_HR_Content 1 
WSS_Orgs_its_Content    1 
WSS_orgs_MA_Content 1 
WSS_orgs_ND_Content 1

Node 1

database_name   IsPreferredBackupReplicaNow 
WSS_Projects_Content    1 
WSS_Teams_Content   1 
WSS_Teams2_Content  1 
WSS_Teams3_Content  1 
WSS_Teams4_Content  1 
WSS_Teams5_Content  1 
WSS_Teams6_Content  1 
WSS_Teams7_Content  1 
WSS_Mysites_Content 1 
WSS_Mysites10_Content   1 
WSS_Mysites11_Content   0 
WSS_Mysites12_Content   0 
WSS_Mysites13_Content   0 
WSS_Mysites14_Content   0 
WSS_Mysites15_Content   0 
WSS_Mysites2_Content    0 
WSS_Mysites3_Content    0 
WSS_Mysites4_Content    0 
WSS_Mysites5_Content    0 
WSS_Mysites6_Content    0 
WSS_Mysites7_Content    0 
WSS_Mysites8_Content    0 
WSS_Mysites9_Content    0 
WSS_Orgs_HR_Content 0 
WSS_Orgs_its_Content    0 
WSS_orgs_MA_Content 0 
WSS_orgs_ND_Content 0

This brings about a number of questions I have and my need to understand what is happening. Why is there a discrepancy between the two nodes? If a backup is kicked off, which node will be used for backups? Will both nodes encounter backups? How can I ensure that only the second replica is the one performing the backups?

Our version is currently at 11.0.5058. Running the following statement on node1 shows secondary:

select name as AG_Name, automated_backup_preference_desc
from sys.availability_groups

All databases are synchronized. And all are in the same availability group.

  • Did you check if all the databases are synchronized? did you made a fail-over from one node to another recently. if you didnt make that change recently the issue might be with the backup settings. Under options, Make sure to unchecked the box "For availability databases, ignore replica priority for backup and backup primary settings" – Hailegziabher Dechassa Jul 5 '16 at 19:58
  • Please double check those databases are in the same availability group and not a different one as the preferred backup is set at the AG level. Additionally please make sure that each database on the secondary replica is healthy (data flow isn't paused, etc), otherwise this can happen based on your backup preference. – Sean Gallardy Jul 6 '16 at 5:28
  • Sorry for the delay in response. All databases are synchronized. And all are in the same availability group. How can I very data flow isn't paused? – Geoff Dawdy Jul 7 '16 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.