Why did we build SQL Jobs Monitoring for BizTalk Environment?

Why do we need this feature?

As you will probably be aware of, BizTalk Server heavily relies on SQL Server databases for storing, for example, the configuration of BizTalk artifacts and the state of in-flight and completed processes. After installing and configuring BizTalk Server, few databases are created. The most important databases are the Configuration database, the MessageBox database and the Tracking database, but depending on the configured features, other databases might be created for BAM, BRE and SSO.

Further, there are a number of SQL jobs that gets created when you install and configure BizTalk Server. These SQL jobs are maintenance jobs and are of vital importance for the health of the BizTalk Server environment. The BizTalk SQL Jobs take care of making backups of the BizTalk databases, transfer tracking data from the MessageBox database to the Tracking database and all kind of cleaning up data from the databases.

Without having these jobs enabled (in cases disabled, not all the jobs are supposed to run) and properly running, it is just a matter of time, before you will get into trouble. This kind of trouble might lead to interruptions of the business process(es) which are being processed through BizTalk. For that reason, it is important to be aware of the (health) status of the SQL Jobs.

What are the current challenges?

There are two basic challenges at the moment with the current default tooling.

• There is no out of the box support for monitoring SQL Jobs and keeping an eye on the health
• Accessing SQL jobs for viewing, changing state, etc requires access to SQL Server

Although for experienced BizTalk administrators, it is obvious that they need to be well aware of the health of their BizTalk databases, for organizations with less experience on administrating BizTalk, this might come as a surprise.

We have seen that BizTalk administrators did not have access or insufficient access to the SQL Server management tools. This could especially be true, in case BizTalk Server has to share its SQL Server instance with databases of other systems, which is not a good idea anyway.

Even if the administrator has access to the SQL Management tools, having to manually monitor these SQL jobs is a time consuming and adds up to all the other assignments of the BizTalk administrator.

How BizTalk360 solves this problem?

As we, at BizTalk360, understand the importance of the SQL jobs, we brought both operational and monitoring features related to these jobs. In this section, we will discuss the Monitoring features.

BizTalk360 allows you to set up monitoring on SQL jobs seamlessly. You need to simply register the name of the SQL Server instances where your SQL jobs are running via the Settings -> Monitoring -> SQL Server Instances and all the SQL jobs will be ready for monitoring configuration.

Once configured, BizTalk360 enables you to achieve the following:

• Monitor the Expected Job State (both enabled and disabled)
• Configure Auto-correct
• Monitor the Expected Last Job Run State

Below, all these capabilities will be discussed.

Monitor the Expected Job State

With this kind of monitoring, you can check whether the SQL job is in the correct state. In most cases, the expected state will be Enabled, but in certain cases, the expected state must be Disabled. BizTalk360 allows you to monitor for both states.

The possibility of negative monitoring comes in handy for BizTalk Server, as the MessageBox_Message_Cleanup_BizTalkMsgBoxDb jobs need to be in disabled state. This job works like a stored procedure and used internally by other jobs. It should not be manually started by a DBA or BizTalk Administrator.

Configure Auto-Correct

The Auto-Correct feature is an extension of Expected State Monitoring. Basically, it allows you to bring State-bound artifacts back to the expected state after they reached the wrong state.

Auto-correction works 2 ways. If an artifact, say a SQL Job, is expected to be in the Enabled state but reached the Disabled state, the Auto-Correct feature will try to bring the SQL job back to the Enabled state.

However, when a SQL job is expected to be in the Disabled state (like the MessageBox_Message_Cleanup_BizTalkMsgBoxDb job) but got Enabled, the Auto-Correct feature will try to bring the job back to the Disabled state.

This helps to maintain the state of the SQL jobs in the desired state even if there is a human error.

This feature works not just for SQL Server jobs, but also for:

• Receive Locations, Orchestrations and Send Ports
• Host Instances
• Windows NT Services
• Azure Logic Apps

Monitor the Expected Last Job Run State

This allows you to check whether the job runs successfully. Once you have setup monitoring for Expected Last Run state, you will be notified in case the Last Run State was unsuccessful.

Conclusion

By bringing SQL job monitoring, including the auto-correct feature, we think that this will make the life of both BizTalk administrators and SQL Server DBA's a bit easier.