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
you’re done.”
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
service.
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
Additional programming
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
done.
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)
conversations:
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
customers.”
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:
https://sqlperformance.com/2014/03/sql-performance/intro-to-service-broker
https://www.sqlteam.com/articles/how-to-troubleshoot-service-broker-problems
Pluralsight – Jonathan Kehayias : Building Simple
Asynchronous Applications, Building Multi-instance Asynchronous Applications
https://gavindraper.com/2012/06/03/sql-server-service-broker-explained/
https://www.sqlpassion.at/archive/2012/06/25/killing-me-softly-with-service-broker/