July 5, 2020
Hot Topics:

Using SQL Server to Implement the Publish-Subscribe Integration Pattern

  • By Jeffrey Juday
  • Send Email »
  • More Articles »

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]

CREATE SERVICE [//TestSSB/PubSub/Target/Service]
   ON QUEUE [dbo].[TestSSB_PubSub_TargetQueue]

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]
         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]

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:

@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 jjuday@crowechizek.com.

Page 2 of 2

This article was originally published on July 31, 2006

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date