SQL Server Service Broker–Part 1

There’s several ‘hidden gems’ in SQL Server, and some of these have been in the product for quite some time but seem to remain the domain of only a handful of SQL Server professionals – relatively speaking.

One of these is Service Broker – which was first introduced to the product back in 2005.

Service Broker is probably best known for two things

• Messaging between different SQL Server Instances.
• Providing asynchronous capabilities when performing long running tasks.

Although some of the less well know things may be

• Helping alleviate contention in certain scenarios.
• Scaling database services

One of the reasons that may have contributed to the lack of uptake of service broker is that often development is started without a clear understanding of the fundamentals of Service Broker.

Another situation would be the DBA who takes over a database instance with Service broker in operation for the first time in their career. At some stage an issue occurs (generally giving the impression that Service Broker is the cause).

Often people in the above situations find themselves facing a complex situation with many more components than just service broker, including:

• Firewalls
• Certificates
• Network Connectivity / bandwidth issues
• XML

This often leads to frustration, poor decision making and ultimately sub-optimal outcomes that generally spiral into more serious issues and can lead to a Service Broker implementation being replaced with far less suitable complements.

One other issue is the fact that Service Broker seems to be somewhat of a victim of its own success.

It’s been a part of SQL Server since 2005 and literally from day one was rock solid. A few additions were made in 2008 and since then only relatively minor additions have made their way into the product.

This tends to lead some people to the (incorrect) conclusion that Service Broker is becoming deprecated – this is most definitely not true.

And because most articles on Service Broker are from the 2005 / 2008 era this often leads to conclusions (again incorrectly) that the product has passed its ‘sell by’ date.

There are a few other popular messaging brokers around including Rabbit MQ.

Some of these Brokers are proving extremely popular at the moment as micro architecture proves to be a popular – and effective way – of scaling applications.

This blog post being part of a series on SQL Server Service Broker that will introduce the tooling from the ground up, starting with complete basics and gradually scaling to a more complex implementation.

In Part 2 we’ll look at implementing a basic Service Broker implementation and get to grips with the components that we need to help us on our journey.