Database Monitoring Module

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

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

Element properties:

TypeProbeActionModuleType
IsolationAny
AccessibilityPublic
RunAsMicrosoft.BizTalk.ProbeAccount
OutputTypeSystem.PropertyBagData

Member Modules:

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

Overrideable Parameters:

IDParameterTypeSelectorDisplay NameDescription
TimeoutSecondsint$Config/TimeoutSeconds$Timeout SecondsThis is the timeout (in seconds) after which execution of the script associated with the module is terminated if not yet completed.
LogSuccessEventstring$Config/LogSuccessEvent$Log Success EventAn event is logged based on successful completion of the script associated with the module when value is set to 'true'.

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 = "sqloledb"
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>