Data Replication with SQL Remote
   PART 3. SQL Remote Administration
     CHAPTER 11. Administering SQL Remote for Adaptive Server Anywhere       

Transaction log and backup management

The importance of good backup practices 

Replication 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 re-extract 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 Recovery", in the Adaptive Server Anywhere User's Guide.

Ensuring access to old transactions 

All transaction logs must be guaranteed available until they are no longer needed by the replication system.

In many setups, users of remote 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.

  Setting the transaction log directory

When the Message Agent needs to scan transaction logs other than the current log, it looks through all the transaction log files kept in a designated transaction log directory. A setting on the Message Agent command line tells the Message Agent which directory this is.

Example 

For example, the following command line tells the Message Agent to look in the directory e:\archive to find old transaction logs. The command must be entered all on one line.

dbremote -c "eng=server_name;uid=dba;pwd=sql" e:\archive

Log names are not important 

The Message Agent opens all the files in the transaction log directory to determine which files are logs, so the actual names of the log files are not important.

This section describes how you can set up a backup procedure to ensure that such a directory is kept in proper shape.

  Backup utility options

The Adaptive Server Anywhere backup utility has several options, accessible through Sybase Central wizard selections or through dbbackup command-line switches, that control its behavior.

This section describes two approaches to using the backup utility in SQL Remote consolidated database backups. Backups must ensure that a set of transaction logs suitable for use by the Message Agent is always available.

  Using the live directory as the transaction log directory

It is recommended that you use the option to rename and restart the transaction log when backing up the consolidated database and remote database transaction logs. For the dbbackup command-line utility, this is the -r command-line switch.

The figure below illustrates a database named consol.db, with a transaction log named consol.log in the same directory. For the sake of simplicity, we consider the log to be in the same directory as the database, although this would not be generally safe practice in a production environment. The directory is named c:\live.

A backup command line 

The following command line backs up the database using the rename and restart option:

dbbackup -r -c "uid=dba;pwd=sql" c:\archive

The connection string options would be different for each database.

Effects of the backup 

If you back up the transaction log to a directory c:\archive using the rename and restart option, the Backup utility carries out the following tasks:

1. Backs up the transaction log file, creating a backup file c:\archive\consol.log.

2. Renames the existing transaction log file to 971201nn.log, where nn is the lowest available integer, starting at 00.

3. Starts a new transaction log, as consol.log.

After several backups, the live directory contains a set of sequential transaction logs.

A Message Agent command line 

You can run the Message Agent with access to these log files using the following command line:

dbremote -c "dbn=hq;..." c:\live

  Using the backup directory as the transaction log directory

An alternative procedure is to use the backup directory as the transaction log directory.

Again, the figure below illustrates a database named consol.db, with a transaction log named consol.log in the same directory. For the sake of simplicity, we consider the log to be in the same directory as the database, although this would not be generally safe practice in a production environment. The directory is named c:\live.

A backup command line 

The following command line backs up the database using the rename and restart option, and also uses an option to rename the transaction log backup file:

dbbackup -r -k -c "uid=dba;pwd=sql" c:\archive

The connection string options would be different for each database.

Effects of the backup 

If you back up the transaction log to a directory c:\archive using the rename and restart option and the log renaming option, the Backup utility carries out the following tasks:

1. Renames the existing transaction log file to 971201nn.log, where nn is the lowest available integer, starting at 00.

2. Backs up the transaction log file to the backup directory, creating a backup file named 971201nn.log

3. Starts a new transaction log, as consol.log.

After several backups, the live directory and also the archive directory contain a set of sequential transaction logs.

A Message Agent command line 

You can run the Message Agent with access to these log files using the following command line:

dbremote -c "dbn=hq;..." c:\archive

  Managing old transaction logs

All transaction logs must be guaranteed available until they are no longer needed by the replication system: at that point, they can be discarded.

The replication system no longer needs the logs when all remote databases have received and successfully applied the messages contained in the log files. Remote databases confirm the successful receipt of messages from the consolidated database, and the confirmation sets a value in the consolidated database SQL Remote tables (see The message tracking system). The old transaction logs at the consolidated database are no longer needed by SQL Remote when this receipt confirmation has been received from all remote databases.

Using the Delete_old_logs option 

You can use the Delete_old_logs database option at the consolidated database to manage old transaction logs automatically.

The DELETE_OLD_LOGS database option is set by default to OFF. If it is set to ON, then the old transaction logs are 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.

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.

Example 

• The following statement sets the public DELETE_OLD_LOGS:

SET OPTION PUBLIC.DELETE_OLD_LOGS = 'ON'

  Recovery from database media failure for consolidated databases

This section describes how to recover from a media failure on the database device at the consolidated database.

The procedures to follow are easiest to describe if there is only one transaction log file. While this might not be common for consolidated databases, it is described first, followed by a more common but complicated situation with a set of transaction log files.

  Recovery with a single transaction log

In this case, we assume that there is a single transaction log file, which has existed since the database was created. Also, we assume previous backups of the database file have been made and are available, for example on tape.

  To recover the database:

1. Make a copy of the database and log file.

2. Restore the database (.db) file, not the log file, from tape into a temporary directory.

3. Start the database using the existing transaction log and the -a command-line switch, to apply the transactions and bring the database file up to date.

4. Start the database in your normal way. Any new activity will be appended to the current transaction log.

Example 

This example illustrates recovery using a mirrored transaction log.

Suppose you have a consolidated database file named consol.db in a directory c:\dbdir, and a transaction log file c:\logdir\consol.log which is mirrored to d:\mirdir\consol.mlg.

  To recover from media failure on the C drive:

1. Backup the mirrored transaction log d:\mirdir\consol.mlg.

2. Replace the failed hardware and re-install all affected software.

3. Create a temporary directory to perform the recovery in (for example, c:\recover)

4. Restore the most recent backup of the database file, consol.db, to c:\recover\consol.db.

5. Copy the mirror transaction log, d:\mirdir\consol.mlg, to the recovery directory with a .log extension, giving c:\recover\consol.log.

6. Start the database using the following command line:

   dbeng6 -a C:\RECOVER\CONSOL.DB

7. Shutdown the database server.

8. Backup the recovered database and transaction log from c:\recover.

9. Copy the files from c:\recover to the appropriate production directories:

   • Copy c:\recover\consol.db to c:\dbdir\consol.db

   • Copy c:\recover\consol.log to c:\dbdir\consol.lOG, and to d:\mirdir\consol.mlg.

10. Restart your system normally.

  Recovery with multiple transaction logs

If you have a set of transaction logs, the procedure is different. We assume previous backups of the database file have been made and are available, for example on tape.

  To recover the database:

1. Make a copy of the database and log file.

2. Restore the database (.db) file, not the log file, from tape into a temporary directory.

3. In the temporary directory, start the database, applying the old logs using the -a command-line switch, applying the named transaction logs in the correct order.

4. Start the database using the current transaction log and the -a command-line switch, to apply the transactions and bring the database file up to date.

5. Start the database in your normal way. Any new activity will be appended to the current transaction log.

Example 

Suppose you have a consolidated database file named c:\dbdir\cons.db. The transaction log file c:\dbdir\cons.log is mirrored to d:\mirdir\cons.mlg.

Assume that you perform full backups weekly, and you perform incremental backups daily using the following command:

dbbackup -c "uid=dba;pwd=sql" -r -t E:\BACKDIR

This command backs up the transaction log cons.log to the directory e:\backdir. The transaction log file is then renamed to dateNN.log, where date is the current date and NN is the next number in sequence, and a new transaction log is started. The directory e:\backdir is then backed up using a third-party utility.

In this scenario you would be running the Message Agent with the optional directory to point to the renamed transaction log files. The Message Agent command line would be

dbremote -c "uid=dba;pwd=sql" C:\DBDIR 

On the third day following the weekly backup the database file gets corrupted because of a bad disk block.

  To recover from media failure on the C: drive:

1. Backup the mirrored transaction log d:\mirdir\cons.mlg.

2. Create a temporary directory to perform the recovery in. We will call it c:\recover.

3. Restore the most recent backup of the database file, cons.db to c:\recover\cons.db.

4. Apply the renamed transaction logs in order, as follows

   dbeng6 -a C:\DBDIR\date00.LOG C:\RECOVER\CONS.DB
   dbeng6 -a C:\DBDIR\date01.LOG C:\RECOVER\CONS.DB

5. Copy the current transaction log, c:\dbdir\cons.log to the recovery directory, giving c:\recover\cons.log.

6. Start the database using the following command:

   dbeng6 C:\RECOVER\CONS.DB

7. Shutdown the database server.

8. Backup the recovered database and transaction log from c:\recover.

9. Copy the files from c:\recover to the appropriate production directories.

   • Copy c:\recover\cons.db to c:\dbdir\cons.db.

   • Copy c:\recover\cons.log to c:\dbdir\cons.log, and to d:\mirdir\cons.mlg.

10. Restart your system as normal.

  Backup procedures at remote databases

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.

Automatic transaction log renaming 

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 server is shut down. The -x option renames transaction log after it has been scanned for outgoing messages.

  Upgrading consolidated databases

This section describes issues in upgrading a consolidated database in a SQL Remote environment. The same considerations apply to Adaptive Server 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 Adaptive Server Anywhere User's Guide.

  Unloading and reloading a consolidated database

If a database is participating in replication, particular care needs to be taken if you wish to unload and reload the databases.

Replication is based on the transaction log. 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.

  To unload and reload a consolidated database:

1. Shut down the existing database.

2. Run the dbtran utility to display the starting offset and current log position. Note these values for later use.

   The following command lists the starting offset and current log offset for the database asademo.db:

   dbtran consol.log

3. Rename the current transaction log file so that it is not modified during the unload process.

4. Unload the database.

5. Reload the database.

6. Erase the current transaction log file.

7. Use dblog with the values noted in step 2 to set the new transaction log starting offset:

   dblog -z 137829 consol.db

8. Place the original log file in the same directory as the new log file using a different name.

9. When you run the Message Agent, provide it with the location of the renamed log file on its command line.