Biztalk Server

 

Introduction

 This article is the first one part of a series of articles intended to illustrate the principles and applications of Microsoft Biztalk Server. The integration of enterprise applications have been a desirable goal of many organizations for years. With the consolidation of concepts (Service-Oriented Architecture-SOA, Message-Oriented Middleware-MOM), methodologies (Business Process Management-BPM, Business Process Reengineering) and standards (XML, XSD, Rosetta.NET) as well as the advent of emerging technologies such as Web Services stack of protocols (WS-*), Enterprise Service Bus (ESB) and others, then vendors have released their own solutions and thus organizations have achieved their challenging goals of automation of business process by the integration of their Information Systems. In this arena, we can find fundamentally two important products: Microsoft Biztalk Server 2004/2006 and Oracle SOA Suite.

 

What is Microsoft Biztalk Server?

 Microsoft Biztalk Server is the Microsoft's premiere server for developing IS integration's solutions to support and automate the business processes in organizations.

 

Biztalk Server architecture comprises two major components:

• Messaging
• Orchestration

 

 

 

Figure 1: Biztalk Server 2006 major components.

 

The messaging component allows the communication with several systems and platforms through the use adapters which implements the underlying protocol rules and data formats. It listens for messages to arrive, route them to the appropriate target systems and then sends them. This is the bottom layer and the heart of the Biztalk engine. All other components in Biztalk Server such as orchestration, Enterprise Single Sign-On, Business Activity Services, Business Activity Monitor, Business Rules Engine and Health and Activity Tracking depend heavily on this layer.

 

The orchestration component allows the creation, execution and management of business processes called orchestrations. It is built on top of the messaging components, therefore consuming its services such as the orchestration's communication outside the engine for the transportation and routing of messages. This component handles the state of each orchestration as well as its correlation with external business entities.

 

Now, I am going to explain the basic flow of messages in Biztalk Server engine as shown in Figure 2. The engine listens for messages by Receive Port. A message is an entity which comprises a header storing information about communication aspects and specific metadata as well as a body which conveys payload such as business entities. The information interchange with external systems is done by adapters which implement the necessary protocol mechanism. The wire message is received in its native format, but Biztalk engine normalizes the schemas of the internal messages using XML as the lingua franca. This task is done by the pipeline component and its sub-components specifically the disassembler component. Then internal XML message is published in the MessageBox. Subscribers consume these internal messages. The main subscribers are the Send Port and Orchestration components which subscribe to internal messages according to their metadata or message context properties. When the underlying subscribed orchestration finished processing the internal messages then it publishes them again in the MessageBox to be consumed by other subscribers. Finally, when one Send Port consumes the internal XML messages, then converts these internal messages into a wire messages according to the schema and format of the intended recipient through the associated pipeline and specifically the subcomponent Assembler. Finally the Send Port uses the underlying adapter to convey the wire message to the target system as shown in Figure 2.

 

 

 

Figure 2: The basic flow of message in Biztalk Server engine.

 

Conclusion

 

We have got acquainted with the architecture and main flow of message in Microsoft Biztalk Server. The following articles will explain the architecture components with more details. And finally, we're going to orchestrate a business process as an illustrative example.

 

Capture Missing Information From SQL Profiler

While SQL Profiler is a very helpful (and even invaluable) tool, it does not always provide all of the information needed to find the root cause of certain problems. If ObjectId, ObjectName, and TextData are all empty / NULL, then all you are left with is the knowledge that the event you are looking for occurred. Knowing only that the event occurred will only help in a controlled environment where you caused the event but not in production where something is happening sporadically.

Take for instance the particularly squirrelly issue of having tempdb grow at an alarming rate, far more than usual and taking up a lot more space than usual. The problem you are dealing with here is determining what is taking up the space. This can be a difficult task since since tempdb space is not always taken up only by temp tables. Many operations use tempdb as work space explicitly, such as Static and Keyset Cursors, as well as DBCC CHECKDB. Other operations use tempdb when needed (typically when there is not enough space in memory to complete the operation or store the data), such as when using DISTINCT, ORDER BY, GROUP BY, AFTER triggers, table variables, table returned in table-valued-functions, hash joins and hash aggregate operations, and when (re)creating Indexes with the SORT_IN_TEMPB option. Additionally, in SQL Server 2005, new features will also use tempdb, such as snapshot isolation level transactions and Multiple Active Results Sets (MARS). And there may be even a few more. That's a lot of possibilities of what can be contributing to tempdb growth!

So first we check to see if that data is in tables or indexes (as that might point to a source of the problem) via the reserved column of tempdb.dbo.sysindexes (be sure to divide the value in that field by 128.0 to see how much it is in Megabytes). Of course, it is very possible that very little of the data that is causing the problem is in tables given how many operations use tempdb. If this is the case we have to find out what is using tempdb. For this we can run SQL Profiler and trap events such as: Sort Warnings (indicates that a sort could not fit in memory) and Data File Auto Grow. Sort Warnings will show us many of the operations that are using tempdb in general and the Data File Auto Grow will indicate which operations are using so much data that the database has to increase the size of tempdb. Unfortunately, the SQL (statement or object) that caused either event is not recorded in the Profiler output; both TextData and ObjectId fields are empty. How unhelpful is that?
Triggers, Temp Tables, and Input Buffers, Oh My!

All is not lost, though, as we can augment the Profiler results saved to a table; we will just add a trigger to store information in the TextData field. While it doesn't matter entirely which fields you choose to report on, you must at least pick: EventClass, DatabaseID, SPID, and TextData. You should also select: EventSubClass and ApplicationName. When you run the trace, be sure to save the results to a table. After you click on "Run" and the trace starts, execute the following (assuming that you have replaced {db_name_here} in the USE statement with whatever database you are storing the trace results in). The script assumes a table name of TraceTable to store the results in. Just change the name in the ON and FROM clauses if you choose a different table name.

Please note that this has only been tested on SQL Server 2000.

USE [{db_name_here}]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[TraceTableExtraInfo]
  ON [dbo].[TraceTable]
  AFTER INSERT
AS
BEGIN
  SET NOCOUNT ON;


  DECLARE @SQL VARCHAR(40)

  SELECT @SQL = 'DBCC INPUTBUFFER(' + CONVERT(VARCHAR(10), ins.SPID) + ')'
  FROM   inserted ins

  CREATE TABLE #DBCCInfo (EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(255))

  INSERT
  INTO #DBCCInfo (EventType, Parameters, EventInfo)
    EXEC(@SQL)


  UPDATE tt
  SET    tt.TextData = info.EventInfo
  FROM   dbo.TraceTable tt
  INNER JOIN   inserted ins
         ON   ins.RowNumber = tt.RowNumber
  CROSS JOIN  #DBCCInfo info
  WHERE  tt.TextData IS NULL

  DROP TABLE #DBCCInfo
END


 

You can then see the data from the trace with the following code:

SELECT * FROM working.dbo.TraceTable

Now there are two minor issues with this approach:

1. You have to create this trigger each time you run the trace. This includes each time you re-run the trace, even when saving to the same table, as it will drop and recreate the table each time. The dropping of the table in turn drops the trigger.
2. The output from DBCC INPUTBUFFER is not guaranteed to be accurate; it might be what is in the buffer after the event was caught by SQL Profiler and the row was inserted into the table. For the operations of Sort Warnings and Auto Grow it seems safe enough to assume that in the majority of cases the process that triggered that event will still be running by the time DBCC INPUTBUFFER runs since the processes needed the resource (either tempdb in the case of a Sort Warning or more File space in the case of an Auto Grow). But for the most part, if there is a real problem then you will clearly see a pattern between the event and what is in the buffer when the event is triggered.

Please note that while the example situation shown here is looking for tempdb and file growth issues, this method can be used on any Profiler event that does not return data in the TextData field. For those events that do return TextData values there is the WHERE condition in the UPDATE statement that ensures that if the TextData field is populated by Profiler, it will not be over-written by this trigger.