Database Monitoring Module

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

This module provides data for monitoring availability of a SQL database.

Element properties:

Member Modules:

Overrideable Parameters:

Source Code:

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

  <Configuration>

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

    <xsd:element name="TargetDBName" 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>BizTalkDatabaseMonitor.vbs</ScriptName>

          <Arguments>"$Config/TargetDBServer$" "$Config/TargetDBName$" "$Config/LogSuccessEvent$"</Arguments>

          <ScriptBody><Script>

'Copyright (c) Microsoft Corporation. All rights reserved



'This script generates monitoring data for availability of a SQL database

'based on connectivity. There are two states - success and error.



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 Database Monitor"

' Event ID Constants

Const EVENTID_SUCCESS = 99

Const EVENTID_SCRIPT_ERROR = 1000



Const StateDataType = 3



Dim oAPI, oBagState

Dim oParams, TargetDBServer, TargetDBName, bLogSuccessEvent

Dim strMonitorStatus, strErrorDetail

Dim dtStart, strMessage



dtStart = Now



Set oParams = WScript.Arguments

if oParams.Count &lt; 3 then

    Wscript.Quit -1

End if



strMonitorStatus = "0"



TargetDBServer = oParams(0)

TargetDBName = oParams(1)

bLogSuccessEvent = CBool(oParams(2))



Set oAPI = CreateObject("Mom.ScriptAPI")

Set oBagState = oAPI.CreateTypedPropertyBag(StateDataType)



GetMonitorStatus



Sub GetMonitorStatus()

    Dim e

    Dim SQLDBConn

    Dim cnADOConnection

    Dim oResults, sDBState



    Set e = New Error

    SQLDBConn = "Server=" &amp; TargetDBServer &amp; ";Database=" &amp; TargetDBName &amp; ";Trusted_Connection=yes"

    oResults = NULL



    Set cnADOConnection = MomCreateObject("ADODB.Connection")

    cnADOConnection.Provider = "SQLNCLI11"

    cnADOConnection.ConnectionTimeout = 15



    e.Clear

    On Error Resume Next

    cnADOConnection.Open SQLDBConn

    e.Save

    On Error Goto 0

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

        strMonitorStatus = "0"

        strErrorDetail = SCRIPT_NAME &amp; ": - Database " &amp; TargetDBName &amp; " Failure on Server " &amp; TargetDBServer &amp; " Error Detail:" &amp; e.Description

    Else

        If cnADOConnection.State = 0 Then

            strMonitorStatus = "0"

            strErrorDetail = SCRIPT_NAME &amp; ": - Database " &amp; TargetDBName &amp; " Failure on Server " &amp; TargetDBServer &amp; " Error Detail: Can not establish connection"

        End If

    End if



    If cnADOConnection.State = 1 Then

        e.Clear

        On Error Resume Next

        Set oResults = cnADOConnection.Execute("SELECT state_desc FROM sys.databases where name = '" + TargetDBName + "'")

        e.Save

        On Error Goto 0



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

            strMonitorStatus = "0"

            strErrorDetail = SCRIPT_NAME &amp; ": - Database " &amp; TargetDBName &amp; " Failure on Server " &amp; TargetDBServer &amp; " Error Detail:" &amp; e.Description

        End If



        If IsNull(oResults) = false Then

            Do While Not oResults.EOF

                sDBState = oResults(0)

                oResults.MoveNext

            Loop

            Set oResults = nothing

        End If



        cnADOConnection.Close

    End If



    If strErrorDetail = "" Then

        If sDBState = "ONLINE" Then

            strMonitorStatus = "1"

            strErrorDetail = ""

        Else

            strMonitorStatus = "0"

            strErrorDetail = SCRIPT_NAME &amp; ": - Database " &amp; TargetDBName &amp; " Failure on Server " &amp; TargetDBServer &amp; " Error Detail: Database State is OffLine"

        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



    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>