SQL Server Log Shipping

SQL Server Log Shipping

Log shipping? Why are we talking about lumber? Actually, log shipping is a powerful yet simple way to ensure failover capability in the Enterprise edition of SQL Server. Baya Pavliashvili gets you started by discussing the advantages and drawbacks of log shipping, and then shows you how to set up log shipping and how to fail your database over to the standby server.

Log shipping is one of the failover solutions offered by SQL Server 2000. In this context, failover means substituting primary server with a backup (sometimes also referred to as standby) server if the primary hardware becomes unusable. Failover solutions can also be used to provide close to 100% uptime—during primary database or server maintenance and during software upgrades you could use the standby server to continue serving your customers.

Failover solutions can be automatic or manual. With automatic failover, the backup server detects when the primary server is not available and takes over without any intervention from the database administrator. An example of an automatic failover solution is clustering. With manual failover, the database administrator (DBA) has to perform some steps to bring the standby server online. In SQL Server 2000, log shipping is a form of manual failover.

NOTE

Only Enterprise and Developer editions of SQL Server support log shipping.

How It Works

Log shipping implementation is straightforward:

1. Full backup of the database is taken on the primary server and copied to the standby server.
2. Standby server maintains a copy of the database.
3. The database is not operational; it can stay in either read-only mode or no-recovery mode.
4. Transaction log for the "log-shipped" database is backed up on the primary server periodically. Note that only databases that are in FULL recovery mode can be log-shipped.
Transaction log backups are placed on a shared drive; standby server's SQL Server Agent account must have access to this shared drive.

5. Transaction log backups are copied to the standby server.
6. Transaction log backups are applied to the database on the standby server in the order that they were taken on the primary server.
7. Either primary server, standby server, or a separate server can be used to monitor log shipping. If you use a separate server for monitoring, it does NOT have to have Enterprise Edition of SQL Server; any edition (other than MSDE) will do.

If the primary server becomes unavailable due to disk failure or some other reason, DBA can take the following steps to fail the database over to the standby server:

1. Perform one last backup of the transaction log on the primary server (if possible).
2. Copy all transaction log backups to the standby server and apply them in the same order they were taken on the primary server.
The last transaction log backup should be restored by using the WITH RECOVERY clause so that the standby database becomes operational.

3. Transfer any logins that exist on the primary server to the standby server. Only the logins that must have access to the log-shipped database must be transferred.
This step might be further complicated if logins with the same name exist on both servers. In such cases, the DBA needs to ensure that appropriate mappings exist between SQL Server logins and database users on the standby server.

During the initial configuration of log shipping, you can allow the standby database to assume the primary role. That means you can ship transaction logs from the standby server to the primary server after you have failed the primary database over. So if primary server (server A) fails over to standby server (server B), servers can switch roles so that you can fail server B back to server A if needed.