How to implement the following request using SSB and powershell?

Category: sql server servicebroker


ydbn on Mon, 14 Jan 2013 19:29:39

I want to implement the following requests,

  • To process a list of workload messages by N sub-process (Script block in powershell). 

Is the following a doable solution?

Main process:

  1. En-queue a list of workload messages. 
  2. En-queue N "End of workload" messages. 
  3. Loop: Dequeue one message, log it if it's a "Log" message. 

N sub-process:

  1. Dequeue one workload message and end the conversation.
  2. Stop dequeue workload message if "End of workload" message received. Keep receiving other system messages. Exit if no more messages.
  3. Process the workload.
  4. Send a "Log" message to main process.

How to implement (2) in the sub-process? The problem is one sub-process may receive multiple "End of workload" messages. Or any other better way to do it? I'm using Invoke-SqlCmd of powershell to run SSB Sql statement so I cannot put (1) and (3), (4) in a transaction. 


davidbaxterbrowne on Mon, 14 Jan 2013 20:39:58

Sort of.  With the restriction that you can't use transactions, this is a bit tricky. 

BTW there's no reason you can't use transactions, you just need to use script to run your commands using System.Data.SqlClient, instead of the invoke-sqlcmd commandlet.  EG

With transactions, and proper conversation design, this is simple.  You just ensure that each set of N workload messages and its end-of-workload message is sent in its own conversation.  The sub-process would then keep the conversation locked as it processed all the messages, then send the Log message and end the conversation.

If you don't have transactions, you'll have to change your message structure so that each message is self-contained.  You can make each message contain a complete "batch" of workload messages so that you don't need the end-of-workload message at all.

David Browne