Database Mirror Status

Microsoft.SQLServer.2016.Mirroring.DatabaseStateMonitor (UnitMonitor)

This monitor checks if database mirror is synchronized.

Knowledge Base article:

Summary

This monitor checks the status of the database mirror as reported by Microsoft® SQL Server™. This is done by running a query against the master database of the SQL Server instance and returning the state of the database mirror. If you receive an alert from this monitor, action is required in order to bring the database mirroring back to an operational state.

Causes

An unhealthy state indicates that the SQL Server™ database mirror is currently unavailable. All states except the SYNCHRONIZED state will cause an unhealthy state. The possible states are detailed below:

SQL Database Mirror State

Description

MOM Health State

SYNCHRONIZED

When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database.

If transaction safety is set to FULL, automatic failover and manual failover are both supported in the SYNCHRONIZED state, there is no data loss after a failover.

If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state.

HEALTHY

SYNCHRONIZING

The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward.

At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up.

WARNING

SUSPENDED

The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover.

A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session.

SUSPENDED is a persistent state that survives partner shutdowns and startups.

WARNING

PENDING_FAILOVER

This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role.

When the failover is initiated, the principal database goes into the PENDING_FAILOVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter.

WARNING

DISCONNECTED

The partner has lost communication with the other partner.

CRITICAL

To find the exact state that was reported for the database, examine the context of the state change or alert.

Resolutions

To resolve the issue try the following:

Overridable Parameters

Name

Description

Default Value

Alert Priority

Defines Alert Priority.

Normal

Alert Severity

Defines Alert Severity.

MatchMonitorHealth

Enabled

Enables or disables the workflow.

Yes

Generates Alerts

Defines whether the workflow generates an Alert.

Yes

Interval (seconds)

The recurring interval of time in seconds in which to run the workflow.

900

Synchronization Time

The synchronization time specified by using a 24-hour format. May be omitted.

 

Timeout (seconds)

Specifies the time the workflow is allowed to run before being closed and marked as failed.

300

Element properties:

TargetMicrosoft.SQLServer.2016.Mirroring.Database
Parent MonitorSystem.Health.AvailabilityState
CategoryAvailabilityHealth
EnabledTrue
Alert GenerateTrue
Alert SeverityMatchMonitorHealth
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.2016.Mirroring.ScriptedThreeStateType
RemotableTrue
AccessibilityPublic
Alert Message
Database Mirror is not Synchronized
Database mirror '{0}' in SQL Server instance '{1}' on computer '{2}' reporting '{3}' state.
RunAsMicrosoft.SQLServer.MonitoringAccount

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.2016.Mirroring.DatabaseStateMonitor" Accessibility="Public" Enabled="true" Target="SQL2016Mirroring!Microsoft.SQLServer.2016.Mirroring.Database" ParentMonitorID="Health!System.Health.AvailabilityState" TypeID="Microsoft.SQLServer.2016.Mirroring.ScriptedThreeStateType" Remotable="true" Priority="Normal" ConfirmDelivery="false" RunAs="GPMP!Microsoft.SQLServer.MonitoringAccount">
<Category>AvailabilityHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.2016.Mirroring.DatabaseStateMonitor.AlertMessage">
<AlertOnState>Warning</AlertOnState>
<AutoResolve>true</AutoResolve>
<AlertPriority>Normal</AlertPriority>
<AlertSeverity>MatchMonitorHealth</AlertSeverity>
<AlertParameters>
<AlertParameter1>$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.Database"]/DatabaseName$</AlertParameter1>
<AlertParameter2>$Target/Host/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.ServerRole"]/InstanceName$</AlertParameter2>
<AlertParameter3>$Target/Host/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/PrincipalName$</AlertParameter3>
<AlertParameter4>$Data/Context/Property[@Name='$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.Database"]/DatabaseName$-State']$</AlertParameter4>
</AlertParameters>
</AlertSettings>
<OperationalStates>
<OperationalState ID="Good" MonitorTypeStateID="Good" HealthState="Success"/>
<OperationalState ID="NeitherGoodNorBad" MonitorTypeStateID="NeitherGoodNorBad" HealthState="Warning"/>
<OperationalState ID="Bad" MonitorTypeStateID="Bad" HealthState="Error"/>
</OperationalStates>
<Configuration>
<ScriptName>GetSQL2016MirroredDBState.vbs</ScriptName>
<ScriptBody><Script>'#Include File:GetSQL2016MirroredDBState.vbs
'Copyright (c) Microsoft Corporation. All rights reserved.
' This script takes a single parameter
' Param 0: The SQL connection string to connect to

Const SQL_MONITORING_CONNECT_FAILURE = -1
Const SQL_MONITORING_QUERY_FAILURE = -2
Const SQL_MONITORING_SUCCESS = 0

Const SCRIPT_EVENT_ID = 4211

Dim GetDatabasesStatusQuery
GetDatabasesStatusQuery = "SELECT " &amp;_
" d.name, " &amp;_
" dm.mirroring_state_desc " &amp;_
" FROM sys.databases d " &amp;_
" INNER JOIN sys.database_mirroring dm ON dm.database_id = d.database_id " &amp;_
" WHERE dm.mirroring_guid IS NOT NULL"

Call GetDBHealth()

Sub GetDBHealth()
If WScript.Arguments.Count = 4 Then
Dim oBag
Set oBag = oAPI.CreatePropertyBag()

Dim state, serviceName

serviceName = GetSQLServiceName(WScript.Arguments(2))
state = GetServiceState(WScript.Arguments(1), serviceName)

if (state &lt;&gt; "Running") And (state &lt;&gt; "Unknown") Then
Call oAPI.Return(oBag)
WScript.Quit()
End If

Dim nResult
nResult = DBHealth(WScript.Arguments(0), oBag, WScript.Arguments(1), WScript.Arguments(2), WScript.Arguments(3))
Call oAPI.Return(oBag)
Call GlobalErrorListToEventLog()
Else
Wscript.Quit()
End If
End Sub

Function DBHealth(ByVal sSQLConnectionString, ByRef oBag, ByVal sComputerName, ByVal sInstanceName, ByVal sTcpPort)
Dim e
Set e = New Error

Dim cnADOConnection
Set cnADOConnection = SmartConnectWithoutSQLADODB(sSQLConnectionString, sTcpPort, sComputerName, sInstanceName, "master")
if cnADOConnection Is Nothing Then
DBHealth = SQL_MONITORING_CONNECT_FAILURE
Exit Function
End If

Dim oResults
e.Clear
On Error Resume Next
Set oResults = cnADOConnection.Execute(GetDatabasesStatusQuery)
e.Save
On Error Goto 0
If e.Number &lt;&gt; 0 Then
DBHealth = SQL_MONITORING_QUERY_FAILURE
ThrowScriptErrorNoAbort "Query execution failed", e
If (oResults &lt;&gt; null) Then oResults.Close
cnADOConnection.Close
Exit Function
End If

Do While Not oResults.EOF
Call oBag.AddValue(oResults(0) &amp; "-State", CStr(oResults(1)))
oResults.MoveNext
Loop

cnADOConnection.Close
DBHealth = SQL_MONITORING_SUCCESS
End Function
</Script></ScriptBody>
<IntervalSeconds>900</IntervalSeconds>
<SyncTime/>
<TimeoutSeconds>300</TimeoutSeconds>
<ConnectionString>$Target/Host/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.DBEngine"]/ConnectionString$</ConnectionString>
<GoodExpression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="String">Property[@Name='$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.Database"]/DatabaseName$-State']</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="String">SYNCHRONIZED</Value>
</ValueExpression>
</SimpleExpression>
</GoodExpression>
<NeitherGoodNorBadExpression>
<Or>
<Expression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="String">Property[@Name='$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.Database"]/DatabaseName$-State']</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="String">SYNCHRONIZING</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
<Expression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="String">Property[@Name='$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.Database"]/DatabaseName$-State']</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="String">UNSYNCHRONIZED</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
<Expression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="String">Property[@Name='$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.Database"]/DatabaseName$-State']</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="String">PENDING_FAILOVER</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
<Expression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="String">Property[@Name='$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.Database"]/DatabaseName$-State']</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="String">SUSPENDED</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
</Or>
</NeitherGoodNorBadExpression>
<BadExpression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="String">Property[@Name='$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.Database"]/DatabaseName$-State']</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="String">DISCONNECTED</Value>
</ValueExpression>
</SimpleExpression>
</BadExpression>
</Configuration>
</UnitMonitor>