Availability Replica Data Synchronization

Microsoft.SQLServer.Windows.Monitor.AvailabilityReplica.DataSynchronizationHealth (UnitMonitor)

This monitor rolls up the data synchronization state of all database replica in the availability replica. The monitor is unhealthy when any database replica is not in the expected data synchronization state. The monitor is healthy otherwise.

Knowledge Base article:

Summary

Causes

At least one availability database on this availability replica has an unhealthy data synchronization state. If this is an asynchronous-commit availability replica, all availability databases should be in the SYNCHRONIZING state. If this is a synchronous-commit availability replica, all availability databases should be in the SYNCHRONIZED state.

Resolutions

Find the database replica with unhealthy data synchronization state using database replica monitor and resolve the issue at the database 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

Element properties:

Target

Microsoft.SQLServer.Windows.AvailabilityReplica

Parent Monitor

System.Health.AvailabilityState

Category

AvailabilityHealth

Enabled

True

Alert Generate

True

Alert Severity

MatchMonitorHealth

Alert Priority

Normal

Alert Auto Resolve

True

Monitor Type

Microsoft.SQLServer.Windows.MonitorType.AlwaysOnSystemPolicy

Remotable

True

Accessibility

Public

Alert Message

MSSQL on Windows: Data synchronization state of Availability Replica is not healthy

The data synchronization state of Availability Replica is not healthy.
Instance Name: {1}
Machine Name: {0}
Availability Group Name: {2}

RunAs

Default

Source Code:

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

  <Category>AvailabilityHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.Windows.Monitor.AvailabilityReplica.DataSynchronizationHealth.AlertMessage">

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>MatchMonitorHealth</AlertSeverity>

    <AlertParameters>

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

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

      <AlertParameter3>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.AvailabilityReplica"]/AvailabilityGroupName$</AlertParameter3>

    </AlertParameters>

  </AlertSettings>

  <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>AlwaysOnArDataSynchronizationHealthPolicy</PolicyName>

    <BagKey>2-$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.AvailabilityReplica"]/UniqueId$</BagKey>

    <SqlExecTimeoutSeconds>60</SqlExecTimeoutSeconds>

    <SqlTimeoutSeconds>15</SqlTimeoutSeconds>

    <TimeoutSeconds>300</TimeoutSeconds>

    <IntervalSeconds>900</IntervalSeconds>

    <SyncTime/>

  </Configuration>

</UnitMonitor>