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

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read