Service Broker: Designing the communication betwee

The goal: Reliable transport of continuously generated records to the central SQL.

Summary: The satellite needs to ask the central what are the latest data on its side, and then it is going to send continuously newer ones.

The satellite SQL server may be (re)started sometimes (related to physicall production machine), the central SQL machine is likely to run longer, but there also can be some downtime. The lines are mostly reliable, but one never knows... I expect the connection problems be solved naturally by the SQL Server Service Broker. However, I need to solve the problem of the initial handshake and sending the data. I need to design the protocol and my SSB knowledge is still poor.

I hope I understand the basics of communication using SSB that are nicely and briefly described as

You should start with each work item on its own conversation. The producer (initiator) begins a dialog and send the message describing the work item, then commits. The consumer (target) receives the message (or gets activated), inspects the payload to understand the work item details, executes the work, then ends the dialog and commit. The resulting EndDialog message gets sent back to the initiator service queue, and an activated procedure on the initiator queue responds to it by ending the dialog on the initiator side.

... by Remus Rusanu (if interested see more details in his earlier answer).

I would like to send the records as XML messages like this (a multiline string here)

<row a="1" b="11" c="111" />
<row a="2" b="22" c="222" />
<row a="3" b="33" c="333" />
<row a="4" b="44" c="444" />

and I have already learned how to write the SELECT to obtain the info from the XML message.

The communication: Say the communication mechanism between the SQL servers was just activated...

The satellite SQL obtained new data, and somehow it knows that there is no pending message between the satellite and the central. But it also does not know what data was already sent to the central. Therefore it has to ask the central what is its the last available data.
If I understand the END CONVERSATION correctly, the command causes sending only a kind of empty message N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'. Because of this, the central should probably send my type ReplyMessage with the answer back before the END CONVERSATION.
Meanwhile, the satellite should probably do nothing to behave asychronously (i.e. waits after sending the RequestMessage). Once the ReplyMessage arrives, it activates a satellite procedure that:
- receives the message and get the wanted information,
- receives the EndDialog message and ends the previous dialog at the satellite side,
- prepares the data to be sent to the central (the XML string form),
- opens the new dialog,
- sends the prepared data,
- and makes itself dormant until another ReplyMessage arrives.
The central should behave similarly. Once the RequestMessage arrives, it activates the central procedure that:
- receives the request message,
- extracts the XML information and updates the central database,
- gets the info about the last available data for the satellite,
- forms and sends the ReplyMessage,
- ends the dialog at the central side.

So far, is my view correct?

Now some details that I am not sure about: As I want to make it robust, working without a human touch, it should start on its own when the mechanism is installed. The communication should always be started by the satellite (the central may not know even if the satellite works or even exists).

The satellite already uses a trigger fired by a raw data that is processed to build the records that are collected at the central. This way, the trigger could initiate somehow the first SSB request of the satellite to the central. But...

How the trigger can check that there is no pending communication between the satellite and the central? As the satellite is always the initiator, the question can also be formulated as... How the trigger can check whether the satellite waits for some ReplyMessage? Or, how to know that there is some open dialog between the satellite and the central?
- If there is no conversation, the trigger forms the data record (stored in a local table), and then it could start the communication proces (see the point 1 above).
- If there is any conversation, the trigger only forms the record that is to be sent later, and nothing else is done. Data will be sent when the ReplyMessage is obtained by the activated procedure (see the point 3 above).
On making the satellite dormant (see the last bullet of the point 3 above) I mean that there should probably be no other message in the message queue (nothing to be processed in the loop) and the activated procedure is to be naturally finished. But I am not sure if I think correctly here. Can you comment on that?
If nothing is broken, and if the data is produced fast enough, the satellite and the central have always something to exchange. This way, the trigger should never try to start the communication.
When there is nothing else to be sent to the central by the activated satellite procedure (actually, it is not active), or when the system is restarted somehow (no dialog exists yet), the trigger starts the communication process. But how could I do it? Should the trigger simply send the RequestMessage to the central? (This way the ReplyMessage would activate the satellite procedure and the procedure would continue until there is anything to process.)
Say, the RequestMessage means here is some data, process them, and reply what is the last (or what next should be send or what next action is recommended from the central -- depends on the bussiness logic, probably not important here). Is it OK to send empty XML in the RequestMessage in the sense I do not know what else you need -- tell me?

First update -- based on the Remus Rusanu's answer below.

I have to agree that this would be chatty protocol. Moreover, the records are created as temperature samples from the real environment and the frequency of sampling is rather low. This means that the complicated protocol could be usefull only in the special case of restarting everything or so.

But when the conversation should be left open forever, the unique identifier for the dialog handle or should be stored persistently in some configuration table, or it could even be hardwired to the code. The trigger will send the records on the fly immediately, and the central will simply not send the ReplyMessage. Is it correct? Can that kind of coversation be effectively considered a monolog?

Second update to make this question reasonably short.

See the continuation Service Broker: How should a trigger start infinitely opened dialog?

I need to solve the problem of the initial handshake and sending the data.

I can't tell you how reading this brought back flash memories from 2004... Back then we had in design publish-subscribe protocol ('monologs' vs. 'dialogs') and one of the design points was the requirement for a 'checkpoint'. Think at it this way: the 'publisher' distributes a catalog of items. It initially sends the content of the catalog as a checkpoint message and then it continues to send any update to the catalog (items added or removed, price changes etc) as update messages. Whenever somebody subscribes to this publication it needs to get the checkpoint message and all subsequent update messages so his catalog is up to date. In time the list of updates becomes prohibitively long, so periodically the publisher re-sends the current catalog state as a 'checkpoint'. Subsequent subscribers need only the last checkpoint, and any update since then. Also the publisher infrastructure can remove all the messages sent prior to that last checkpoint because no subscriber will need it anymore. This solves the problem of new subscriber joining 'in the middle' of the data stream and getting a consistent state. The problem (and solution) is no different from how SQL Server itself handles logging and recovery (and the name 'checkpoint' is no coincidence...). Now of course, the whole pub-sub implementation got canned eventually and all you have is dialogs, which do not provide the means to achieve what I described 'out-of-the-box', which is why I see you're trying to invent a replacement yourself... But I digress.

I would summarize your proposed message exchange pattern as follow: a sattelite needs to ask the central station about the current status ('watermark') of the center. It does so in a typical request-response pattern: BEGIN_TRAN->BEGIN_DIALOG->SEND_Request->COMMIT on sender side, Activation->BEGIN_TRAN->RECEIVE->SEND_Response->END->COMMIT on the receiver size, Activation->BEGIN_TRAN->RECEIVE->END->COMMIT on the sender side.

This is an OK pattern in general, but I don't think is the pattern you desire. What you'll end up with is something like this: for every new piece of information the sattelite has, it will ask the center:'do you have this already?' and then the center will respond 'Well, yes I do' or 'No, please send it to me'. This is an incredibly chatty protocol, involving a lot of messages being exchanged for every information that eventually makes it from sattelite to center. I think you should keep your conversations open a long time, consider them as a communication channel. Once established, the sattelite will simply send the data each and every time it has some new information. You do not need to ask for center status, is SSB job to ensure that anything you sent does arrive at the center, even in the presence of network failures. Starting a conversation, sending a 1MM messages and leaving it open is perfectly OK in SSB programming. So is sending another 1MM messages 3 months later. And even doing so while the system is disconnected for 2 weeks and can only actually deliver the messages after Thanksgiving. That is exactly what SSB is designed to handle. The gotcha is that your program code should also be prepared to handle this (ie. never wait for a reply, it may come in... 2 weeks).

Few more words about SSB programming model. Normally there is nowhere anytime anybody actively 'waiting' on anything, SSB programming is all about responding to events. Activation model is much closer to what nowadays is 'Functional programming'. Think node.js. The vast majority of your code will rely in activated procedures. Your code should be always in the form 'begin transaction ->receive message->read current state from DB for the item the message pertains to->decide outcome->update db state->send response (if any)->commit->exit. The onyl exceptions are things like the trigger you have, that is code that injects new messages in this ping-pong of activation driven procedures. This code (the trigger) should never ever wait for a response on whatever it sent. It should SEND and continue. Any response to this trigger's SEND should be handled by activation. Asynchronously, again. Think C#'s new await async.