Monitoring SQL Server Agent Jobs and Databases

BizTalk Server includes multiple SQL Server Agent jobs that perform important functions to keep your servers operational and healthy. You should monitor the health of these jobs and ensure that they are running without errors. The Microsoft SQL Server Management Pack contains rules for monitoring items such as SQL databases and SQL Server Agent jobs. You should configure the SQL Server Management Pack for comprehensive monitoring of all BizTalk Server databases and SQL Server Agent jobs.

The BizTalk Server management pack includes two disabled rules for monitoring the health of two of the most important BizTalk SQL Server Agent jobs:

  • Critical Error: A BizTalk SQL Server Agent job failed - Backup BizTalk Server

  • Critical Error: A BizTalk SQL Server Agent job failed – Tracked Message Copy

To monitor all BizTalk Server SQL Server Agent jobs from within the BizTalk Server management pack, you must enable these rules and create additional rules for other jobs that you want to monitor using the following process.

  • In the Operations Manager Administrator console, create a copy of either of the two preceding rules in the BizTalk Server Core Rule group, and rename the rule appropriately.

  • In the criteria section for the rule, change the wildcard comparison for Parameter 1 appropriately.

  • In some cases, job names are dependent on database names that the user creates, for example, the name of the MessageBox database.

  • Your rule can be targeted either towards a job associated with a single MessageBox or all MessageBoxes.

To ensure that the SQL Server Agent jobs are monitored, the SQL Server computers must be included in the BizTalk Server 2009 computer group in Operations Manager 2007. This is because the rule needs to be evaluated on the SQL Server computer, and the SQL Server computer is not recognized as a BizTalk Server computer unless SQL Server and BizTalk Server are installed on the same computer. The steps are the same as in How to Add Enterprise Single Sign-On Computers to the List of Computers Monitored by the BizTalk Server Management Pack at in BizTalk Server 2009 Help.