There doesn’t seem to be much documentation out on the web regarding the AlwaysOn Availability Group policies and associated conditions that come with SQL Server 2012, so I thought I would aggregate / organize some of the condition logic here for reference-sake… Also – looking at conditions in SSMS, you’ll see that for some conditions like AlwaysOnArDataSynchronizationHealthCondition, you cannot expand the full field information in the GUI or even select the various fields in order to see the descriptions. Given this, in some cases I had to confirm the expression logic via msdb.dbo.syspolicy_conditions (looking through the Expression XML). Of course, you could just use the raw XML for documentation, but it isn’t very friendly to look at.
So here is the reference information for AG-related conditions as of SQL Server version 11.0.3349…
AlwaysOnAgAutomaticFailoverHealthCondition
- References the Availability Group State facet
- Referenced by AlwaysOnAgAutomaticFailoverHealthPolicy
- Expression:
- (@IsAutoFailover = True AND @NumberOfSynchronizedSecondaryReplicas > 0) OR @IsAutoFailover = False
AlwaysOnAgOnlineStateHealthCondition
- References the Availability Group State facet
- Referenced by AlwaysOnAgOnlineStateHealthPolicy
- Expression:
- @IsOnline = True
AlwaysOnAgReplicasConnectionHealthCondition
- References the Availability Group State facet
- Referenced by AlwaysOnAgReplicasConnectionHealthPolicy
- Expression:
- @NumberOfDisconnectedReplicas = 0
AlwaysOnAgReplicasDataSynchronizationHealthCondition
- References the Availability Group State facet
- Referenced by AlwaysOnAgReplicasDataSynchronizationHealthPolicy
- Expression:
- @NumberOfNotSynchronizingReplicas = 0
AlwaysOnAgReplicasRoleHealthCondition
- References the Availability Group State facet
- Referenced by AlwaysOnAgReplicasRoleHealthPolicy
- Expression:
- @NumberOfReplicasWithUnhealthyRole = 0
AlwaysOnAgSynchronousReplicasDataSynchronizationHealthCondition
- References the Availability Group State facet
- Referenced by AlwaysOnAgSynchronousReplicasDataSynchronizationHealthPolicy
- Expression:
- @NumberOfNotSynchronizedReplicas = 0
AlwaysOnAgWSFClusterHealthCondition
- References the Server facet
- Referenced by AlwaysOnAgWSFClusterHealthPolicy
- Expression:
- @ClusterQuorumState = NormalQuorum
AlwaysOnArConnectionHealthCondition
- References the Availability Replica facet
- Referenced by AlwaysOnArConnectionHealthPolicy
- Expression:
- @ConnectionState = Connected
AlwaysOnArDataSynchronizationHealthCondition
- References the Availability Replica facet
- Referenced by AlwaysOnArDataSynchronizationHealthPolicy
- Expression:
- (@AvailabilityMode = AsynchronousCommit AND (@RollupSynchronizationState = Synchronizing OR @RollupSynchronizationState = Synchronized) OR @RollupSynchronizationState = Synchronized)
AlwaysOnArJoinStateHealthCondition
- References the Availability Replica facet
- Referenced by AlwaysOnArJoinStateHealthPolicy
- Expression:
- @JoinState != NotJoined
AlwaysOnArRoleHealthCondition
- References the Availability Replica facet
- Referenced by AlwaysOnArRoleHealthPolicy
- Expression:
- @Role = Primary OR @Role = Secondary
AlwaysOnDbrDataSynchronizationCondition
- References the Database Replica State facet
- Referenced by AlwaysOnDbrDataSynchronizationState (notice the change in naming convention)
- Expression:
- (@ReplicaAvailabilityMode = AsynchronousCommit AND @SynchronizationState != NotSynchronizing) OR @SynchronizationState = Synchronized
AlwaysOnDbrJoinStateCondition
- References the Database Replica State facet
- Referenced by AlwaysOnDbrJoinStatePolicy
- Expression:
- @IsJoined = True
AlwaysOnDbrSuspendStateCondition
- References the Database Replica State facet
- Referenced by AlwaysOnDbrSuspendStatePolicy
- Expression:
- @IsSuspended = False
IsHadrEnabled
- References the Server facet
- Referenced by all of the AlwaysOnAg / AlwaysOnAr and AlwaysOnDbr policies
- Expression:
- @IsHadrEnabled = True
I bothered with documenting this in order to better understand some of the availability group health model logic at the availability group, availability replica and database replica scope. Microsoft did talk a little about the “AlwaysOn Health Model” in the following post as well, so I recommend reviewing this if you plan on deploying availability groups in your environment:
The AlwaysOn Health Model Part 1 — Health Model Architecture
If you want to extend the model further (or override policies), Microsoft’s Will Snavely also blogged about it here:
The AlwaysOn Health Model Part 2 — Extending the Health Model