Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We currently have two datacenters in Active - Active mode and using SQL replication between the two datacenters (with a small delay). I'm developing a new process that will use SQL Server Broker Service (external activator) to process a time-based job queue.

There are currently 1200 jobs and each one should be scheduled once every 15 minutes, so I decied to utilize BEGIN CONVERSATION TIMER to schedule the jobs and an external activator to retrieve the message from the queue. In theory (and with some limited testing) everything appears like it would work properly.

So my question is related to SQL replication - in this scenerio,

  1. Would we want the queue tables replicated?
  2. Can they be replicated?
  3. How can I split the jobs (somewhat evenly) between both datacenters without risk of running the same job twice?

I'm just not real clear on this scenario and we want to avoid running the same job twice. I've searched and searched (Google mostly) but can't find anything relavent to my situation. Any clarification / direction on this issue would be greatly appreciated.

TIA.

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted
+100

Replicating queues is a no option, as they cannot be replicated. But I recommend something completely different: Service Broker load balancing. You can have a battery of services (in your case, two) and declare a route to each. As you start dialogs with these services Service Broker will load balance the dialogs across these services (they are hashed based on the conversation_id). So if you have 1000 jobs to schedule and, say, 10 workstations that can execute these jobs you would create an executor service on each of this workstation (eg. 'Worker' service, they'll all named the same) and then send messages to 'Worker', w/o specifying which worker. SSB will load balance the jobs (the messages) across all possible destinations resulting in a load balanced scheduler. The advantage is that everything is deployment time configuration. You can add more workers w/o modifying a single line of code in the application, just by adding new routes.

I recommend you read the article linked, as declaring multiple routes to identical services is a bit tricky as the 'broker_instance' in the route plays a crucial role.

share|improve this answer
    
Very good article. One question: the jobs are initiated by calling a stored proc (insert + begin conversation). The record is replicated to the other datacenter, but no job starts because the stored proc wasn't used for the insert. Are you saying that calling BEGIN CONVERSATION TIMER could start the conversation timer in either datacenter (load balanced) ? –  Chris Gessler Jul 23 '12 at 21:10
    
No, conversation timers are always local. You must send a real messages to be delivered remotely. However, the message may be a message that acts as a 'timer', the processing of this message could be to begin a conversation timer. This, in effect, means that you can start a conversation timer remotely. –  Remus Rusanu Jul 23 '12 at 21:36
    
Makes sense. Our DBAs are going to try out your solution this week. Thanks for the point in the right direction. –  Chris Gessler Jul 23 '12 at 23:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.