BizTalk Health Monitor Custom Rules and Queries

BizTalk Health Monitor is a valuable tool to validate the current state of a BizTalk Server Environment. I encourage you to use recurrently review warnings and act on the issues. We use it extensively when doing BizTalk Health Checks and working with support cases. A useful feature is the possibility to add custom queries and rules.

If you want to read more about basic functions of BHM visit the BizTalk Health Monitor Team blog
https://blogs.msdn.microsoft.com/biztalkhealthmonitor/
https://blogs.msdn.microsoft.com/biztalkhealthmonitor/2014/06/26/overview-of-biztalk-health-monitor-bhm/

My sample: ExtBHM-CustomExamples

Adding a Custom Query

BHM Queries collect information to which you can apply rules. If you have some information about your solution that you want to include as part of the health review. Custom queries can be of different types, the possibilities are a bit different, the table below enumerates the types and shows how information is presented. The option I used most is PowerShell, generally I create a script that I can create, and test manually and then just add the full path to the script and send parameters.

Type Comment
WMI WQL Select Statement, all properties included on the select will be accessible for rules and display. About WQL
Example:
SELECT AdapterName, HostName, IsDefault FROM MSBTS_SendHandler2 Where ( HostName = ‘TrackingHost’ )ExtBHM-02
SQL SQL Select Statement, all columns included in the Select statement will be accessible for rules and display. Do not query the BizTalk databases. Changes to the BizTalk databases is not advisable and not supported.
Example:
SELECT @@SERVERNAME AS ServerName, name, recovery_model_desc FROM sys.databases
Where database_id > 4ExtBHM-03
VBS VB Script, each line printed to the console will be collected but no additional columns. Validate the line text or row count.
Example:
Set g_objShell = createobject(“Wscript.shell”)
MaxDTCLogSize = g_objShell.RegRead (“HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MSDTC\MaxLogSize”)Wscript.Echo MaxDTCLogSize
PowerShell PowerShell Script each line printed to the console will be collected but no additional columns. Validate the line text or row count.
Example:
$QueryString = Gwmi Win32_OperatingSystem -Comp MyComputer
Write-Host $QueryString.CaptionExtBHM-04
BAT A batch of commands each line printed to the console will be collected but no additional columns. Validate the line text or row count.
Example:
ECHO LISTENING && netstat -ano | find /c “LISTENING”
ECHO SYN_SENT && netstat -ano | find /c “SYN_SENT”
ECHO SYN_RCVD && netstat -ano | find /c “SYN_RCVD”
ECHO ESTABLISHED && netstat -ano | find /c “ESTABLISHED”
ECHO FIN_WAIT_1 && netstat -ano | find /c “FIN_WAIT_1”
ECHO CLOSING && netstat -ano | find /c “CLOSING”
ECHO FIN_WAIT_2 && netstat -ano | find /c “FIN_WAIT_2”
ECHO TIME_WAIT && netstat -ano | find /c “TIME_WAIT”ExtBHM-05
CMD Output from one command each line printed to the console will be collected but no additional columns. Validate the line text or row count.
Example:
Tasklist.exe

ExtBHM-01

  1. Navigate to the profile you want to customize, tab Queries
  2. Click on New Query after selecting the type you want to use
  3. Add the Query commands
  4. Set properties like Caption and target
  5. Test and select Ok when done

Targets

Each query has a target which is the name of the server where to run the query. There are some macros to address the query to multiple servers in an environment. You can use ALLBTSVR to run a query on every BizTalk Server by setting the TargetID element in the configuration file.

Note: I have only made it work with PowerShell queries.

Adding a Custom Rule

A rule acts on the data collected with a query. Rules can be set to give a warning on each failing instance or once for all errors. You can have more than one condition for a rule. In a condition you can validate a column value (%GLOBALPROP_REPORTVALUE:##COLNAME##%) or the number or rows generated by the query(%GLOBALPROP_ROWCOUNT%). Actions give you the possibility to act when conditions are met. Actions can use data from the failing rows and have URLs to documentation, i.e. to a wiki with information on what to do.

  1. Navigate to the profile you want to customize, tab Rules
  2. Write a name and add description comments. Set evaluation rows and trigger actions.
    ExtBHM-06
  3. Select Commit changes
  4. Select and edit the default condition
    ExtBHM-07
  5. Select Commit changes
  6. Add more conditions if needed
    ExtBHM-10
  7. Edit the default action to match your requirements
    ExtBHM-08
  8. Select Commit changes
  9. Test and Save
    ExtBHM-09

Activating the rules

Now that we have rules we need to activate them by going to the Information Level tab and select the custom queries we want to have in the profile.

ExtBHM-11

Moving the profile to another environment

The profile configuration files are stored under %ProgramData%\Microsoft\BizTalkHealthMonitor as xml files with a name starting with MBVSETTINGS_. Simply edit the file to match the names on the new environment and save it in the new location.

Generate restore scripts for BizTalk Server backups

The BizTalk backup process

A BizTalk group’s data consists of a number of databases with data moving between them. To keep a consistent state when taking a backup, the job uses a marked transaction. A description of that process can be found here. Every time the backup job runs some rows are added to the adm_BackupHistory table in the BizTalkMgmtDb database containing information like the MarkName and files. This information is used by BizTalk Log Shipping to keep track of backups.

BizTalk Log Shipping

BizTalk Log Shipping is the only fully supported way of disaster recovery. When backups are taken SQL Server jobs will restore them to a passive server. You can find a description of BizTalk Log Shipping here and how to configure it here. There might be situations when you need to manually restore the databases. This process is both tedious and error prone, especially during a critical situation.

The task explained

You have a number of backup files of data and logs and need to restore manually. When restoring the last transaction logs, you must use the STOPATMARK argument to keep the databases in a consistent state. What can we do to be prepared in this situation? Having a script prepared to restore is a good way. The best moment to generate the script is directly after we have taken the backups. The idea is to add an extra step to the backup job that generates a restore script based on the information in the adm_BackupHistory table, using the same information as the BizTalk Log Shipping.

Note: You should never change the logic of the BizTalk Server jobs; in this case we’re augmenting the process without changing what the backup job does.

The script

How it works

What do the script need to do to create the restore script?

  • Get last mark name from adm_BackupHistory
  • Get last full backup file information from adm_BackupHistory
  • Loop all databases
    • Generate full database restore script for the current database
    • Get log file information for the current database from adm_BackupHistory
    • Generate log restore script for the current database using with no recovery for all except for the last one which have the current mark. For the file with the current mark generate script using STOPATMARK.
Configure the script

In the top of the script you have three variables that might need to be updated.

Variable Comment
$ServerName Database Server name where BizTalk management database is located. If it is located on a named instance the instance name must be provided.
$MgmtDb The database name for the BizTalk management database.
$SqlScriptDestination Default set to $null, then the generated scripts will be stored in the same location as the full backups. Set a path if you want the scripts saved on a different location.

clip_image002

Getting the script in place

1. Add an extra step to the backup job of PowerShell type. Add your version of the script in the command field or the full path to the PowerShell script.
Note: If you have implemented Log Shipping you must use a file since the script is longer than the 3200 characters that the Log Shipping process will copy.
clip_image004

2. Change the ”Clear Backup History” step to go to next step on success. Also set the new step to quit responding success or failure.
clip_image006

The resulting SQL scripts

For each backup a new set of script files is created, one file for each database instance that is included in the environment. The naming is “Server_Instance” (Green rectangle in the picture), “Markname of starting full backup” (Blue) and then the “last mark” (Red).

clip_image008

clip_image010
Each database will be set to single user mode and close all open connections. All the databases in the backup have a restore database using with move in the expression so you have the possibility to change the location of the database files. Every restore of logs have NORECOVERY except the last one that use STOPATMARK to stop at the transaction mark.

Restore process

When you are in the process of restoring and have an environment with no activity, I.e. all BizTalk Services and SQL Server agent turned off, you can run the scripts on the database instances where you want to restore. The scripts can be edited to have a different file location if needed. If you’re moving databases to another server don’t forget the normal procedure running the UpdateDatabase.vbs script with and updated SampleUpdateinfo.xml file.

The script is licensed “as-is.” You bear the risk of using it. The contributors give no express warranties, guarantees or conditions. Test it in safe environment and change it to fit your requirements.
Download the code and description 

Note:
This version only supports writing the full path to the script in the jobstep.
This version don’t work with Availability groups, a new post is coming soon.