Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
Often in software development, a new tool introduces old, reliable, battle-tested techniques to an equally reliable and mature platform. SQL Server 2005 Service Broker is such a technology. Service Broker brings the world of asynchronous messaging to the SQL Server database platform.
Enterprise Integration Patterns by Gregor Hohpe and Bobby Wolf is one of the best-known books on asynchronous messaging. Along with explaining asynchronous messaging, it categorizes various messaging design ideas into groups of software design patterns. According to the book, asynchronous messaging is a form of application-to-application communication that allows different applications with differing throughput and capabilities to reliably exchange data.
By using Service Broker, a few new Transact-SQL (T-SQL) statements, some new concepts, and existing SQL Server knowledge, a T-SQL developer can leverage the power of asynchronous messaging from within SQL Server 2005. Utilizing design patterns with Service Broker development allows you to assess and select appropriate solutions for all of your SQL Server 2005 asynchronous messaging needs.
This article shows you how to use Service Broker to implement some common software patterns from the book Enterprise Integration Patterns. Because a complete introduction to enterprise integration patterns and asynchronous messaging is beyond the scope of this article, it focuses on ideas that are specific to the Publish-Subscribe (PubSub) asynchronous messaging pattern.
Introduction to Integration Patterns
Loose coupling is a familiar term to most software developers. According to Enterprise Integration Patterns, loose coupling entails the following ideas:
- An application’s only requirement is to communicate with the messaging system or a component attached to the messaging system (also called an endpoint or gateway). The messaging system handles safe delivery of the message; such that a message delivered to the messaging system is never lost or removed until it reaches its required destination.
- Applications communicate asynchronously. That is, an application delivers its message and continues to function normally, regardless of whether the consumer has done anything with the message. Likewise, an application receiving the message can dictate how frequently messages are received from the messaging system.
Creating a channel is one of the ways a messaging system implements loose coupling. An application transmitting a message puts the message into a channel, and the application receiving the message reads the message from the channel.
Channels are more than a convenient place to share data. They can be configured to utilize specific message formats and can perform broadcast-like functions to groups of channels as well.
An example of a channel broadcast function and the Publish-Subscribe asynchronous messaging pattern (the pattern this article implements) is the Publish-Subscribe channel. In the pattern, an application (publisher) transmits to a single channel (Publish-Subscribe channel). The Publish-Subscribe channel delivers copies of the message to subscribing channels. Applications receiving messages read from the subscribing channels. The Publish-Subscribe channel ensures that only one copy of the message is sent to each subscribing channel. (See Figure 1, which uses the notation from the book.)
Figure 1. Publish-Subscribe Overview
Service Broker Architecture
The Service Broker performs all the functions of the channel pattern described in the previous section. Figure 2 shows a layout of the Service Broker components.
Figure 2. Layout of Service Broker Components
A contract defines the message format (message type) and the direction of the messaging communication. Message types are based on a schema collection that contains the XML schema of a message.
In a SQL Server database, queues are the physical places that store the messages. A service ties a contract to a queue, and applications interact with a service via a dialog. When a dialog is initiated, an application specifies the initiating service, the recipient service, and the appropriate contract.
As stated previously, all of the channel creation commands are implemented using T-SQL. Because Service Broker is part of the SQL Server Engine, it can participate in T-SQL transactions, an important fact to note when you delve into the coding. (An upcoming section looks at the source code.)
You administrate Service Broker using T-SQL commands and can perform a number of administrative actions using SQL Server Management Studio (see Figure 3).
Figure 3. Perform Administrative Actions Using SQL Server Management Studio
Now, you can get an overview of the PubSub example and find out how to implement the pattern.
The PubSub Example
You’ve seen the pattern perspective of the example. Figure 4 shows the Service Broker implementation of the example along with the appropriate pattern representation (click here to download the source code).
Figure 4. Service Broker Implementation of Publish-Subscribe
Technically speaking, services define a channel better but much of the configuration is done on the queue. So, I used the queue name rather than the service name to refer to the channel.
Whenever a change is made to an employee in the HR database, a message is posted to the TestSSB_PubSub_TargetQueue (//TestSSB/PubSub/Target/Service) (Publish-Subscribe channel). TestSSB_PubSub_TargetQueue writes a copy of the message to an Accounting (//TestSSB/Accounting/Target/Service) and TimeBilling service (//TestSSB/TimeBilling/Target/Service).
Each Subscriber service writes to the target Accounting/TimeBilling database by using a stored procedure.
Define Your Service Broker Contracts and Message Types
The first step to developing a Service Broker application is defining the message formats and the direction of the dialogs. The following is the XML schema for the message used in the example:
<?xml version="1.0" encoding="UTF-16"?>
<xs:schema
elementFormDefault="qualified"
targetNamespace="http://testSSB/EmployeeInfo.xsd"
xmlns_xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Root">
<xs:complexType>
<xs:sequence>
<xs:element name="EmpID" type="xs:string" />
<xs:element name="EmpName" type="xs:string" />
<xs:element name="EmpTitle" type="xs:string" />
<xs:element name="Street" type="xs:string" />
<xs:element name="City" type="xs:string" />
<xs:element name="State" type="xs:string" />
<xs:element name="Zip" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
As previously stated, message schemas are stored in a schema collection. Schema collections are created by using the CREATE XML SCHEMA COLLECTION statement. You can view schema collections by using SQL Server Management Studio (see Figure 5).
Figure 5. View Schema Collections Using SQL Server Management Studio
Message types are based on a schema collection. The CREATE MESSAGE TYPE statement defines a message type. Contracts tie the message type to a communication pattern. In the following statement, the contract dictates that only the dialog initiator can send the message type defined in the example:
CREATE CONTRACT [//TestSSB/Employee/Contract] ( [//TestSSB/Employee/Message] SENT BY INITIATOR)
Now, you’ll look at how to define queues and services.
Define Your Service Broker Queues and Services
Queues and services are even easier to define than contracts and message types. The following are examples of the CREATE QUEUE and CREATE SERVICE statements:
CREATE QUEUE [dbo].[TestSSB_PubSub_TargetQueue] GO CREATE SERVICE [//TestSSB/PubSub/Target/Service] ON QUEUE [dbo].[TestSSB_PubSub_TargetQueue] ([//TestSSB/Employee/Contract]) GO
All messaging is activated by manipulating the queue. For instance, you can be enable, disable, and configure queues to run under different security contexts.
Reading data from a queue is normally performed by a stored procedure that is activated when messages are received in the queue. The following example illustrates queue activation using the ALTER QUEUE statement:
ALTER QUEUE [dbo].[TestSSB_PubSub_TargetQueue]
WITH STATUS = ON,
ACTIVATION (
STATUS = ON ,
PROCEDURE_NAME = PubSub.dbo.p_ReceiveChannel_PubSubMain,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF )
Note: In the preceding statement, pay special attention to the MAX_QUEUE_READERS directive. Under heavy messaging loads, the directive dictates the maximum number of stored procedure instances instantiated to service the queue.
Now, move on to interacting with the services you’ve configured.
Send and Ye Shall Receive
Sending and receiving messages from a queue is straightforward. To send a message, you must initiate a dialog by using the BEGIN DIALOG CONVERSATION statement to create the conversation handle UNIQUEIDENTIFIER. The following code sends a message:
SEND ON CONVERSATION @conversationHandle MESSAGE TYPE [//TestSSB/Employee/Message] (@msg)
To transmit data, you need a source queue and a destination. Supplying a source for a new message may seem strange at first. However, consider that a destination may be on another server and that a channel must provide loose coupling, and it’s not so strange after all.
The following code receives messages from the queue:
RECEIVE TOP(1) @message_type_name=message_type_name, @message_body=message_body, @dialog = conversation_handle FROM [TestSSB_PubSub_TargetQueue]
Normally, you create a transaction before you send or receive a message. So, should sending or receiving fail, the transaction can be rolled back, thus preserving the integrity of the data and the former state of the interaction.
Some Guidelines for Your Own Solution
If you’re integrating two SQL Server 2005 databases and you need a messaging solution, Service Broker is a no-brainer. Beyond that, you have other options. MSMQ is independent of SQL Server and fills another niche in Microsoft’s asynchronous messaging repertoire. Also, don’t rule out BizTalk or a solution combining BizTalk with Service Broker. In fact, consider monitoring and the tools such as BizTalk BAM that may serve as a monitoring process. CLR capability is new with SQL Server 2005. Combining CLR with Service Broker can be a potent mix if you need some custom .NET code.
Although this example used XML, you are not confined to XML. Had you not used XML, you would’ve needed to define something structurally similar to XML notation to be true to the pattern implementation.
Finally, keep in mind that almost anything that can call T-SQL commands in SQL Server 2005 can use Service Broker.
A Built-in Asynchronous Messaging Solution
Service Broker is a new asynchronous messaging solution built into SQL Server 2005. By using T-SQL and some new concepts, a SQL database developer can implement some of the patterns in the book Enterprise Integration Patterns. This article described how to implement the Publish-Subscribe channel pattern.
Download the Code
To download the accompanying source code for the example, click here.
Resource
Enterprise Integration Patterns Web site
About the Author
Jeffrey Juday is a software developer with Crowe Chizek in South Bend, Indiana. He has been developing software with Microsoft tools for more than 12 years in a variety of industries. Jeff currently builds solutions using BizTalk 2004, ASP.NET, SharePoint, and SQL Server 2000. You can reach Jeff at jjuday@crowechizek.com.
