To keep a standby server in a state where it can take over during failure (with little difference between it and the production server), you may want to use a technique called "log shipping." Log shipping is found in SQL Server 2000 Enterprise Edition and can be set up in the SQL Server Database Maintenance Wizard and applied to a single specific database. Basically, transaction logs on your production server are backed up and then copied or shipped automatically to the standby server, followed by restoration. While using this technique, it is possible to control the time difference between your backups and restores.
You'll also need to transfer your login information so that the standby server can be made active successfully.
When a production server fails, the administrator brings a standby server online manually. The amount of time from system failure to promoting the standby server will depend on the equipment and the transactional load. To minimize the loss of data, data should be backed up, copied and then committed to the orphaned log using the NO_TRUNCATE command, if your connection to the primary server isn't lost yet and if this is still possible.
To learn more about failing over and synchronizing the login information, check the topic "How to set up and perform a log shipping role change" in
Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield, MA). He has written extensively on a variety of computer topics.
This was first published in March 2005