When should you refer to this document?
You should refer to this document when the following 3 conditions exist:
1) You have lost the log file and mirror log file for a database participating in a SQL Remote replication set up. If you are not using a mirror log file, then loss of the log file is sufficient.
AND
2) You have not been running dbremote with the "-u" switch to "Process only backed up transactions", or if you have been running dbremote with the "-u" switch, you do not have a valid backup available.
3) You are using SQL Remote for Adaptive Server Anywhere
What is the importance of the log file to the SQL Remote message tracking system?
The SQL Remote message tracking system tracks messages based on the log offset from the transaction log, of the sending database, that corresponds to the operations contained in the messages. When a log file is lost, a gap is introduced into the sequence of transaction log offsets. Since the SQL Remote message tracking system relies on the sequence of log offsets being contiguous, the introduction of a gap breaks the system. The log offsets required by the message tracking system are maintained in the sys.sysremoteuser table.
For more detailed information on the SQL Remote message tracking system, please refer to the following section of the documentation:
Replication and Syncronization Guide
PART 3. SQL Remote
CHAPTER 18. SQL Remote Administration
The message tracking system
URL: http://manuals.sybase.com:80/onlinebooks/group-sas/awg0700e/dbrsen7/@Generic__BookView
What are the consequences of a lost log file to data movement?
SQL Remote generates and sends messages based on the contents of the transaction log(s). When a log file is lost, the information required to replicate transactions that had been recorded in that log file is also lost. This means that when you lose a log file there are row values, or transactions, in that database which have not yet been replicated and which do not exist at any other node in the replicating system. Given this, resetting the SQL Remote message tracking system consists of two principle tasks:
1) Reconciliation of the data between the consolidated database and the remote databases
2) Resetting of the log offsets contained in the sys.sysremoteuser table
Reconciliation of the Data
We will consider 3 scenarios under which the requirements to reconcile data differ. These scenarios are:
1) One-way publication of data from the consolidated database down to the remote databases
2) One-way publication of data from the remote databases up to the consolidated database
3) Bi-directional publication between the consolidated and remote databases
One-way publication of data from the consolidated database down to the remote databases
If your replication environment consists entirely of one-way publications moving data down from the consolidated database to the remote databases, then all of the data in the system will be contained in the consolidated database. In this scenario, the remote databases can be re-extracted without risk of data loss. For guidelines on how best to perform a re-extraction of an existing remote database, please refer to "Appendix A - Recommendations for Re-extracting Remote Databases" in this document.
One-way publication of data from the remote database up to the consolidated database
In this scenario, data exists on the remote sites which does not exist on the consolidated site. Re-extraction of a database without first reconciling the data would result in the loss of any data that had not yet replicated. Since the data flow is from the remote database up to the consolidated database, the version of the data on the remote database can be taken as correct.
One possible technique for reconciling the data is presented in "Appendix B - Data Reconciliation Using Proxy Tables".
Bi-directional publication between the consolidated and remote databases
This scenario is very similar to the one-way publication in which data moves from the remotes up to the consolidated database. However, this is a more complicated situation since it is possible for a given row to exist on both the consolidated and the remote database but with different values for some columns. Since the data flow is in both directions, the values neither at the consolidated nor at the remote can arbitrarily be considered to be correct. The data owners will be required to make a decision as to the correct values of the data. The business rules that are implemented in the conflict resolution triggers of the consolidated database can be used as a guideline for deciding which version of the data should be considered correct.
For more information on how SQL Remote would handle conflicts during normal operation, please refer to the following section of the documentation:
Replication and Syncronization Guide
PART 3. SQL Remote
CHAPTER 15. SQL Remote Design for Adaptive Server Anywhere
Managing conflicts
URL: http://manuals.sybase.com:80/onlinebooks/group-sas/awg0700e/dbrsen7/@Generic__BookView
·Note that the conflict resolution triggers themselves will not fire during the reconciliation process. Their value to the reconciliation process is as documentation of the business rules for resolving conflicts.
One possible technique for reconciling the data is presented in "Appendix B - Data Reconciliation Using Proxy Tables".
Resetting of the log offsets
You can reset the SQL Remote message tracking system either by re-extracting a given remote database or by manually issuing a REMOTE RESET command. For guidelines on how best to perform a re-extraction of an existing remote database, please refer to "Appendix A - Recommendations for Re-extracting Remote Databases" in this document.
The REMOTE RESET command reinitializes the values in the sys.sysremoteuser table for the user specified in the command. If you choose to use this command, then it must be executed at both the consolidated and the remote databases. The next time you run dbremote after you have executed the REMOTE RESET command, new instances of the "0" message will be generated and exchanged between the remote and the consolidated database. For more information on the "0" message, please refer to "Appendix A - Recommendations for Re-extracting Remote Databases" in this document.
It is important to note that the REMOTE RESET command does not recover data. Rather, it introduces a gap in the data by forcing dbremote to ignore all transactions in the log file prior to point at which the REMOTE RESET command was issued. Issuing the REMOTE RESET command is a safe and effective method of resetting the message tracking system without having to re-extract a remote database when:
1) the databases involved are completely up to date
AND
2) the sections of the log files, at both ends, that will be skipped do not contain transactions to be replicated
On the other hand, if you are not satisfied that the databases involved are up to date, or if you know that the databases are definitely out of synch, then issuing the REMOTE RESET command will increase rather than decrease the gap in data between the consolidated and remote databases. Even in this situation, you may choose to issue the REMOTE RESET commands to reset the message tracking system and allow transactions to be replicated while a new database is being extracted and deployed.
For more information on the REMOTE RESET command, please refer to the following section of the docum