Availability Replicas Connection monitor

Microsoft.SQLServer.Windows.Monitor.AvailabilityGroupHealth.AllAvailabilityReplicasAreConnected (UnitMonitor)

This monitor rolls up the connection state of all availability replicas and check whether any availability replica is DISCONNECTED. The monitor is unhealthy when any availability replica is DISCONNECTED. The monitor is healthy otherwise.

Knowledge Base article:

Summary

This monitor rolls up the connection state of all Availability Replica's and check whether any Availability Replica is DISCONNECTED. The monitor is unhealthy when any Availability Replica is DISCONNECTED. The monitor is healthy otherwise.

Causes

In this Availability Group, at least one secondary replica is not connected to the primary replica. The connected state is DISCONNECTED.

Resolutions

Find the Availability Replica which is DISCONNECTED using Availability Replica monitor state and resolve the issue at the Availability Replica.

Overridable Parameters

Name	Description	Default Value
Enabled	Enables or disables the workflow.	Yes
Generates Alerts	Defines whether the workflow generates an Alert.	No
Interval (seconds)	The recurring interval of time in seconds in which to run the workflow.	900
Synchronization Time	The synchronization time is specified by using a 24-hour format. Can be omitted.
Timeout (seconds)	Specifies the time the workflow is allowed to run before being closed and marked as failed.	300
Timeout for query execution (seconds)	The workflow will fail and register an event, if the query execution takes longer than the specified period.	60
Timeout for database connection (seconds)	The workflow will fail and register an event, if it cannot access the database during the specified period.	15
Number of samples	The number of times a measured value must breach the threshold before the state is changed.	2

Element properties:

Target	Microsoft.SQLServer.Windows.AvailabilityGroupHealth
Parent Monitor	System.Health.AvailabilityState
Category	AvailabilityHealth
Enabled	True
Alert Generate	False
Alert Auto Resolve	True
Monitor Type	Microsoft.SQLServer.Windows.MonitorType.AlwaysOnSystemPolicyWithSampling
Remotable	True
Accessibility	Public
RunAs	Default

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.Windows.Monitor.AvailabilityGroupHealth.AllAvailabilityReplicasAreConnected" Accessibility="Public" Enabled="true" Target="SqlDiscW!Microsoft.SQLServer.Windows.AvailabilityGroupHealth" ParentMonitorID="Health!System.Health.AvailabilityState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.Windows.MonitorType.AlwaysOnSystemPolicyWithSampling" ConfirmDelivery="false">

  <Category>AvailabilityHealth</Category>

  <OperationalStates>

    <OperationalState ID="Success" MonitorTypeStateID="HealthState" HealthState="Success"/>

    <OperationalState ID="Critical" MonitorTypeStateID="ErrorState" HealthState="Warning"/>

  </OperationalStates>

  <Configuration>

    <MachineName>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</MachineName>

    <NetbiosComputerName>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/NetbiosComputerName$</NetbiosComputerName>

    <InstanceName>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</InstanceName>

    <ConnectionString>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/ConnectionString$</ConnectionString>

    <InstanceVersion>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Version$</InstanceVersion>

    <InstanceEdition>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Edition$</InstanceEdition>

    <MonitoringType>$Target/Host/Property[Type="SqlDiscW!Microsoft.SQLServer.Windows.DBEngine"]/MonitoringType$</MonitoringType>

    <PolicyName>AlwaysOnAgReplicasConnectionHealthPolicy</PolicyName>

    <BagKey>1-$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.AvailabilityGroupHealth"]/AvailabilityGroupName$</BagKey>

    <NumSamples>2</NumSamples>

    <SqlExecTimeoutSeconds>60</SqlExecTimeoutSeconds>

    <SqlTimeoutSeconds>15</SqlTimeoutSeconds>

    <TimeoutSeconds>300</TimeoutSeconds>

    <IntervalSeconds>900</IntervalSeconds>

    <SyncTime/>

  </Configuration>

</UnitMonitor>