Home / server / Questions / 435667
In Process
arex1337
Asked: 2012-10-08 04:34:36 +0800 CST

How does a SQL Server Availability Group decide on which secondary will become the new primary when an automatic failover is triggered?

  • 772

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.

windows-server-2008

1 Answers

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

    • 1