SQL Server 2012 – What’s new in Service Broker?

Service Broker has come a long way since its’  introduction in Microsoft SQL Server 2005. SQL Server 2008 brought a host of Service Broker enhancements like conversation priorities, the ssbdiagnose utility, alternate poison message handling and new performance objects & counters. SQL Server 2012 is a major release that comes with an astonishing  array of enhancements and new features, including  new bells & whistles for Service Broker.

A significant new feature for Service Broker is the ability to Multicast messages. This enables a single initiator service to send messages to multiple target services, akin to SQL Server Replication, where multiple subscribers can subscribe to the same publication. The syntax of the send command has been extended to enable multicasting , by allowing multiple conversation handles.

 @TestMsgBody XML ;

SET @TestMsgBody = '<test>Test Message</test>' ;

FROM SERVICE [//InitiatorService]
TO SERVICE '//TargetService1'
ON CONTRACT [//TestProcessing] ;

FROM SERVICE [//InitiatorService]
TO SERVICE '//TargetService2'
ON CONTRACT [//TestProcessing] ;

FROM SERVICE [//InitiatorService]
TO SERVICE '//TargetService3'
ON CONTRACT [//TestProcessing] ;

SEND ON CONVERSATION (@cvh1, @cvh2, @cvh3)
 MESSAGE TYPE [//TestMgsType]
 (@TestMsgBody) ;

Another new feature is the addition of a new column – message_enqueue_time – to the Queues. This column helps us determine the time spent by a message on the queue.  The message_enqueue_time column is of datetimeoffset data type and stores the date and time (with time zone awareness) when the message arrives on the queue. Its’ exposed to the application via a direct query on the Queue itself, as well as a column in the Receive statement. Let’s look at an example;

-- Begin dialog & send message

 FROM SERVICE [manual_activation_initiator_service]
 TO SERVICE 'manual_activation_target_service'
 ON CONTRACT [manual_activation_contract]

 MESSAGE TYPE [manual_activation_demo_request]

-- A Direct Select statament from the Queue
 ,CAST ( message_body AS XML) AS [msg_body]
 ,DATEDIFF(second,message_enqueue_time,getutcdate()) AS [time_in_queue(Seconds)]
FROM [manual_activation_target_queue];

-- Results
conversation_handle                  msg_body                        time_in_queue(Seconds)
------------------------------------ ------------------------------- ----------------------
C0CB3200-809F-E111-8B29-005056BE0088 <test>test_message_body</test>  21

(1 row(s) affected)

-- Retrieve the message off of the Queue with a Receive
 ,CAST(message_body AS XML) AS [msg_body]
 ,DATEDIFF(second,message_enqueue_time,getutcdate()) AS [time_in_queue(Seconds)]
FROM manual_activation_target_queue),

-- Results
conversation_handle                  msg_body                        time_in_queue(Seconds)
------------------------------------ ------------------------------- ----------------------
C0CB3200-809F-E111-8B29-005056BE0088 <test>test_message_body</test>  21

(1 row(s) affected)

Please note that this column is not well documented yet and to derive the time spent by a message on the queue  in seconds, one must use a few date & time functions.   

 Service Broker Remote Messaging needs some additional configuration in order to get along with the Always On Availability Groups, due to the complexity added by the Listener for an Availability group. The Always On Availability Groups feature  introduced in SQL Server 2012,  is a high availability and disaster recovery solution that provides an enterprise level alternative to database mirroring.

For a Service in an Availability Group to be able to Receive remote messages;

  • The Availability Group must have a listener configured
  • Service Broker Endpoint must be created and configured with the Listener, for every instance of SQL Server in the Availability Group
  • Connect permissions must be granted on the endpoints to the the appropriate login(s)
  • msdb must contain a route

For a Service in an Availability Group to be able to Send messages to a remote service;

  • Configure a route to the target service using the listener
  • msdb must contain a route

And last but not the least, its always worth mentioning that since SQL Server 2008 R2, Poison message handling can be disabled at the time of queue creation or at a later time by using the alter queue statement. You can also check the status of poison message handling for each queue, by querying the sys.service_queues catalog view, which has the column, is_poison_message_handling_enabled to indicate whether poison message is enabled or disabled.


Posted on May 18, 2012, in Service Broker, SQL Server 2012 and tagged , , . Bookmark the permalink. 9 Comments.

  1. Great article on Service Broker 2012. I can’t find much documentation on it.

    I am trying to send and receive events/messages between a windows service and SSIS v2012. Is Service Broker a good candidate? If so, what mechanism do I use in SSIS to listen to the Service Broker?

    I see that SSIS has an interation with MSMQ, but not Message broker.
    Any comments are much appreciated.

    I am a .net developer, but have little to no experience with SSIS 2012. That’s about to change!!

    • Thanks Dan. Some of the lack of documentation on Service Broker 2012 is mainly due to the fact that few few features changed between 2008R2 and 2012 version. Could you give me a brief background of your use case ? Do you need the SSIS package to be executed based on an unscheduled event ? Do you need to Queue a message from within an SSIS package or read a Queued message in the SSIS package ?

      SSIS is quite intuitive if you think in terms of data flow and the TSQL in terms of Sets. I gave a presentation about Service Broker at a SQL Saturday event recently. You can see the slides and demo scripts for brief working introduction to service broker – http://www.sqlsaturday.com/viewsession.aspx?sat=214&sessionid=12941

      • I have several WindowsServices (NTServices) that I want to fire events sending messages to SSIS to begin a workflow.
        The reverse is also true. Based on workflow processing SSIS could send messages to the Windows Services.


      • Dan,

        Both of these can be done. You will need to use “automatic activation” with your service broker queues. If you know what are triggers on database table, “automatic activation” can be though of as a trigger on a service broker queue. This can be used to execute a stored procedure when a message arrived on a queue. The content of the stored procedure could easily be a call to your SSIS package to begin the workflow. You can follow this link to read about how to call an SSIS package from a Stored procedure – http://code.msdn.microsoft.com/Calling-a-SSIS-Package-a35afefb

        You would want to interact with a Service broker queue using Send and Receive stored procedures. You will write these stored procedures with the necessary code for performing the respective action. You can then call these procs from the SSIS package to either send a message on the queue and receive a message from the queue.

  2. Thanks for your time. Sounds like a good solution.

    • Thanks Dan. I would also encourage you to post your question on the MSDN SQL Server Forum for Service Broker topics. You will definitely hear great ideas from several experts on the technology.

  3. Nice post! I have a problem to the SQL Server 2012 Serivce Broker.. So I create through VB.NET an SqlDependency Object with timeout, but timeout does never “fire” and endpoints remain open.. Do you have any idea? (everything worked perfect on SQL Server 2008 R2)

  4. Nice post.. Could you give me a hand with a problem I have in SQL Server 2012? I use to create an SqlDependency Object in VB.NET with timeout. But timeout never “Fires” and conversation_endpoint remains open! Do you have any idea? (in sql server 2008 R2 everything worked like a charm!)

  1. Pingback: SQL Server 2012 – Other Features & Enhancements – Learning Resources « Datta's Ramblings on Business Intelligence 'N' Life

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: