Is anyone using the Service Broker in SQL Server?

Question

Hi all!

When I attended a presentation of SQL Server 2008 at Microsoft, they did a quick gallup to see what features we were using. It turned out that in the entire lecture hall, my company was the only one using the Service Broker. This surprised me a lot, as I thought that more people would be using it.

My experience with SB is that it does it's job well, but is pretty tough to administer and it's hard to get an overview.

So, have you considered using the Service Broker? If not, why not? Did you go for MSMQ instead? Is there anything in SQL Server 2008 that would make you consider using the Service Broker.

Answer 1

I have recently considered using Service Broker for a project, but yes, decided to go for MSMQ instead.

Our architecture consisted of a number of (clustered) servers, each needing to write information into a single instance of SQL reliably.

As I understand it, SB only works for SQL to SQL communication, so we would have needed an instance of SQL on each clustered box. We felt this was a bit unnecessary, hence using MSMQ

To be honest, i'm can't think of a scenario where I would use SB - I'm interested in knowing a bit more about your scenario, to see if I'm missing something vital.

Answer 2

I have been using SB in 2005 for about two years now with one implementation handling several hundred thousand messages a day. I would say the biggest challenge has been not so much in the architecture but understanding all the nuances involved. The documentation from Microsoft is poor with very few practical examples. Remus Rusanu's blogs have really been helpful in doing things like dialog reuse and activation stored procedure tuning. I have found it's REALLY important to reuse dialogs as much as possible (and working through all the associated locking involved with that) as well as handling multiple received messages as a set rather than one at a time.

Monitoring SB an be a pain. You basically depend on a bunch of system views to tell you what's going on. Orphaned messages are pain. There's just a lot of little gotchas that can, well, getcha.

Aside from the problems, and there aren't THAT many, I think it has really worked out better than I expected it to. Since SB is integrated into the database, there's no separate message queues to back up outside the database. It's all transacationally consistent. Performance is goo. It's a great solution.

I would use it again and will continue to use it.

Answer 3

I've been using SQL Service Broker since a couple of months after SQL 2005 was released. We use it none stop here sending hundreds of thousands of messages through it per day.

We use it to load data from staging tables to production tables so that the service that loads the staging table doesn't have to wait for the data to actually process, it can go back and get more data to load.

We use it to queue the deletion of files from the file system. (When the row is deleted the file needs to be deleted as well.)

At prior companies I've used it to print loan documents and the checks that were sent out to the customers.

I even used Service Broker to do ETL from an OLTP database to an OLAP database for real time reporting.

Most people (especially DBAs) don't like Service Broker because there isn't any UI for it. If you want to use service broker or see what its doing you have to actually write and run some T/SQL.

Answer 4

What do you guys mean by MSMQ instead? How is it possible to asynchronously push a message to MSMQ in an MSSQL trigger without using the service broker?

Answer 5

At my current company, our usage of SB is somewhat different to that of the other posters. We use SB in SQL2005 mainly as a management tool. For example, we use it to manage updates to a small set of mutable tables that are present in a large number of otherwise immutable databases. All the messages are between services running on the same instance and the message volume is very low.

My experience with SB has been that it can be somewhat 'fiddly' to setup correctly and, as you mentioned in your question, it is hard to get an overview of the state of SB because there is not a single monitoring tool.

Nevertheless, we have found it hugely valuable as a way to automate a lot of database management tasks in a traceable and reliable way.