Why did we build SQL Jobs Monitoring for BizTalk Environment?

|  Posted: February 5, 2018  |  Categories: BizTalk360

BizTalk360 SQL Jobs Monitoring

This blog is a part of the series of blog articles we are publishing on the topic “Why we built XYZ feature in BizTalk360”. Read the main article here.

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.

BizTalk360 Add SQL servers for monitoring

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 list of SQL jobs BizTalk360

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.

If you want to read more about SQL job monitoring, please read this article.

Get started with a Free Trial today!

If you are struggling with all the above-mentioned challenges, why not give BizTalk360 a try. It takes about 10 minutes to install on your BizTalk environments and you can witness and check the security and productivity of your own BizTalk Environments. Get started with the free 30 days trial.

BizTalk360-Free-Trial

Author: Lex Hegt

Lex Hegt works in the IT sector for more than 25 years, mainly in roles as developer and administrator. He works with BizTalk since BizTalk Server 2004. Currently he is a Technical Lead at BizTalk360.

Back to Top