As a clustered resource, the availability group clustered resource/role have configurable cluster properties, like possible owners and preferred owners. SQL Server actively manages these resource properties. These properties are set automatically by SQL Server and should not be modified using Failover Cluster Manager .
SQL Server automatically manages an availability group's Possible and Preferred Owner properties
- The current primary replica is always set as the availability group resource's Possible Owner and also as a Preferred Owner.
- When two replicas are configured for AUTOMATIC failover mode (limit is two replicas), both replicas (primary and failover partner) are selected as Possible Owners and Preferred Owners.
- Preferred owners has a priority. The priority is first for current primary replica.
Note:
This is only for the availability group resource and not for other resources. For example, dependent resources like listener, listener IP have all availability group failover nodes as possible owners. Another example is SQL cluster resources (their possible owners are not changed), since their dependencies are installed during setup.
Demo (change from Manual to Automatic)
Consider an availability group with failover mode as manual. This has possible owners and preferred owners mentioned further below.
Now, if we change the Failover Mode from Manual to Automatic, then SQL Server automatically changes the Preferred and Possible owners if required.
Demo (failover to a replica)
If we failover (like from SQLONE to SQLTWO), then possible and preferred owner is automatically changed by SQL Server, if required. In below scenario, the order of preferred owners get changed since SQLTWO is now the primary replica.
In conclusion, there are clustered resources for which you can configure possible / preferred owners to control. For availability groups, SQL server dynamically configures these values, and they should not be modified. Manual modification can result in unexpected behavior of resource/group.
More Information:
Reference query to get failover mode:
SELECT replica_server_name, failover_mode_desc FROM sys.availability_replicas;
replica_server_name failover_mode_desc
--------------------------------------
SQLONE AUTOMATIC
SQLTHREE AUTOMATIC
SQLTWO MANUAL
(3 row(s) affected)
Additional information:
The possible owner change is done using rcm::RcmApi::AddPossibleOwner, rcm::RcmApi::RemovePossibleOwner.
Keyword(s):
AlwaysOn, SQL Server.
Author:
Vijay Rodrigues.