Database Mirror Mode Compliance

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

Checks if a database mirror mode is compliant with Desired Configuration

Knowledge Base article:

Summary

This monitor checks if the operational mode of a DB mirror set according to the desired configuration setting.

Causes

Warning state when the mirror is not operating in the desired mode (High-performance mode / High-safety mode without automatic failover / High-safety mode with automatic failover).

This may have been caused by manual intervention or failure of a witness instance.

Resolutions

Connect to the DB Engine and correct the operation mode to match the Desired Configuration setting.

Use ‘SQL Server Management Studio’ to access the mirroring page in a DB’s properties.

Or change the Desired Configuration Settings by modifying the XML file on the Operations Manager Root Management Server. It is normally named c:\DesiredDBMirrors.xml

Additional

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

Element properties:

Target

RABurri.SQLServer.2008.MirroredDatabase

Parent Monitor

System.Health.ConfigurationState

Category

ConfigurationHealth

Enabled

True

Alert Generate

True

Alert Severity

MatchMonitorHealth

Alert Priority

Normal

Alert Auto Resolve

True

Monitor Type

RABurri.SQLServer.Mirroring.OleDB.TwoState.PreProbeExpression.MonitorType

Remotable

True

Accessibility

Public

Alert Message

Database Mirror Mode Configuration is not compliant

Database mirror {2} in SQL Server instance {1} on computer {0} is not currently running according to its desired configuration.
Current Mode: {5}
Desired Mode: {6}

RunAs

Default

Source Code:

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

  <Category>ConfigurationHealth</Category>

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

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>MatchMonitorHealth</AlertSeverity>

    <AlertParameters>

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

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

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

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

      <AlertParameter5>$Target/Property[Type="MirroringLibrary!RABurri.SQLServer.MirroredDatabase"]/DesiredMirroringRole$</AlertParameter5>

      <AlertParameter6>$Data/Context/Columns/Column[10]$</AlertParameter6>

      <AlertParameter7>$Target/Property[Type="MirroringLibrary!RABurri.SQLServer.MirroredDatabase"]/DesiredMirroringMode$</AlertParameter7>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

    <OperationalState ID="NotCompliant" MonitorTypeStateID="ErrorWarningState" HealthState="Warning"/>

  </OperationalStates>

  <Configuration>

    <Interval>300</Interval>

    <PreProbeExpression>

      <And>

        <Expression>

          <RegExExpression>

            <ValueExpression>

              <Value Type="String">$Target/Property[Type="MirroringLibrary!RABurri.SQLServer.MirroredDatabase"]/DesiredMirroringRole$</Value>

            </ValueExpression>

            <Operator>MatchesRegularExpression</Operator>

            <Pattern>^.+$</Pattern>

          </RegExExpression>

        </Expression>

        <Expression>

          <RegExExpression>

            <ValueExpression>

              <Value Type="String">$Target/Property[Type="MirroringLibrary!RABurri.SQLServer.MirroredDatabase"]/DesiredMirroringMode$</Value>

            </ValueExpression>

            <Operator>MatchesRegularExpression</Operator>

            <Pattern>^.+$</Pattern>

          </RegExExpression>

        </Expression>

      </And>

    </PreProbeExpression>

    <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>

    <ErrorWarningQuery>

					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

				</ErrorWarningQuery>

    <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[2]</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="String">$Target/Property[Type="MirroringLibrary!RABurri.SQLServer.MirroredDatabase"]/MirroringGUID$</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

        <Expression>

          <Or>

            <Expression>

              <SimpleExpression>

                <ValueExpression>

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

                </ValueExpression>

                <Operator>Equal</Operator>

                <ValueExpression>

                  <Value Type="String">$Target/Property[Type="MirroringLibrary!RABurri.SQLServer.MirroredDatabase"]/DesiredMirroringMode$</Value>

                </ValueExpression>

              </SimpleExpression>

            </Expression>

            <Expression>

              <RegExExpression>

                <ValueExpression>

                  <Value Type="String">$Target/Property[Type="MirroringLibrary!RABurri.SQLServer.MirroredDatabase"]/DesiredMirroringMode$</Value>

                </ValueExpression>

                <Operator>DoesNotMatchRegularExpression</Operator>

                <Pattern>^.+$</Pattern>

              </RegExExpression>

            </Expression>

          </Or>

        </Expression>

      </And>

    </SuccessExpression>

    <ErrorWarningExpression>

      <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[2]</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="String">$Target/Property[Type="MirroringLibrary!RABurri.SQLServer.MirroredDatabase"]/MirroringGUID$</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

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

            </ValueExpression>

            <Operator>NotEqual</Operator>

            <ValueExpression>

              <Value Type="String">$Target/Property[Type="MirroringLibrary!RABurri.SQLServer.MirroredDatabase"]/DesiredMirroringMode$</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

        <Expression>

          <RegExExpression>

            <ValueExpression>

              <Value Type="String">$Target/Property[Type="MirroringLibrary!RABurri.SQLServer.MirroredDatabase"]/DesiredMirroringMode$</Value>

            </ValueExpression>

            <Operator>MatchesRegularExpression</Operator>

            <Pattern>^.+$</Pattern>

          </RegExExpression>

        </Expression>

      </And>

    </ErrorWarningExpression>

  </Configuration>

</UnitMonitor>