Backing up databases involved in synchronization and replication

If your database is part of a SQL Remote installation, the Message Agent must have access to old transactions. If it is a consolidated database, it holds the master copy of the entire SQL Remote installation, and thorough backup procedures are essential to ensure that no data is lost.

If your database is a primary site in a Replication Server installation, the Replication Agent requires access to old transactions. However, disk space limitations often make it impractical to let the transaction log grow indefinitely.

If your database is participating in a MobiLink setup using dbmlsync, the same considerations apply. However, if your database is a MobiLink consolidated database, old transaction logs are not required.

For synchronization and replication environments, you can choose backup options to rename and restart the transaction log. This kind of backup prevents open-ended growth of the transaction log, while maintaining information about the old transactions.

This kind of backup is illustrated in the figure below.

For a database involved in replication, the database file and log file are copied to the backup directory. The transaction log is renamed and the database file is left in place. Information about old transactions is still available for the Message Agent and the Replication Agent.

For more information, see Make a backup and rename the original transaction log.

Backup procedures are not as crucial on remote databases as they are on 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. See Log Translation utility (dbtran)).

Even if you do choose to rely on replication to protect remote database data, backups may 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 are deleted automatically by the Message Agent when they are no longer needed.

Automatic transaction log renaming in SQL Remote

Use the -x Message Agent option to eliminate the need to rename the transaction log on the remote computer when the database server is shut down. The -x option renames the transaction log after it has been scanned for outgoing messages. See Message Agent (dbremote).


Managing the transaction log