SQL query: hourly processed messages for BizTalk ports

One of my clients wanted to know how many messages were processed on a certain BizTalk port.
To show some statistics, I created a SQL query that lists a message count of the messages processed per hour for a certain Port.

Here's the SQL query I used:

use BizTalkDTAdb

SELECT top 200
      a.[Event/Direction],
      a.[Event/Port],
      CONVERT(VARCHAR(10), a.[Event/Timestamp], 111)   as [date],
      DATEPART(HOUR,a.[Event/Timestamp]) as [Hour],
      COUNT(*) as [Count]  
FROM [dbo].[dtav_MessageFacts] a
inner join dbo.btsv_Tracking_Parts b on a.[MessageInstance/InstanceID] = b.uidMessageID
WHERE a.[Event/Port] <> 'PortSqlMonitor'
AND a.[Event/Port] = 'BizTalkPort'
AND a.[Event/Direction] = 'Receive'
AND a.[Event/Timestamp] > DATEADD(day, -3,GETDATE())
GROUP by  a.[Event/Port], CONVERT(VARCHAR(10), a.[Event/Timestamp], 111), DATEPART(HOUR,a.[Event/Timestamp]), a.[Event/Direction]
ORDER BY CONVERT(VARCHAR(10), a.[Event/Timestamp], 111), DATEPART(HOUR,a.[Event/Timestamp])

Using Fiddler for viewing exchanged messages with BizTalk

I was trying to view the SOAP messages and headers that were actually exchanged with a certain webservice.
To do so, I used Fiddler2... available from http://fiddler2.com/.

Fiddler2 will allow you to monitor traffic when using HTTP, SOAP or WCF ports in BizTalk. However, Fiddler will not trace any messages sent to endpoints by BizTalk by default as it does not use WinInet. To overcome this issue, you'll need to configure a proxy to allow Fiddler to intercept the messages.
Note that these proxy settings will need to be removed when Fiddler is not running. Because otherwise all traffic through this proxy will not be received by anything.

In the send port of BizTalk go to the Configuration settings. Open the tab 'Proxy' to configure the necessary settings. Now you should change following settings:

• Server: 127.0.0.1
• Port: 8888

That's is all you need to do. Now open fiddler2 and process a message. If all is configured correctly, you should see the exchanged messages coming through fiddler.

BTUG.be is born

I would like to make a small announcement... today we publicly announced the birth of a brand new Belgian BizTalk User Group, called BTUG.be! We were planning something like this for quite some time now, and you even might have picked up some rumours about it in the past. But as of today it is official, Belgium now has its very own BizTalk User Group.



I'm proud to say that I'm one of the co-founders of this BTUG.be. Together with Sam Vanhoutte we have started this initiative under excellent guidance of Lex Hegt, who shared his knowledge he gained with the Dutch BizTalk User Group.
And to start out, we wanted to make it a joined collaboration of the Belgian BizTalk Community. In this spirit we are glad that also i8c decided to support the initiative and Sven Van den brande agreed to fulfill our board of the BTUG.be. So that makes the all new Belgian BizTalk User Group will be worked out as a joined effort by Cnext, Codit and i8c with in the board at startup myself, Sam Vanhoutte and Sven Van den brande.
I'm already looking forward on having a great time working together with these guys organising all upcoming events.

A very first launch event is being planned to have place in november. For keeping up to date on all matters of this BTUG.be, I'll recommend you subscribe to the mailing list on the website http://www.btug.be and even keep a close eye to the website for all announcements. And certainly don't forget to follow our twitter account @BTUGbe.

And last of all, I would like to call out for sponsors... As the entire organization will be driven on volunteer work. So to cover all the necessary costs we will need to make for hosting events, catering, travel expenses in case of international speakers, etc. we are on the lookout for sponsors to keep it fun and free for everyone. You can always contact us on any questions or to share your interest for sponsoring using this entry form on the website.

BizTalk blog posts 02/2013 - 05/2013

The past time I haven't posted a lot on this blog, especially not much blog posts on BizTalk subjects. This doesn't mean that I haven't done any BizTalk related blog posts, I just haven't done it on my personal blog for a while... I've mostly posted on my companies blog, where I am the sole contributor and administrator.

But for all of you loyal readers of my personal blog, I thought it would be nice to give an overview of all of my latest posts. I'm thinking of doing this every once in a while to keep all of you updated on ALL of my posts.

So here goes for the period till May 2013...

• BizTalk error: ‘The message found multiple request response subscriptions’: solution to sending messages to multiple request-response send ports
• BizTalk file mask difference for windows 7 and windows server 2008 r2: A difference in file mask usage between windows 7 and windows server 2008 r2
• BizTalk360 v6.0: new features explained: a quick view on some of the improvements introduced in the new BizTalk360 version 6.0
• BizTalk Server 2013 is now RTM!: the announcement of the new BizTalk version release on MSDN, with the new features listed
• BTS2013 features explained: XslCompiledTransform: I started a new series on viewing some of the new features of BizTalk 2013 in more detail, the first one was about the XslCompiledTransform
• BTS2013 features explained: Improvements in dynamic send ports: follow up post on a new BizTalk 2013 feature on the Improvements in dynamic send ports
• BTS2013 features explained: Dependency tracking: an in depth view of the new dependency tracking feature of BizTalk 2013
• First Cumulative Update for BizTalk 2013… available through Microsoft Update!: the announcement that the very first cumulative update was released for BizTalk 2013, and that it even was available through Microsoft Update
• BAM error: Failed to list permissions for BAM view – System.Data.SqlTypes.SqlNullValueException: Data is Null: a solution to a BAM issue I encountered

Hope you enjoy these blog posts!

Became an official BizTalk360 Product Specialist

Yesterday, BizTalk360 introduced the whole new title "BizTalk360 Product Specialist" on their blog. Because of the constantly increasing popularity of the monitoring tool BizTalk360, to streamline the process the new title and badge where introduced. The idea is to honour individuals who have gained adequate knowledge in installing, configuring and implementing BizTalk360 solution at customer sites.

The only way of obtaining this title is by receiving the confirmation from the BizTalk360 team itself. I myself filed a request immediately... And I am proud to announce my request got approved. Now I have the honour to officially call myself a "BizTalk360 Product Specialist"!

As you might have seen, I already placed the official badge at my own blog on the column on the right. I also updated my LinkedIn profile accordingly.

Guide to a BizTalk High Availability Server Environment

I just discovered a series of blog post that I just had to share with all of you... It's a complete guide to set up a High available BizTalk environment.
A perfect walthrough from start to finish to obtain a BizTalk High availability environment.

The guide covers 7 blog posts in total:

• Part 1: Preparations
• Part 2: Domain Controller Installation
• Part 3: SQL & BizTalk Active Directory Accounts
• Part 4: Prepping our SQL & BizTalk Failover Clusters
• Part 5: SQL Server 2008r2 Failover Cluster
• Part 6: BizTalk 2010 Failover Cluster Creation
• Part 7: BizTalk 2010 Installation, Configuration and Clustering

Congrats to René Brauwers on the great guide for setting up a High available BizTalk environment.

CU1 for BizTalk 2013... using Microsoft Update!

The Microsoft Team has just announced the release of the very first Cumulative Update For BizTalk 2013.

But an even more surprising news is that it is available using Microsoft Update!... This is the very first time a BizTalk Update can be obtained with Microsoft Update, as before it had to be done by downloading it manually through the hotfix download. An amazing improvement if you ask me, making it a whole lot easier for administrators.

The update will still be available through the hotfix download though.

The Content of the Cumulative Update

The very first CU for BizTalk 2013 brings 3 fixes:

• User cannot perform certain database-related operations in BizTalk Server 2013
• BAM tools cannot be configured in a multi-node BizTalk Server 2013 environment
• The vertical scroll bar on the target schema does not work correctly when you use Visual Studio to design a BizTalk Server 2013 map

How to install using Microsoft Update

The following steps are applicable if you have Microsoft Update enabled in the machine where BizTalk Server is installed.

1. Log into a server where BizTalk Server is already installed, and Microsoft Update is enabled
2. Check for windows update
3. Notice available fixes for BizTalk Server under optional updates
4. Select the update and install

(Perform the above operations in each of the BizTalk nodes)

The original announcement can be found here.