"Hot swapping" Service Broker queue...

Question

On my wish list for a project is to hot swap a Service Broker queue from one database to another. Can this be done (I'm currently a Service Broker novice)?

The behavior I'm after would be like the following:

Starting configuration: Endpoints on MyServer1 and MyServer2 are sending/receiving messages. Initially MyServer1's MyDatabase1 has the message types, contracts and queue configured for this communication. 

This is what I'd like to do:

1) Pause or stop the Service Broker on MyDatabase1. Complete processing of MyDatabase1's queue.

2) Start the Service Broker on MyDatabase2 using the same message types and contracts.

Note: Endpoints off this server shouldn't be aware of this change. No messages should be lost in the swap.

Best regards,

Scott

Answer 1

Any suggestions?

Answer 2

I'm no expert in Service Broker, though I have experience with it.

I don't think such a thing can be possible with a direct approach.

You see, Service Broker objects are database-dependent. You can't just de-couple them from the database or move them to another database. Just as you can't do the same with a table (the Service Broker queue in its most basic sense is, in fact, a table). Even the Service Broker Service is a database-dependent object (though it creates an instance-level endpoint).

The closest thing I can think of is building some kind of wrapper queue. Have a third database with a Service Broker queue. That queue will be the main entry point for your messages.

When that queue receives messages, it will "forward" them to the relevant "secondary" queue (on MyDatabase1 or MyDatabase2).
Perhaps you could use a configuration table or something to let the "wrapper" queue know which secondary queue is the currently active one.

So the basic logic would be:

1. Send a message to the Service which maps to a queue on MyDatabase3.
2. The activation procedure checks a configuration table on MyDatabase3 which lets it know which database is active: MyDatabase1 or MyDatabase2.
3. It then forwards the message to the currently active database's queue.

When you want to swap between the databases (for example from MyDatabase1 to MyDatabase2):

1. Change the configuration table to let the wrapper queue know of the change.
2. Wait for the messages to clear out in MyDatabase1's queue.

---

Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

Answer 3

This is not possible.

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com