Share via

BizTalk: Monitoring tracking configuration

  • Article

 

Introduction

This stored procedure will detect if tracking configuration is enabled in any BizTalk Artifacts like:

  • Orchestrations
  • Pipelines
  • Schemas
  • Send ports
  • Receive ports
  • Bussines rules

if tracking is enabled, it will raise a SQL Server Error. The idea of this sp, is to create a SQL Server agent job that calls this sp to create an event log alert with the error description.  Is very common that developers overwrite the tracking configuration in production for one of the following reasons:

  1. They are deploying a new version of the application (and the binding file is overwritten with the development settings)
  2. They are troubleshooting an issue, they enable tracking and they forgot to disable it.

Depending on the size of tracking this might impact perfomance negatively. In these cases, having a procedure to detect if tracking is enabled in some artifacts will help BizTalk administrators to practively change tracking settings.

Creating the SQL Server Agent Job

 

After you create the stored procedure, you just need to créate a SQL Server Agent Job with an T-SQL Step:

 

image

Enable the option “Write to the Windows Event log” under the Notifications section:

image

Save the Job and Run it. If your environment has any kind of tracking enabled, the job will fail like this:

image

Creating also the associated event:

image

Now, go to the job history and you will get the BizTalk artifacts that have tracking enabled:

image

Download the Code

You can download the code here, in TechNet code gallery

https://gallery.technet.microsoft.com/BizTalk-SQL-Server-stored-c8316895

This Code only includes the stored procedure, not the SQL Server Agent Job.

 

Code

USE [BizTalkMgmtDb]

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Agustín Mántaras

-- Create date: 20/04/2015

-- Description: Stored procedure for monitoring BizTalk Server tracking

-- =============================================

ALTER PROCEDURE [dbo].[MonitoringTracking]

 

AS

BEGIN

 

 

DECLARE @NumberOfItemsWithTracking as int

DECLARE @errorString as nvarchar(255)

DECLARE @item as nvarchar(255)

 

SET @errorString = 'There are items with message body tracking enabled'

 

IF OBJECT_ID('tempdb..#TrackingItems') IS NOT NULL

  DROP TABLE #TrackingItems

 

 

Create Table #TrackingItems (

Item Nvarchar(255) )

 

 

INSERT INTO #TrackingItems

SELECT [nvcName]

   

  FROM [BizTalkMgmtDb].[dbo].[bts_receiveport]

  Where nTracking > 0

 

 

INSERT INTO #TrackingItems

SELECT [nvcName]

   

  FROM [BizTalkMgmtDb].[dbo].[bts_sendport]

  Where nTracking > 0

 

 

INSERT INTO #TrackingItems

SELECT strServiceName

      FROM [BizTalkMgmtDb].[dbo].[StaticTrackingInfo]

  where [ismsgBodyTrackingEnabled] <> 0 AND dtUndeploymentTime is null

 

 

 

 

SELECT @NumberOfItemsWithTracking = count (*)

      FROM #TrackingItems

 

 

 

  if (@NumberOfItemsWithTracking > 0)

  Begin

 

      declare Tracking_Cursor insensitive cursor for

      SELECT Item

      FROM #TrackingItems

 

 

 

      open Tracking_Cursor

      fetch next from Tracking_Cursor into @item

      while @@fetch_status = 0

      begin

            SET @errorString = @errorString + ' ## ' + @item

            fetch next from Tracking_Cursor into @item

      end

      RAISERROR(@errorString, 11, 1) WITH LOG

      DEALLOCATE Tracking_Cursor

  end

 

drop table #TrackingItems

 

 

 

END

Comments

  • Anonymous
    June 16, 2015
    Nice