biztalk cannot connect to sql server

BizTalk Health Monitor – A BizTalk Monitoring Tool

Published on : Jan 26, 2017

Category : BizTalk Server Support

Rachit Sikroria

Author

A quick and complete dashboard of a BizTalk group which will help BizTalk administrators monitor the health of the BizTalk platform. The standalone version of BHM can be downloaded from Microsoft Download Center here: BizTalk Health Monitor.

Register BizTalk Health Monitor Snap-In

After downloading and unzipping the BHM.ZIP file from the Microsoft Download Center, use the “InstallUtil.exe” which comes with BizTalk Health Monitor tool to register the BHM snap in.

Important: Unzip the BHM.ZIP to a final destination before you register the snap-in for example: C:\Program Files (x86)\Microsoft BizTalk Server 2010\SDK\Utilities\BHM. Once you register the BizTalk Health Monitor Snap-In you cannot delete the folder or BHM will stop working

To accomplished that we need to:

  • Open a command prompt as an administrator
  • Navigate to the directory file where you unzipped the BHM.ZIP file
    • For example: C:\Program Files (x86)\Microsoft BizTalk Server 2010\SDK\Utilities\BHM
  •  And from the command prompt type:
    •  InstallUtil.exe MBVSnapIn.dll
  •  And hit enter. This step will do some registry changes and register the SnapIn to be used.

Integrate BHM Snap-In into BizTalk Administration Console

BHM Snap-In can be used independently and need not to be integrated with BizTalk Administration Console. The handicap of this approach is that a BizTalk Administration will need to use two different places/tools to monitor and administrate the environment.

To be easier and more convenience for BizTalk Administrators BHM Snap-In can also be integrate it so that it can be used with BizTalk Administration Console.

To accomplished that we need to open a 32-bit Microsoft Management Console (MMC):

  • Click Start, click Run, type:
    • mmc /32
  • Press enter or click OK. This will open a new 32-bit version of MMC (MMC32).
  • From MMC console, go to File menu and select “Options…” option
    • In the text box, replace “Console1” for “BizTalk Administration Console”
    • In the Console mode combo box, select “User mode – full access”
    • And confirm that the option “Do not save changes to this console” is uncheck

Integrate BHM Snap-In

  • From MMC console, go to File menu and select “Add/Remove Snap-in…” option

Add/Remove Snap-in

  • From the “Add or Remove Snap-ins” window, add following snap-ins and then click Ok
    • Microsoft BizTalk Server Administration
    • BizTalk Health Monitor
    • You can add the snap-ins by selecting them from the “Available snap-ins” list and click “Add >”
    • Is recommend that you respect the order present in the “Selected snap-ins” list as showed in the picture above

Selected snap-ins

  • This will generate for us a new MMC which contains both the BizTalk Server Administration and BizTalk Health Monitor. And by now your new MMC is ready which shows both the BizTalk Server Administration and BizTalk Health Monitor

BizTalk Health Monitor

Now to save this as a new .msc file so that you don’t have to repeat these steps again but before we complete the creation process of the “new” BizTalk Administration Console, I recommend that you navigate to the BizTalk Server Installation folder:

  • Example: C:\Program Files (x86)\Microsoft BizTalk Server 2010
  • And rename “BTSmmc.msc” file, for example: “BTSmmc-old.msc”

This because we will save the “new” BizTalk Administration Console as “BTSmmc.msc” so that you don’t need to create new shortcuts or having different ways to access the BizTalk Administration Console – however this step is optional!

To finished the creation process of the “new” BizTalk Administration Console

  • From MMC console, go to File menu and select “Save As…” option:
    • Give a name and then save it.
      • Access to BizTalk Server Installation folder
        • C:\Program Files (x86)\Microsoft BizTalk Server 2010
      • Save the file as “BTSmmc.msc”

From next time onwards, when you open the BizTalk Server Administration Console the “new” BizTalk Administration Console will be incorporated with the BizTalk Health Monitor

BizTalk Health Monitor

Working of BizTalk Health Monitor

1.) Generate and View MBV report

Once a group node is added in the BHM hierarchy, you can start immediately to analyze its corresponding BizTalk group by right-clicking on the node and select the “Analyze now” menu item.

The analysis consists of running a number of queries to collect all important information of the BizTalk group, evaluating a set of rules for each of them, and raising any warnings (critical or not) if there is any.

Working of BHM

 Each time an analysis process is completed, a child node report is added in the group node. Each child report has further sub-nodes to present the collected information:

  • Critical Warnings
  • Warnings
  • Summary
  • Topology
  • Queries

Group settings

This sub-nodes list details depends on the level of information you selected to display in BHM. The property page Information level of the group node settings allows you to select which sub-nodes you want to display for each generated report, and the level of information to collect during the analysis process (as shown below). The more queries you select to include in the analysis, the more information and potentially more warnings BHM will raise.

You can so customize as you want the level of information to analyse and display in BHM for each added group node.

Topology sub-node

This is for example the output of the Topology sub-node:

overall health of BizTalk Environment

2.) Dashboard view for the overall health of BizTalk Environment

The output of the “Analyze Now” action will also generate a dashboard (as shown below) presenting a quick summary all the information you need to know about the BizTalk platform. We have categorized the information under different tiles as their names suggest. Each tile will be presented with a small icon on the bottom right of the tile which represents the overall health of that category. A green right mark shows that this category is healthy. A yellow exclamation sign represents some non-critical warnings whereas a red exclamation represents some critical warnings which need immediate attention. You can click on these tiles to get additional details on the lower pane.

Report collection

3.) Schedule Report collection

A BizTalk platform processing messages 24×7 needs to be monitored at least daily. BHM provides you a way to create a scheduled task to automate a periodic collection of BHM report.

The property page Schedule of the group node settings provides an option to enable a scheduled analyze. This schedule task can be of course modified then in the Windows Task Scheduler.

Email Notifications

4.) Send Email Notifications

Receiving by mail the list of warning that MBV generates is a feature that has been demanded by lot of MBV users. We have incorporated this feature in BHM where you have an option to send the dashboard and the list of warnings to an email address after a fresh analysis.

BHM provides so an option to send automatically by mail the dashboard and the list of warnings after a fresh analysis. You can enable and configure this feature from the property page of the group node settings (select the “Settings” menu item when right clicking on a group node). An email will also be sent each time if you have scheduled the BHM report collection.

We have also provided a Test button which you can use to test the SMTP settings you have entered. It will send a test email to make sure that SMPT settings are correct.

Current activity

5.) Perfmon integration with pre-loaded scenario based Performance counters

BizTalk provides a large number of performance counters to monitor like the state of the BizTalk databases, the BizTalk queues, the messaging and XLANG performances, the  throttling states, the adapters performances etc…There are various such counters available but they are less known and visible to the BizTalk administrators.

BHM directly integrates Perfmon tool in its own MMC which provides a quick and easy way to monitor these counters. Furthermore, for each group node, BHM provides you a list of predefined views with pre-selected counters that you can quickly display at any time.

BHM will let you display either the current activity of the monitored BizTalk platform, or a recorded performance activity.

Current activity

When the current activity node is selected, you can choose a BizTalk server belonging to the monitored BizTalk group or any server you want. BHM provides default tabs in the current activity view with default counters but you can create your own tabs with your own counters  (right-click on tab to display the context-menu and create a new one).

Recorded activity

Recorded activity

BHM can also automate the recording of a perfmon activity for a given period. You can enable and configure this feature from the property page Recorded Performance activity of the group node settings (as show below).

BHM provides you a set of pre-defined scenarios with selected performance counters and you can choose the preferred scenario. You can also add your own counters if you chose the custom scenario option. The property page allows you to configure the sample interval and the duration of the recorded activity.

sub-node

Once the recorded activity is configured, BHM can automate the creation, the start & stop of a performance log file corresponding to the selected scenario. When the recorded activity is completed, you can display it inside BHM by selecting the corresponding sub-node (as show below).

Custom queries

6.) Custom queries

BHM repository has a collection of almost 200 queries and now with v2 you have an option to build and add your own queries to this repository. The type of a custom query you can add are:

  • WMI query
  • VB Script
  • PowerShell query
  • Batch file (.bat)
  • CMD

 Once a custom query is added in a profile, it will be saved in that profile and visible in the query tree view of the “Information Level” of the profile settings and can be checked or unchecked, like any native queries of BHM. And like any native BHM queries, a custom query can be pinned to a dashboard.

To add a custom query:

  • Right click on the Profile select Settings.
  • Select the “Queries” tab which also list all the BHM queries.
  • Click on the button “New <query type>”
  • Fill the query body in the new query Dialog Box and test your query
  • Validate the query Dialog Box
  • The query will be visible at that time in the tree view of the information level tab so that you can check it to include in the collect statement
  • Validate the settings page to persist the custom query in your profile

You can of course edit or remove custom queries

Custom Rules

Notes:

  • Only WMI queries allow you to specify a target server; for the other types of query, execution will be local to the server running BHM.
  • Only WMI queries will create columns in the query output; for the other types of query, the output will be on a unique columns named “Output” (this possibility exists in MBV engine used by BHM but not yet exposed)
  • Each query of BHM can be quickly tested using the “Test” button in the tab “Queries”  

7.) Custom Rules

BHM engine has a repository of rules which are applied on its queries and are displayed in the report accordingly. Out of the box BHM comes up with around 450 rules which does various kind of checks on your BizTalk environment. And if that’s not enough BHM v2 will give you an option to write your own custom rules on existing or custom queries.

A Rule in BHM is a set of conditions (AND between the conditions) checking each row of a query output, and a set of triggered actions if all the conditions are validated. Each rule target a query and will be executed at the end of the query execution, parsing each row of the query output to validate the conditions.

A condition is composed of two fields to compare using a predicate. One field represent the column value to check for the current parsed row and the other field is the value to compare with. The predicate can be selected among a given list. You can add as many conditions as you want, and if all the conditions are validated for a parsed row then the rule will trigger some actions.

You can create three types of action:

  1. Add an entry in the summary or warnings sections  (if the information level field  is a warning)
  2. Add an entry in the topology section
  3. Spawn a process

Follow this to add a rule:

  1. Right click on the Profile and select settings.
  2. Select the tab “Rules” listing all the BHM rules and their source query.
  3. Click on the button “Add custom rule”
  4. Select the target query, click ok.
  5. Fill the rule properties, conditions and actions in the rule Dialog Box
  6. Test your rule : an HTML view will be displayed with the query output and the Summary & warnings sections
  7. Validate that the rule is added to the rules list view
  8. Validate at the settings page that the rule is persisted as a custom rule in your profile

You can of course edit or remove custom rules

Custom Rules

BHM

 

Notes:

  • You can also add a rule by right clicking on a query in the queries output node
  • To help you creating your own rule, we recommend to look at the details of existing native rules of BHM :
  • For example select any rule targeting the query “BizTalk events in last hours” and then click on the button “Show Rule details”. You can then select each condition and actions of the rules and see their details.
  • Some rules of BHM will not display conditions because they are coded in a custom assembly.
  • Because of only AND logic between the conditions and of the limited number of predicates available, complex custom rule cannot be created using the rule Dialog Box.

MBV engine used by BHM allows to create complex rules in custom assemblies but this feature is not yet exposed in BHM.

Here, you’ll find several BizTalk monitoring tools for administering and monitoring your BizTalk Server environment that still exist in the current market.

References