Using SQL Server to Implement the Publish-Subscribe Integration Pattern

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.

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 xmlns="http://testSSB/EmployeeInfo.xsd"
           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.

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.

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.

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.



About the Author

Jeffrey Juday

Jeff is a software developer specializing in enterprise application integration solutions utilizing BizTalk, SharePoint, WCF, WF, and SQL Server. Jeff has been developing software with Microsoft tools for more than 15 years in a variety of industries including: military, manufacturing, financial services, management consulting, and computer security. Jeff is a Microsoft BizTalk MVP. Jeff spends his spare time with his wife Sherrill and daughter Alexandra.

Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • On-demand Event Event Date: September 10, 2014 Modern mobile applications connect systems-of-engagement (mobile apps) with systems-of-record (traditional IT) to deliver new and innovative business value. But the lifecycle for development of mobile apps is also new and different. Emerging trends in mobile development call for faster delivery of incremental features, coupled with feedback from the users of the app "in the wild." This loop of continuous delivery and continuous feedback is how the best mobile …

  • Packaged application development teams frequently operate with limited testing environments due to time and labor constraints. By virtualizing the entire application stack, packaged application development teams can deliver business results faster, at higher quality, and with lower risk.

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds