Data Replication with SQL Remote
PART 3. SQL Remote Administration
CHAPTER 10. SQL Remote Administration
SQL Remote has a message tracking system to ensure that all replicated operations are applied in the correct order, no operations are missed, and no operation is applied twice.
Message system failures may lead to replication messages not reaching their destination, or reaching it in a corrupt state. Also, messages may arrive at their destination in a different order from that in which they were sent. This section describes the SQL Remote system for detecting and correcting message system errors, and for ensuring correct application of messages.
If you are using an e-mail message system, you should confirm that e-mail is working properly between the two machines if SQL Remote messages are not being sent and received properly.
The SQL Remote message tracking system is based on status information maintained in the remoteuser SQL Remote system table. The table is maintained by the Message Agent. The Message Agent at a subscriber database sends confirmation to the publisher database to ensure that remoteuser is maintained properly at each end of the subscription.
For Adaptive Server Anywhere, the remoteuser table is the sys.sysremoteuser system table. For Adaptive Server Enterprise, this is the sr_remoteuser table.
Status information in the remoteuser tableThe remoteuser SQL Remote system table contains a row for each subscriber, with status information for messages sent to and received by that subscriber. At the consolidated database, remoteuser contains a row for each remote user. At each remote database, remoteuser contains a single row maintaining information for the consolidated database. (Recall that the consolidated database subscribes to publications from the remote database.)
The remoteuser SQL Remote system table at each end of a subscription is maintained by the Message Agent.
Tracking messages by transaction log offsetsThe message-tracking status information takes the form of offsets in the transaction logs of the publisher and subscriber databases. Each COMMIT is marked in the transaction log by a well-defined offset. The order of transactions can be determined by comparing their offset values.
When messages are sent, they are ordered by the offset of the last COMMIT of the preceding message. If a transaction spans several messages, there is a serial number within the transaction to order the messages correctly. The default maximum message size is 50,000 bytes, but you can use the Message Agent -l command-line switch to change this setting.
The log_sent column holds the local transaction log offset for the latest message sent to the subscriber. When the Message Agent sends a message, it sets the log_sent value to the offset of the last COMMIT in the message. Once the message has been received and applied at the subscribed database, confirmation is sent back to the publisher. When the publisher Message Agent receives the confirmation, it sets the confirm_sent column for that subscriber with the local transaction log offset. Both log_sent and confirm_sent are offsets in the local database transaction log, and confirm_sent cannot be a later offset than log_sent.
When the Message Agent at a subscriber database receives and applies a replication update, it updates the log_received column with the offset of the last COMMIT in the message. The log_received column at any subscriber database therefore contains a transaction log offset in the publisher database's transaction log. After the operations have been received and applied, the Message Agent sends confirmation back to the publisher database and also sets the confirm_received value in the local SYSREMOTEUSER table. The confirm_received column at any subscriber database contains a transaction log offset in the publisher database's transaction log.
SQL Remote subscriptions are two-way operations: each remote database is a subscriber to publications of the consolidated database and the consolidated database subscribes to a matching publication from each remote database. Therefore, the remoteuser SQL Remote system tables at the consolidated and remote database hold complementary information.
The Message Agent applies transactions and updates the log_received value atomically. If a message contains several transactions, and a failure occurs while a message is being applied, the log_received value corresponds exactly to what has been applied and committed.
The remoteuser SQL Remote table contains two other columns that handle resending messages. The resend_count and rereceive_count columns are retry counts that are incremented when messages get lost or deleted for some reason.
The log_send column is updated by the Message Agent based on the SEND frequency information (SEND AT or SEND EVERY as specified in the GRANT REMOTE statement or sp_grant_remote procedure). The value of log_send must be greater than that of log_sent for a user in order for messages to be sent to that user.
Handling of lost or corrupt messagesWhen messages are received at a subscriber database, the Message Agent applies them in the correct order (determined from the log offsets) and sends confirmation to the publisher. If a message is missing, the Message Agent increments the local value of rereceive_count, and requests that it be resent. Other messages present or en route are not applied.
The request from a subscriber to resend a message increments the resend_count value at the publisher database, and also sets the publisher's log_sent value to the value of confirm_sent. This resetting of the log_sent value causes operations to be resent.
Users cannot reset log_sent |
Each message is identified by three values:
Its resend_count.
The transaction log offset of the last COMMIT in the previous message.
A serial number within transactions, for transactions that span messages.
Messages with a resend_count value smaller than rereceive_count are not applied; they are deleted. This ensures that operations are not applied more than once.