Author: Richard Seroter

Have you ever wanted a clean query of traffic through BizTalk on a per application basis? And, how about exposing that information to your internal users in a very Web 2.0 fashion?

Our chief architect asked me if it was feasible to syndicate BizTalk metrics using a product like RSSBus. Given that BizTalk’s messaging metrics are stored in a database, I figured it would be fairly straightforward. However, my goal was to not only present overall BizTalk traffic information, but ALSO do it on a per application basis so that project teams could keep track of their BizTalk components.

So, the first step was to write the SQL queries that would extract the data from the BizTalk databases. I wanted two queries: one for all messaging metrics per application, and, one for all suspended messages per application. I figured that it’d be useful for folks to be able to see, real-time, how many messages had failed for their application.

My “traffic” query returns a result set like this:

Based on the interval you provide (past day, 2 weeks, 6 months, etc), all services for the given application are shown. Getting the “application” information required joining with the BizTalkMgmtDb database. Also, I had to take into account that BizTalk database timestamps are based on UTC. So, I subtracted 7 hours from the service completion time to get accurate counts.

Next is the “suspended messages by application” query. For this one, I got inspiration from Scott Woodgate’s old Advanced MessageBox Queries paper. Once again, I had to join on the BizTalkMgmtDb database in order to filter by application. The result of this query looks like this:

For each service, you see the type and the count by status.

The next step was taking this from “randomly executed SQL query” to syndicated feed. RSSBus is a pretty cool product that we’ve been looking for an excuse to use for quite some time. RSSBus comes with all sorts of connectors that can be used to generate RSS feeds. Naturally, you can write your own as well. This shot below is of a Yahoo! connector that’s included …

I took my SQL scripts and turned those into stored procedures (in a new database, avoiding any changes to the BizTalk databases). I then used the “SQL Connector” provided by RSSBus to call the stored procedure. Since we don’t have an enterprise RSS reader yet at my company, I used the RSSBus option of exposing a “template” instead. I added some HTML so that a browser-user could get a formatted look at the traffic statistics …

To prevent unnecessary load on the MessageBox and other BizTalk databases, I set the cache interval, so that the query will be executed no more than once per hour.

Pretty cool, eh? “Traffic by Application” was something I wanted to see for a while, so hopefully that helps somebody out.

Technorati Tags: BizTalk

I’m happy to see Rob starting a “BizTalk Performance” blog, and have high hopes that this doesn’t fall into the BizTalk team dust bin of dead blogs.

Subscribe to both of Rob’s blogs (personal BizTalk thoughts here and reference-oriented content here). You’ll find that he’s already put some good content down around planning performance labs.

Technorati Tags: BizTalk

I’ll be honest. I can’t say that I’ve ever fully understood all the nuances of the BizTalk BAM infrastructure layer. Sure, I have the basics down, but I often found myself turned around when talking about some of the movement between the BAM databases (specifically, archiving).

Something in Darren’s Professional BizTalk Server 2006 book got me thinking, so I did a quick test to truly see how the BizTalk BAM process archived and partitioned data. The BAMPrimaryImport database has a table named bam_[ActivityName]_Activity_Completed which stores completed records. According to the documentation, once a given amount of time has passed, the records are moved from the bam_[ActivityName]_Activity_Completed table to a newly created partition named bam_[ActivityName]_Activity_[GUID}.

One of the views (named bam_[ActivityName]_Activity_AllInstances) in the BAMPrimaryImport database aggregates the bam_[ActivityName]_Activity_Completed and all the various partitions. This view is used by the BAM Portal. So if you count up the records in the bam_[ActivityName]_Activity_AllInstances view, it should:

• equal the number of rows in your “Activity Search” from the BAM Portal
• equal the number of rows in the bam_[ActivityName]_Activity_Completed table and all subsequent partitions

Now, you may ask, what creates these partitions, and how the heck do I get rid of them over time?

There is a table named BAMArchive created during the BAM Configuration. By default, this table is empty. The SSIS/DTS jobs that get created when deploying your BAM infrastructure do pretty much all of the archiving work for you. Until recently, my understanding of the BAM_DM_[ActivityName] SSIS job was that it “cleaned stuff up”. Let’s look closer. When the BAM_DM_[ActivityName] job runs, it creates new partitions, and also purges old ones. So when you run this job, you’ll often see new partitions show up the BAMPrimaryImport database. This job ALSO rebuilds the view, so that the new partition is included in queries to the bam_[ActivityName]_Activity_AllInstances view. Neato.

How does this BAM_DM_[ActivityName] archive stuff? It uses the Metadata_Activities table in the BAMPrimaryImport database to determine how long before data should be archived. As you can see below, the default for an activity is 6 months.

You could set this OnlineWindowTimeLength to 30 minutes, or 10 days or 18 months. Whatever you want. You can either change this directly in the database table, or more appropriately, use the bm.exe set-activitywindow -Activity: -TimeLength: -TimeUnit:Month|Day|Hour|Minute command. In my case, I set this to a short range in order to prove that data is archived. I then executed the BAM_DM_[ActivityName] job to see what happened.

As hoped for, the BAMPrimaryImport now had fewer partitions as the ones containing old data were removed. Where did the data go? If I check out my BAMArchive database, I now see new tables stamped with the time the data was archived.

If I go to the BAM Portal (or check out the bam_[ActivityName]_Activity_AllInstances view directly) my result set is now much smaller. The BAMArchive data does NOT show up in any BAM query, and is only accessible through direct access to the database via custom queries. BAMArchive is purely an archive, not a readily accessible query store.

There you go. A peek into BAM archiving and bit of detail in what that darn BAM_DM_[ActivityName] job does. It’s also important to ask consumers of BAM data what they expect the “active window” to be. Maybe the default of 6 months is fine, but, you better ask that up front or else face the wrath of users who can’t access the BAM data so easily anymore!

Technorati Tags: BizTalk

I recently purchased a copy of Darren Jefford’s new Professional BizTalk Server 2006 book and am quite pleased with the material.

I had the pleasure of checking this book out during its construction, and must admit, my first thought during that review was “wow, this is great … but it seems to be a bit of a brain dump.” It seemed like lots of great topics and points, but I didn’t grasp the continuity (probably because I was skipping through chapters, and, reading them out of order). Now that I’m holding the printed copy, I am REALLY impressed with the organization and content. I love the other BizTalk 2006 books out there, but this is now my favorite. I just bought an armful of copies for my team.

So what’s good about it? My favorite things were:

• Most thorough investigation of adapters and specific adapter settings and properties that I’ve seen
• Excellent content on BAM that provided me with a few “lightbulb” moments
• The most printed material in existence on the Business Rules Engine
• Outstanding perspectives and details on testing (unit/integration/perf , etc) and tools
• Strong details on performance tuning (complimentary to the Pro BizTalk 2006 material), and low-latency tuning
• Sufficient depth on BizTalk Administration, still the most criminally under-documented part of the BizTalk lifecycle

Great stuff. Darren (and Kevin and Ewan) should be very proud of this effort. This book is simply required reading for any BizTalk architect.

Technorati Tags: BizTalk

This past weekend our company did it’s first significant BizTalk application deployment into a production environment. I encountered a few issues along the way, and I thought I’d list the problem and resolution.

In order of annoyance to me (from least to greatest):

• Transport does not have read/write privileges for receive location … We had a network share set up to poll for files, but as soon as we’d turn the port on, we’d get the “privileges” error. We confirmed that the BizTalk service account had read/modify/delete rights on the folder/share. After reviewing some KB articles, I found the answer in Tom’s blog post. Basically, I also needed to assign “delete subfolders and files” rights as well.
• The identity of application pool ‘BizTalkAppPool’ is invalid … the application pool is disabled. After first installing the HTTPReceive virtual directory on one of our servers, I got this error. After a quick search, I was reminded that the application pool service account needs to be in the IIS_WPG group on the box.
• The outbound transport could not be resolved because a matching transport protocol prefix could not be derived from the URL . This one killed me for a couple hours. Since my orchestration dynamically assigns a file name for a SharePoint site, I also dynamically assign the SharePoint URL. The WSS URL sits in the btsntsvc.exe.config file and is read at run-time. However, the first orchestrations that executed triggered this error. It implied that my “wss://” prefix for the dynamic send port was wrong. After looking at every possible technical solution, I finally realized that there was a “space” before the ” wss://” address in the configuration file. So, the error made sense, but damn.
• Failed to decode the S/MIME message.The S/MIME message may not be valid. Our application starts by receiving an email containing formatted text. In the orchestration we parse the text we want and throw away the rest. However, messages were getting suspended at the adapter layer with this error. After reviewing the email message header, I noticed that it was missing any MIME declarations (MIME-Version, Content-Type). So, I deduced that the POP3 adapter’s default parsing behavior was failing because it couldn’t determine MIME encoding of the inbound mail. Ignoring that fact that MIME is a freakin’ standard encoding that any email sender should include, we need(ed) to figure out a way to parse these messages with the POP3 adapter. The solution I’ve put together involves turning off the Apply MIME Decoding flag in the POP3 receive location, then parsing the inbound email string and looking for the “from:” address (since turning off MIME decoding at the receive location means no POP3 promoted values). I think it’ll work.

Good times.

Technorati Tags: BizTalk