Windows WF on SQL Service Broker

  • Article

Windows WF on SQL Service Broker

From the time we first started working on the Service Broker programming models five or six years ago, it was obvious that most SSB programs end up looking a lot like a workflow. Messages come in and the application processes them often sending out more messages to other services. When the application is waiting for a response from a message, the state of the application is stored so that it doesn’t have to be kept around if it takes a long time for the response to come back. The Conversation Group ID is a great way to identify state so that when a message arrives it’s easy to find the right sate for the message. The original design for Service Broker even included a “Contract Language” to define the flow for messages within a contract. This was dropped early on because we didn’t want to invent yet another workflow language but some kind of workflow was something I have been talking about for years. When the workflow guys first started talking about making workflow hostable, I thought hosting it on Service Broker was a natural move. My friend Harry Pierson has been working on WF hosted on Service Broker for one of his projects so when I needed to do a demo for an MDM talk I was giving, I decided to write my own WF service hosted on Service Broker.

Harry has played with WF in a CLR stored procedure but for my MDM hub application, I wanted to run the service outside SQL Server which also simplified some of the hosting code. Even though I thought going in that Service Broker was a good fit for workflow, I was surprised how naturally workflow integrated with the Service Broker programming model. WF is really well designed so it wasn’t too hard to do what I needed. I did just enough to support the demo I needed to do but I tried whenever possible to build a general purpose hosting application. When I have a little more time, I’ll try to finish up the more general purpose code. I think the whole WF environment can be a great way to develop Service Broker services. It won’t be as efficient as a stored procedure for simple services but it makes writing complex Service Broker services much more approachable. The workflow designer is hostable so it should be possible to build a whole SSB service development environment based on WF. My MDM sample could be expanded into a toolkit for building MDM synchronization applications. I’m also playing with some string pattern matching algorithms implemented as CLR stored procedures for possible use in duplicate detection workflows.

If you have ever written Service Broker code, you will recognize the main processing loop in the WF hosting code. It’s just a loop that receives a message at the top on then drops into a switch statement based on the message type. The EndDialog and DialogError messages are handled in the hosting code for now. At some point it may make sense to pass these up into the workflow so you can build some custom event handling for them. Normal message types are passed up into a workflow instance for handling. The initial message in a workflow starts up a new workflow instance, assigns the Conversation Group ID as the Workflow Instance ID, and passes the message contents as parameters into the workflow. Any subsequent messages are packaged as Workflow events and passed into the appropriate instance identified by the conversation group ID. This algorithm obviously requires a way to tell the difference between a message that starts a new workflow and one that is passed to an existing workflow. The current code has a hard-coded message type as the initial message but I think this can be generalized so that the first message in a dialog where this service is a target will always start a new instance. I plan to try this later this week.

Most of the logic I wrote was event handling code because pretty much all communication between the hosting code and the workflow is done through events. Begin Dialog, End Dialog, and Send Message are events from the workflow to the hosting code and Message Received is an event from the host code into the workflow. I also wrote several events to handle the MDM activities I needed for the demo. All the database activity is done in the hosting code so I can use the same database connection for everything and make the database transactions work the way I wanted to for data integrity. I made all the event handlers into custom activities so I can use them pretty simply from the designer surface because they are available in the toolbox.

WF comes with a persistence class that stores workflow state in SQL Server but this class manages the SQL connection and transactions. I needed to store the workflow state using the same connection and transaction as the Service Broker messages and my MDM hub updates so I had to write my own persistence class. Fortunately there’s a good sample so it didn’t take long to write. There’s quite a bit of logic for locking the state to prevent simultaneous access but since the state is always locked by the Conversation Group lock, locks are not necessary for my persistence class.

There are parts of WF that I haven’t looked into yet. like transaction scope and compensation but so far I don’t need them. I have only played with sequential workflows but I think a state machine workflow would also work well. I use the dialog handles in messages as correlation ids to tie sends to receives so that I can send out multiple messages on multiple dialogs in parallel and still get the received message routed to the correct event handler. So far it look like my parallel activities execute serially but I’m hoping that’s just because I’m running in the debugger.

Like most demo software, this has been debugged to the extent that it can handle two messages in a row pretty consistently but not much more. There really isn’t any error handling to speak of so it’s way too fragile for use in a real application. As with all prototypes, my next step is to take what I learned, throw out the prototype and start over.