Blog Archives

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.

DECLARE @cvh1 UNIQUEIDENTIFIER,
 @cvh2 UNIQUEIDENTIFIER,
 @cvh3 UNIQUEIDENTIFIER,
 @TestMsgBody XML ;

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

BEGIN DIALOG @cvh1
FROM SERVICE [//InitiatorService]
TO SERVICE '//TargetService1'
ON CONTRACT [//TestProcessing] ;

BEGIN DIALOG @cvh2
FROM SERVICE [//InitiatorService]
TO SERVICE '//TargetService2'
ON CONTRACT [//TestProcessing] ;

BEGIN DIALOG @cvh3
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
DECLARE @ch UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @ch
 FROM SERVICE [manual_activation_initiator_service]
 TO SERVICE 'manual_activation_target_service'
 ON CONTRACT [manual_activation_contract]
 WITH ENCRYPTION = OFF ;

SEND ON CONVERSATION @ch
 MESSAGE TYPE [manual_activation_demo_request]
 ('<test>test_message_body</test>');
GO

-- A Direct Select statament from the Queue
SELECT
 conversation_handle
 ,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
WAITFOR (
 RECEIVE TOP(1)
 conversation_handle
 ,CAST(message_body AS XML) AS [msg_body]
 ,DATEDIFF(second,message_enqueue_time,getutcdate()) AS [time_in_queue(Seconds)]
FROM manual_activation_target_queue),
TIMEOUT 5;

-- 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.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: