On his SQL Server with Mr. Denny blog, SQL Server expert Denny Cherry discusses the basics of SQL Server functionality. One topic he covers extensively is the SQL Server Service Broker, a message queue system with
Requires Free Membership to View
the same basic concept as the Microsoft Message Queue (MSMQ). Whether you are a
newcomer to the Service Broker or a seasoned professional, this tutorial provides all the
information you need to both setup and fine-tune this SQL Server addition.
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.
TABLE OF CONTENTS
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
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.
This was first published in December 2008
Join the conversationComment
Share
Comments
Results
Contribute to the conversation