Log Shipping
In Microsoft® SQL Server™ 2000 Enterprise Edition, you can use log shipping to feed transaction logs from one database to another on a constant basis. Continually backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the destination database synchronized with the source database. This allows you to have a backup server and also provides a way to offload query processing from the main computer (the source server) to read-only destination servers.
Log Shipping Model
The illustration shows the log shipping model.
In this example, an enterprise has five servers: server A, server B, server C, server D, and server E. Server B is the source server, the server on which log backups and restores are performed and copied. Server C, server D, and server E contain the destination databases on which the log backups from server B are restored, keeping these servers in synchronization with server B. Server A is the monitor server on which the enterprise-level monitoring of log shipping occurs. Each destination or source server is maintained by only one monitor server. The Database Maintenance Plan Wizard is used to define an appropriate delay between the time server B backs up the log backup and the time server C, server D, and server E must restore the log backup. If more time elapses than defined, then server A generates an alert using SQL Server Agent. This alert can aid in troubleshooting the reason the destination server has failed to restore the backups.
Do not use the monitor server as the source server, because the monitor server maintains critical information regarding the log shipping system. The monitor server should be regularly backed up. Keeping the monitor server independent is also better for performance, because monitoring adds unnecessary overhead. Also, as a source server supporting a production workload, it is most likely to fail, which would disrupt the monitoring. The source and destination servers can be on the same computer. However, in this case, SQL Server 2000 failover clustering may provide better results. For more information, see Failover Clustering.
Configuring Log Shipping with the Database Maintenance Plan Wizard
To easily configure log shipping, use the Database Maintenance Plan Wizard. With this wizard, you can:
- Define how often the logs are generated, the time between a backup and a restore operation, and when a destination server is out of synchronization with a source server.
- Register any new servers.
- Create the source databases on all destination servers. When adding a destination database through the Database Maintenance Plan Wizard, you have the option of creating the databases on the destination server or using existing databases. Any existing databases must be in standby mode before you can configure them for log shipping.
- Specify which destination servers might assume the role of the source server.
- Set a restore delay. This delay defines how old a transaction log must be before it is restored. If something goes wrong on the source server, this delay provides an extra time before the corrupted log is restored onto the destination server.
- Create a schedule that sets the backup schedule.
Before using the Database Maintenance Plan Wizard, consider the following:
- The user configuring log shipping must be a member of the sysadmin server role in order to have permission to modify the database to log ship.
- You can configure log shipping only on one database at a time. If you select more than one database, the log shipping option on the wizard is disabled.
- The login used to start the MSSQLServer and SQLServerAgent services must have access to the log shipping plan jobs, the source server, and the destination server.
- When you use the Database Maintenance Plan Wizard to configure log shipping, you can log ship only to disks. The backup-to-tape option is not available.
Configuring Log Shipping Manually
SQL Server 2000 supports manual log shipping from a SQL Server version 7.0 Service Pack 2 (SP2) transaction log if the pending upgrade option is enabled on the computer running SP2.
To enable this option, execute the following code:
EXEC sp_dboption 'database name', 'pending upgrade', 'true'
However, when you are restoring the database after log shipping, you can recover only with the NORECOVERY option.
Note When you manually configure log shipping between a computer running SP2 and a computer running an instance of SQL Server 2000, you cannot use SQL Server replication.
For more information, see the SP2 documentation.
To configure log shipping
To remove log shipping
Changing Log Shipping Roles
SQL Server 2000 allows the changing of log shipping roles through system stored procedures. Before roles can be changed, several preliminary tasks must be performed. For more information, see How to set up and perform a log shipping role change (Transact-SQL).