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.
Prev. page
[1]
2
3
next page 
