When a BizTalk server environment is set up, there are SQL jobs that get installed and scheduled automatically to maintain the health of the BizTalk environment. SQL jobs are responsible for variety of purposes in a BizTalk environments such as Moving Tracking Data from BizTalk Message Box database to the tracking database, archiving and purging tracking data based on your company policy, purging unused subscriptions, and so on. If the SQL jobs stop processing you will start seeing significant database growth and performance degradation, so monitoring these jobs to be running according to the best practices is vital for a healthy BizTalk environment.
BizTalk360 allows the administrators to set the threshold for SQL jobs. They have two threshold options - Expected Job State and Expected Last Run State. The best practices for monitoring BizTalk Server jobs is that all jobs should be enabled and that the last run was successful but with a few notable exceptions, the MessageBox_Message_Cleanup_BizTalkMsgBoxDb job which should be disabled and the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job which never ends with success. BizTalk360 allows you to defined the right monitoring conditions for all the jobs by assigning an Expected Job State of enable for all jobs except the MessageBox_Message_Cleanup_BizTalkMsgBoxDb which should be assigned disabled; you should also assign an Expected Last Run State to Successful to all jobs except the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb.
Setting up monitoring for SQL jobs
- Log in to the BizTalk360 application
- Click 'Monitoring and Notification' in the Menu panel on the left side of the screen
- Select 'SQL Server Instances' from the drop down in the top right corner of the screen
- Select the SQL Server instance for which you want to set up the monitoring on SQL jobs
- In the top right corner of the page, select the Alarm name (see Creating an alarm) for which you would like to associate the alerts
- Select the check boxes against the SQL Jobs that you wish to monitor
- Set the value of Expected Job State and Expected Last Run State by selecting from the values from drop down. For instance, if the current state of the SQL jobs is Enabled and the Expected Job State is Disabled, you need to set the value in the drop down to Disabled. Similarly, if the Last Run State of the SQL Job was Successful, you can set up monitoring over the Expected Last Run State to either Successful or Do Not Monitor state.