データベース ミラーリング パートナーの状態

Microsoft.SQLServer.2012.Mirroring.PartnersStateMonitor (UnitMonitor)

このモニターは、データベース ミラーが同期しているかどうかを調べます。

Knowledge Base article:

Element properties:

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.2012.Mirroring.PartnersStateMonitor" Accessibility="Public" Enabled="true" Target="SQL2012Mirroring!Microsoft.SQLServer.2012.Mirroring.Witness" ParentMonitorID="Health!System.Health.AvailabilityState" TypeID="Microsoft.SQLServer.2012.Mirroring.ScriptedTwoStateType" Remotable="true" Priority="Normal" ConfirmDelivery="false" RunAs="SQL!Microsoft.SQLServer.SQLProbeAccount">

  <Category>AvailabilityHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2012.Mirroring.PartnersStateMonitor.AlertMessage">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Error</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Property[Type="SQL2012Mirroring!Microsoft.SQLServer.2012.Mirroring.Witness"]/DatabaseName$</AlertParameter1>

      <AlertParameter2>$Target/Property[Type="SQL2012Mirroring!Microsoft.SQLServer.2012.Mirroring.Witness"]/Principal$</AlertParameter2>

      <AlertParameter3>$Target/Property[Type="SQL2012Mirroring!Microsoft.SQLServer.2012.Mirroring.Witness"]/Mirror$</AlertParameter3>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

    <OperationalState ID="Bad" MonitorTypeStateID="Bad" HealthState="Error"/>

  </OperationalStates>

  <Configuration>

    <ScriptName>GetSQL2012MirroringPartnersState.vbs</ScriptName>

    <ScriptBody><Script>'#Include File:GetSQL2008MirroringPartnersState.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 = 4001



Dim GetPartnersStatusQuery

GetPartnersStatusQuery = "SELECT " &amp;_

                            " mirroring_guid, " &amp;_

                            " partner_sync_state_desc " &amp;_

                        " FROM sys.database_mirroring_witnesses "



Call GetPartnersStatus()



Sub GetPartnersStatus()

    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 = PartnersStatus(WScript.Arguments(0), oBag, WScript.Arguments(1), WScript.Arguments(2), WScript.Arguments(3))

        Call oAPI.Return(oBag)

        On Error Resume Next

        Call GlobalErrorListToEventLog()

    Else

        On Error Resume Next

        Call GlobalErrorListToEventLog()

        Wscript.Quit()

    End If

End Sub



Function PartnersStatus(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

		PartnersStatus = SQL_MONITORING_CONNECT_FAILURE

        ThrowScriptErrorNoAbort "Cannot connect to '" &amp; sSQLConnectionString &amp; "' SQL Server", e

        Exit Function

    End If

    	    

    Dim oResults

	e.Clear

	On Error Resume Next

		Set oResults = cnADOConnection.Execute(GetPartnersStatusQuery)

		e.Save

	On Error Goto 0

	If e.Number &lt;&gt; 0 Then

		PartnersStatus = SQL_MONITORING_QUERY_FAILURE

        ThrowScriptErrorNoAbort "Query execution failed for '" &amp; sSQLConnectionString &amp; "' SQL Server", 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), CStr(oResults(1)))

		oResults.MoveNext

	Loop

	

	cnADOConnection.Close

    PartnersStatus = SQL_MONITORING_SUCCESS

End Function

</Script></ScriptBody>

    <IntervalSeconds>900</IntervalSeconds>

    <SyncTime/>

    <TimeoutSeconds>300</TimeoutSeconds>

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

    <GoodExpression>

      <SimpleExpression>

        <ValueExpression>

          <XPathQuery Type="String">Property[@Name='$Target/Property[Type="SQL2012Mirroring!Microsoft.SQLServer.2012.Mirroring.Witness"]/MirroringGUID$']</XPathQuery>

        </ValueExpression>

        <Operator>Equal</Operator>

        <ValueExpression>

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

        </ValueExpression>

      </SimpleExpression>

    </GoodExpression>

    <BadExpression>

      <SimpleExpression>

        <ValueExpression>

          <XPathQuery Type="String">Property[@Name='$Target/Property[Type="SQL2012Mirroring!Microsoft.SQLServer.2012.Mirroring.Witness"]/MirroringGUID$']</XPathQuery>

        </ValueExpression>

        <Operator>Equal</Operator>

        <ValueExpression>

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

        </ValueExpression>

      </SimpleExpression>

    </BadExpression>

  </Configuration>

</UnitMonitor>