Chapter 7: Log Shipping

As noted in Chapter 3, Making a High Availability Technology Choice, log shipping is a tried-and-true method for achieving high availability for a Microsoft SQL Server database. There are two types of log shipping: you can code your own or you can use the log shipping functionality provided with the Developer or Enterprise editions of Microsoft SQL Server 2000 (depending on whether you are working in a development or production environment, respectively). This chapter describes how to plan for, configure, and administer a log shipping solution for SQL Server.

More Info

For a refresher on the basic concepts and terminology of log shipping, review Chapter 3, Making a High Availability Technology Choice.

Uses of Log Shipping

Log shipping has a variety of uses, most of which are related to availability, as follows :

• Primary or additional form of availability for SQL Server This is the no-brainer use of log shipping. You can use log shipping without any other technology for SQL Server high availability as a primary method of availability for your SQL Servers (for example, if you cannot afford or are not able to use failover clustering as a primary method) or in more of a disaster recovery role as a secondary form of availability (for example, to ensure that a failover cluster, which is local, is protected if the data center is damaged and you need to switch to a remote site).

• Planned downtime Planned downtime on the primary database (such as to apply a SQL Server service pack) can be longer than what you can normally tolerate . Instead of waiting for the primary server to become available while waiting for maintenance to complete, you can increase availability by switching to the secondary server. Doing so means that you are promoting the secondary server to be the new primary database accessed by applications and clients .

• Upgrading or migrating SQL Server Whether you are upgrading from Microsoft SQL Server 7.0 or going from SQL Server 2000 to another instance of SQL Server 2000, log shipping can facilitate highly available upgrades or migrations.

  More Info

  To see how to log ship from SQL Server 7.0 to SQL Server 2000, see the section Configuring Log Shipping from SQL Server 7.0 to SQL Server 2000 later in this chapter.

• A database for read-only access This scenario applies only in limited cases. Although it is not a high availability use of log shipping, it is possible to use the secondary database for read-only reporting purposes as transaction logs are being applied at scheduled intervals. Using the log shipped database for read-only access is not recommended if your primary goal is high availability and you are generating and applying transaction logs on a very frequent basis; use another method. If you want to use your log shipped database for read-only access, be aware of the following:

  • When restoring the database that will have the transaction logs applied to it, you must restore it with the STANDBY option.

  • Because SQL Server requires exclusive access to apply transaction logs to the database, no users can be accessing the now read-only copy of the database during the transaction log restore. As noted earlier, you can configure the database not to kill the connections to the database, but that means you have to manually kill the connections, adding administrative overhead. This also means that, no matter what, the users cannot have continuous access to the database for read-only use; they will be interrupted at regular intervals.

  • You must ensure that users have the proper user names , logons , and permissions configured to access the secondary.

  • Set the copy and load times appropriately, which means longer intervals between copies and loads of the transaction logs, to allow users sufficient time to use the secondary for reporting queries. This could cause transaction logs to queue, thereby lengthening the time to bring the secondary online as a new primary in a disaster recovery scenario.

  • Secure the secondary in the same way you secure the primary for read-only access.

  • If you disabled the guest account on the secondary before configuring log shipping and you want to use it for read-only purposes, you might cause a failure when logging into the secondary server to access the read-only database.

    More Info

    See Knowledge Base article 315523, PRB: Removal of Guest Account May Cause Handled exception Access Violation in SQL Server, and article 303722, HOW TO: Grant Access to SQL Logins on a Standby Database When ˜quest User Is Disabled, to see how to enable a database restored with STANDBY to be accessed for read-only purposes. Knowledge Base articles can be found at http://support.microsoft.com .