Using SQL Server to Implement the Publish-Subscribe Integration Pattern
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.
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.
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 email@example.com.
Page 2 of 2