BizTalk administrators often rely on tracking data, which shows you past events that happened in BizTalk. Querying this information through the BizTalk Administration Console can be quite slow if you have a large tracking database, and also has a learning curve before you can construct the right queries.
Fortunately, many of these queries just return data from BizTalkDTADb, the BizTalk tracking database, which isn’t too hard to understand for yourself. Here are some ways to write your own queries to get the tracking information you need.
Firstly, a couple of pointers on BizTalkDTADb. This is the tracking database, so querying it should never affect “real” BizTalk activity. There is a background process that collects data from the main BizTalk databases and adds it to the tracking database. If you’ve configured your hosts correctly, only one Host should have tracking enabled, and you shouldn’t run anything else on that host. This avoids your tracking processes sharing any resource with your main BizTalk activity.
Secondly, I’d recommend using WITH (NOLOCK) in any SQL queries on the tracking database. It’s better not to lock any tables to be on the safe side, in case you delay tracking data being written.
Lastly, remember that tracking data will only be stored depending on your tracking settings. Any events you don’t track, won’t be shown in the tracking database. Duh.
So here is how you can make use of the tracking tables. Once you the queries you need, you can save them, or find a way to incorporate them into your own UI.
Table structure
The heart of the useful information is the table dta_MessageInOutEvents. As its name suggests, this stores a log of when messages were actually sent between BizTalk processes, either internally to the Message Box or through send/receive ports.
If you look at the table, it won’t make loads of sense on its own. This is because it makes use of various foreign keys. These are the keys and the tables they join to, to save you hunting around.
- uidServiceInstanceId: Joins to the dta_ServiceInstances table. This is the most important join, as it allows you to find out what type of service this was, and its start/end time, etc.
- nAdapterId: Joins to dta_Adapter, identifying the adapter the message was sent through. This will only be populated if this was a send/receive port activity. If it was an internal BizTalk event instead (to/from the MessageBox), the ID will be 0.
- nPortId: Joins to dta_PortName. Gets you the name of the port the message went through. This can either be a send/receive port (as visible in the admin console), or an internal port within orchestrations.
- nStatus: Joins to dta_MessageStatus, though the key nMessageStatusId. As well as indicating success or failure, the status also crucially indicates the direction of the message if it was successful (“Send” or “Receive”).
- nSchemaId: Joins up to dta_SchemaName. This helps make sense of what type of message this was.
With those 5 main joins available, you should have a much more useful set of BizTalk activity information. You now know what type of message was delivered through which port and when.
So how can we make it even more useful by tying together different service instances?
The secret sauce: uidMessageInstanceId
The above is a nice start. Once you narrow by service instance ID, you can see all the events that happened as part of the same process, and make sense of them.
But you’re still only looking at one process in isolation. What if you could follow the activity on to the next port/orchestration/whatever? You would then be able to track a message as it passed between different orchestrations, ports, etc.
Turns out this is fairly easy to do, and it replicates the functionality of the “Message Flow” feature in the BizTalk Administration Console.
The key to this is uidMessageInstanceID, which gives an ID for each message. This allows you to find the same message as it is sent by one process, and then as it’s received by another, thereby providing the link between two separate service instances.
Try this if you want to work an example: use the joins above to locate a service instance (and its ID) that publishes a message. Choose a message that you already know has subscribers. Then grab the uidMessageInstanceId of that published message from dta_MessageInOutEvents. Now, re-query the same table, but this time just look for anything matching that uidMessageInstanceId. You should now see at least 2 events. One will be from the service you were just looking at, and hopefully at least one other event will share the same uidMessageInstance, but relating to a different uidServiceInstanceId. Congrats! You now know the instance ID of the next service that picked up your message.
So now you know how to tie your tracking tables together, you can narrow down to just the fields you need, sort your WHERE clauses to make them useful, and enjoy more performant queries that can feed any UI you like.
Enjoy!