SQL Server Service Broker - An Introduction
May 6, 2010
SQL Server Service Broker (SSBS), introduced with SQL Server 2005 and enhanced in SQL Server 2008, allows you to write queuing/message based applications within the database itself. This article discusses SSBS' important features and how to use it in different scenarios.
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:
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
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.
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.
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.