User's Guide Part III. Using SQL Anywhere Chapter 28. SQL Remote AdministrationReplication depends on access to operations in the transaction log, and access to old transaction logs is sometimes required. This section describes how to set up backup procedures at the consolidated and remote databases to ensure proper access to old transaction logs.
It is crucial to have good backup practices at SQL Remote consolidated database sites. A lost transaction log could easily mean having to resynchronize remote users. At the consolidated database site, a transaction log mirror is recommended.
For information on transaction log mirrors and other backup procedure information, see the chapter "Backup and Data Recovery".
In many setups, users of laptop databases may receive updates from the office server every day or so. If some messages get lost or deleted, and have to be resent by the message-tracking system, it is possible that changes made several days ago will be required. If a remote user takes a vacation, and messages have been lost in the meantime, changes weeks old may be required. If the transaction log is backed up daily, the log with the changes will no longer be running on the server.
You can specify a directory on the DBREMOTE command line. This directory is where old transaction logs are kept. This section describes how you can set up a backup procedure to ensure that such a directory is kept in proper shape.
One of the options to the backup utility is to rename the transaction log on backup and restart. For the DBBACKUP command-line utility, this is the -r command-line switch. It is recommended that you use this option when backing up the consolidated database and remote database transaction logs.
To see how this option works, consider a consolidated database named HQ.DB, in directory C:\HQ, with a transaction log in directory D:\HQLOG\HQ.LOG. Backing up this transaction log to a directory E:\HQBAK using the rename and restart option carries out the following tasks:
After several backups, the directory D:\HQLOG will contain a set of sequential transaction logs.
You can run the Message Agent with access to these log files using the following command line:
dbremote -c "dbn=hq;..." d:\hqlog
The log directory should not contain any transaction logs other than the sequence of logs generated by this backup procedure.
You can also run the Message Agent pointing to the directory where backup copies are held. However, the backup utility makes backups to the same file name each time by default, so you will need to ensure that old logs are renamed before subsequent backups.
The DELETE_OLD_LOGS database option is set by default to OFF. If it is set to ON, then the old transaction logs will be deleted automatically by the Message Agent when they are no longer needed. A log is no longer needed when all subscribers have confirmed receiving all changes recorded in that log file. This option can help to manage disk space in replication setups.
You can set the DELETE_OLD_LOGS option either for the PUBLIC group or just for the user contained in the Message Agent connection string.
Backup procedures are not as crucial at remote databases as at the consolidated database. You may choose to rely on replication to the consolidated database as a data backup method. In the event of a media failure, the remote database would have to be re-extracted from the consolidated database, and any operations that have not been replicated would be lost. (You could use the log translation utility to attempt to recover lost operations.)
Even if you do choose to rely on replication to protect remote database data, backups still need to be done periodically at remote databases to prevent the transaction log from growing too large. You should use the same option (rename and restart the log) as at the consolidated database, running the Message Agent so that it has access to the renamed log files. If you set the DELETE_OLD_LOGS option to ON at the remote database, the old log files will be deleted automatically by the Message Agent when they are no longer needed.
You can use the -x Message Agent command-line switch to eliminate the need to rename the transaction log on the remote computer when the database engine is shut down. The -x option renames transaction log after it has been scanned for outgoing messages.
This section describes issues in upgrading a consolidated database in a SQL Remote environment. The same considerations apply to SQL Anywhere databases that are primary sites in a Sybase Replication Server installation.
Installing new software does not always make new features available. In many cases, new features require the Upgrade utility to be run on databases. The Upgrade utility adds any information to the system catalog required for new features to be available. When you run the Upgrade utility, it tells you to archive the transaction log. The reason for this is that a new transaction log is created by the Upgrade utility, with a new file format.
When using SQL Remote or Replication Server, the transaction log must be kept for the Message Agent and the Replication Agent, respectively. After running the Upgrade utility, you should shut down the engine, rename the log, and leave it for the Message Agent to delete. The log should also be archived for backup purposes.
For information on the Upgrade utility, see "The Upgrade utility".
If a database is participating in replication, it cannot be unloaded and reloaded without re-synchronizing the databases in the replication system.
Replication is based on the transaction log, and when a database is unloaded and reloaded, the old transaction log is no longer available. For this reason, good backup practices are especially important when participating in replication.