Components of a Service Broker application in SQL Server 2005

by Tim Chapman | Jan 29, 2007 6:30:00 PM

Tags: Databases, Tim Chapman, Microsoft SQL Server, service program, Microsoft SQL Server 2005, Service Broker, Service Broker application, SQL Server Newsletter

Takeaway: SQL Server 2005 Service Broker applications provide powerfully robust functionality that allow for scalability and asynchronous processing. Tim Chapman takes a look at how you can use Service Broker to model business processes.

Last week, I wrote an introduction to message-based applications in SQL Server 2005. In the article, I briefly mentioned Service Broker, the new component in SQL Server 2005 for building asynchronous message-based applications. A Service Broker application is a collection of one or more programs that execute a set of related task(s). To get a better idea of what that means, let's take a look at the objects that make up the application.

Note: Service Broker is a complex technology with many configurable moving parts. The forthcoming description is a high-level view of the components that comprise a Service Broker application, and is by no means a detailed guide. For more information about Service Broker, visit MSDN.

Message
A message is the basic unit of information transferred in a Service Broker application. Within Service Broker, messages are always received in the order in which they are sent and are guaranteed to only be sent and received one time. Messages are also guaranteed not to be lost. Sometimes a message is sent and cannot immediately be delivered. When this happens, Service Broker saves the message and tries to send it again later. Validations can be placed upon messages to ensure that the data transported by them is what is expected. The maximum size of a message that can be transferred is quite large, 2 GB.

Conversation
When a message is sent in a Service Broker application, it uses a Conversation (or a Dialog). The conversation—which is typically created for a particular task and deleted when the task has been completed—is actually the primary information exchange construct in Service Broker, not the message. Conversations occur between two endpoint services: the service that begins the conversation (the Initiator), and the service that receives the conversation request (the Target).

Queue
In a Service Broker application, messages are stored within a Queue until they are processed. Internally, a Service Broker queue is a special type of table and can be viewed with a SELECT statement on the name of the queue. You cannot, however, issue INSERT, UPDATE, or DELETE statements on a queue. The messages you place in a queue persist even upon a server restart, which guarantees that messages are not lost.

Service
A service program is one that reads messages from a queue and processes them. This service can be ad hoc, a stored procedure, or a different program that connects to the database. Every service must be associated with a queue. As mentioned earlier, conversations occur between services.

Conversation groups
Conversation groups are used to serialize the processing of messages and correlate them. Every conversation is a member of a conversation group.  The main idea is that some messages are related to others, and the conversation group keeps them together in an ordered fashion. In fact, transactions are given exclusive serialized access to all of the messages in a conversation group until the transaction has ended.

There are many other moving parts related to Service Broker applications. The components mentioned above are the main players in the Service Broker game. The more familiar you are with them, the better you will be at writing Service Broker applications. Now let's explore how you can use a Service Broker application to implement business transactions.

Business processing

The tasks in a business process are rarely performed in a synchronous manner. These processes often consist of independent tasks that may occur at the same time, may overlap, and may not be dependent upon other steps in the process. This is often the case with manufactured products, especially custom-ordered purchases, such as automobiles.

When someone orders a custom automobile, the steps taken to create the parts of the automobile are not necessarily dependent upon one another. For instance, many of these parts can be constructed at the same time. However, at the end of the process, they all come together to assemble the automobile. You will encounter:

• Steps in the process that are dependent upon previous steps.
• Steps that may be more critical to the overall success of the project if they encounter a failure along the way.
• Steps that require additional input from the buyer.

In addition to these circumstances, there also must be logic in the process to compensate if the potential buyer cancels the order. You are likely familiar with a type of business process that has some of these characteristics.

When a process such as this is implemented in the database, it is usually implemented as a series of database transactions, with each transaction performing a separate atomic task. While a database transaction is either committed or rolled back, these series of related business transactions usually cannot be accomplished in such a way. They must be designed so that if they fail, logic is used to roll the business transaction back. The entire business process is difficult to perform because these independent transactions are actually related to each other—they comprise one overall goal. This is where a queuing architecture, such as Service Broker, can be of real benefit.

In a Service Broker application, concurrent processing is possible and often desirable. You are able to model your application in such a way to conform to your business process. In our custom automobile example, you could design your application in such a way so that the module that keeps track of chassis construction and the module that keeps track of engine construction occur at the same time. Best of all, the processing of these separate parts are related to each other using conversation groups.

Next time

Now that you understand the characteristics of message-based applications and the components of a Service Broker application, you see how the components can be used to write scalable, asynchronous applications that are able to manage business processes. In my next article, I'll put this logic together and write our own Service Broker application.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.