Monitor BizTalk SQL Jobs

Monitor BizTalk SQL Jobs using BizTalk360

Published on : May 21, 2012

Category : BizTalk Server



When you install and configure your BizTalk Server environment a bunch of SQL jobs get installed and scheduled automatically. SQL Jobs are crucial to keep the health of your environment. SQL jobs are responsible for variety of things in a BizTalk environments. Some of them include

  • Moving Tracking Data from BizTalk Message Box database to tracking database
  • Achieving and purging tracking data based on your company policy
  • Backing up your BizTalk (and custom) databases using Marked transactions
  • Purging unused subscriptions, etc
A complete list of all the SQL jobs and their functionalities can be seen in the KB article. If those jobs are not running as expected, you message box/tracking database can get bloated and adversely affect the overall performance of your environment.

Key takeaways from the blog

  • How to Monitor BizTalk Server SQL job fails?
  • How to setup SQL Jobs monitoring?
    • Create Alarm
    • Configure your expected SQL jobs state
    • Start getting alerts, when the SQL jobs are not enabled as expected
  • Monitor BizTalk Server BizTalkmgmt SQL Agent

How to Monitor BizTalk Server SQL Job fails?

BizTalk360 monitor and notify capability comes with a plugin to monitor SQL jobs. It’s designed keeping BizTalk solutions in mind. The important thing to note on SQL jobs related to BizTalk server is, not all the jobs are enabled. Some of the jobs like “MessageBox_Message_Cleanup_BizTalkMsgBoxDb” must be kept in a disabled state (these jobs are internally called by other jobs). Enabling them will have adverse impact in the environment. BizTalk360 allows users to set threshold to expected state. The user can set certain jobs to be in enabled state and certain jobs to be in disabled state. If they are not in the expected state then threshold will be violated, and alert notifications (email/SMS) will be triggered.

How to setup SQL Jobs Monitoring?

Setting up SQL Jobs monitoring is very simple in BizTalk360, it just takes few clicks.
  1. Create Alarm
  2. Configure your expected SQL jobs state
  3. Start receiving alerts whenever the threshold is violated

1. Create Alarm:

Creating alarm is generic to all the monitors in BizTalk360, so please refer to this blog post showing how to create/manage alarms

2. Configure your expected SQL jobs state:

You navigate to the “Manage Mapping” section and select “SQL Server instances” tab  This lists all the SQL jobs that are related to your BizTalk environment. Select the Alarm name (see Manage Alarms) from the drop-down for which you would like to associate the alerts. Select the instance for which you need to set up monitoring/alerting.

monitor biztalk server sql job biztalk server sql job

Set the value of ‘Expected Job State’ by selecting the appropriate value from the drop-down box

Set the value of ‘Expected Last Run State’ after selecting Expected Job State from the drop-down box

Depending on your business requirements, you can setup the expected state. If all the jobs are in expected state, the overall health will be good (Green).

If all the jobs are in expected state, the overall health will be good (Green). biztalk server sql job monitor biztalk server sql job For demo purpose, we just set the second job expected state to be disabled, whereas the current state is enabled. As soon as you save the configuration, the icons will turn red as shown below highlighting the SQL jobs are not in expected state. biztalk server sql job

3. Start getting alerts, when the SQL jobs are not enabled as expected

In addition to the status displayed in UI, BizTalk360 will also constantly monitor your SQL jobs configurations, and whenever it violated the condition, based on your alarm threshold settings it will send you email/SMSalerts. You can see the alert emails coming up every 2 mins (the value we configured while defining the alarm), and the subject will clearly show the type of alarm (“Down”). The subject line will show the environment and the alarm name (ex: “SQL Jobs Health Check”)

biztalk server sql job An example email notification will look as shown below. The summary will be displayed at the top followed by detailed description of each configured job and the threshold.

How to monitor SQL Server agent with BizTalk360?

biztalk server sql job

SQL Server agent is actually a Windows NT service and users can monitor such services with BizTalk360. Let us see how it works!

As a BizTalk administrator to make sure that your NT services are up and running in your BizTalk Server, you always log in to services.msc console to take necessary action against the services.

With BizTalk360, you can perform all the possible operations such as Start, Stop, Pause, and restart as similar as done in services.msc console in Windows. Users don’t need to context switch to the services window on your server for performing the operations. Likewise, users can monitor the NT Services with BizTalk360 itself. Please take a lookout at this blog and this gives a great insight how BizTalk360 monitors your NT services.