What is Service Broker in SQL Server?

What is Service Broke开发者_运维百科r in SQL Server and is it meaningful to enable it in a simple database, not in a distributed database?

---

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.

---

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

---

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.