Sybase DC38133-01-0902-01 Message tracking system, Status information in the remoteuser table

Chapter 10. SQL Remote Administration

The message tracking system

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 table

The 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 offsets

	The 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.
Message ordering	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

237