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.
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:
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.
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.
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.
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')))
1.
SELECT
DISTINCT
Date
, Category, Caption, Value
2.
FROM
dbo.Summary
3.
WHERE
CollectTitle = N
'Test Environment'
4.
AND
(
= (
TOP
(1)
MAX
)
5.
6.
(CollectTitle = N
)))
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.
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
t.Caption
AS
Category,
t.Value
Test,
a.Value
Acceptance,
p.Value
Production
dbo.MostRecentSummaryTest
t
INNER
JOIN
dbo.MostRecentSummaryAcceptance
a
7.
ON
t.Category = a.Category
t.Caption = a.Caption
8.
dbo.MostRecentSummaryProduction
p
9.
a.Category = p.Category
a.Caption = p.Caption
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.
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.
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!
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.
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.