Purpose

Maintain a redundant system that is available at a moment’s notice should the primary MS SQL Server become unusable. At the same time preserve a restorable archive of dump activity. The process must make an exhaustive effort to notify someone if problems occur and have minimal impact on the primary system performance.

Description

Initially and then periodically perform a full dump of the source system and restore this to the target system to synchronizes the source and target databases. A stored procedure, dbSync is used for this operation. The databases are held in this synchronized state by controlling checkpoint processing in both systems and updatability of the target system. If the data is changing regularly, it is necessary to periodically apply all transactions that have occurred since the last dump. A second stored procedure logSync is used for this operation. To support the ability to recover from the database dumps it is important to establish a balanced and interleaved execution of the dbSync and the logSync processes. Too many log dumps between database dumps yields a long and precarious restore process. Too long of an interval between log dumps results in expensive network bandwidth hits when the dump files are transported to the target system. To minimize the impact of this process on the source system, a pull implementation from the target (Hot Site) system is preferred.

An archive of the last two full dumps and any log dumps adjunct to the database dumps is maintained on both the target and the source systems. In the event that the servers cannot communicate, this will leave each with a stand alone recovery path. The usability of each dump is proven on the target system. (Many forward recovery and disaster recovery plans omit a verification of dumps as part of the normal process!) To conserve network bandwidth, database dumps are zipped before transfer and held in archive as a zip file. A script that will forward recover from the last full dump through all subsequent log dumps is available as forward_recovey. Should the most recent recovery path prove unusable, a script to restore the prior full dump and all associated log dumps, rollback_recovery, can be executed.

Setup

A small database is required for this process. A 10MB database named as admin with the log on the same device and truncate on checkpoint enabled is suggested (follow the link for documentation on creating the database).

SQL Mail needs to be correctly configured for use by the stored procedures in admin. One method of installing SQL Mail that works is at Michael Culver's web site and at Stephen Wynkoop's site. There is also a troubleshooter on Microsoft's site, and some info in the Microsoft KB.

This process is implemented using PKZIP 2.04. This version of PKZIP supports command line zip and unzip operations. Versions or products that don't work from the command line won’t work from xp_cmdshell (duh). Contention for the zip exe’s has been a problem. For this reason, a private copy of the utility is made available for each database. That's the main reason pkzip 2.04 is preferred. The executables are quite small (pkzip 17k, pkunzip 42k) compared to later DOS versions (pkzip25 332k).

All databases that will implement the logSync stored procedure must have their log on it’s own device. The SQL Server DUMP TRAN and LOAD TRAN statements are the reason for this requirement. By the same token, the source and target databases need to be exact duplicates according to sp_helpdb <database_name> to avoid internal issues with the DUMP and LOAD commands.

The user that logs on to the SQL Server running admin must have full privileges on all directories involved in the process (i.e., source server, target server, log_manager server).

The top level of the archive directories must be established before running the stored procedures.

Usage

Schedule all dbSync and logSync tasks in the MS SQL Server Enterprise Manager.

Coordinate the log management schedule with database maintenance tasks.

Always keep the target database options set as follows:

Source
no checkpoint on recovery enabled This is not hard and fast. If you want to avoid the need to reinitiate the dbSync process whenever the source SQL Server goes down, keep this option set.
truncate log on checkpoint disabled. This is mandatory only for databases where the logSync procedure is implemented.
Select into/bulk copy disabled. This is mandatory only for databases where the logSync procedure is implemented.
Target
read only enabled. This is the only way to assure a mirror image of the source system. If any change is made to the target database, the logSync process will fail. A dbSync is necessary to restore the synchronization of the source and target databases.
no checkpoint on recovery enabled. Even if you decide not to enable this option on the source, do it on the target. This will give you flexibility to recycle SQL Server for development and testing on the target system.
truncate log on checkpoint disabled. This is mandatory only for databases where the logSync procedure is implemented. Even if the logSync procedure is not used, the log can not grow in a read only database.
Select into/bulk copy disabled. This is mandatory only for databases where the logSync procedure is implemented.
If either the source or target database is checkpointed, a dbSync will be required.

Archive

The archive architecture consists of a directory hierarchy with a sub folder for each database for which a hot site is being kept. Each database folder in turn has an archive subfolder.

An examination of the directory structure will help to illustrate the archive.

H:\backup>tree
Directory PATH listing for volume SQL-RAID5 #1
H:
_historical
|    |______archive
|
|_bwtest
|    |______archive
|
|_master
|    |______archive
|
|_control
|    |______archive
|
|_prod
|    |______archive
|
|_users
     |______archive

Looking inside a databases folder:
H:\backup\bwtest>tree /F
Directory PATH listing for volume SQL-RAID5 #1
H:
|_bwtedump.DAT
|    |________PKUNZIP.EXE
|    |________PKZIP.EXE
|    |________unzipdmp.bat
|    |________zipdump.bat
|    |
|    |_archive
         |________BWTEDUMP.ZIP
         |________BWTEDUMP.zzz

This document will be updated as time permits. It is not complete. (duh) If you have any specific questions, feel free to contact me by email.