23

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.

Jonas Lincoln
  • 9,567
  • 9
  • 35
  • 49
  • I think one of the reasons that it's so under-used is because the documentation and tooling is either poor or non-existent. Scattered blog posts and incomplete examples do not encourage widespread adoption. There is a single book on the subject, written four years ago and, as far as I can tell, SQL Server 2012 offered no enhancements to the product. All of these things make me rather reluctant to use it but the only real alternative that I'm aware of is MSMQ or similar products. – 3Sphere Aug 26 '12 at 18:59

5 Answers5

25

I've been using SQL Service Broker since a couple of months after SQL 2005 was released. We use it non-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.

EBarr
  • 11,826
  • 7
  • 63
  • 85
mrdenny
  • 4,990
  • 2
  • 21
  • 28
  • 2
    Jonas - I can vouch for MrDenny. He's modest, but if you need any Service Broker help, you should talk to him and read his blog - http://itknowledgeexchange.techtarget.com/sql-server/ – Brent Ozar Feb 04 '09 at 17:00
12

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 can be a pain. You basically depend on a bunch of system views to tell you what's going on. Orphaned messages are a 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 transactionally consistent. Performance is good. It's a great solution.

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

EBarr
  • 11,826
  • 7
  • 63
  • 85
Mitch Schroeter
  • 1,001
  • 5
  • 6
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.

Amal Sirisena
  • 1,479
  • 10
  • 10
2

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.

Paul Nearney
  • 6,965
  • 2
  • 30
  • 37
  • 1
    SB Queues are stored inside SQL Databases, but any server that can access the database can use them. So you only really need one central database to store the queues on. – Martin Brown Nov 09 '11 at 14:15
  • 1
    SQL Query Notificatons (http://msdn.microsoft.com/en-us/library/ms130764.aspx) haven't been mentioned here but it is a large area of use for Service Broker. It provides a great cache expiration facility. – EBarr May 18 '12 at 21:24
0

Service Broker can be used in various cases where automation is required to be done in the distributed architecture. Such applications receiving events from various devices and need processing to be done reliably. Where events from devices (detection) or sensors are used for processing the logic of automation. To do exchange of data between multiple database or applications.

I hope the implementation can be more secured and reliable with SB

VyasD
  • 1