SQL Server Service Broker – Introduction

SQL Server Service Broker is a messaging technology that’s baked right into the SQL Server engine.  It was first introduced in SQL Server 2005, and if you ask me, is one of the most under-utilized, under-appreciated features in SQL Server.

What’s so great about Service Broker?

So why am I such a big fan of Service Broker and if it’s so great, why isn’t everybody using it? Let me start by telling you why I’m such a fan.

Asynchronous

The biggest benefit of Service Broker, or probably any messaging technology, is that is decouples long running processing from the client application.

A great example of what I mean by this is Amazon.com.  When you place an order at Amazon, a whole series of backend processes are initiated.  Your payment is processed. Inventory is verified and updated. Fulfillment and shipping is initiated, etc.  All of which ultimately end with a box arriving on your doorstep.  But the website doesn’t wait for all of that stuff to finish before it thanks you for your order.  Your order information is added to a queue and the page returns.  All that other stuff is handled asynchronously.  And that’s what Service Broker lets you do in your database.

Guaranteed message delivery

Most of the time in a Service Broker application, you’ll be sending messages between servers.  What happens if the network link between those servers goes down?  Well, if you’re relying on synchronous connections or linked servers, you’d be in trouble.  But with Service Broker, messages are queued until the communication link is back up and they are automatically sent then.  Guaranteed.

Guaranteed message sequencing

Similar to guaranteeing all messages will be delivered, Service Broker uses internal locking and sequencing mechanisms to also guarantee that all messages will be processed in the order in which they were sent.

Durability

Service broker messages and conversations are persistent across server restarts.

Easier application development

All of the perks I mentioned above are built right into Service Broker, which means you don’t need to worry about any of that when you develop your code.

Any Edition

Service Broker is available in any edition of SQL Server.  Now how many features can you say that about?

Wow, Service Broker sounds awesome!  Why isn’t everybody using it?

While there are some pretty cool benefits of using Service Broker, it hasn’t really seen widespread adoption, and I think there are a couple reasons for that.  The first reason is that many people don’t know where they could use messaging technology like Service Broker.  So here are some ideas to get you started.

Event Notifications

If you’ve been to my blog much, you’ll know I’m kind of into auditing.  Event notifications are one of the ways I’ve accomplished SQL Server auditing in the past, and event notifications use Service Broker.

Asynchronous triggers

Any time you implement a trigger, you should make sure it is as lightweight as possible.  Many times I’ve seen developers and DBAs try to cram too much work into a trigger and the application suffers as a result.  If there is logic in your trigger that doesn’t need to be performed synchronously, as part of the current transaction, consider using Service Broker to offload some of that processing to an asynchronous process.

Data warehouse ETL

Whether it’s in conjunction with triggers or with technology like Change Data Capture (CDC), Service Broker is a great way to send data changes to your data warehouse.

Distributed server-side processing for client applications

I mentioned Amazon as an example of this earlier, but this isn’t confined to order entry systems.  Think about what back-end processing you have in your environment

The other problem with Service Broker is that it’s not always easy to understand and implement.  And it can be pretty easy to mess up.  I liken it to a black box of sorts:  messages go in and we hope they come out on the other side.  But when they don’t, we don’t always know where to look.

In this series of blog posts, I’ll walk through the Service Broker architecture, how Service Broker works, how to troubleshoot when things go wrong (and don’t worry, they will), best practices, and more.  Next time, we’ll begin with basic Service Broker components and the role that each one plays.

Original post by Colleen M. Morrow found here