SQL Server 2012 - New Service Broker Features

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.

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;

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

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.