BizTalk DevOps: Monitor your BizTalk environment using PowerShell – SQL Agent Jobs Monitoring (Part 2)

One of the principal needs for BizTalk Administrators is the ability to monitor the health of BizTalk environments on a regular basis and react promptly to solve any possible issues that may appear in order to keep your BizTalk Server applications accessible to your users/organization.

When monitoring BizTalk Server, keep these points in mind:

  • Your infrastructure could be healthy, but your applications might not be (for example, they are receiving invalid messages and are unable to process them).
  • Your infrastructure could be unhealthy, but your applications might be running fine (for example, if a server is down, but there are enough servers assigned to the host to take over the load).
  • An infrastructure problem could surface as an application problem (for example, messages are not being processed fast enough because a server is down).

Source: Monitoring BizTalk Server

You can accomplish this by using certain tools such as: BizTalk Administration Console; BizTalk360; SCOM and many more… However, unfortunately, many times, some of these tools are not available for us but we still need to accomplish this task.

Since BizTalk Server 2010 the product brings a job (Monitor BizTalk Server) that monitors the health of your environment identifying any known issues:

  • Messages without any references
  • Messages without reference counts
  • Messages with reference count less than 0
  • Message references without spool rows
  • Message references without instances
  • Instance state without instances
  • Instance subscriptions without corresponding instances
  • Orphaned DTA service instances
  • Orphaned DTA service instance exceptions
  • TDDS is not running on any host instance when global tracking option is enabled.

However, this is not enough…

So how can PowerShell help us?

In some of my previous post:

I demonstrated how we could use PowerShell to monitor some aspects or features of your BizTalk environment including a script to monitor BizTalk Jobs, however, this script will only monitor if the last execution of the job was successfully or not and if not send a notification.

Although this is a useful, at least in my opinion, in the last two month I found three additional situations that are important to monitor:

  • Some of the jobs were disable
  • MessageBox_Message_Cleanup_BizTalkMsgBoxDb was enable
  • SQL Server Agent was running, BizTalk Jobs were correctly enabled… but despite all that, no job was running (the last execution has been 7 days back – coinciding with an intervention in the system)

So I decided to create another PowerShell script. With this script you can be able to monitor SQL Agent Jobs in your BizTalk environment using PowerShell, checking:

  • If the jobs are being executed according to the schedules that are configured
  • If all jobs (with the exception of MessageBox_Message_Cleanup_BizTalkMsgBoxDb) are enable
  • If MessageBox_Message_Cleanup_BizTalkMsgBoxDb is disable

This script allows you to set:

  • The Jobs execution timeframe and configurations
WITH MostRecentSched AS
 (
 -- For each job get the most recent scheduled run date (this will be the one where Rnk=1)
 SELECT job_id,
		last_executed_step_date,
		RANK() OVER (PARTITION BY job_id ORDER BY last_executed_step_date DESC) AS Rnk
 FROM sysjobactivity
 )
 select name [Job Name],
	last_executed_step_date [Last Scheduled Run Date],
        DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) AS [Minutes Delayed],
		CASE WHEN enabled=1 THEN 'Enabled'
          ELSE 'Disabled'
        END [Job Status]
from MostRecentSched MRS
JOIN   sysjobs SJ
ON     MRS.job_id=SJ.job_id
where Rnk=1
and ((
		((name = 'Backup BizTalk Server (BizTalkMgmtDb)' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 17))
		OR (name = 'CleanupBTFExpiredEntriesJob_BizTalkMgmtDb' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 722)
		OR (name = 'Monitor BizTalk Server (BizTalkMgmtDb)' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 11520)
		OR (name = 'Rules_Database_Cleanup_BizTalkRuleEngineDb' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 62)
		OR (name = 'MessageBox_UpdateStats_BizTalkMsgBoxDb' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 7)
		OR (name IN ('DTA Purge and Archive (BizTalkDTADb)','MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb', 'MessageBox_Parts_Cleanup_BizTalkMsgBoxDb', 'Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb', 'PurgeSubscriptionsJob_BizTalkMsgBoxDb', 'TrackedMessages_Copy_BizTalkMsgBoxDb') and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 1)
	) OR (name IN ('Backup BizTalk Server (BizTalkMgmtDb)','CleanupBTFExpiredEntriesJob_BizTalkMgmtDb',
	               'DTA Purge and Archive (BizTalkDTADb)', 'MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb',
				   'MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb', 'MessageBox_Parts_Cleanup_BizTalkMsgBoxDb',
				   'MessageBox_UpdateStats_BizTalkMsgBoxDb', 'Monitor BizTalk Server (BizTalkMgmtDb)',
				   'Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb',
				   'PurgeSubscriptionsJob_BizTalkMsgBoxDb', 'Rules_Database_Cleanup_BizTalkRuleEngineDb',
				   'TrackedMessages_Copy_BizTalkMsgBoxDb') AND SJ.enabled = 0)
	  OR (name = 'MessageBox_Message_Cleanup_BizTalkMsgBoxDb' AND SJ.enabled = 1)
	)
order by name, last_executed_step_date desc;
  • And configure your email notification settings
#Set mail variables
[STRING]$PSEmailServer = "smtp"
[STRING]$Subject = "BizTalk Job Monitor"
[STRING]$From = "biztalk@monitor.com"
[array]$EmailTo = ("support@biztalk.com")

if($count -gt 0)
{
    #Send mail
    foreach ($to in $EmailTo)
    {
        $Body = $HTMLmessage
        $SMTPClient = New-Object Net.Mail.SmtpClient($PSEmailServer)
        $message = New-Object Net.Mail.MailMessage($from, $to, $Subject, $Body)
        $message.IsBodyHtml = $true;
        $SMTPClient.Send($message)
    }
}

Report sample:

BizTalk-SQL-Jobs-another-way-monitor

Note: This type of script must be viewed as a complement to the tools mentioned above or used in the absence of them.

Credits: Special thanks to José Dias who helped me developing this SQL Script.

The script can be found and download on Microsoft TechNet Gallery:
Another way to monitor BizTalk SQL Agent Jobs with PowerShell (2.8 KB)
Microsoft TechNet Gallery

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

3 thoughts on “BizTalk DevOps: Monitor your BizTalk environment using PowerShell – SQL Agent Jobs Monitoring (Part 2)”

Leave a Reply

Your email address will not be published. Required fields are marked *

BizTalk360
BizTalk Server

Over 500+ customers across
30+ countries depend on BizTalk360

Learn More
Serverless360
Azure

Manage and monitor serverless
components effortlessly

Learn More
Atomicscope
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top