To accomplish a backup server setup, there are a couple of changes that need to be made to the databases on Standby. After initially creating the databases on Standby, the two database options read only and no chkpt on recovery need to be set for each database that will be maintained. As stated earlier, when loading a database and transaction logs, no update activity can take place in the database until all logs have been loaded. These two options help to reduce the risk of having any activity between log loads. When SQL Server is started, a checkpoint record is written in each database after it has been recovered. Setting the no chkpt on recovery option prevents SQL Server from writing this checkpoint, so that if the Standby server goes down, after SQL Server is restarted the databases will still show that no activity has taken place, and transaction logs can continue to be loaded from the Primary server.
If SQL Server is manually stopped by using the SHUTDOWN statement, the shutdown procedure waits for all active processes to complete and checkpoints each database before stopping. The writing of this checkpoint prevents any further transaction log loads, because there would have been an update made to the database. Setting the read only option not only prevents users from performing any write activity in a database, it also causes SQL Server to bypass writing a checkpoint record in the database when a SHUTDOWN statement is issued. When these two options are set for the databases on Standby, database and transaction logs should be able to continue loading regardless of how the Standby server goes down. Note that if the Standby server is needed to replace Primary, you must turn off the no chkpt on recovery and read only options before users can resume updating.
The replication capabilities of Microsoft SQL Server 6.0 can be used to establish and maintain one or more backup servers. For information about replication, see Part 6, Replication.