Health monitoring is concerned with how BizTalk Server and your solution are working at the application level. This is where your business processes live. The following table shows health-monitoring tools.

Table 1 Tools for health monitoring and troubleshooting

Availability monitoring answers the question "Is there anything preventing the system from running correctly?" These issues are almost exclusively system-level ones, such as availability of services and connections. For example, if an adapter is failing because the Enterprise Single Sign-On service is stopped, this is an availability issue. The following table shows availability-monitoring tools.

Table 2 Tools for availability monitoring and troubleshooting

Performance monitoring answers the question, "How efficiently is the system performing its work?" This kind of monitoring focuses primarily on the load on physical resources like databases and disks. For example, if the CPU utilization is consistently at 90 to 100 percent and a backlog of messages is forming, this is a performance issue at the computer level. The following table shows performance-monitoring tools.

Table 3 Tools for performance monitoring and troubleshooting

• Scheduling. As often as possible
  
• Required privileges. Local Administrator, although follow-up may require BizTalk Administrator privileges
  
• Tools. To open Event Viewer, click Start, point to Programs, point to Administrative Tools, and then click Event Viewer.
  

Event Viewer is the key to monitoring the health of BizTalk Server. If anything goes wrong during system startup, operation, shutdown, or message processing, an event appears in the tool. Although this tool seems simple, it provides critical troubleshooting information.

Because Event Viewer is a Microsoft Management Console (MMC) snap-in, you can use any MMC console that has the Event Viewer snap-in, or you can create your own console by starting MMC and adding the snap-in. Conveniently, the BizTalk Administration console contains the Event Viewer snap-in.

When you use Event Viewer, look for events from the following sources:

• BizTalk Server 2004
  
• ENTSSO (Enterprise Single Sign-On)
  
• MSSQLSERVER
  
• MSSQLServerOLAPServices
  
• SQLSERVERAGENT
  
• MSDTC (Distributed Transaction Coordinator)
  
• Windows SharePoint Services 2.0
  

The Event Viewer error events typically give you an idea of where to start troubleshooting. Problems generally fall into one of two categories:

• BizTalk message problems
  
• System problems
  

The only two sources that post message error events are BizTalk Server 2004 and XLANG. Any other errors that you find are system issues.

With BizTalk Server 2004 or XLANG error events, you often find several events from other sources clustered together. This is because failing systems have a cascading effect, with the result being that BizTalk Server suspends the instance. Consider this when determining whether you have a health issue or an availability issue.

BizTalk Server 2004 Errors

ENTSSO (Enterprise Single Sign-On) Errors

MSSQLSERVER (SQL Server) Errors

MSSQLServerOLAPServices (SQL Analysis Services) Errors

SQLSERVERAGENT (SQL Server Agent) Errors

MSDTC (Distributed Transaction Coordinator) Errors

Windows SharePoint Services 2.0 Errors

• Scheduling. As required
  
• Required privileges. BizTalk Administrator
  
• Tools. To open the Health and Activity Tracking (HAT) tool, click Start, point to Programs, point to Microsoft BizTalk Server 2004, and then click Health and Activity Tracking.
  

The HAT tool queries the BizTalk Server system for the state of messages and orchestrations. This is typically the first troubleshooting tool you use when a message-level exception occurs. You often use HAT in response to a BizTalk Server event posted in Event Viewer.

You can use HAT to do the following:

• Use the query builder to create your own queries.
  
• Use the Orchestration Debugger to step through suspended orchestrations. This lets you resolve exceptions and replay completed orchestrations to examine the path taken by a message.
  

1. In Event Viewer, make a note of the BizTalk Server message instance ID or the service instance ID.

2. In HAT, on the Configuration menu, click either Pipelines or Orchestrations. Then select the events you want to track. At a minimum, you should track the following events:

   • Service Start and End
     
   • Message Send and Receive
     

3. On the Operations menu, run a HAT query by clicking either Service Instance or Messages.

   The report of either of these queries shows you all the suspended messages and orchestrations in your BizTalk Server system.

   The Status column displays information about the current state of a specific instance. If one of the following states is displayed, you should take action immediately:

   • Suspended (resumable)
     
   • Suspended (non-resumable)
     
   • In breakpoint
     

   If you see the In breakpoint state, a breakpoint has been set at the class level of an orchestration. This means that if the computer is a production computer, every time that the orchestration is called, it stops where the breakpoint has been set and waits for you to open it in the debugger. As you can imagine, this dramatically affects performance.

• Scheduling. Daily
  
• Required privileges. BizTalk Administrator
  
• Tools. To open the BizTalk Administration console, click Start, point to Programs, point to Microsoft BizTalk Server 2004, and then click BizTalk Server Administration.
  

The BizTalk Administration console is the primary application for starting, stopping, configuring, and administering an operational BizTalk Server system. Also, when performing availability monitoring, the BizTalk Administration console provides a quick status of the following:

• Hosts
  
• Host instances
  
• Receive locations
  
• Send ports
  
• Send port groups
  
• Orchestrations
  
• Servers
  
• Adapters
  
• Business Activity Monitoring Event Bus statistics
  

The Event Viewer snap-in is included in the BizTalk Administration console. This lets you move from an event in Event Viewer directly to the problem area in BizTalk Server.

For more information about using the BizTalk Administration console, see the BizTalk Server documentation.

• Scheduling. Business Activity Monitoring (BAM) schedules vary on a solution-by-solution basis. Because BAM processes data in an ongoing manner, you should work with business users and developers to create BAM views.
  
• Required privileges. You must be a BizTalk Administrator to configure BAM; however, for monitoring only, you can customize privileges on a view-by-view basis.
  
• Tools. For monitoring with BAM, you need a Web browser and the Office Web Components version 10.
  

BAM is the business-side complement to Health and Activity Tracking (HAT). Where HAT helps you analyze the health of your underlying system, BAM helps you analyze the more abstract business processes that exist in your solution.

At first, BAM appears to be a tool that only a business user would use; however, BAM also serves well as an administrative tool. For example, where a business user may want to see how many times a particular purchase order process has been started, an administrator can insert a simple measurement or dimension and use the tool to see how long the operation takes to complete. With this small adjustment, an administrator can now see and monitor the BizTalk solution, not just the system. Because business solutions vary greatly, you must work with your developer to make sure that the appropriate dimensions are in place.

The workflow for BAM typically follows these stages:

1. The business user defines the dimensions and measurements in Microsoft Excel.
   
2. You (the BizTalk administrator) add dimensions and measures to the Excel file before giving it to the developer. To better understand the orchestrations, work with the developer so that you can determine what dimensions and measures will help you monitor most effectively.
   Of particular interest with any orchestration are the points where the orchestration connects to another computer or process. Often these points either are or become bottlenecks for many systems. By using BAM to monitor the response times for these connections, you can see trends and proactively deal with issues before they become problems.
   
3. The developer uses the Excel file and the Tracking Profile Editor to link the dimensions and measures to the solution's orchestrations and deploys the profile.
   
4. You use the BAM Manager (bm.exe) to generate the OLAP cubes and views that the business user can see on the Business Activity Services (BAS) site.
   

For more information about BAM, dimensions, measurements, OLAP cubes, and so on, see the BizTalk Server documentation.

• Scheduling. Schedules are built into MOM.
  
• Required privileges. Privileges are specific to each management pack.
  
• Tools. Microsoft Operations Manager (MOM)
  

One tool you should consider when you build out your monitoring system is Microsoft Operations Manager (MOM) and the downloadable BizTalk Server 2004 Management Pack. Although discussion of the intricacies of MOM and the management pack is beyond the scope of this document, some description is appropriate.

MOM helps simplify identification of issues, streamlines the process for determining the root cause of a problem, and makes it easier to quickly restore services and to prevent potential IT problems. You can set rules that analyze a set of monitoring data and prescribe actions to take when the rule criteria are met. The actions can be alerts, system remedies, or other actions.

MOM management packs are packs of rules that you import into MOM. These packs, which are designed and built by software developers, aggregate data like events in Event Viewer. After the data is aggregated, the rules more effectively recommend steps to resolve the problem. Each rule in a management pack has information from the developer regarding the recommended steps to take for resolution.

One additional small but important feature of MOM is that of event consolidation. Often a single error in a system causes a glut of event messages in Event Viewer. These events, though informative, can distract you from the real problem in your system. MOM, together with the management packs, consolidates these error messages into smaller, actionable chunks for more focused troubleshooting.

Both Microsoft SQL Server and Microsoft BizTalk Server have management packs that have many useful rules, which relieve you of the laborious tasks of repeated monitoring.

For more information about Microsoft Operations Manager, see the MOM Web site. This site also contains a link for downloading management packs.

This section discusses the database tables that provide information about the MessageBox. The following table lists these database tables.

Table 4 Database tables for analyzing the MessageBox

The following scripts help you monitor your BizTalk MessageBox databases.

Caution
Making modifications to the BizTalk Server SQL databases is a high-risk activity that should only be performed by a SQL Server expert.

Some of the concepts you should be familiar with include the following:

• The four isolation levels
  
• The difference between a clustered and non-clustered index
  
• Knowledge of deadlocks
  

If you are unfamiliar with these concepts, ask your SQL Server administrator for help. Although the following procedures should not have any detrimental effects, you should have a recent backup of your database.

Guidelines

SQL Script: Orchestration Instances Grouped by Status

SQL Script: Host Queue Counts

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW

CREATE TABLE #Temp (AppQueue nvarchar(256), QueueSize int)
declare @nvcAppName sysname

declare MyCursor CURSOR FAST_FORWARD FOR
SELECT nvcApplicationName FROM [BizTalkMsgboxDb]..[Applications] WITH (NOLOCK)

open MyCursor
FETCH NEXT FROM MyCursor INTO @nvcAppName

WHILE (@@FETCH_STATUS = 0)
BEGIN
    INSERT INTO #Temp
    exec ('SELECT ''' + @nvcAppName + 'Q'', COUNT(*) 
FROM [BizTalkMsgBoxDb]..[' + @nvcAppName +'Q] 
WITH (NOLOCK)')
    
INSERT INTO #Temp
exec ('SELECT ''' + @nvcAppName + 'Q_Suspended'', COUNT(*) 
FROM [BizTalkMsgBoxDb]..[' + @nvcAppName +'Q_Suspended] 
WITH (NOLOCK)')
   
INSERT INTO #Temp
exec ('SELECT ''InstanceStateMessageReferences_' + @nvcAppName + ''', COUNT(*) 
FROM [BizTalkMsgBoxDb]..[InstanceStateMessageReferences_' + @nvcAppName + '] 
WITH (NOLOCK)')
    
FETCH NEXT FROM MyCursor INTO @nvcAppName
END  
SELECT * FROM #Temp ORDER BY QueueSize DESC

close MyCursor
deallocate MyCursor
DROP TABLE #Temp

SQL Script: Number of State Messages Grouped by Instance

SQL Script: Count of Active Messages for a Specific Send Port

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW

CREATE TABLE #Temp(MaxRetries int, Active int, SendPort nvarchar(256), IsPrimaryTransport int)

declare @nvcAppName sysname
declare MyCursor CURSOR FAST_FORWARD FOR

SELECT nvcApplicationName NameFROM [BizTalkMsgBoxDb]..[Applications] WITH (NOLOCK)
open MyCursor
FETCH NEXT FROM MyCursor INTO @nvcAppName

WHILE (@@FETCH_STATUS=0)
BEGIN
INSERT INTO #Temp
exec('SELECT MAX(q.nRetryCount) AS MaxRetries,
COUNT(*) AS Active,
sp.nvcName AS SendHandlersHostServiceName,
spt.bIsPrimary AS IsPrimaryTransport 
FROM [BizTalkMsgBoxDb]..[' + @nvcAppName + 'Q] as q 
WITH (NOLOCK) 
INNER LOOP JOIN [BizTalkMsgBoxDb]..[Subscription] as s 
WITH (NOLOCK) ON q.uidServiceID = s.uidServiceID 
AND s.uidPortID = q.uidPortID
INNER LOOP JOIN [BizTalkMgmtDb]..[bts_sendport] AS sp 
WITH (NOLOCK) ON q.uidServiceID = sp.uidGUID
INNER LOOP JOIN [BizTalkMgmtDb]..[bts_sendport_transport]
AS spt 
WITH (NOLOCK) ON sp.nID = spt.nSendPortID 
AND spt.uidGUID = q.uidPortID
GROUP BY sp.nvcName, s.uidPortID, spt.bIsPrimary
ORDER BY SendHandlersHostServiceName ASC')

FETCH NEXT FROM MyCursor INTO @nvcAppName
END
SELECT * FROM #Temp ORDER BY Active DESC

close MyCursor
deallocate MyCursor
DROP TABLE #Temp

SQL Script: Most Recent Duration for BizTalk Server SQL Server Agent Jobs

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW

CREATE TABLE #Temp (JobID uniqueidentifier,JobName sysname,Status int,DateStarted int,TimeStarted int,Duration int,OpEmailed nvarchar(20),OpNetSent nvarchar(20),OpPaged nvarchar(20),Retries int,Server nvarchar(30))

CREATE TABLE #Final (MessageBox sysname,JobName sysname,DateStarted int,TimeStarted int,Duration int,Status int)

declare @dbName sysname
declare MyCursor cursor FAST_FORWARD FOR
select DBName FROM [BizTalkMgmtDb]..[adm_MessageBox] WITH (NOLOCK)

open MyCursor

FETCH NEXT FROM MyCursor INTO @dbName
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO #Temp
exec ('[msdb]..[sp_help_jobhistory] @job_name = ''MessageBox_DeadProcesses_Cleanup_' + @dbName + '''')

INSERT INTO #Final
SELECT TOP 1 @dbName, JobName, DateStarted, TimeStarted, Duration, Status FROM #Temp
TRUNCATE TABLE #Temp    

INSERT INTO #Temp
exec ('[msdb]..[sp_help_jobhistory] @job_name = ''MessageBox_Message_Cleanup_' + @dbName + '''')
INSERT INTO #Final
SELECT TOP 1 @dbName, JobName, DateStarted, TimeStarted, Duration, Status FROM #Temp
TRUNCATE TABLE #Temp    

INSERT INTO #Temp
exec ('[msdb]..[sp_help_jobhistory] @job_name = ''MessageBox_Parts_Cleanup_' + @dbName + '''')
INSERT INTO #Final
SELECT TOP 1 @dbName, JobName, DateStarted, TimeStarted, Duration, Status FROM #Temp
TRUNCATE TABLE #Temp    

INSERT INTO #Temp
exec ('[msdb]..[sp_help_jobhistory] @job_name = ''PurgeSubscriptionsJob_' + @dbName + '''')
INSERT INTO #Final
SELECT TOP 1 @dbName, JobName, DateStarted, TimeStarted, Duration, Status FROM #Temp
TRUNCATE TABLE #Temp    

INSERT INTO #Temp
exec ('[msdb]..[sp_help_jobhistory] @job_name = ''TrackedMessages_Copy_' + @dbName + '''')
INSERT INTO #Final
SELECT TOP 1 @dbName, JobName, DateStarted, TimeStarted, Duration, Status FROM #Temp
TRUNCATE TABLE #Temp    

FETCH NEXT FROM MyCursor INTO @dbName
END

SELECT * FROM #Final ORDER BY MessageBox

close MyCursor
deallocate MyCursor

drop table #Temp
drop table #Final