Achieving High Availability - Part 2 1 2 3 4 5

You have a large database to manage.  You have enough storage capacity and maintenance window to perform a full backup every day.  You can suffer as much as 5 minutes worth of data loss.  You can be off-line for as much as 30 minutes during a failover to the standby.  The restoration of a transaction log will take at most 1 minute when dealing in 5 minute increments.  You would perform a full backup once per day, a differential backup once per hour, and a transaction log backup every 5 minutes.   Your biggest concern would be with the time it would take to restore the differential backup and the full backup.  You are still capturing the backups, they just get queued up once per hour and once per day when you apply the full and differential backups to the warm standby.  This simply introduces latency and does not introduce data loss.

The latency is one of the key factors to manage with a warm standby.  It will still take a small amount of time to bring a warm standby on-line in the event of a failure of the primary.  But, it will take less time than for a cold standby.

One of the biggest considerations in setting up the warm standby is in the stored procedures you will write to manage the backup, move, and restoration of the backups.   You can achieve this functionality by using xp_cmdshell and command line isql.   You can set up a job on the warm standby that executes a backup on the primary, pulls a copy of that backup to the warm standby, restores the copy to the standby, and archives the backup.  If you are running SQL Server 7.0, each of these operations should be a job step in a single job.  This prevents you from "stepping" on the backup by trying to move a backup file to the standby while it is still being written to.  This same functionality can be accomplished by dropping a very small control file into the directory where you place your backups.  This control file would signify that the backup is complete.

The restore process can give you difficulties without proper planning.  You will not be able to restore a backup while another restore is already running.  For this I prefer to use a tracking table in an administrative database.  If you have worked with any of my installations, one of the tell tale signs is a database called admin.   This database contains all of the administrative procs and tracking tables.   Create a table called backuptrack that contains whatever data you wish to track.   I normally track the database name, start time, type of backup (full, differential, tran), and a flag for active.  If you are having a significant amount of queuing occurring, you would also want to record the end time in order to evaluate your restores.  When a restore job kicks off, it places an entry in this table setting the flag to 1.  It then checks to ensure that this flag is not set to 1 on any rows in the table, with the exception of the one it just added.  If there is another row set to 1, it grabs the ID of the row with the earliest start time and stores that in a variable.  It then goes into a loop waiting for the flag on that row to be set to zero signifying completion.  It then kicks off its restore process.  This effectively serializes the restores so that one does not attempt to step on another one.   If you are maintaining several databases on your warm standby, you simply serialize by database instead of the entire server.  This serialization process can be enhanced to notify you of significant latency problems such as having 3 or 4 backups queued up or having a backup that has to wait for 10 minutes or more.

While this will serialize the restore process, it does not necessarily serialize the backups that are attempted to be restored.  You also have to include logic to ensure that you don't get backups out of order during an automated restore.   I normally accomplish the first part of this by standard naming conventions.   The backups get named with the database name, type of backup, the date, and the time in 24 hour format down to the minute.  I also define a single directory on the warm standby to transfer files into.  The file is named as follows: <date><time><database><type>.  You can then perform a dir and order the results to place the files into chronological order by creation date.  The final piece of logic that you would include is to check the files in the directory for the oldest backup.  This ensures that you restore backups in the proper order on the warm standby.

This is a pretty simple and high level overview of this process.  I will be spending a future article building a warm standby system complete with all of the associated scripts, tracking tables, and explanations.

Achieving High Availability - Part 2 1 2 3 4 5