- 4 minutes to read
Monitor copy of BizTalk tracking to Nodinite
Easily monitor and get proactive alerts for the internal copy of tracked data from BizTalk to Nodinite using custom SQL statements. This guide helps you ensure data integrity and rapid troubleshooting for your BizTalk-to-Nodinite integration.
✅ Proactively monitor BizTalk-to-Nodinite data transfer health
✅ Instantly receive alerts for tracking or data copy issues
✅ Use custom SQL statements for flexible, targeted monitoring
✅ Empower rapid troubleshooting and ensure data integrity
Microsoft BizTalk uses two independent pumps to move data from the BizTalk Messagebox to the tracking (DTA) database:
- Events (BizTalk TrackingHost)
- Body/Context (SQL Job - TrackedMessages_Copy_BizTalkMsgBoxDb)
These two processes work independently, so events may be copied before or after the actual payload and metadata. Nodinite copies Events and Body/Context as soon as they are available from 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 two monitoring examples below as SQL ADHOC statements to the Database Monitoring Agent.
Setup
Use the SQL Statements user guide to learn how to add the two monitoring examples.
Example of configuring SQL Statements for BizTalk-to-Nodinite monitoring.
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