When you install and configure your BizTalk server environment a bunch of SQL jobs gets 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
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 effect the overall performance of your environment.
Monitoring BizTalk SQL Jobs using BizTalk360
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.
- Create Alarm
- Configure your expected SQL jobs state
- 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 “Monitor and Notify” tab and expand SQL Server Instances node in the tree view. It will list all the instances related to your BizTalk environment automatically. Select the instance for which you need to set up monitoring/alerting.
On the SQL jobs section, you can select the required jobs and click either one of the button “Must be Enabled” or “Must be Disabled” depending on your requirements.
If all the jobs are in expected state, the overall health will be good (Green).
For demo purpose, we just set the first 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.
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/sms alerts.
You can see the alert emails coming up every 2 mins (the value we configured while defining the alarm), 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”)
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.