query distribution using service broker

Hello ,

I am wokring on an application where in I am migrating the legacy application to SQL server. for this when ever I make any changes to the legacy application,I immediately move this changes to SQL, using insert/delete/update to SQL.

I also want all these data on 2 to 3 other SQL servers which are on different remote machines. for this i thought of using the service broker to send the query as messages.

So on the initiator(where the legacy application reside), i create 2 to 3 queues(based upon number of receivers) and each time a query is successful on the initiator, i move these messages(queries) to the initiator queues and send them to the various targets. on the target i just pop these messages and execute them so that the query run on the all the targets and all my databases are in synch.

I need some info on various operations on the Service broker queues like:

1.How do i insert/update/delete from the queue.
2.Can i set some size constraint on the queue
3.Can i get the info like when the queue is full.
4.Can i set some custom flags on the queue like, queue is invalid or valid(setting and getting these falgvalues).
5.Clearing the entire queue.

These are the operations, i need on the queue for various use cases(failure cases)

Can you point to some documentation/tutorials whihc sheds more light on these queue operations.
Thanks,

1. Unlike other messaging products like MSMQ, MQSeries, etc, Service Broker doesn't allow you to enqueue directly to queues. Instead, it provides reliable communication via dialogs between services. Queues are just backing stores used by services to store incoming messages until they are processed. But the targettable endpoints are Services and the primitive for communication is a dialog or conversation.

In your application, you could create one initiator service from where you will send the data, one intermediate service which would receive the data and in turn send copies to each of the target services. The target services would reside in each of your databases on the different SQL Server instances.

2. Service Broker Queues do not have size constraints and therefore there is no such thing as queue is full. Like database tables, they can grow logically up to infinity or physically up to the size of the physical media they reside on.

3. You can enable/disable the queue which causes the service to accept/reject inbound messages.

4. You cannot clear the queue in a single operation. But you could iterate over all the conversation endpoins and end them causing messages associated with those conversations to be deleted from the queue.

Books Online (the SQL Server documentation) and Roger Wolter's book are good resources to learn about Service Broker. Also look at my links on our homepage.