We tried replication under SQL 6.5, and, as I recall, replication would not push DDL (i.e., table and stored procedure) changes from the source server to the destination server. I’m thinking that the same behavior exists in SQL 7.0 and 2000. (Someone please correct me if I’m wrong.)

We tested log shipping in SQL 2000, but it looked like a nightmare to manage if you have a large number of databases. In our environment, the Log Shipping software would backup a transaction log, copy the backup somewhere else (which can take a lot of time and disk space), and then restore the transaction log. There just seemed to be a lot of places to look if something went “bump in the night.” Besides, you can only apply the logs while no one is connected to the database--the “spid whacker” that comes with Log Shipping just didn’t seem to suit our needs.

To create a warm standby server, we now do the following:

- Restore all databases on the warm standby server just once--the databases on that server are left in standby (read-only) mode, which gives our Developers a copy of Production to use as a playground for testing read-only queries (especially reports) with large volumes of realistic data

- Run a full database backup on all databases of the live server nightly

- Run transaction log backups on the live server on an hourly basis throughout the day--this backups get written to a third server so that we can restore them during the day to the warm standby server, if necessary in order to make it the live server in the event of a failure or a "data oops"

- Restore the transaction log backups from the live server to the warm standby server on an nightly basis around midnight

- Resync the logins and default databases on the warm standby server with the live server

This process seems fairly reliable. The last time we had to restore the warm standby server was when we created it over 4 months ago.