Message Received

The excited chatter about SQL Server 2005's Common Language Runtime (CLR) integration and other new features has all but eclipsed another very powerful component: SQL Server Service Broker. Service Broker, a database-application framework that lets internal or external database-related processes send messages to and receive them from each other, provides a great new way to implement database-oriented middleware and distributed database applications. It uses extensions to T-SQL and (through embedded CLR support) standard .NET programming languages to provide guaranteed, reliable, asynchronous message delivery and confirmation. Service Broker uses the SQL Server 2005 (formerly code-named Yukon) database engine and extensions to SQL Server triggers to implement this service. It also uses developer-defined message queues that are housed inside the databases. Using database-resident message queues provides several benefits, such as transactional integrity, which I cover later in the article.

One of my client companies spent years implementing a middleware solution that lets them automate the collection of external data and update distributed corporate databases. Service Broker will eliminate much of that type of work or at least make it a lot easier. Service Broker lets you define Messages, data Queues, and Dialogs between instances of SQL Server. Because these definitions reside inside instances of SQL Server, Service Broker can take advantage of SQL Server's inherent transactional nature. Also, having everything implemented inside of distributed SQL Servers solves one of the more difficult problems in messaging: guaranteeing that a message sent from one endpoint is delivered to the receiving endpoint once and only once. SQL Server Service Broker guarantees ordered, one-time, asynchronous delivery of messages. Few other messaging systems (including Microsoft Message Queue Services—MSMQ) can make this claim.

Let's take a quick look at how Service Broker works before drilling into the details. When an application program wants to send a message to a program or stored procedure running in another instance of Service Broker (which could be running in the same or another instance of SQL Server), the application program at the originating location simply invokes a local stored procedure that the DBA defines as a Service. The stored procedure then issues a Send request containing the data to send. Service Broker subsequently puts this data in a local queue for sending and returns control to the sending application. Later, the receiving Service Broker retrieves the data from the queue and sends it to the receiving location. At the receiving location, several things happen. First, the receiving Service Broker places the data in a queue. If the stored procedure that was defined to process this message is already active, it can dequeue the message, process it, and send a reply. If that stored procedure isn't active, an incoming message can start it. If the rate of message arrival is higher than a single instance of the stored procedure can handle, multiple instances of the stored procedure will start up to handle the backlog of messages that need processing. These additional message-processing procedures terminate after a program-determined interval during which no new messages have arrived. This automatic termination provides a high level of scalability. Now let's look at the architecture that makes this process possible.

Service Broker Architecture

A set of new Data Definition Language (DDL) and Data Manipulation Language (DML) statements in SQL Server 2005 manage Service Broker's messaging process. Figure 1 shows the Service Broker architecture. Service Broker includes several new terms and entities that define and manage the message-brokering process. Let's look at these terms and what they mean.

Contracts. A Contract defines the types of Messages that the Service Brokers at two endpoints can send and receive. It also specifies which Messages each endpoint can use.

Messages. A Message defines the data that a stored procedure can send and receive. A Message includes a unique message-type identifier along with the data to be transferred in the body of the Message. Messages can contain binary data, valid XML, or even XML that references a specific XML schema. Service Broker throws an error if the XML doesn't match the referenced schema or is invalid in any way.

Dialogs. Messages are the building blocks of Dialogs. Dialogs are one- or two-way Message interactions that involve Messages sent between Service Brokers. The endpoints can be multiple SQL Server databases in the same SQL Server instance, multiple SQL Server instances running on the same machine, or SQL Server instances running on other local or remote machines.

A Dialog lasts from the time the sending Service Broker receives the request and creates the Dialog until no more Messages remain for the receiving Service Broker to process or until an error occurs. A Dialog also supports crisp failure semantics. Service Broker transactions across multiple endpoints are atomic in that a failure at any point causes Service Broker to notify both endpoints of the failure and roll back the entire transaction. The net result of this process is that when an error occurs, Service Broker puts Messages that it has removed for processing back into the queue.

Conversations. Just as Messages are the building blocks of Dialogs, multiple Dialogs can be grouped together (at the time that they're defined) into Conversations. A Conversation is a long-running, reliable, asynchronous exchange of Messages between two endpoints. It can involve multiple Dialogs.

Conversation Groups. Every Conversation is part of a Conversation Group. A Conversation Group is a group of Dialogs that perform some set of related tasks, usually to implement a business function. An example is the order-fulfillment process that needs to begin after an order-entry application accepts an order. The originating Service Broker assigns a Conversation Group ID (CGID) to each Conversation Group. This CGID identifies a group of Conversations that work together to accomplish a distinct task. The CGID is also a globally unique ID (GUID) that the receiving procedure can use as a database table key in case the application needs to maintain any kind of application state related to processing the Message. If maintaining state is a requirement, the database is the natural place to store this state, and the application can use the CGID as the key of a table that stores the state information.

From the database standpoint, a Conversation Group defines as a unit a grouping of Dialogs that in turn define a logical unit of work that Service Broker can complete or roll back. For example, an order-entry application might have three separate processes: order entry, order billing, and order shipping. These three entities define a Conversation Group. With this architecture, you can accept and validate an order while the customer is on the phone. Then, the order-entry subsystem can engage in Dialogs with the billing and shipping subsystems in parallel and asynchronously. When each of those processes completes, they can send Messages back to the order-entry subsystem, indicating that the transaction is complete and that SQL Server can commit it.

Queues. Queues store Messages at the originating location before transmission and at the destination before processing. Service Broker uses both send and receive Queues to protect the end-to-end integrity of Messages. Queues hold multiple sets of related Message types.

Service Programs. Service Programs are responsible for performing the processing logic associated with writing and reading a Message in the Queue. A Service Program can be either a stored procedure in the target database or an external program. An incoming Message can activate the Service Program, or it can be started by some event external to SQL Server such as a program execution request issued by a scheduled task. Service Programs run as part of each SQL Server 2005 instance.

One major benefit of this architecture is its integration of the messaging system's management and operation within the database. Thus, when you're backing up and recovering the database or transporting the database to another system, the state of the Queues and of the application automatically travels with the database, and the same procedures that protect the database's integrity can protect the Queues. This means that the Queues are backed up, recovered, and so on along with the database data. This integration with the database automatically takes advantage of advanced database features such as log shipping, database mirroring, and failover clustering.

Routing and Load Balancing

Although Conversations and Dialogs move Messages between two Service Broker instances, the process might involve several intermediate Service Brokers. For example, the originating Service Broker (SB1) in Figure 2 sends a Message to the receiving Service Broker (SB4 or SB5). However, this Message might go through one or more intermediate Service Brokers, such as SB2 and SB3, before reaching the target Service Broker.

The advantage of this architecture over a classical message-handling architecture is that the Service Brokers can route encrypted data to the next Service Broker in a chain without having to fully decrypt the data in transit. In contrast, an external messaging system such as MSMQ has to decrypt, analyze, re-encrypt, then forward the message at each intermediate point. This ability to route encrypted data without decrypting it makes the Service Broker solution more efficient.

You can also use Service Broker to improve scalability by implementing load balancing. By defining several target Service Programs, you can spread the messaging load across multiple Service Brokers at multiple target locations.

An Example

A short example involving the aforementioned order-entry application illustrates how the messaging process works. To keep the example simple, let's focus on the entry of an order into the order-entry subsystem and the printing of a pick list at the warehouse as part of the order-fulfillment subsystem. Figure 3 shows the physical configuration of the application.

The order-entry application enters the order and checks inventory at the front end while the customer is still on the phone with the order-entry clerk. When the order-entry process is complete, you need to send the order to the warehouse for fulfillment processing. This warehouse process involves printing a pick list, using the pick list to pull the products in the order, then packing and shipping the products.

Before invoking any application code, you have to write Service Programs, and a developer or administrator has to set up database structures to define Messages, Dialogs, Conversations, and Conversation Groups. You need to add these definitions to SQL Server at the sending and receiving Service Broker locations.

Figure 4 shows schematically the Order Entry and Pick List Service Programs. The Order Entry Service Program simply begins a transaction, initiates a Dialog with the Pick List Service Program, sends a Message to it over the Conversation, then commits the transaction. All processing of the Message then occurs asynchronously, leaving the original program to continue with other processing. As I mentioned, the Pick List Service Program is either pre-started by some outside SQL Server transaction or process or started by an incoming message. Figure 4 shows the Internet/intranet and Service Broker infrastructure as a cloud connecting the two Service Programs.

Like a Windows application, a typical Service Broker application follows a standard message-processing loop design. On startup, the program enters an endless loop, waiting for Messages to process. This loop is broken only when one of three things occurs: Either the program receives a Message, a preset timeout interval expires with no Messages received, or the program terminates based on the number of Messages it has processed or some other application-specific criteria.

The first thing the procedure does in the message-processing loop is issue a database BEGIN TRANSACTION operation. This operation protects the entire Message send-and-receive process in a transaction and lets Service Broker automatically replace the Messages in the Queue if a failure or rollback occurs.

After starting a transaction, the procedure waits for either a Message or the expiration of the timeout interval. If a timeout occurs, the program simply rolls back the transaction and terminates. If a Message comes in, the procedure processes it. In this case, the application logic does whatever is necessary to satisfy the request and may return a response Message to the originator or other cooperating application. Finally, after processing the Message, the program ends the Conversation, commits the transaction, and continues waiting for another Message or for a timeout to occur.

Service Broker's Place in the Middleware World

Now that you see how Service Broker works, one natural question is, How does Service Broker compare with other middleware products such as MSMQ? Microsoft's answer to this frequently asked question is that Service Broker deals with transactional delivery of messages between multiple databases. MSMQ, on the other hand, lets applications send messages between distributed programs where a database might not be involved. You should use Service Broker whenever you have distributed, multidatabase SQL Server applications that perform such tasks as

I've only scratched the surface of Service Broker's capabilities. Service Broker supports strong security features that I didn't discuss in this article. It also comes with performance-monitoring and troubleshooting functionality and many other capabilities that wouldn't fit in this overview article. I hope this introduction motivates you to dig deeper into SQL Server Service Broker's capabilities and see what it can do for you and for your company.