So, I've got an 'A' server and a 'B' server. We are using SQL Service Broker to perform replication. An application we have will need to write data to either the 'A' server, or the 'B' server. (one may be down, and the other one should take over)
Data is written to the database only within stored procedures, which are wrapped in a transaction to keep the data in the tables consistent.
One of these transactions will include all of the messages to be sent to the peer server. So, in my application code, when I attempt to commit a transaction, it should write all of the data to the tables, and send the messages to the peer server.
However, if the peer server is down, then the messages will wait in sys.transmission_queue.
Message order within a dialog conversation is guaranteed, but can I ensure that dialog conversations will be processed in the order that I created them?
For example, I could have a problem if the 'B' server is down, A user writes data to the 'A' server. Once the 'B' server is back up again, if those dialog conversations are processed out of order, they may not work correctly. (They could try to insert data with a bad foreign key, or something like that).
I am working under the following assumptions:
I should have a single dialog conversation for each transaction.
The form of the solution must be as replication by way of sql service broker. (This is done in other parts of the database, so any reason to use an alternate strategy must be compelling).