Table of Contents


Introduction

Most BizTalk consultants are familiar with the MessageBoxViewer (or MBV).  It is best practice to use this tool to generate reports on the health of your BizTalk Group. It is regularly updated by Jean-Pierre Auconie of Microsoft France.

The most recent version of MessageBoxViewer can be downloaded at this site (the latest version 13.880).

Besides HTML reports, MBV can also generate reports in XML-format, which then can be used for integrating with monitoring product BizTalk360.

This article describes how MBV stores its output in a SQL Server database and how it could be used for comparison between multiple BizTalk environments.

Store MBV output in SQL Server

When you open MBV, the tab page Settings on the main screen shows all configurable settings. The 3 settings in the red box shown below are related to storing output into SQL Server tables. By default storing output to SQL Server is turned off, so you'll have to explicitly turn it on!

The output is stored into four tables named:

  • Dashboard
  • Header
  • Summary
  • Warnings

Each table corresponds with the MBV report of the same name. For an example see the screenshots below for the partial HTML and SQL Server output of the same MBV run.

If you want to compare the current state of multiple BizTalk environments you'll have to configure MBV on these environments to store its output to the same SQL Server Instance. Normally MBV is installed and run on at least one BizTalk instance of a BizTalk Group (Environment) from which you want to have the health reports. In our scenario this will be beneficial, as we need to distinguish the output from the different environments (BizTalk Groups). Therefore you'll need to use different report titles for each environment (BizTalk Group). This can be configured on the Settings tab page of MBV.

The Visio diagram below depicts how MBV runs on different BizTalk environments but stores its output in one SQL Server repository. It also shows the different report titles.

Views per environment

Now that we know how to setup MBV on the distinct environments, we can concentrate on reporting the output per environment. Basically what we want is an overview which shows all categories and values per environment, just like the example shown here.

This is the partial output from the Summary table and shows, for each environment, the output from the most recent run of MBV. To create a view like this we first need to create views per environment, afterwhich we'll create another view which shows the information like above.

You could use the following names for the distinct views per environment:
  • MostRecentSummaryTest
  • MostRecentSummaryAcceptance
  • MostRecentSummaryProduction
The name of these views reflect that:
  • the most recent MBV data is returned
  • the data from the Summary table is returned
  • the data from MBV runs of the distinct environments is returned

The views look like this:

1.SELECT DISTINCT Date, Category, Caption, Value
2.  FROM dbo.Summary
3. WHERE CollectTitle = N'Test Environment'
4.  AND (Date = (SELECT TOP (1) MAX(Date)
5.           FROM dbo.Summary
6.          WHERE (CollectTitle = N'Test Environment')))

As you can see, the view for the Test environment is shown. For the views of the Acceptance and Production environment, you'll have to replace the values to the Report titles of those environments.

The view over all environments

This summary view can be named MostRecentSummaryTAP and looks like this:

1.SELECT t.Caption AS Category,
2.    t.Value AS Test,
3.    a.Value AS Acceptance,
4.    p.Value AS Production
5. FROM dbo.MostRecentSummaryTest AS t
6. INNER JOIN dbo.MostRecentSummaryAcceptance AS a
7.   ON t.Category = a.Category AND t.Caption = a.Caption
8. INNER JOIN dbo.MostRecentSummaryProduction AS p
9.   ON a.Category = p.Category AND a.Caption = p.Caption

How to create views in SQL Server

To create views in SQL Server Management Studio (SSMS), we navigate to the MsgBoxViewer database and create a new view.

After giving the view the appropriate name, you can design the view with the Designer, but in this case, you could also copy/paste the SQL commands shown in this article, verify the results and save the view.

This should give you enough information to create the needed views. When you have created them, you can simply run MBV on the environments and run the MostRecentSummaryTAP view.

Summary

We have seen how we can aggregate the output of MBV runs in a single SQL Server repository. In this Wiki article we have created a view based on the data in the Summary table, but of course we could also create views on the other tables which are filled by MBV.

Besides just creating these views we could also create views and/or queries for certain specific needs. You can think of topics like:
  • Table size of specific tables like Spool, MarkLog
  • Message Box Integrity
  • Server configuration
  • etc.

When you have a monitoring solution like BizTalk360, you could even write custom queries and set alarms on them, so you will get informed automatically in case of certain thresholds!

Downloads

To give you an idea of the capabilities of using SQL Server as a data store and executing queries against it, a collection of 18 queries have been composed and uploaded to the Gallery. The collection contains queries regarding database sizing, MessageBox integrity and server configuration.

See Also

Another important place to find an extensive amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.