Chapter 6: SQL Server Service Broker

The SQL Server Service Broker is a new subsystem that adds guaranteed asynchronous queuing support to SQL Server 2005. Asynchronous queuing adds a dimension of scalability to SQL Server 2005. Asynchronous queuing is found in many other highly scalable applications, including the operating system’s I/O subsystems, web servers, and even the internal operations of the SQL Server database engine itself. The addition of asynchronous queuing to SQL Server 2005 brings the capability of handling asynchronous queuing to end-user database applications as well. Asynchronous queuing is an important factor for scalability because it allows a program to respond to more requests than the platform may be able to physically handle. For instance, in the case of a web server if ten thousand users simultaneously requested resources from the server, without asynchronous queuing the web server would be overwhelmed as it attempted to launch threads to handle all of the incoming requests. Asynchronous queuing enables all of the requests to be captured in a queue so that instead of being overwhelmed, the web server can process entries from the queue at its maximum levels of efficiency. In the case of the web server, asynchronous queuing enables the server to effectively handle a far greater number of user connections than would otherwise be possible. The SQL Server Service Broker enables you to build this same type of scalability into your database applications.

In this chapter, you’ll learn about the new features provided by the SQL Server Service Broker. You’ll get an overview of the new subsystem and learn about its core components. Then you’ll see the basics of how you create SQL Server Service Broker applications. Here you learn about the SQL Server Service Broker’s metadata and its programming model. First, you’ll see how to create message types and queues. Then, you’ll see how to use the new T-SQL commands that enable messaging applications to add entries to a queue as well as receive the entries that have been added to a queue. Finally, you’ll learn about some of the administrative features found in the SQL Server Service Broker subsystem.

SQL Server Service Broker Overview

The SQL Server Service Broker adds the ability to perform asynchronous queuing to SQL Server 2005. The new queuing capability is built into the SQL Server engine and is fully transactional. Transactions can incorporate queued events and can be both committed and rolled back. You can access the SQL Server Service Broker using new SQL statements. Examples of these commands will be presented in the next section of this chapter. In addition, the new SQL Server Service Broker also supports reliable delivery of messages to remote queues. This means that queuing applications built using the SQL Server Service Broker can span multiple SQL Server systems and still provide guaranteed message delivery—even to remote queues. The messages that are sent across the queues can be very large, up to 2GB. The SQL Server Service Broker will take care of the mechanics required to break apart the large messages into smaller chunks that are sent across the network and then reassemble at the other end. There are both 32-bit and 64-bit versions of the SQL Server Service Broker.

Queue Application Design

While the idea of queuing in applications may be a bit foreign to most database designers, queues are common in highly scalable applications. One of the most well-known of these types of applications is the airline reservation systems used by all major airlines such as United, Delta, and American, as well as by travel brokers such as Expedia and CheapTickets.com. To get an idea of how queuing is used in one of these applications, you can refer to Figure 6-1, where you can see the design of a sample queued application.

Figure 6-1: Queued application design

Figure 6-1 presents a high-level overview of an example airline reservation system. Here you can see that the application’s presentation layer is delivered to the end user’s browser by an application running on a web farm. That application could be written using ASP.NET or some other web development language. The front-end application will then interact with the actual reservation system that’s normally running on another system.

Because applications like these must support thousands of simultaneous users, they can’t afford to lock rows while a given user waits to decide on the final details of a flight or even starts a reservation and then goes to lunch, planning to finish later. Row locking in this type of scenario would seriously inhibit the application’s scalability and even the application’s usability. Queuing solves this problem by enabling the application to make an asynchronous request for a reservation; it sends the request to the back-end reservation system and is immediately freed for other work. At no point in the process of placing the reservation have any locks been placed on the database tables. The back-end reservation system, which is essentially operating in batch mode, will take the reservation request off the queue and then perform the update to the database. Since the update is being done in batch mode, it happens very quickly with no user interaction and minimal time is needed to lock rows while the update is performed. If the request is successful, the end user’s reservation is confirmed. Otherwise, if the request is denied because all of the seats were booked or for some other reason, then the reservation will not be accepted and the user will be contacted with the status.

Dialogs

Dialogs are an essential component of Microsoft’s new SQL Server Service Broker. Essentially, dialogs provide two-way messaging between two endpoints. The endpoints for these messages can be two applications running on different servers or instances, or they can be two applications running on the same server. Figure 6-2 illustrates the SQL Server Services Broker’s dialog.

Figure 6-2: SQL Server Service Broker dialog

The main purpose of a SQL Server Service Broker dialog is to provide an ordered sequence of events. The SQL Server Service Broker dialogs maintain reliable event ordering across servers or threads even if there are network, application, or server failures that temporarily disrupt the processing of the queued events. When the queue processing is restored, the events will continue to be processed in order from the point of the last processed queued event. Dialogs enable queued messages to always be read in the same order that they are put into the queue. Dialogs can be set up to process events in either full-duplex mode or half-duplex mode.

Conversation Group

Another core component of the SQL Server Service broker subsystem is the conversation group. A conversation group is a set of related SQL Server Service Broker dialogs. An application might require multiple dialogs to complete a task, and a conversation group enables you to logically group together all of those related dialogs. For instance, an order entry application might have one dialog to process orders, another to process inventory, another to handle shipping, and yet another to handle billing requests. All of these related dialogs can be grouped together to form a conversation group. In this case, the conversation group basically represents all of the dialogs for an application. Figure 6-3 shows the relationship of the conversation group and the SQL Server Service Broker dialog.

Figure 6-3: SQL Server Service Broker conversation group

The main purpose behind the conversation group is to provide a locking mechanism for related queues. In this sense, it’s important to understand that the locking is not applied to any underlying database tables. Instead, the locking that’s enabled by the conversation group is applied to entities in the queues. In the case of an order, this might mean that no process can read the orders for any of the queues in your conversation group while your application holds a lock on those entries.

In addition to locking, the conversation group also enables the application to maintain state. Maintaining state is always one of the most difficult aspects of an asynchronous application because there can be lengthy time delays between the arrival of messages. Keeping active threads open during that time period isn’t an option because all the unused threads would consume excessive system resources and limit scalability. Instead, the conversation group maintains the state of an entity using a state table. The conversation group uses an instance ID (a GUID) as the primary key for identifying the entries in the group of queues.

SQL Server Service Broker Activation

SQL Server Service Broker activation is another unique feature of the SQL Server Service Broker subsystem. Activation enables you to create a stored procedure that is associated with a given input queue. The purpose of the stored procedure is to automatically process messages from that queue. As each new message comes in, the associated stored procedure is automatically executed to handle the incoming messages. If the stored procedure encounters an error, it can throw an exception and be automatically recycled.

Periodically, the SQL Server Service Broker checks the status of the input queue to find out if the stored procedure is keeping up with the incoming messages on the input queue. If the SQL Server Service Broker determines that there are waiting messages on the queue, then it will automatically start up another instance of the queue reader to process the additional messages. This process of automatically starting additional queue readers can continue until the preset MAX_QUEUE_READERS value is reached. Likewise, when the SQL Server Service Broker determines that there are no remaining messages on the queue, it will begin to automatically reduce the number of active queue readers.

SQL Server Service Broker queues don’t necessarily need to be associated with stored procedures. Messages that require more complex processing can also be associated with external middle-tier procedures. Since these middle-tier processes are external to the database, they need to be activated differently. To enable the automatic activation of external processes, the SQL Server Service Broker also supports firing a SQL Server event. These events can be subscribed to using WMI (Windows Management Instrumentation). In this case, when an event comes into the queue the SQL Server event is fired and read by the WMI subscriber, which in turns starts up the external queue reader.

Message Transport

The SQL Server Service Broker message transport protocol enables messages to be sent across the network. The SQL Server Service Broker message transport is based on TCP/IP, and the overall architecture of the SQL Server Service Broker message transport is a bit like the architecture used by TCP/IP and FTP. The SQL Server Service Broker Message transport is composed of two protocols: the Binary Adjacent Broker protocol, which is a lower-level protocol like TCP, and the Dialog protocol, which is a higher-level protocol like FTP and rides on top of the lower-level Binary Adjacent Broker protocol.

Binary Adjacent Broker Protocol

The Binary Adjacent Broker protocol is a highly efficient low-level TCP/IP protocol that provides the basic message transport. It is a bidirectional and multiplexed protocol and so can handle the message transport for multiple SQL Server Service Broker dialogs. The Binary Adjacent Broker protocol doesn’t worry about message order or confirming message delivery. That’s all handled by the Dialog protocol. Instead, the Binary Adjacent Broker protocol simply sends messages across the network as quickly as it can.

Dialog Protocol

The Dialog protocol handles end-to-end communications for a SQL Server Service Broker dialog. Designed to provide one-time-only in-order delivery of messages, it handles sending messages and acknowledging them. It also provides symmetric failure handling, where both end nodes are notified of any message delivery failures. In addition, the Dialog protocol is responsible for authentication and encryption of messages.