Problem solve Get help with specific problems with your technologies, process and projects.

Service Broker in SQL Server: How and when to use it

Service Broker allows database developers to write applications that work across multiple instances in SQL Server. Contributor Serdar Yegulalp explains the benefits of this queued messaging system.

By Serdar Yegulalp, Contributor

The description that Microsoft provides for SQL Server 2005's Service Broker is maddeningly vague: "Service Broker...

is a new technology … that helps database developers build secure, reliable, and scalable applications … [It] provides queuing and reliable messaging as part of the Database Engine. These features provide the infrastructure necessary to build high-performance applications that easily scale-up or scale-out."

What does all this mean for the average database developer? For one thing, it's suddenly much easier to write applications that work across multiple instances of SQL Server, whether on the same machine or across multiple machines. Service Broker guarantees that a message passed from one server to another will get there. If by chance it doesn't, the resulting error can be trapped and dealt with.

Service Broker's basic unit of communication between two instances of SQL Server is called a dialog; and it allows messages to be passed in both directions. It also ensures that the messages are received in the order they were sent. This way, you're not, for instance, trying to reference data that hasn't yet been inserted. Messages can also be subjected to a contract, which is, essentially, a rule enforced on both ends regulating what message types can be included in a conversation and who is authorized to send and receive them. Messages are subject to security restrictions and can be encrypted in transit, which comes in handy if you're using WAN links to pass said messages.

The really interesting stuff comes with Service Broker's queuing system. Received messages are held in a queue -- a concept that should already be familiar to SQL Server admins. Service Broker's queues are not just passive mechanisms; they can be queried with a SELECT statement like a table (for instance, to see if a given message is in the queue and waiting to be processed). This makes it possible to do some fairly elegant anticipatory programming.

Here are some examples of reasons you might use Service Broker:

  • Asynchronous triggers. You can construct asynchronous triggers either to a remote server or looping back to the same server. The latter is actually fairly common. A stored procedure or T-SQL command could fire a trigger that queues up another job through Service Broker, which is performed in a separate transaction and divorced entirely from the context of the original job.

  • Anticipatory scheduling. If you have a job that runs regularly and you want to make sure no commands of a given type are running or about to be run (such as a bulk insert or some other major data-changing action), you can poll the message queue. This will see if any such jobs are waiting and then either void them or wait until they have been processed before beginning the scheduled job.

  • Asynchronous data collection. If you need to poll several different servers for data, you could use the Service Broker to dispatch a request and perform the data collection that way. The results could be collected in a table and then sorted later.

  • Parallel processing. Any operations that need to be performed on multiple servers at once (such as harvesting and aggregating information from several databases) could be coordinated through Service Broker.

  • As a substitute for "contrived queues." Many databases I've worked with involved a mechanism I call a "contrived queue" -- a table in the database that functions as a queue. A programmer could switch this sort of thing over to Service Broker and benefit from having a standardized way to queue actions for local processing. It is more efficient and a bit better at self-documenting, since it might not be as clear what the queue table is for or how it's used.

    • In short, any operations that require queues or messages to be set up and passed consistently and reliably between instances of SQL Server, or even back to the same instance, could benefit from using Service Broker.

    About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!

    More information from SeachSQLServer.com

  • ATE: Stored procedure concurrency problems in SQL Server 2005
  • Tip: Developing with SQL Server 2005 Express
  • Guide: T-SQL Learning Guide


    • This was last published in August 2006

    Dig Deeper on SQL Server Stored Procedures

    Start the conversation

    Send me notifications when other members comment.

    Register

    Please create a username to comment.

    -ADS BY GOOGLE

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close