Log Shipping Essay, Research Paper

The concept of standby servers is not a new one. It has been around a long time and been used by many DBAs. Traditionally, using a standby server for failover has involved manually making database and log backups on the production server and then restoring them to the standby server on a regular basis. This way, should the production server fail, then users could access the standby server instead, and downtime and data loss would be minimized.

What is Log Shipping

Essentially, log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. This in effect keeps the two SQL Servers in “synch”.

Problems with Log Shipping

log shipping is a compromise. It is not the ideal solution, but it is often a practical solution given real-world budget constraints. Some of the problems with log shipping include:

+ Log shipping failover is not automatic. The DBA must still manually failover the server, which means the DBA must be present when the failover occurs.

+ The users will experience some downtime. How long depends on how well you implemented log shipping, the nature of the production server failure, your network, the standby server, and the application or applications to be failed over.

+ Some data can be lost, although not always. How much data is lost depends on how often you schedule log shipping and whether or not the transaction log on the failed production server is recoverable.

+ The database or databases that are being failed over to the standby server cannot be used for anything else. But databases on the standby server not being used for failover can still be used normally.

+ When it comes time for the actual failover, you must do one of two things to make your applications work: either rename the standby server the same name as the failed production server (and the IP address), or re-point your user’s applications to the new standby server. In some cases, neither of these options is practical.

Log Shipping with SQL Server 7.0

In the past, SQL Server developers and DBAs have had to create customized scripts to implement log shipping. But with SQL Server 7.0, Microsoft provided undocumented hooks into the extended stored procedure xp_sqlmaint to help automate log shipping. The Microsoft BackOffice 4.5 Resource Kit (BORK) Log Shipping utility consists of scripts and instructions that use these hooks into xp_sqlmaint for creating a mostly automated log-shipping solution. You can get BORK from Microsoft Press for $250 retail (about $130 street price).

Microsoft developed the BORK Log Shipping utility, as it did all its resource kit utilities, for internal use at Microsoft; Microsoft Product Support doesn’t support the utility. And you’ll find the BORK Log Shipping utility rough around the edges. Although it automates much of the log-shipping process, the utility requires a fair amount of manual setup and administration. However, SQL Server 2000 Enterprise Edition fully supports log shipping and provides a more polished interface than the BORK utility.

In a log-shipping implementation, failing over to the standby server is a manual process. If you require automated failover, a better solution is Microsoft Cluster Services (MSCS), which automatically fails over one cluster node to another cluster node. However, the clustering solution costs more than log shipping, requires additional specialized hardware, and forces a closer coupling, or dependency, of primary and secondary servers. To set up log shipping, all you need is the BORK utility, enough disk space on your production and standby servers to store the database and transaction log backup files, and a network connection between the servers.

Log Shipping with SQL Server 2000

SQL Server 2000 Enterprise Edition fully supports log shipping setup and monitoring. The Enterprise Edition’s Database Maintenance Plan Wizard includes a check box on the Transaction Log Backup dialog box that lets you specify shipping the log to another server. To use this option, you must first establish file shares on the source and destination servers for the transaction log folders. The wizard will then perform the full database backup and restore, create the transaction log backup job, and create the jobs on the destination server that copy and load the backups.

As with the Microsoft BackOffice 4.5 Resource Kit (BORK) Log Shipping utility for SQL Server 7.0, these log-shipping jobs call the xp_sqlmaint extended stored procedure to perform their tasks. The wizard also creates alert jobs for the copy and load operations; these jobs call the same monitoring stored procedures as the BORK utility calls. However, the stored procedures and tables underlying SQL Server 2000’s log-shipping functionality differ from and have more consistent names than the stored procedures and tables that provide the BORK functionality. SQL Server 2000 has seven tables to BORK’s three and has four stored procedures for monitoring.

Overall, the SQL Server 2000 system is more polished than the BORK version. With SQL Server 2000, you can monitor log shipping through the Enterprise Manager instead of through the stored procedures. When you set up log shipping, the Database Maintenance Plan Wizard places a Log Shipping Monitor in the Management node of the server you specify. This monitor consists of a Tab dialog box that lets you observe log shipping’s status and change settings for the source and destination servers. You can also view copy and load histories by right-clicking the Log Shipping Monitor in the details pane.

Like SQL Server 7.0, SQL Server 2000 doesn’t offer automated support for failover or for changing the servers’ roles so that log shipping goes the other direction. Instead, SQL Server 2000 supplies four stored procedures to assist in the failover process and relies on a new built-in Data Transformation Services (DTS) feature to transfer logins from the source to the destination server. You can use the stored procedures to resolve the logins on the former standby server, to change the servers’ roles, and even to change which server the Log Shipping Monitor resides on.