Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server 2012 - New Service Broker Features

By Sanil Mhatre,

Service Broker has come a long way since it's 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 and 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 as shown below.

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 a 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 and time functions.   

Service Broker Remote Messaging needs some additional configuration in order to work 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.

Conclusion

Service Broker has evolved into a mature and robust platform for asynchoronous messaging and Queuing solutions, built into the SQL Server Engine. These new feature are only the icing on top of the cake and help make service broker more feasible technology for your Queuing solutions

References

Total article views: 2785 | Views in the last 30 days: 11
 
Related Articles
FORUM

SQL server Service Broker -- Reading same Message by multiple Users in the queue

SQL server Service Broker -- Reading same Message by multiple Users in the queue

ARTICLE

An AlwaysOn Bug with Service Broker Transmission Queue

Paul Brewer talks about an AlwaysOn problem affecting the Service Broker Transmission Queue.

FORUM

Service Broker Messaging Issue with Real IP

Service Broker Messaging Issue with Real IP

BLOG

Getting Fast Counts of Large Service Broker Queues

Getting Fast Counts of Large Service Broker Queues This question regarding getting a fast count f...

FORUM

Service Broker

Service Broker

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones