The same thing happens every day.
Me (to my child): “Is your room clean?”
Child: <cricket sounds for several seconds> “Well,
it’s almost clean….”
Me: “So, what am I about to say?”
Child: “To clean my room?”
Me: “It’s like you can read my mind, child! Call me when
How does this relate to SQL Server, you ask? Meet Service Broker.
Service Broker is nothing more or less than a message
queueing system. Messages from Point A
are sent to Point B, and/or Point C, and/or Point D (you get the picture). You can build on that premise to use the
messages to queue processes to fire off on a server – or even on a different
server, if you like.
There is some basic architecture involved that you need to
put in place, consisting of a message type, a contract, a queue, and a
You get four message types:
- EMPTY – the message, the whole message, and
nothing but the message.
- NONE – A binary LOB, up to 2 GB in size.
- WELL_FORMED_XML – Needs to be valid XML
- VALID_XML WITH SCHEMA COLLECTION – XML that can
be validated against a specific schema
So, you have a message type.
Now, you have to decide what the rules of the exchange are. Who can send what? You will need a contract. The contract will enforce the rules for the message
type(s) you are dealing with. You pass
it the message type(s) and specify who gets to send messages on it.
We have a message type and some ground rules. Now, we need a way to get our messages from
Point A to Point B. Enter the queue. There must be at least one on
each side of the conversation.
Finally, we have a service.
These are our endpoints. They sit on
top of the queue.
Once our architecture is in place, we can clear our throats, let Service Broker know that we are getting ready to have a conversation, and let the discussion begin. That’s in English. Here is what a very simple conversation might look like in TSQL, from a Service Broker class that I recently taught for my team:
/* Let's make sure we begin with a clean slate. */IF OBJECT_ID('tempdb..#CG1') IS NOT NULL DROP TABLE #CG1; IF OBJECT_ID('tempdb..#CG2') IS NOT NULL DROP TABLE #CG2; IF OBJECT_ID('tempdb..#Msg1') IS NOT NULL DROP TABLE #Msg1; /* We start the groundwork by making a sandbox database. */IF NOT EXISTS ( SELECT name FROM sys.databases WHERE name = 'ServiceBrokerDemo' ) CREATE DATABASE ServiceBrokerDemo; GO ALTER DATABASE ServiceBrokerDemo SET RECOVERY SIMPLE; /* Is Service Broker enabled? */USE ServiceBrokerDemo; GO SELECT is_broker_enabled FROM sys.databases WHERE name = 'ServiceBrokerDemo'; GO /*If you needed to enable it, you'd use this: ALTER DATABASE ServiceBrokerDemo SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE; GO *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* If using encryption, at this point, you would want to make a master key: CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PutPasswordHere!' GO *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* Go ahead and figure out what kind of message you would like to send. Here are your choices. For this demo, I'm using WELL_FORMED_XML. EMPTY The message, the whole message, and nothing but the message. Use for acknowledgements. NONE You will send a binary LOB, up to 2 GB in size. WELL_FORMED_XML The message sent has to be valid XML. VALID_XML WITH SCHEMA COLLECTION The message sent must be XML that can be validated against a specific schema – the deluxe version of WELL_FORMED_XML. *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* Create your message type. Choose wisely! https://www.youtube.com/watch?v=0H3rdfI28s0 */CREATE MESSAGE TYPE Demo_Msg VALIDATION = WELL_FORMED_XML; GO /* Now, you need to make a well-reviewed binding contract. A contract consists of the ground rules for which party can send what kind of message type. The first message must be by the initiator. You can define as many message types as you want, and you can dictate who sends them (the INITIATOR, the TARGET, or ANY). I'm going to choose ANY, so in this case, either party will be able to send the message. */CREATE CONTRACT Demo_Contract ( Demo_Msg SENT BY ANY ); GO /* Next, we make a queue. Queues are the containers for the messages. You'll want at least one on each side of the dialog, or the mail won't make it to the post office box. */CREATE QUEUE SBDQueue_Initiator; CREATE QUEUE SBDQueue_Target; GO /* Let's make our endpoints. These are called services. They sit on top of the queue and aside from being an endpoint, they see that the contracts are enforced. You can call for more than one contract on the service. Just use a comma. */CREATE SERVICE DemoService_Initiator ON QUEUE SBDQueue_Initiator ( Demo_Contract ); CREATE SERVICE DemoService_Target ON QUEUE SBDQueue_Target ( Demo_Contract ); GO /* So, let's start talking. First, we'll let Service Broker know that we have something to say. */CREATE TABLE #CG1 ( Convo1GUID UNIQUEIDENTIFIER NULL ); DECLARE @Convo1GUID UNIQUEIDENTIFIER; BEGIN DIALOG CONVERSATION @Convo1GUID FROM SERVICE DemoService_Initiator TO SERVICE 'DemoService_Target' ON CONTRACT Demo_Contract WITH ENCRYPTION = OFF; /* We'll put the GUID that tracks the dialog into a temp table for safekeeping, because if you lose it, you lose the whole conversation. */INSERT INTO #CG1 ( Convo1GUID ) SELECT @Convo1GUID; SELECT 'Starting Convo', conversation_handle, conversation_id, is_initiator, state_desc FROM sys.conversation_endpoints; /* Hello, World! */DECLARE @GetConvoGUID UNIQUEIDENTIFIER; SET @GetConvoGUID = ( SELECT Convo1GUID FROM #CG1 ) /* When you are doing sends and receives, you need a semicolon before the SEND or RECEIVE statement. This helps SQL Server to parse the statement. Also, you can only receive a message once! */; SEND ON CONVERSATION @GetConvoGUID MESSAGE TYPE Demo_Msg ('<Hello_World/>'); GO /* We'll check our work...*/CREATE TABLE #Msg1 ( ConvoHandle UNIQUEIDENTIFIER NULL, message_type_name sysname NULL, message_body VARBINARY(MAX) NULL ); INSERT INTO #Msg1 SELECT conversation_handle, message_type_name, message_body FROM SBDQueue_Target; SELECT * FROM SBDQueue_Target; SELECT 'Send Check', conversation_handle, conversation_id, is_initiator, state_desc FROM sys.conversation_endpoints; /* So, something is indeed there. Let's go read it. */DECLARE @ConvoGUID UNIQUEIDENTIFIER, @MessageType sysname, @MessageBody VARBINARY(MAX); RECEIVE TOP (1) @ConvoGUID = conversation_handle, @MessageType = message_type_name, @MessageBody = message_body FROM SBDQueue_Target SELECT ConvoHandle FROM #Msg1; IF @MessageType = 'Demo_Msg' BEGIN SELECT CONVERT(XML, @MessageBody) AS Message, @ConvoGUID AS GUIDNo; END; ELSE BEGIN RAISERROR( 'This did not work as expected. You need to go back and ensure there is a conversation in the queue', 16, 1 ); END; GO /* Don't talk back to me in that tone of code! */DECLARE @Convo1HandleReturn UNIQUEIDENTIFIER; --Insert the handle from the preceding section SET @Convo1HandleReturn = ( SELECT ConvoHandle FROM #Msg1 ); SEND ON CONVERSATION @Convo1HandleReturn MESSAGE TYPE Demo_Msg ('<Goodbye_Forever/>'); GO SELECT 'Goodbye Forever Check', conversation_handle, conversation_id, is_initiator, state_desc FROM sys.conversation_endpoints; GO /* We've got mail! Let's go see...*/DECLARE @ConvoHandleReturn UNIQUEIDENTIFIER; --Insert the handle from the preceding section SET @ConvoHandleReturn = ( SELECT ConvoHandle FROM #Msg1 ); --End the conversation in order to keep the queue from growing... END CONVERSATION @ConvoHandleReturn; SELECT 'Goodbye Forever Hangup Check', conversation_handle, conversation_id, is_initiator, state_desc FROM sys.conversation_endpoints; GO GO DECLARE @ConvoHandleReturn UNIQUEIDENTIFIER, @MessageTypeName sysname, @MessageBodyRead VARBINARY(MAX); --Get all the messages, and "hang up the phone" on the other side... --Side note - I wanted to show that as long as your semicolons are in place, you don't need them RIGHT in front of the SEND or RECEIVE statement (but you may want to, just to be safe). RECEIVE TOP (1) @ConvoHandleReturn = conversation_handle, @MessageTypeName = message_type_name, @MessageBodyRead = message_body FROM SBDQueue_Initiator IF @MessageTypeName = 'Demo_Msg' BEGIN SELECT CONVERT(XML, @MessageBodyRead) AS ReturnMessage, @ConvoHandleReturn AS GUIDNo; END; ELSE IF @MessageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN SELECT 'Over and Out...'; END CONVERSATION @ConvoHandleReturn; SELECT 'End Dialog Hangup Check', conversation_handle, conversation_id, is_initiator, state_desc FROM sys.conversation_endpoints; END; GO EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'ServiceBrokerDemo'; GO USE [ServiceBrokerDemo]; GO USE [master]; GO USE [master]; GO ALTER DATABASE [ServiceBrokerDemo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO USE [master]; GO DROP DATABASE [ServiceBrokerDemo]; GO
to make the database do something upon reception of the messages not included.
Let’s go back and look at the conversation at the beginning
of the article. My child knows when I
ask if her room is clean, that means she should mentally review her task
list. Is my bed made? Is my stuff off the floor? Are my clothes hung up?
If the answer to those questions is yes, she knows to tell me so, and to run
out and play with her friends (please be home on time, and have fun!). If the answer is no, she waits for me to tell
her to go ahead and finish her work first, and to let me know when she is
What this whole exchange boils down to is this: I send a message. She receives it and replies. I receive her message, and based on her
response, deliver another message. That
message queues a task to be done.
Service Broker allows you to do the same thing. Imagine these (highly simplified)
Database A to OtherDBs : “Are we done with all our major processes
for the day?”
OtherDBs to Database A: “Yes!”
Database A: “Great.
Let’s fire off Ola and clean house!”
OtherDBs to Database A: Click
(imagine the phone being hung up)
Database A: Click
Web Service to Database B: “I just finished taking an order.”
Database B: “Great.
I’ll take over from here and finish processing it. You go back to the
Database B: Click
Web Service: Click
There are so many great Service Broker demos and classes out
there that I thought I’d just include a blog roll for you to check out. Yes, some of these are older, but there don’t
seem to have been any big changes made since 2012:
Pluralsight – Jonathan Kehayias : Building Simple
Asynchronous Applications, Building Multi-instance Asynchronous Applications