Monday, September 9, 2013

TSQL Tuesday SSB Rube Goldberg Solutions


For those of you who don’t know me, I was one of the program managers for the SQL Server 2005 Service Broker.  One of the harder parts of being the Service Broker (SSB) PM was positioning it.  The first reaction I got when I did SSB talks was usually “Is this really different than MSMQ”?  To demonstrate that Service Broker was really much more than yet another reliable messaging product, I worked with several of our early adopters to come up with creative uses for the reliable, transactional, asynchronous queuing aspects of SSB.  I also wrote up a few uses that may not have ever been implemented.  Here are the more significant SSB Rube Goldberg ideas:

Data Warehouse Load Balancing

The first ever SSB customer was an internal Microsoft reporting application.  They had 4 or 5 copies of a database used for doing large ad-hoc queries.  When a query came in it was routed to one of the copies for processing.  The first SSB application was the obvious one – queries came in to a front-end server and were put on a queue on one of the query servers for processing.   The query engine would receive a message from the queue with the query, execute the query, and return the results in a response message.  This worked well and the transactional messaging ensured that queries weren’t lost but the customer wanted to be more intelligent about where queries should be routed.  To do this we set up a dialog timer on each of the query servers.  When the timer expired (every 10 seconds or so) an activation procedure would gather some statistics about running queries, processor, memory, and disk usage, etc. and send the info to the front end server.  The front end server could then use this information to decide which server was the least loaded and route the queries accordingly.

X-ray transmission

This one started as a SOAP toolkit question.  A research project at a college had a web service that people could use to search for and download x-rays.  The problem they were running into was the x-ray files were very large and some of the SOAP clients couldn’t handle the large messages.  We wrote a service that broke an x-ray down into a bunch of Service Broker messages that were sent to the SOAP server.  The client would request a message at a time and assemble the x-ray.  The client could request a message as many times as necessary.  SSB would send the same message until the client acknowledged it.  This was a pretty minor change to the web service that made it usable to significantly more clients.

Express backup

One of the biggest pieces of negative feedback we got for SQL Express was that since it didn’t include SQL Agent there wasn’t an easy way to set up periodic database backups.  The Service Broker dialog timer looked like a good way to do this so I came up with a way to backup SQL Express at any chosen interval:  One of the cool things about this is that the backup routine is implemented in the database being backed up so if you move the database to another server, backups continue to work.  I like to think of this as the first step in the contained database effort.

Task Management

It was a pretty logical step from using SSB for scheduled database backups to using SSB for scheduling any database command.  You just need an activated stored procedure that reads SSB messages and executes them.  Service Broker is distributed so distributed tasks just work.  I had a lot of interest when I published this article but I didn’t hear any implementation stories:

Windows Workflow

Service Broker was designed to support reliable, transactional, asynchronous applications.  The typical Service Broker application queues a message to request an action and then goes away until the action is complete and a response is received.  I always thought that pattern looked a lot like a workflow application so one day I built a Windows Workflow framework that used Service Broker as the eventing layer.  This resulted in a reliable, transactional infrastructure for building workflow applications.  This was pretty cool because as long as the actions the workflow invoked were transactional, the workflow was totally reliable.  The workflow application could die at any point and Service Broker would recover and pick up where it left off as soon as the database started again.

Well, those are my Service Broker Rube Goldberg stories.  I would be very interested in hearing about other unique SSB applications.

No comments:

Post a Comment