BizTalk Tracking Data to Azure Application Insights -Under the hood

This blog will explore how Microsoft BizTalk team’s latest release of Feature Pack-1’s (FP1) Azure Application Insights for BizTalk tracking feature works under the hood. This helps us to understand this feature better in order to use and manage it. Also to get to know how it works in our environments.

FYI BizTalk Server 2016 Feature Pack-1 is now termed as “Microsoft BizTalk Server 2016 Update 1”, which can be downloaded at BizTalk Server 2016 feature update.

If you’re looking for “how to use” this feature, I would strongly recommend BizTalk360 Srinivasa’s blogs:

For “how it works“, continue reading this blog.

When the messages are processed through BizTalk, each host instance will serialize the data (Message events and message body), store the tracked message events to one of the tracking data tables in BizTalkMsgBoxDb database and message bodies are stored across four tables in the BizTalkMsgBoxDb database: Spool, MessageParts, Parts, and Fragments.

FP1 only sends the tracked messaging events, service instance information and context properties of the associated service instance to Azure Application Insights.

BizTalk Tracking Tables

When BizTalk FP1 is installed, it updates and creates SQL objects (tables, stored procedures) in BizTalkMsgBoxDb and in BizTalkDTADb databases.

BizTalk without the FP1 will have 2*4 series of tracking tables in BizTalkMsgBoxDb and in BizTalkDTADb databases.

  • TrackingData_0_0
  • TrackingData_0_1
  • TrackingData_0_2
  • TrackingData_0_3
  • TrackingData_1_0
  • TrackingData_1_1
  • TrackingData_1_2
  • TrackingData_1_3

The 0*4 series tracking tables (TrackingData_0_*) are used for storing the tracked message events for BAM (Business Activity Monitoring)

The 1*4 series tracking tables (TrackingData_1_*) are used for storing the tracked message events for HAT/DTA (Document Tracking and Administration)

When you install BizTalk FP1, BizTalkMgmtDb.TDDS_Destinations table has been updated with a new record for “Analytics Target System” as one of the destinations along with HAT and BAM.

BizTalk-FP1-AI-DTADB-TDDS_Destinations

This table contains the metadata (name and connection-string) about the destinations for which TDDS will move the tracked message events. Now “Analytics Target System” is also added.

The installation also creates 2 additional series of tracking tables (TrackingData_2_* and TrackingData_3_*) in BizTalkMsgBoxDb and in BizTalkDTADb databases.

BizTalk FP1-AI-MessageBox-Tables   BizTalk FP1-AI-DTADB-Tables

Not all of these tables are used. Only TrackingData_2_* in BizTalkMsgBoxDb are used, but not TrackingData_3_*. In BizTalkDTADb database TrackingData_3_* are used, but not TrackingData_2_*.

BizTalkMsbBoxDb_DTA-Tables_2

With specific to Analytics data in BizTalkDTADb:

BizTAlkDTADb-FP1-Analytics data to target system

Tracking Data Decode Service (TDDS or Tracking Host) moves data from BizTalkMsgBoxDb to the BizTalkDTADb for HAT and for BAM, it moves data from BizTalkMsgBoxDb to BAMPrimaryImport database. As part of FP1, TDDS is updated also to move tracked message events from BizTalkMsgBoxDb to BizTalkDTADb and to the cloud –Azure Application Insights.

So the same principle of TDDS also applies to track data for Azure Application Insights through BizTalk’s FP 1 i.e. make sure there is at least one host configured with “Allow host tracking” enabled. This will run TDDS service responsible for moving the data from BizTalkMsgBoxDb to Azure Application Insights.

BizTalk Tracking Data to Azure Application Insights

If I were to list the flow of control in high-level for Azure Application Insights for tracking feature in BizTalk, it would be something like:

  1. When a message is received in BizTalk. Host instance process the message.
  2. The message is stored in BizTalkMsgBoxDb db.
  3. If “Enable group-level analytics” is enabled at “BizTalk Group –> Setting”, tracked message events for analytics are stored in BizTalkMsgBoxDb’s TrackingData_2_* tables.
  4. And if at least one host configured with “Allow host tracking” enabled and started i.e. TDDS is created and running, TDDS moves the tracked event for analytics from BizTalkMsgBoxDb (TrackingData_2_* tables) to BizTalkDTADb (TrackingData_3_* tables)
  5. TDDS also moves the tracked message event for Analytics from BizTalkDTADb TrackingData_3_* tables to the target system which is the configured Azure Application Insights instance.

FYI: Only the tracked messaging events, service instance information and context properties of the associated service instance are sent to Azure Application Insights.

Useful Administrator SQL Queries related to Analytics tracking

For administrative purpose, if you need a SQL query to get the details of the Receive ports, Send ports and Orchestrations where Analytics tracking has been enabled, get the following SQL scripts from the TechNet-Gallery.

These SQL queries uses tables like

  • [BizTalkMgmtDb].dbo.[analytics_PortTrackingInfo]
  • [BizTalkMgmtDb].dbo.[adm_GroupSetting]
  • [BizTalkMgmtDb].dbo.[bts_receiveport]
  • [BizTalkMgmtDb].dbo.[bts_sendport]
  • [BizTalkMgmtDb].dbo.[bts_orchestration]
  • [BizTalkMgmtDb].dbo.[StaticTrackingInfo]

Key Assemblies used for Analytics tracking

Now we will look into some of the assemblies which facilitate BizTalk to track the events to Azure Application Insights.

When the BizTalk FP1 is installed, specific to this feature following important assemblies are copied to tracking folder under Microsoft BizTalk Server 2016 installation folder. For me it’s on C:\Program Files (x86)\Microsoft BizTalk Server 2016\Tracking:

  • BizTalk.Analytics.Common.dll
  • BizTalk.Analytics.EventTracker.dll
  • BizTalk.Analytics.Interceptors.dll
  • BizTalk.Analytics.TDDS.dll

Microsoft.BizTalk.Analytics.Common.dll – This assembly contains generic members to facilitate analytics like BizTalkMgmtDb database accessor, constants etc.

Microsoft.BizTalk.Analytics.Interceptors.dll – This is an interceptor component where the properties of the custom events that have been tracked for ports and Orchestration are defined.

Microsoft.BizTalk.Analytics.TDDS.dll – Helper used by TDDS for moving the analytics data.

Microsoft.BizTalk.Analytics.EventTracker.dll – This assembly has some important functions like TrackEvent, TestConnection and Flush. As a generic helper component, you can use these methods, even in your own custom applications to track events to Application Insights without using FP1. Following is a sample code to track my custom events:


public static void AnalyticsEventLogger()
 {
 //Provide your Application Insights Instrumentation key
 Microsoft.BizTalk.Analytics.EventTracker.AppInsight objAppInsight = new Microsoft.BizTalk.Analytics.EventTracker.AppInsight("ba99de99-9999-9999-9b70-15a065611a07");
 
 Microsoft.BizTalk.Analytics.Common.AnalyticsEventRecord analyticsEventRecord = new Microsoft.BizTalk.Analytics.Common.AnalyticsEventRecord();
 analyticsEventRecord.EventName = string.Format("{0}_{1}", "AshwinPrabhu", "TestIn");
 analyticsEventRecord.EventTime = DateTime.UtcNow;

 //AddOrUpdateProperty property is a collection, you add all the properties of your custom event to track
 analyticsEventRecord.AddOrUpdateProperty("Direction", "Inbound");
 analyticsEventRecord.AddOrUpdateProperty("TestEvent", "This is a test event");

 if (!objAppInsight.TestConnection())
 {
 }
 objAppInsight.TrackEvent(analyticsEventRecord);
 objAppInsight.Flush();
 }

 

Frequently Asked Questions (FAQs)

Now let’s see some frequently asked questions (FAQs) on Azure Application Insights for tracking feature in BizTalk FP1:

  1. How often tracked data are moved/transferred to Azure Application Insights?

Once the message has been processed by BizTalk host instance, the “maximum time” for TDDS to move the tracked events between the tracking tables from BizTalkMsgBoxDb (TrackingData_2_* tables) to BizTalkDTADb (TrackingData_3_* tables) and to the cloud should slightly more than TDDS’s refresh internal setting – 140 seconds.

(140 seconds for TDDS refresh interval, if your tracked event was received exactly at the same time when TDDS’s next run just started and few more seconds for data to be moved. 140 is the default refresh interval for TDDS. Reference: BizTalkMgmtDb.TDDS_Settings table)

The time interval between the on-premise and cloud as per Microsoft engineer (no official info though) is “seconds to minutes”. Refer to the following replies from Microsoft engineers on the same:

https://stackoverflow.com/questions/34797662/application-insights-uwp-app-what-is-the-expected-delay-for-telemetry-data-to

https://stackoverflow.com/questions/35057976/application-insights-delay

In my experience, the maximum time it took for me to appear in Application Insights is 3 minutes, including the time the message received by BizTalk and for the custom events to appear in Azure Application Insights.

  1. How long tracked data is stored in the cloud for Azure Application Insights?

For both Basic and Enterprise tier of your Azure Application Insights instance, a data retention period is 90 days.

For a longer retention period, you can use the Continuous Export option to store your telemetry in an Azure storage account in JSON format. From there you can download your data for analytics. Note: Using Continuous Export may occur extra cost based on your Azure Application Insights instance tier. For enterprise tier, Continuous Export feature in part of the tier without any additional cost.

Reference:

Application Insights Pricing Details: https://azure.microsoft.com/en-us/pricing/details/application-insights/

Continuous Export: https://docs.microsoft.com/en-us/azure/application-insights/app-insights-export-telemetry

  1. Does the tracked data stored interim on-premise before it’s been moved to the cloud for Azure Application Insights?

Yes, as mentioned in this article tracked message events are stored in the BizTalkMsgBoxDb (TrackingData_2_* tables) then moved to BizTalkDTADb (TrackingData_3_* tables) before transferred to the cloud for Azure Application Insights.

  1. Where and in what format the tracked data is stored in the cloud?

The underlying format and the storage in which the telemetry/custom events for Azure Application Insights stored is not known and never publicly documented.

If you want to export the telemetry/custom events for longer periods using Continuous Export, then it is exported in JSON format. But that doesn’t mean for Azure Application Insights it’s stored in JSON.

FYI: In on-premise, your tracked data in BizTalkMsgBoxDb and in BizTalkDTADb stored as BLOB in TrackingData_*_* tables.

  1. Is there a way to delete the tracked data in the cloud, as we purge our tracked data in BizTalkDTADb DB?

Application Insights does not allow us to delete the data. By default, data will be retained as long as your data retention period which is 90 days for raw and aggregated data.

  1. Is there a way to control the amount of data being sent to Application Insights and what is the data allowance in Application Insights?

Amount of data being sent can be controlled in a number of ways:

In BizTalk, limit the number of artifacts where tracking has been enabled. Or limit the level of tracking, for example, track either “before port processing” or “after port processing”.

In Application Insights, per application i.e. per instrumentation key monthly data allowance is 1 GB. Or if you have chosen Enterprise tier, data allowance is 200 MB per node each day. So if you have 4 nodes, then your data allowance is 1 GB (4*200 MB) per day shared across nodes.

  1. Can we archive/backup the older tracked data once it’s moved to the cloud for our later reference?

As mentioned, Continuous Export is an option to store your telemetry in an Azure storage account in JSON format for longer than the default retention period.

  1. Can we use other analytics tool/service other than Azure Application Insights for the tracked data in the cloud?

Application Insights data can be exported to Power BI. Power BI is an analytics tool from Microsoft. Using Power BI desktop, you can import your Application Insights, queries and then publish it to the web or to your Power BI cloud workspace

Reference: Feed Power BI from Application Insights: https://docs.microsoft.com/en-us/azure/application-insights/app-insights-export-power-bi

You can export the charts, query and its results as Excel file (*.xlsx). Query results as CSV files.

Azure ApplicationInsights-MetricsExplorer-Export

Azure ApplicationInsights-Analytics-Export

 

Posted in: Application Insights, Azure, BizTalk, Tracking 2 Comments July 3, 2017

About M.R.ASHWINPRABHU

M.R.ASHWINPRABHU is the founder and CEO of Fortuvis Systems Limited, a consulting company specialised in Microsoft technologies. Ashwin is a highly experienced integration consultant who works with clients to deliver high quality solutions. He works as technical lead developer, application architect and consultant, specializing in custom applications, enterprise application integration (BizTalk), Web services and Windows Azure.

Comments (2)

Leave a Reply

Your email address will not be published. Required fields are marked *