SQL Remote User's Guide
Administering SQL Remote for Adaptive Server Enterprise
Adaptive Server Enterprise transaction log and backup management
Media failure on the transaction log can cause committed transactions to be lost. If the transaction log has been scanned and these transactions have already been sent to subscriber databases, then the subscribing databases contain transactions that are lost from the publishing database, and the databases are in an inconsistent state.
The transaction log is needed, even after the entries have been scanned into the stable queue, to guard against media failure on the database file. If the database is lost, it must be recovered to a point that includes every transaction that may have been sent to remote databases.
This recovery is done by restoring a database dump and loading transaction dumps to bring the database up to date. The last transaction dump restored is the dump of the active transaction log at the time of the failure.
There are two ways of protecting against inconsistency arising from media failure on the transaction log:
Mirror the transaction log When a device is mirrored, all writes to the device are copied to a separate physical device.
Only replicate backed-up transactions There is a command-line option for the Message Agent that prevents it from sending transactions until they are backed up.
The only way to protect against media failure on the transaction log is by mirroring the transaction log.
Disk mirroring can provide nonstop recovery in the event of media failure. The disk mirror command causes an Adaptive Server Enterprise database device to be duplicated—that is, all writes to the device are copied to a separate physical device. If one of the devices fails, the other contains an up-to-date copy of all transactions.
For information on disk mirroring in Adaptive Server Enterprise, see the chapter "Mirroring Database Devices", in the Adaptive Server Enterprise System Administration Guide.
The Message Agent also provides a command-line option (-u
) that only sends transactions that have been backup up. In Adaptive Server Enterprise, this means transactions complete before the latest dump database command or dump transaction command.
The goal of the strategy is to reduce the possibility of requiring re-extraction of remote databases to an acceptable level. In large setups, the possibility must be as close to zero as possible, as the cost of re-extraction (in terms of down time) is very high.
The Message Agent -u
command-line option can be used instead of transaction log mirroring when recovery of all transactions in a consolidated database is not needed and mirroring is considered too expensive. This may be true in small setups or setups where there are no local users on the consolidated database.
The Message Agent -u
command-line option can also be used in addition to mirroring to provide additional protection against total site failure or double media failure.