19

What is Service Broker in SQL Server and is it meaningful to enable it in a simple database, not in a distributed database?

Kara
  • 6,115
  • 16
  • 50
  • 57
Nasser Hadjloo
  • 12,312
  • 15
  • 69
  • 100

3 Answers3

31

SQL Service Broker is an extension mechanism that allows you to queue events for asynchronous processing.

There is no intrinsic harm in enabling the broker. If it's not used, it will just be idle.

It works in both simple and distributed DBs. A simple use case would be a logging queue. We used it at a client to queue XML messages to be processed asynchronously. So we push an XML to an InitatorQueue, and then had a service pull them from the queue, extract some necessary attributes via XPath, and insert them into a persistence table in our database.

Here is a good reference from Microsoft.

Pang
  • 9,564
  • 146
  • 81
  • 122
Nix
  • 57,072
  • 29
  • 149
  • 198
8

Service Broker is a messaging system built into the SQL server db engine. Here's some articles you might read to see how it works.

Centralized Asynchronous Auditing with Service Broker
Centralized Asynchronous Auditing across Instances and Servers with Service Broker
How to troubleshoot Service Broker problems

Mladen Prajdic
  • 15,457
  • 2
  • 43
  • 51
2

Service Broker is an asynchronous messaging system. It allows you to send a message to a queue. Then some worker process picks up the message. There are no guarantees about the order, or when, a message is picked up. But SQL Server does guarantee that message processing happens in a transactional way.

Asynchronous messaging is one of the heaviest architectures out there. You should consider carefully whether the added value is worth the complexity.

SHMHP
  • 63
  • 1
  • 5
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 6
    actually Service Broekr guarantees messages are received in order they are sent in. – Mladen Prajdic May 18 '10 at 15:26
  • @Mladen Prajdic: That's true for messages in the same conversation, but not generally. For example, say you send a CreateClient message to the ClientCreator, and then a CreateAddress message to the AddressCreator. The second might be processed before the first is finished. – Andomar May 18 '10 at 17:19
  • 1
    Because Service Broker is integrated with Sql Server engine, it doesn't need to use distributed transactions, which actually makes it one of the most lightweight (and therefore well performing) architectures out there. – Pawel Marciniak May 18 '10 at 18:12
  • @Pawel Marciniak: Asynchronous programming is a heavy architecture. Agree that within asynchronous programming, Service Broker is a relatively lightweight approach – Andomar May 18 '10 at 18:39