SQL Server Broker Service with Active - Active replication in place

Question

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.

Answer 1

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.