Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted July 9, 2012

An Introduction to SQL Server Service Broker

By Deanna Dicken

Introduction

Service Broker is a technology built into SQL Server and utilized by the engine for its internal asynchronous processing. The great thing about Service Broker is that its functionality is exposed so we can build our own custom data integrations. This article serves to introduce you to the concepts needed to create a service broker integration.

What is Service Broker?

Inside Microsoft SQL Server lies a robust messaging system. First available with SQL Server 2005, SQL Service Broker (SSB) provides database driven applications the option to kick off asynchronous processing with guaranteed delivery utilizing queues for loosely coupled and highly scalable integrations.

This allows the application's core functionality to complete without concern for the integration. The user is not waiting on the application to perform the integration, nor is he at risk of losing his work should the integration fail or become deadlocked. The integration processes asynchronously behind the scenes with guaranteed delivery to the processing component.

The processing component (or target) can be an application that reads from the target queue or it can be a stored procedure that is activated by the target queue. The target queue can be in the same database or a different one…even within a database on a remote server.

Some of the benefits of SQL Service Broker include:

  • Reliability - SSB lives inside the database and is utilized under the covers to support SQL Server. It benefits from all the same reliability aspects of SQL Server.
  • Guaranteed Delivery - messages are persisted and guaranteed to be delivered to target, even in the case of server failure (rolled forward from transaction log).
  • Replay-ability - messages can be retried if the target is not responding and ultimately written to a dead letter queue for investigation or replay when the service is available.
  • Separation of concerns - the application does not need to be concerned with or held up by the integration. Integration occurs in one location despite the number of applications that may affect a piece of data.
  • Scalability - activation allows the solution to automatically scale to match the activity on the queue (up to the configured maximum reader setting).
  • Priority processing - messages can be assigned a priority to allow high-priority items to process first.
  • Guaranteed ordering - messages are guaranteed to arrive in order and only once.

Anatomy of a Service Broker Integration

A SSB integration consists of Conversations (or Conversation Groups), Message Types, Contracts, Services and Queues. Fortunately, you do not need to learn a new language to implement these pieces. Good ol' T-SQL is all you need to get the integration up and running.

Conversations and Conversation Groups

Services communicate via messages. The messages form a conversation. For instance, assume you sent me an email asking me what the weather was going to be tomorrow. You could not care less if I answered that email in 5 minutes or 50 minutes, just as long as I got back to you in a timely manner. When I get back to my desk, I read the email, look up the weather for Friday, and send a response regarding our conversation. Again, I don't care if you get that response immediately, just that you got it. You'll read it when you have time. This is the same idea behind the asynchronous conversations between services with SSB.

Should a service have several related conversation types, SQL Server 2008 and greater provides for the grouping of those types. This is referred to as a conversation group. Using a conversation group, you can ensure that one application instance processes all the messages that come in for that group. Meaning you don't have to write the code to make sure the various messages are processed in order. This allows your application increased scalability.

Message Types

For the conversation to be understood, the components of a service broker integration have to agree on the message types used in the communication. Each message type is defined by a name and contains the format for the message. There is a default message type in SSB in case a specific message type is not needed.

Contracts

In an SSB integration, contracts define which message types can be used in a conversation (or dialog). For each message type in the contract, it is noted who can produce that type. The producer can be the initiator, the target or both. For example, only the order system can produce a shipping request message and only the shipping system can produce the shipping response message, but either system could produce a status message in the conversation.

SSB provides a default contact. That default contract implements the default message type.

Queues

The asynchronous nature of an SSB integration relies on queues. Queues hold the messages for a service to process. One of the unique aspects of SSB integrations is that the messages are not processed first-in-first-out from SSB queues, but rather in the order in which they were sent. SSB keeps track of this for the applications so the developer doesn't have to code for or around out-of-order messages.

Services

Services have to be created that can handle the messages in a given contract. A service can have multiple contracts, but is associated to only one queue. Contracts are assigned to the services that are the target of the message in those contracts. If a service does not implement a contract, it may only initiate a conversation and not act as a target. A service may implement the default contract and thus consume the default message type.

Activation

If you are using internal activation, then the service is being implemented by a stored procedure, which can run multiple instances for scalability. In this case, the queue will need to be set for internal activation. If the application (that which implements the service) resides outside of the database, this is considered external activation. There are many things to consider with external activation that are beyond the scope of this article. On the short list, you will need to create routes to the external service and think about securing the communication.

Conclusion

SQL Service Broker is an effective facilitator for asynchronous integration, providing for reliable, secure, scalable, and persisted messaging. It is especially useful when multiple sources can be responsible for initiating the integration of the same data. Therefore rather than build the integration multiple times into each system, one can build the integration on top of the data itself, thus ensuring no transaction will be missed.

See all articles by Deanna Dicken



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM