Return to SQL Consulting Home

Using SQL Server in the Enterprise
issue 2, number 1 - January 2010

Sent to you by the Microsoft-Certified experts at SQL Consulting, Inc.

SQL Server Magazine Publishes our Article

SQL Server Magazine will publish an article written by SQL Consulting, Inc. President, Kurt Survance in their February 2010 issue. 

The article is an expansion of one you read in our July 2009 issue Chasing Deadlocks with Deadlock Graph

Don't miss it! 

In This Issue

Been wondering about Service Broker? This month we provide some examples of how Service Broker can be used to solve difficult problems. You might be surprised at how much help it can be.

Browse Our Back Issues 

SQL Consulting Home

Send Us Email

Write an article 

Have you found a better way to deal with a common SQL Server problem?  Share it with us and other readers.

Send us your article

Free SQL Advice

Sometimes all you need is the answer to a question or two to save yourself hours of searching. If a few minutes of our time can help you, we are more than happy to give it to you at no charge. Give us a call at 503.914.6442 when you’re stuck. 

What Can Service Broker Do For You?
When Microsoft released Service Broker with SQL Server 2005 a lot of users quickly found uses for a simple, asynchronous messaging system within SQL Server.  But with all of the new features introduced in SQL Server 2005 many users did not have the time to look into this new technology to see what possibilities it might have for them.

The intent of this article is to give you a quick look at Service Broker with an emphasis on how it can be used to solve problems in your SQL Server environment.  This is not a tutorial for implementing a specific solution with Service Broker.  However there are links at the end of this article to tutorials and downloads of sample Service Broker applications that will point you in the right direction.

What is Service Broker Exactly?
Simply put, Service Broker sends messages between two queues asynchronously. You can send just about anything as a Service Broker message, text, xml, numeric data, etc. An xml document is often used to send structured data such as a record or record set. The messages are guaranteed to arrive in the order they were sent and guaranteed to be sent only once. 

Service Broker is remarkably simple to use.  It is implemented as a set of TSQL commands and database objects.  There is no need to learn a different programming paradigm.

This simple functionality is useful in many scenarios that are not immediately obvious from the description.   We will show in a couple of examples just how to take advantage of this functionality.

Queues and Messages
A service broker queue is a repository for messages.  It is similar to a table in that you can query it, but you cannot directly modify anything in a queue. Queues work in pairs normally, a sending queue and a receiving queue.  Either can be located on the local server or on a remote sql server across the room or across the globe.
A queue can be associated with any stored procedure you create.  Normally the procedure you associate with a queue would process all messages that arrive in the queue. It would apply whatever business logic is required to process them. The simplicity with which you can apply complex business logic to process messages is what gives Service Broker its great flexibility.

Because a message is sent as a varbinary(max) data type, the binary representation of any type of data can be sent.  On reception, your stored procedure will use the CAST function to cast the message content as a string, as xml or whatever data type is required. 
This brief description doesn’t really stir the imagination about what Service Broker can do for you.  It is easier to see how useful an asynchronous messaging system can be if we look at a couple of examples.

Scenario 1:  e-Commerce
During the busy part of the day there are thousands of shoppers on your website. An order is created each time a shopper finishes the check-out process.  A number of processing steps then have to take place before the order is complete. 

The post-processing of each order is slowing down the database.   At the peak load, the processing of so many orders is causing the checkout process to time out.  Orders are being lost during those times.

To solve this problem you could use service broker to separate the order-taking from the order post-processing. Set up an order post-processing procedure on another sql server. When the order is entered, instead of processing it synchronously as before, send an xml message containing the order information to a service broker queue.  The back-end order processing procedure will pull messages from the order queue and process them.

Since the connection between the two applications is asynchronous, the front end application can continue taking orders as fast as possible even when the order processing module has fallen behind or is off-line.  If the server processing the orders has a problem and has to be re-started, the order taking can still go on.  When the order processing server is running again it will start processing orders where it left off.

Scenario 2: Change Auditing
Your company provides an on-line legal practice management service.  New security requirements mandate auditing of all changes to client records.  You implement auditing by creating triggers on all client tables. The triggers write a record of each change to an audit table.   However, the locking involved in updating the audit tables causes serious blocking and deadlocking issues. The application becomes unacceptably slow.

You might use Service Broker to eliminate the need to update the audit table within the trigger.  You can rewrite the trigger to simply throw the update information over the wall in the form of a message to the service broker queue.   This queue can be in the same database, another database on the same server, or on a remote server.  The stored procedure associated with the receiving queue will manage the update of the audit tables without creating the complex locking situation that is causing your current problems.

Since the connection is asynchronous, you will have the option of processing the audit records when it is most convenient.  You could process records as they are put in the queue or you could hold them in the queue and process them in the middle of the night at a time of low system usage.

Scaling Out
As you can see, much of the value of Service Broker lies in the fact that it is asynchronous.  The sending process does not wait for a response.  The two processes, one sending and one receiving, are decoupled. This has important implications for creating a scale-out architecture.  Service Broker provides a communication mechanism for loosely-coupled modules of your application that could then be moved to separate SQL Servers.

Sometimes there are transactional considerations that prohibit asynchronous connections between certain processes.  But there are still many opportunities apply this strategy and move functional units of your application onto separate servers to allow your application to scale  to greater loads.

The intent of this article was to give you enough information to start thinking about possible roles for Service Broker in your database architecture.  We hope it has done that.  The next step might be to get more concrete information about implementing Service Broker. 

For a programmer, one way to learn is to look at the code behind other implementations of Service Broker.  Here is a link to several downloadable examples.

For a more complete look at Service Broker technology and security, you might start with Microsoft’s Service Broker page.

Return to SQL Consulting Home