SQL Server Service Broker - An Introduction

May 6, 2010

Introduction

SQL Server Service Broker (SSBS) is a new architecture (introduced with SQL Server 2005 and enhanced further in SQL Server 2008) which allows you to write asynchronous, decoupled, distributed, persistent, reliable, scalable and secure queuing/message based applications within the database itself. This integration of queuing and message based infrastructure into the database provides several advantages from other message/queuing based applications out of the database. For example, SSBS provides simplified administration, inbuilt security, improved performance, transaction support without making it distributed (more details later), simplified recovery process as queued data are backed up as part of the normal database back up etc. SSBS in itself is a very big topic, requiring several hundred pages to explain. Therefore, I will be talking about its important features; how to use it in different scenarios and what are the improvements in SQL Server 2008 for SSBS across several articles.

This introductory article on SSBS discusses on a very basic level, how it differs from MSMQ, looks at SSBS architecture and its different components.

How SSBS differs from MSMQ/Other message based technology

Similar to SSBS, Microsoft Message Queuing (MSMQ) is another technology that enables applications to send and read messages from queues; it comes as a part of the Windows OS. However, SSBS outweighs the features and benefits that MSMQ provides. For example, in comparison with MSMQ, SSBS provides the following:

• Faster transactional message processing
• Reliability and enhanced recoverability
• Optimize the performance
• Inbuilt Security
• Parallel processing
• Reduced management and maintenance overhead

MSMQ has been in use a long time and is a well-tested technology. Even though MSMQ is the preferred technology for writing queuing and message based applications and provides several advantages over other alternatives, in comparison to SSBS, SSBS outweighs MSMQ in several ways in an environment where a SQL Server instance is already available.

Advantages of SSBS over MSMQ/other queuing and message based technology

• SSBS is a part of the database engine itself and is more efficient for data-intensive queuing operations. It has improved performance, efficient and faster transactional message processing because transaction contexts are managed by SQL Server internally, whereas MSMQ requires Distributed Transaction Coordinator (DTC) to coordinate transaction context between MSMQ and SQL Server. Remember DTC uses two-phase commit protocol.
• Service Broker queues are maintained in the database as database objects and are backed up during normal database backup processes. This provides reliability and enhanced recoverability. MSMQ stores messages in the memory when you optimize it for faster performance. This is fine as long as there is no failure, but if there is any failure or you restart MSMQ service, the stored messages will be gone.
• SSBS optimizes the performance by directly writing messages to the receiver queue (bypassing the sender queue) if the receiver queue is in same database or another database on the same server.
• SSBS handles sending and receiving messages internally by database and SSBS commands are executed over the same connection to the database (no need of separate connection and therefore no need of DTC as would be required when it is external to database). This ensures the access permissions of the senders and receivers are checked by the database engine itself. Having a single identity check for messaging and database it provides the inbuilt security, which is easier to configure and maintain in one place.
• SSBS achieves parallel processing by letting us define the number of simultaneous queue readers. Each reader can process the messages of a particular conversation group. To ensure messages of a particular conversation group are being processed by a single reader it uses a new kind of lock, called conversation group lock.
• With SSBS, you store both logic and data in the database itself, so chances of getting both out of sync is minimized to a great extent when you restore your system after any failures.

MSMQ uses TCP/IP or HTTP transport protocols whereas SSBS supports only TCP/IP so if you want your messages to communicate over HTTP, MSMQ would be your choice.

Service Broker - Architecture

In SSBS terminology, the Sender of the message is called Initiator and the Receiver of the message is called Target. Depending on the placement of Initiator and Target the SSBS application's architectures can be categorized in three different scenarios.

1. Initiator and Target in the same database in the same SQL Server instance.
2. Initiator and Target in different databases but still at the same SQL Server instance.
3. Initiator in a database at one SQL Server instance whereas Target in another database at another SQL Server instance.

The behavior in scenario A and B above is almost same. In these cases, SSBS optimizes performance by writing messages directly to the Target Queue. If, while writing message to the Target Queue, it encounters any problem (for example the Target service is not available, Target Queue is disabled, etc.) it keeps that message in the sys.transmission_queue table temporarily so that it can push the message to Target Queue once it is available. In the image below, you can see there are two databases, Initiator and Target, on a SQL Server instance. When Initiator sends (by using SEND T-SQL command) a message, the message is directly written to Target Queue from where the Target reads (by using RECEIVE T-SQL command) it. If the Target sends a response back to the Initiator, the message is directly written to Initiator Queue.

The behavior in the third scenario, scenario C, becomes a bit complex and interesting too. In this case, messages travel from one server to another and the network is involved, hence SSBS ensures successful delivery of the message by storing messages temporarily in the sys.transmission_queue temporary queue at Initiator and removing it from there only if it receives receipt acknowledgement from the Target. This means as long as messages are in transit they will reside in the sys.transmission_queue queue. This is what has been depicted in the image below. You can see there is a queue at database level and there is sys.transmission_queue temporary queue at instance wide.

When an application issues the SEND command to send a message, that message is stored in sys.transmission_queue queue at Initiator, then SSBS sends that message over the network to the Target and at same time, it marks the status of this message as waiting for acknowledgement from the Target. On the Target, when the message is received successfully in the Target queue, it sends the acknowledgement back to Initiator. At this point only, the message is deleted from sys.transmission_queue queue at the Initiator. If the Target issues a SEND command to send a response message back to the sender, the response message goes into the sys.transmission_queue queue at the Target, again SSBS sends that response message over the network to the Initiator and at same time, it marks the status of this response message as waiting for acknowledgement from the Initiator. On the Initiator, when the response message is received successfully in the Initiator queue, it sends the acknowledgement back to the Target. At this point only, the message is deleted from the sys.transmission_queue queue at the Target.

Please note, the sys.transmission_queue temporary queue is not specific to a database but rather is only for each instance.

Conclusion

In this first article, I've introduced SQL Server Service Broker (SSBS) to write message based asynchronous applications. I briefly talked about SSBS and its capabilities. Then I showed how it differs from other message-based technologies, for example MSMQ, in what scenarios SSBS can be considered over other technologies and what benefits it provides. Then I discussed the different SSBS architectural setups and how they worked in each scenario.

My next article on this topic discusses the different components of SSBS, their importance, how they work, how they are related to each other and how you can create them while writing your SSBS application, so stay tuned.

References

MSDN: Microsoft Message Queuing
MSDN: SQL Server Service Broker
Getting Started with SQL Server Service Broker

» See All Articles by Columnist Arshad Ali