Multiple Databases Monitoring Module

Microsoft.BizTalk.Server.2016.Monitor.MultipleDatabase.ProbeAction (ProbeActionModuleType)

This module provides data for monitoring availability of SQL databases as a single monitor.
Element properties:

Show References:
- Unit Monitor Types (1)
Type	ProbeActionModuleType
Isolation	Any
Accessibility	Public
RunAs	Microsoft.BizTalk.ProbeAccount
OutputType	System.PropertyBagData
Member Modules:

ID	Module Type	TypeId	RunAs
Pass	ProbeAction	System.PassThroughProbe	Default
Script	ProbeAction	Microsoft.Windows.ScriptPropertyBagProbe	Default
Overrideable Parameters:

ID	ParameterType	Selector	Display Name	Description
LogSuccessEvent	string	$Config/LogSuccessEvent$	Log Success Event	An event is logged based on successful completion of the script associated with the module when value is set to 'true'.
TimeoutSeconds	int	$Config/TimeoutSeconds$	Timeout Seconds	This is the timeout (in seconds) after which execution of the script associated with the module is terminated if not yet completed.
Source Code:

<ProbeActionModuleType ID="Microsoft.BizTalk.Server.2016.Monitor.MultipleDatabase.ProbeAction" Accessibility="Public" RunAs="Microsoft.BizTalk.ProbeAccount" Batching="false" PassThrough="false">

  <Configuration>

    <xsd:element name="MgmtDBServer" type="xsd:string"/>

    <xsd:element name="MgmtDBName" type="xsd:string"/>

    <xsd:element name="LogSuccessEvent" type="xsd:boolean"/>

    <xsd:element name="TimeoutSeconds" type="xsd:integer"/>

  </Configuration>

  <OverrideableParameters>

    <OverrideableParameter ID="LogSuccessEvent" Selector="$Config/LogSuccessEvent$" ParameterType="string"/>

    <OverrideableParameter ID="TimeoutSeconds" Selector="$Config/TimeoutSeconds$" ParameterType="int"/>

  </OverrideableParameters>

  <ModuleImplementation Isolation="Any">

    <Composite>

      <MemberModules>

        <ProbeAction ID="Pass" TypeID="System!System.PassThroughProbe"/>

        <ProbeAction ID="Script" TypeID="Windows!Microsoft.Windows.ScriptPropertyBagProbe">

          <ScriptName>BizTalkMultipleDatabaseMonitor.vbs</ScriptName>

          <Arguments>"$Config/MgmtDBServer$" "$Config/MgmtDBName$" "$Config/LogSuccessEvent$"</Arguments>

          <ScriptBody><Script>

'Copyright (c) Microsoft Corporation. All rights reserved



'This script generates monitoring data for availability of a group of SQL databases

'as a single entity based on connectivity. Monitor states can be error (primary

'database not available), warning (some non-primary databases not available), success

'(all databases available).



Option Explicit



'Event Constants

Const EVENT_TYPE_SUCCESS = 0

Const EVENT_TYPE_ERROR = 1

Const EVENT_TYPE_WARNING = 2

Const EVENT_TYPE_INFORMATION = 4

'Other constants

Const SCRIPT_NAME = "BizTalk Server Multiple Database Monitor"

' Event ID Constants

Const EVENTID_SUCCESS = 99

Const EVENTID_SCRIPT_ERROR = 1000



Const MGMTDB_CONNECT_FAILURE = -1

Const MGMTDB_QUERY_FAILURE = -2

Const StateDataType = 3



Dim oAPI, oBagState

Dim oParams, MgmtDBServer, MgmtDBName, bLogSuccessEvent

Dim strMonitorStatus, strErrorDetail

Dim dtStart, strMessage



strMonitorStatus = "0"

strErrorDetail = ""

dtStart = Now



Set oParams = WScript.Arguments

if oParams.Count &lt; 3 then

    Wscript.Quit -1

End if



MgmtDBServer = oParams(0)

MgmtDBName = oParams(1)

bLogSuccessEvent = CBool(oParams(2))



Set oAPI = CreateObject("Mom.ScriptAPI")

Set oBagState = oAPI.CreateTypedPropertyBag(StateDataType)



Dim bPrimaryDown, numSecondaryDown

bPrimaryDown = false

numSecondaryDown = 0



call GetDatabaseStatus()

GetMonitorStatus



Function GetDatabaseStatus()

    Dim e

    Dim SQLDBConn

    Dim cnADOConnection1, cnADOConnection2

    Dim oResults1, oResults2, sDBState

    Dim bError

    Dim strErrorMsg



    Set e = New Error

    SQLDBConn = "Server=" &amp; MgmtDBServer &amp; ";Database=" &amp; MgmtDBName &amp; ";Trusted_Connection=yes"

    oResults1 = null

    Set cnADOConnection1 = MomCreateObject("ADODB.Connection")

    cnADOConnection1.Provider = "SQLNCLI11"

    cnADOConnection1.ConnectionTimeout = 15



    e.Clear

    On Error Resume Next

    cnADOConnection1.Open SQLDBConn

    e.Save

    On Error Goto 0

    If 0 &lt;&gt; e.number then

        GetDatabaseStatus = MGMTDB_CONNECT_FAILURE

        Exit Function

    End If



    e.Clear

    On Error Resume Next

    Set oResults1 = cnADOConnection1.Execute("select DBServerName, DBName, IsMasterMsgBox from adm_MessageBox with (NoLock)")

    e.Save

    On Error Goto 0



    If 0 &lt;&gt; e.number then

        GetDatabaseStatus = MGMTDB_QUERY_FAILURE

        If (oResults1 &lt;&gt; null) Then oResults1.Close

        Exit Function

    End If



    bError = false

    Do While Not oResults1.EOF

        SQLDBConn = "Server=" &amp; CStr(oResults1(0)) &amp; ";Database=" &amp; CStr(oResults1(1)) &amp; ";Trusted_Connection=yes"

        oResults2 = NULL



        Set cnADOConnection2 = MomCreateObject("ADODB.Connection")

        cnADOConnection2.Provider = "SQLNCLI11"

        cnADOConnection2.ConnectionTimeout = 15



        e.Clear

        On Error Resume Next

        cnADOConnection2.Open SQLDBConn

        e.Save

        On Error Goto 0

        If 0 &lt;&gt; e.number then

            bError = true

            strErrorMsg = SCRIPT_NAME &amp; ": - Database " &amp; CStr(oResults1(1)) &amp; " Failure on Server " &amp; CStr(oResults1(0)) &amp; " Error Detail:" &amp; e.Description

        Else

            If cnADOConnection2.State = 0 Then

                bError = true

                strErrorMsg = SCRIPT_NAME &amp; ": - Database " &amp; CStr(oResults1(1)) &amp; " Failure on Server " &amp; CStr(oResults1(0)) &amp; " Error Detail: Can not establish connection"

            End If

        End if



        If cnADOConnection2.State = 1 Then

            e.Clear

            On Error Resume Next

            Set oResults2 = cnADOConnection2.Execute("SELECT state_desc FROM sys.databases where name = '" + CStr(oResults1(1)) + "'")

            e.Save

            On Error Goto 0



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

                bError = true

                strErrorMsg = SCRIPT_NAME &amp; ": - Database " &amp; CStr(oResults1(1)) &amp; " Failure on Server " &amp; CStr(oResults1(0)) &amp; " Error Detail:" &amp; e.Description

            End If



            If IsNull(oResults2) = false Then

                Do While Not oResults2.EOF

                    sDBState = oResults2(0)

                    oResults2.MoveNext

                Loop

                Set oResults2 = nothing

            End If



            cnADOConnection2.Close

        End If



        If strErrorMsg = "" Then

            If sDBState = "ONLINE" Then

                strErrorMsg = ""

            Else

                bError = true

                strErrorMsg = SCRIPT_NAME &amp; ": - Database " &amp; CStr(oResults1(1)) &amp; " Failure on Server " &amp; CStr(oResults1(0)) &amp; " Error Detail: Database state is offline"

            End If

        End If



        if bError = true then

            if CStr(oResults1(2)) = "-1" then

                bPrimaryDown = true

            else

                numSecondaryDown = numSecondaryDown + 1

            end if

        end if



        If bLogSuccessEvent Then

            strMessage = "The script '" &amp; SCRIPT_NAME &amp; "' completed successfully in " &amp; DateDiff("s", dtStart, Now) &amp; " seconds."

            CreateEvent EVENTID_SUCCESS, EVENT_TYPE_INFORMATION, strMessage

        End If



        bError = false

        strErrorDetail = strErrorDetail &amp; strErrorMsg &amp; vbCrLf

        strErrorMsg = ""

        oResults1.MoveNext

    Loop



    cnADOConnection1.Close

End Function



Sub GetMonitorStatus

    if bPrimaryDown = true then

        strMonitorStatus = "0"

    else

        if numSecondaryDown &gt; 0 then

            strMonitorStatus = "1"

        else

            strMonitorStatus = "2"

        end if

    end if

    oBagState.AddValue "State", strMonitorStatus

    oBagState.AddValue "ErrorDetail", strErrorDetail

    oAPI.AddItem oBagState

    Call oAPI.ReturnItems

End Sub



Sub CreateEvent(lEventID, lEventType, strMessage)

    oAPI.LogScriptEvent SCRIPT_NAME, lEventID, lEventType, strMessage

End Sub



Function MomCreateObject(ByVal sProgramId)

    Dim oError

    Set oError = New Error



    On Error Resume Next

    Set MomCreateObject = CreateObject(sProgramId)

    oError.Save

    On Error Goto 0



    If oError.Number &lt;&gt; 0 Then ThrowScriptError "Unable to create automation object '" &amp; sProgramId &amp; "'", oError

End Function



Class Error

    Private m_lNumber

    Private m_sSource

    Private m_sDescription

    Private m_sHelpContext

    Private m_sHelpFile

    Public Sub Save()

        m_lNumber = Err.number

        m_sSource = Err.Source

        m_sDescription = Err.Description

        m_sHelpContext = Err.HelpContext

        m_sHelpFile = Err.helpfile

    End Sub

    Public Sub Raise()

        Err.Raise m_lNumber, m_sSource, m_sDescription, m_sHelpFile, m_sHelpContext

    End Sub

    Public Sub Clear()

        m_lNumber = 0

        m_sSource = ""

        m_sDescription = ""

        m_sHelpContext = ""

        m_sHelpFile = ""

    End Sub

    Public Default Property Get Number()

        Number = m_lNumber

    End Property

    Public Property Get Source()

        Source = m_sSource

    End Property

    Public Property Get Description()

        Description = m_sDescription

    End Property

    Public Property Get HelpContext()

        HelpContext = m_sHelpContext

    End Property

    Public Property Get HelpFile()

        HelpFile = m_sHelpFile

    End Property

End Class

                  </Script></ScriptBody>

          <TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>

        </ProbeAction>

      </MemberModules>

      <Composition>

        <Node ID="Script">

          <Node ID="Pass"/>

        </Node>

      </Composition>

    </Composite>

  </ModuleImplementation>

  <OutputType>System!System.PropertyBagData</OutputType>

  <TriggerOnly>true</TriggerOnly>

</ProbeActionModuleType>