Home
  •  

Database Mirror Status

RABurri.SQLServer.2008.MirroredDatabase.State.Monitor (UnitMonitor)

Checks if a database mirror is synchronized.

Knowledge Base article:

Summary

This monitor checks if a SQL Server DB mirror is synchronized.

Causes

Critical state when the DB mirror state is Disconnected

Warning state when the state is unsynchronized or suspended.

Resolutions

Connect to the DB Engines and correct issues that caused the mirror to fail.

Use ‘SQL Server Management Studio’ to access the mirroring page in a DB’s properties. Or use the ‘Database Mirroring Monitor’.

Additional

If alerting is enabled, two alerts will be risen by this monitor. One per mirror member database.

Element properties:

TargetRABurri.SQLServer.2008.MirroredDatabase
Parent MonitorSystem.Health.AvailabilityState
CategoryAvailabilityHealth
EnabledTrue
Alert GenerateTrue
Alert SeverityMatchMonitorHealth
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeRABurri.SQLServer.Mirroring.OleDB.ThreeState.MonitorType
RemotableTrue
AccessibilityPublic
Alert Message
Database Mirror not Synchronized
Database mirror {0} in SQL Server instance {1} on computer {2} is {3}
RunAsDefault

Source Code:

<UnitMonitor ID="RABurri.SQLServer.2008.MirroredDatabase.State.Monitor" Accessibility="Public" Enabled="true" Target="RABurri.SQLServer.2008.MirroredDatabase" ParentMonitorID="Health!System.Health.AvailabilityState" Remotable="true" Priority="Normal" TypeID="MirroringLibrary!RABurri.SQLServer.Mirroring.OleDB.ThreeState.MonitorType" ConfirmDelivery="false">

  <Category>AvailabilityHealth</Category>

  <AlertSettings AlertMessage="RABurri.SQLServer.2008.MirroredDatabase.State.Monitor_AlertMessageResourceID">

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>MatchMonitorHealth</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Property[Type="SQLLibrary!Microsoft.SQLServer.Database"]/DatabaseName$</AlertParameter1>

      <AlertParameter2>$Target/Host/Property[Type="SQLLibrary!Microsoft.SQLServer.ServerRole"]/InstanceName$</AlertParameter2>

      <AlertParameter3>$Target/Host/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/PrincipalName$</AlertParameter3>

      <AlertParameter4>$Data/Context/Columns/Column[6]$</AlertParameter4>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

    <OperationalState ID="Unsynchronized" MonitorTypeStateID="WarningState" HealthState="Warning"/>

    <OperationalState ID="Disconnected" MonitorTypeStateID="ErrorState" HealthState="Error"/>

  </OperationalStates>

  <Configuration>

    <Interval>300</Interval>

    <ConnectionString>Provider=SQLOLEDB;Server=$Target/Host/Property[Type="SQLLibrary!Microsoft.SQLServer.DBEngine"]/ConnectionString$;Database=master;Integrated Security=SSPI;</ConnectionString>

    <SuccessQuery>

			  select CAST(d.name as varchar(256)) AS [1],

			  Cast(dm.mirroring_guid as nvarchar(256)) AS [2],

			  Cast(dm.mirroring_role as int) AS [3],

			  CAST(dm.mirroring_role_desc as varchar(256)) AS [4],

			  CAST(dm.mirroring_state as int) AS [5],

			  CAST(dm.mirroring_state_desc as varchar(256)) AS [6],

			  CAST(dm.mirroring_witness_state AS int) AS [7],

			  CAST(dm.mirroring_witness_state_desc AS varchar(256)) AS [8],

			  CAST(dm.mirroring_witness_name AS varchar(256)) AS [9],

			  CAST(CASE

			  WHEN dm.mirroring_safety_level = 1

			  THEN 'High-performance mode'

			  WHEN (dm.mirroring_safety_level = 2) AND (Len(dm.mirroring_witness_name) = 0)

			  THEN 'High-safety mode without automatic failover'

			  WHEN (dm.mirroring_safety_level = 2) AND (Len(dm.mirroring_witness_name) &gt; 0)

			  THEN 'High-safety mode with automatic failover'

			  END	AS varchar(256)) AS [10]

			  from sys.database_mirroring dm

			  join sys.databases d on (dm.database_id=d.database_id)

			  where mirroring_guid is not null

		  </SuccessQuery>

    <WarningQuery>

			  select CAST(d.name as varchar(256)) AS [1],

			  Cast(dm.mirroring_guid as nvarchar(256)) AS [2],

			  Cast(dm.mirroring_role as int) AS [3],

			  CAST(dm.mirroring_role_desc as varchar(256)) AS [4],

			  CAST(dm.mirroring_state as int) AS [5],

			  CAST(dm.mirroring_state_desc as varchar(256)) AS [6],

			  CAST(dm.mirroring_witness_state AS int) AS [7],

			  CAST(dm.mirroring_witness_state_desc AS varchar(256)) AS [8],

			  CAST(dm.mirroring_witness_name AS varchar(256)) AS [9],

			  CAST(CASE

			  WHEN dm.mirroring_safety_level = 1

			  THEN 'High-performance mode'

			  WHEN (dm.mirroring_safety_level = 2) AND (Len(dm.mirroring_witness_name) = 0)

			  THEN 'High-safety mode without automatic failover'

			  WHEN (dm.mirroring_safety_level = 2) AND (Len(dm.mirroring_witness_name) &gt; 0)

			  THEN 'High-safety mode with automatic failover'

			  END	AS varchar(256)) AS [10]

			  from sys.database_mirroring dm

			  join sys.databases d on (dm.database_id=d.database_id)

			  where mirroring_guid is not null

		  </WarningQuery>

    <ErrorQuery>

			  select CAST(d.name as varchar(256)) AS [1],

			  Cast(dm.mirroring_guid as nvarchar(256)) AS [2],

			  Cast(dm.mirroring_role as int) AS [3],

			  CAST(dm.mirroring_role_desc as varchar(256)) AS [4],

			  CAST(dm.mirroring_state as int) AS [5],

			  CAST(dm.mirroring_state_desc as varchar(256)) AS [6],

			  CAST(dm.mirroring_witness_state AS int) AS [7],

			  CAST(dm.mirroring_witness_state_desc AS varchar(256)) AS [8],

			  CAST(dm.mirroring_witness_name AS varchar(256)) AS [9],

			  CAST(CASE

			  WHEN dm.mirroring_safety_level = 1

			  THEN 'High-performance mode'

			  WHEN (dm.mirroring_safety_level = 2) AND (Len(dm.mirroring_witness_name) = 0)

			  THEN 'High-safety mode without automatic failover'

			  WHEN (dm.mirroring_safety_level = 2) AND (Len(dm.mirroring_witness_name) &gt; 0)

			  THEN 'High-safety mode with automatic failover'

			  END	AS varchar(256)) AS [10]

			  from sys.database_mirroring dm

			  join sys.databases d on (dm.database_id=d.database_id)

			  where mirroring_guid is not null

		  </ErrorQuery>

    <SuccessExpression>

      <And>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="String">/DataItem/Columns/Column[1]</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="String">$Target/Property[Type="SQLLibrary!Microsoft.SQLServer.Database"]/DatabaseName$</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="String">/DataItem/Columns/Column[6]</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="String">SYNCHRONIZED</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

      </And>

    </SuccessExpression>

    <WarningExpression>

      <And>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="String">/DataItem/Columns/Column[1]</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="String">$Target/Property[Type="SQLLibrary!Microsoft.SQLServer.Database"]/DatabaseName$</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

        <Expression>

          <Or>

            <Expression>

              <SimpleExpression>

                <ValueExpression>

                  <XPathQuery Type="String">/DataItem/Columns/Column[6]</XPathQuery>

                </ValueExpression>

                <Operator>Equal</Operator>

                <ValueExpression>

                  <Value Type="String">SYNCHRONIZING</Value>

                </ValueExpression>

              </SimpleExpression>

            </Expression>

            <Expression>

              <SimpleExpression>

                <ValueExpression>

                  <XPathQuery Type="String">/DataItem/Columns/Column[6]</XPathQuery>

                </ValueExpression>

                <Operator>Equal</Operator>

                <ValueExpression>

                  <Value Type="String">UNSYNCHRONIZED</Value>

                </ValueExpression>

              </SimpleExpression>

            </Expression>

            <Expression>

              <SimpleExpression>

                <ValueExpression>

                  <XPathQuery Type="String">/DataItem/Columns/Column[6]</XPathQuery>

                </ValueExpression>

                <Operator>Equal</Operator>

                <ValueExpression>

                  <Value Type="String">SUSPENDED</Value>

                </ValueExpression>

              </SimpleExpression>

            </Expression>

          </Or>

        </Expression>

      </And>

    </WarningExpression>

    <ErrorExpression>

      <And>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="String">/DataItem/Columns/Column[1]</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="String">$Target/Property[Type="SQLLibrary!Microsoft.SQLServer.Database"]/DatabaseName$</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="String">/DataItem/Columns/Column[6]</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="String">DISCONNECTED</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

      </And>

    </ErrorExpression>

  </Configuration>

</UnitMonitor>