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 posts:

• Monitor your BizTalk environment using PowerShell – Disk Space Monitoring
• Monitor your BizTalk environment using PowerShell – Monitoring Windows Updates and Pending Restarts
• Monitor your BizTalk environment using PowerShell – SQL Agent Jobs Monitoring

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 useful, at least in my opinion, in the last two months I found three additional situations that are important to monitor:

• Some of the jobs were disabled
• 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:

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 develop this SQL Script.

Download

THIS POWERSHELL IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.

You can download Monitoring SQL Agent Jobs in your BizTalk environment with PowerShell from GitHub here: