Data Replication with SQL Remote
PART 3. SQL Remote Administration
CHAPTER 12. Administering SQL Remote for Adaptive Server Enterprise
You must protect against losing transactions that have been replicated to remote databases. If transactions are lost that have already been replicated to remote databases, the remote databases will be inconsistent with the consolidated database. In this situation, you may have to re-extract all remote databases.
Protecting against media failure on the transaction logMedia 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 switch 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 switch (-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 switch 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 switch can also be used in addition to mirroring to provide additional protection against total site failure or double media failure.
Stable queue recovery issuesKeeping the stable queue in a separate database complicates backup and recovery, as consistent versions of the two databases have to be recovered.
Normal recovery automatically restores the two databases to a consistent state, but recovery from media failure takes some care. When restoring database dumps and transaction dumps, it is important to recover the stable queue to a consistent point.
Two procedures in the stable store database are provided to help with recovery from media failure:
sp_queue_dump_database This procedure is called whenever a dump database is scanned from the transaction log.
sp_queue_dump_transaction This procedure is called whenever a dump transaction is scanned from the transaction log.
You can modify these stored procedures to issue dump database and dump transaction commands in the stable store database.
Transaction log managementThe Adaptive Server Enterprise log transfer interface allows the Message Agent to scan the Adaptive Server Enterprise transaction log. When this interface is being used, it sets a truncation point in the transaction log. The truncation point prevents Adaptive Server Enterprise from re-using pages in the transaction log before they have been scanned by SSREMOTE. For this reason, DUMP TRANSACTION will not necessarily release transaction log pages that are before the oldest open transaction. DUMP TRANSACTION will not release transaction log pages beyond the "truncation point".
The SQL Remote setup script (ssremote.sql) initializes the truncation point with the following command
dbcc settrunc( 'ltm', 'valid' ).
The truncation point can be reset with the following command
dbcc settrunc( 'ltm', 'ignore' ).
This command tells Adaptive Server Enterprise to ignore the truncation point, allowing transaction log pages beyond the truncation point to be released for reuse. You should only use this command when you are no longer interested in SQL Remote replication with the database and you want to be able to reclaim space in the transaction device with DUMP TRANSACTION commands. Continuing to run SQL Remote after ignoring the truncation point will fail to replicate any transactions that were in transaction log pages that were not scanned by the Message Agent and were freed by DUMP TRANSACTION.