As part of the backup and recovery plan, along with periodically testing the recovery process, it is considered a best practice to validate the backup database after a successful backup. This is the best way to ensure that your production database is valid, and that the backup is clean and will actually be recoverable should you need it.
Unfortunately, starting a backup database on a server would cause (at a minimum) a checkpoint to occur, advancing the expected starting offset for the log file associated with the backup database. This meant that should you ever have to use that backup database for recovery, you would not be able to apply any log files from your production database to it because expected log offsets would no longer match.
In order to ensure that the backup is valid, and that it can still be used for recovery, you have to start the backup database in “read-only” mode (using the server –r option or the FOR READ ONLY clause of the START DATABASE statement) to ensure no changes are made to the database/log. This works well in most, but not all cases. If your backup was started and there were open transactions in the database (which is common), the backup database has to go through recovery when it starts (to either complete or rollback those transactions and ensure an ACID compliant database), which means you cannot use read-only mode for validation.
In these cases, you have to make a copy of the backup database, start it up and let the database recover, and then do the validation. Obviously this adds a lot of overhead to the backup plan, in both time and disk space.
For version 17, we have addressed this by adding a new in-memory mode called "in-memory validation" or IMV, which is intended to allow backups to be validated without making a separate copy of the database and log files. Unlike the other in-memory modes for SQL Anywhere (in-memory checkpoint-only mode (-im c) and in-memory never-write mode (-im nw )), which are separately licensed, In-memory validation mode is included with all versions of SQL Anywhere: it is not a separately licensed component.
In-memory validation mode is enabled by specifying the "-im validation" switch (or "-im v" for short) on the server command line:
dbsrv17.exe –im v foo.db
The server in-memory validation mode has the following behaviours:
- Database files and log files are never modified on disk
- All modifications are maintained in the database server's cache and therefore the server may run out of cache (ie. memory) if the checkpoint log, redo log and/or undo logs modify a large enough number of database pages.
- The server will create & use a temp file to alleviate cache pressure where it can.
- In-memory modifications (DML, DDL, etc) can only be made by the recovery tasks (ie. for applying the checkpoint log, redo log(s) and undo log(s)).
- All non-recovery operations such as those from client connections, user-defined events, etc are read-only (similar to the server read only (-r) mode).
- Databases will not be shut down after recovery when a flag such as -a, -ad, or -f is specified.
- The server will ignore the "-r" (read-only) command line option(s) and ignore the FOR READ ONLY clause on START DATABASE statements.
- A backup of a database running on a server in IMV mode will generate a backup identical to the original, unmodified database file that was started on the IMV server.
Updates to DBValid/DBValidate()
In addition to the behaviour changes in the server, the dbvalid command line tool now supports a new "-im <mode>" switch to allow control over the in-memory mode of the server but only for servers that are autostarted. Valid modes include: none, v, c, nw. The default mode is v.
When a mode other than 'none' is used (including the default 'v' mode), dbvalid appends the "-im <mode>" switch the StartLine connection parameter, if provided. Otherwise, StartLine is set to "dbeng16 -im <mode>". If StartLine already contains a "-im <mode>" command line switch, the server will respect the first one (ie, the one from the connection string, not the one appended by dbvalid). When –im 'none' is specified, StartLine is neither set nor modified.
The dbtools library DBValidate(…) method has also been updated to support the new in memory mode option.
