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.
