- 0 minutes to read

Monitor copy of BizTalk tracking to Nodinite

Microsoft BizTalk has 2 pumps that move data from the BizTalk Messagebox to the tracking (DTA) database:

  1. Events (BizTalk TrackingHost)
  2. Body/Context (SQL Job - TrackedMessages_Copy_BizTalkMsgBoxDb)

These 2 work independent of one another and Events may be copied before or after the actual payload and metadata. Nodinite therefore copies Events and Body/Context as soon as it is available from within the BizTalk Tracking (BizTalkDTADb) database.

If you want external alerts whenever there is a problem copying data from BizTalk to Nodinite. You can add the 2 examples with SQL ADHOC statements to the Database Monitoring Agent from this user guide.

Setup

Use the 'SQL Statements' user guide to find out how to add the 2 monitoring examples.
Remote Config

Statement - Tracked events

This example monitors the events.

-------------------------------------------------- 
-- Monitor copy of events from BizTalk to Nodinite
--------------------------------------------------
USE [NodiniteConfig_Dev] -- << Nodinite Config database name
DECLARE  
------------------------
-- settings
------------------------
 @logAgentValueId INT = 1  -- << BizTalk log agent id
,@raiseError       INT = 12 -- << number of hours before error
,@raiseWarning     INT = 2  -- << number of hours before warning
-------------------------
-- private
-------------------------
,@lastSeqNoForTrackedEventsDTA BIGINT = 0 -- From BizTalk			
,@lastInsertedDateTimeDTA DATETIME  -- From BizTalk
,@tsql NVARCHAR(MAX)
,@paramDef NVARCHAR(500)
,@bizTalkDTAServerName SYSNAME
,@bizTalkDTADatabaseName SYSNAME
,@optimizedDTAServerDatabaseName VARCHAR(512)
,@lastDateTimeForTrackedEvents DATETIME2(3) = null
,@lastSeqNoForTrackedEvents BIGINT = null -- From IM

SELECT @bizTalkDTAServerName= Log_ufnBizTalkInformation.BizTalkDTAServerName, @bizTalkDTADatabaseName = Log_ufnBizTalkInformation.BizTalkDTADatabaseName FROM Log_ufnBizTalkInformation(@logAgentValueId)
SET @optimizedDTAServerDatabaseName = '[' + @bizTalkDTAServerName  +'].[' + @bizTalkDTADatabaseName + ']'
SET @optimizedDTAServerDatabaseName = REPLACE(@optimizedDTAServerDatabaseName, '[localhost].','')

SET @paramDef = '@lastSeqNoForTrackedEventsDTAOut BIGINT OUTPUT, @lastInsertedDateTimeDTAOut DATETIME OUTPUT'
SET @tsql = 'SELECT @lastSeqNoForTrackedEventsDTAOut = [nEventId], @lastInsertedDateTimeDTAOut = [dtInsertionTimeStamp] FROM ' + @optimizedDTAServerDatabaseName + '.[dbo].[dta_MessageInOutEvents] WITH (NOLOCK) WHERE [nEventId] = (SELECT MAX(nEventId) FROM ' + @optimizedDTAServerDatabaseName + '.[dbo].[dta_MessageInOutEvents] WITH (NOLOCK))'
EXEC sp_executesql @tsql, @paramDef, @lastSeqNoForTrackedEventsDTAOut = @lastSeqNoForTrackedEventsDTA OUTPUT, @lastInsertedDateTimeDTAOut = @lastInsertedDateTimeDTA OUTPUT

execute [Log_Events_GetLastFromBizTalk] @lastDateTimeForTrackedEvents OUTPUT, @lastSeqNoForTrackedEvents OUTPUT, @logAgentValueId
PRINT 'BizTalk  : ' + ISNULL(CONVERT(VARCHAR(30), @lastInsertedDateTimeDTA, 121), 'N/A')
PRINT 'Nodinite : ' + ISNULL(CONVERT(VARCHAR(30), @lastDateTimeForTrackedEvents, 121), 'N/A')
	
DECLARE @dateDiff INT 
SET @dateDiff = DATEDIFF(HOUR, @lastDateTimeForTrackedEvents, @lastInsertedDateTimeDTA)

IF(@dateDiff >= @raiseError)
BEGIN
	RAISERROR('Problem with the copy of tracked events from BizTalk to Nodinite',18,1)
END
ELSE IF(@dateDiff >= @raiseWarning)
BEGIN
    RAISERROR('Problem with the copy of tracked events from BizTalk to Nodinite',18,2)
END

Statement - Tracked data

This example monitors payload and context.

-------------------------------------------------- 
-- Monitor copy of data from BizTalk to Nodinite
--------------------------------------------------
USE [Config_Dev] -- << Nodinite Config database name
DECLARE  
------------------------
-- settings
------------------------
 @logAgentValueId INT = 1  -- << BizTalk log agent id
,@raiseError       INT = 12 -- << number of hours before error
,@raiseWarning     INT = 2  -- << number of hours before warning
-------------------------
-- private
-------------------------
,@bizTalkDTAServerName SYSNAME
,@bizTalkDTADatabaseName SYSNAME
,@lastDateTimeForTrackedData DATETIME2(3) = null
,@bizTalklastDateTimeForTrackedData DATETIME2(3) = null
,@bizTalklastDateTimeForTrackedDataOut DATETIME2(3)
,@optimizedDTAServerDatabaseName VARCHAR(512)
,@tsql NVARCHAR(MAX)
,@paramDef NVARCHAR(500)

SELECT @bizTalkDTAServerName= Log_ufnBizTalkInformation.BizTalkDTAServerName, @bizTalkDTADatabaseName = Log_ufnBizTalkInformation.BizTalkDTADatabaseName FROM Log_ufnBizTalkInformation(@logAgentValueId)
SET @optimizedDTAServerDatabaseName = '[' + @bizTalkDTAServerName  +'].[' + @bizTalkDTADatabaseName + ']'
SET @optimizedDTAServerDatabaseName = REPLACE(@optimizedDTAServerDatabaseName, '[localhost].','')

execute [Log_MessageData_GetLastFromBizTalk] @lastDateTimeForTrackedData OUTPUT, @logAgentValueId

SET @paramDef = '@bizTalklastDateTimeForTrackedDataOut DATETIME2(3) OUTPUT'
SET @tsql = 'SELECT @bizTalklastDateTimeForTrackedDataOut = MAX(TS1.[dtInsertionTimeStamp]) FROM ' + @optimizedDTAServerDatabaseName + '.[dbo].[Tracking_Spool1] TS1 WITH (NOLOCK)'
		
EXEC sp_executesql @tsql, @paramDef, @bizTalklastDateTimeForTrackedDataOut = @bizTalklastDateTimeForTrackedData OUTPUT
	
PRINT 'BizTalk  : ' + ISNULL(CONVERT(VARCHAR(30), @bizTalklastDateTimeForTrackedData, 121), 'N/A')
PRINT 'Nodinite : ' + ISNULL(CONVERT(VARCHAR(30), @lastDateTimeForTrackedData, 121), 'N/A')

DECLARE @dateDiff INT 
SET @dateDiff = DATEDIFF(HOUR, @lastDateTimeForTrackedData, @bizTalklastDateTimeForTrackedData)

IF(@dateDiff >= @raiseError)
BEGIN
	RAISERROR('Problem with the copy of tracked body/context from BizTalk to Nodinite',18,1)
END
ELSE IF(@dateDiff >= @raiseWarning)
BEGIN
	RAISERROR('Problem with the copy of tracked body/context from BizTalk to Nodinite',18,2) 
END