What is Service Broker, and Why Would We Use It?

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://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/ms345113(v=sql.90)

https://www.sqlpassion.at/archive/2012/06/25/killing-me-softly-with-service-broker/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s