How does a SQL Server Availability Group with multiple synchronous-commit secondary replicas decide on which secondary replica will become the new primary when an automatic failover is triggered? Can't find any information on this in the documentation.

  • What version of SQL Server are you using? – smithian Oct 16 '12 at 16:22

When using SQL 2012 AlwaysOn clusters (what you linked to) It appears that this functionality is controlled by Windows Failover Clustering. In WSFC, Each application can be given a server activation preference list, like so:

  1. Right-click SQL Server group in the cluster administrator and select properties
  2. On the 'General' tab, the preferred owners section contains all cluster nodes that can own resources in the group. The order in the box is the activation preference
  3. Click 'Modify' and set the order that you wish

Note that the activation preference is only one factor in the failover decision. For instance, a server with a failed SQL service will not become the active node in a failover scenario, even if it is next in the preference order.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.