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.
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
- MSDN Service Broker - http://msdn.microsoft.com/en-us/library/bb522893.aspx
- Code examples and details - http://sqlwithsanil.com/2012/05/18/sql-server-2012-whats-new-in-service-broker/