SQL Server Service Broker Tutorial and Reference Guide

This SQL Server tutorial will continue to be updated with content from SQL Server guru Denny Cherry, so please bookmark this page and check back for regular updates.

SQL Service Broker Tutorial and Reference

  → SQL Server Service Broker basics
  → Visit the SQL Server with Mr. Denny blog

	What is IT Knowledge Exchange?
	IT Knowledge Exchange (ITKE) is a community of IT pros that share and pool their knowledge. You can ask your own IT question or share your expertise by answering a question. Do you have an IT problem that's driving you crazy? Come ask your peers and colleagues on IT Knowledge Exchange. You can even start your own blog and earn some cash for posting regularly.   → Visit the ITKE Community Blog to learn how to get IT answers

↓ SQL Server Service Broker basics

↑ Table of Contents

SQL Server Service Broker Overview
The SQL Server Service Broker is a message queue system that can give you guaranteed delivery order with single processing of messages -- always in the order received. Messages can be processed on demand or automatically via the queue activation.

SQL Server Service Broker Message Types
The message type is the first specific object in Service Broker. The message type defines the name of the message, and performs any validation Service Broker needs to do on the contents of the message prior to sending it.

SQL Server Service Broker Contracts
Every conversation in Service Broker needs a contract. The contract defines which message type or types will be used, as well as who can use these message types. The contract is specified when the conversation is created.

SQL Server Service Broker Queues
The SQL Server Service Broker queue is where messages are stored after they are delivered, but before they are processed. They are similar to tables, in that you insert data into them (sending the message) and view the data in them (receiving the message).

SQL Server Service Broker Services
SQL Server Service Broker services are used to bind contracts to queues. They are also used to bind database to database, or server to server routing of messages via routes.

SQL Server Service Broker Routes
SQL Server Service Broker routes are only needed when sending messages from one server to another. They define the SQL Server and TCP Port, which the sending SQL Server will connect to, in order to deliver the message.

SQL Server Service Broker SEND command
The SEND command is similar to the INSERT command, except it is used only with Service Broker. With Service Broker, you put data into a queue instead of a table. Instead of inserting a command into the queue, you send a message to the queue, much like sending an email to someone else.

SQL Server Service Broker RECEIVE command
The RECEIVE command in Service Broker gets the message out of the queue so you can process the data within the message_body column. Essentially, the RECEIVE command works just like the SQL SELECT statement.

SQL Server Service Broker message removal error
There is an issue with SQL Server Service Broker message removal that others may also have noticed. The conversations in the sys.conversation_endpoints DMV remain in a CONVERSING state rather than a CLOSED state.

Improving SQL Server Service Broker performance
In a high load SQL Server Service Broker environment, there is some impressive performance improvement that can be achieved by reusing the service broker sessions.

Monitoring the health of your SQL Server Server Broker
Here is a simple little query to see how backlogged your SQL Server Service Broker is.

	GOT IT QUESTIONS? YOUR PEERS HAVE ANSWERS
	Your peers and colleagues have faced the same or similar problems. So ask your own question on the IT Knowledge Exchange community.