Export (0) Print
Expand All

Chapter 13 - Log Shipping

It is 2 PM on a record business day. The Microsoft® SQL Server™ 2000 server housing your mission-critical customer database experiences a disk failure. What do you do? When was the last known good backup? Is there another server available to restore the backup onto? Is SQL Server even on that server? One way to protect your database from a potentially prolonged outage is to utilize a feature new to SQL Server 2000 Enterprise Edition: log shipping.

Log shipping is a process that takes transaction logs from a primary SQL Server and applies them sequentially on a scheduled basis to another SQL Server. In the event of a failure, an application could be redirected to this other server, which would be only slightly out of sync with the primary server or potentially up to date with it, depending on when the last transactions were applied. Log shipping is a means of protecting organizations in the event of a logical or physical system failure.

For the first time with SQL Server 2000, log shipping is built into the product, and is no longer a custom implementation. The feature is robust, and it is easy to configure flexible deployments and monitor them in SQL Server Enterprise Manager. Log shipping complements other technologies in SQL Server, such as replication and SQL Server 2000 failover clustering. Its foundation is built on the standard SQL Server backup and restore commands, and is augmented by additional tables and stored procedures.

How Log Shipping Works

Cc917640.spacer(en-us,TechNet.10).gif Cc917640.spacer(en-us,TechNet.10).gif

To understand how to use the log shipping feature in SQL Server, you must first become familiar with the components involved in the process.


The SQL Server 2000 implementation of log shipping is comprised of the following elements:

  • Primary server 

    This is the production box that contains the source database to be log shipped. 

  • One or more secondary servers 

    This server will receive the transaction logs and apply them to the proper database. This server is also known as a "warm standby." 

  • Monitor Server 

    This server will display the status of the log shipping processes configured. This is known as the Log Shipping Monitor, and can be found under the Management folder in SQL Server Enterprise Manager. 

The combination of a primary server, with the database to log ship, and a secondary server, with the database to which to apply the transaction logs, is known as a log shipping pair.

Note You cannot implement log shipping between two instances of SQL Server 2000 if one part of the log shipping pair is not an Enterprise Edition installation. Log shipping can be set up between SQL Server version 7.0 with Service Pack 2 installed and SQL Server 2000 (see "Log Shipping Interoperability Between SQL Server 7.0 and SQL Server 2000" later in this chapter).

Database Tables

In addition to the basic components involved in log shipping, a number of tables designed to support log shipping are included in SQL Server 2000. They are:

  • log_shipping_database 

    This table contains a list of all databases configured to be log shipped, as well as the maintenance plan ID associated with it. Only the primary server uses this table. 

  • log_shipping_monitor 

    This table stores the name of the monitor server, and how to connect to it. All primary and secondary log shipping servers use this table. 

  • log_shipping_plan_databases 

    This table stores the relevant parameters involved in the log shipping process: the maintenance plan, the source and destination databases, the last file copied and loaded, and other configuration parameters. This is populated only on the warm standby server. 

  • log_shipping_plan_history 

    This table stores the history information for the log shipping plans: the maintenance plan name, the file name to be shipped, the source and destination databases, whether or not the process was a success, how long it took, and what error occurred if it was unsuccessful. This is populated only on the warm standby server. 

  • log_shipping_plans 

    This table stores the information associated with each log shipping maintenance plan, namely the server and path names involved, and any jobs associated with the log shipping maintenance plan. This is populated only on the warm standby server. 

  • log_shipping_primaries 

    This table stores the information associated with each log shipping maintenance plan on the primary server only, including the server name, plan name, database name, thresholds, and the name of the last transaction log backup file. 

  • log_shipping_secondaries 

    This table stores the information associated with each log shipping maintenance plan on the secondary server, including the server name, plan name, database name, thresholds, and the name of the last transaction log backup file copied and loaded. This is populated only on the primary server. 

  • sysdbmaintplans 

    This is a system table that stores the complete information about the maintenance plans associated with log shipping. 

  • sysjobs 

    This is a system table that stores the complete information about the jobs associated with log shipping. 

  • syslogins 

    This is a system table that stores the logins for each SQL Server database. 

Stored Procedures

Listed below are all of the stored procedures used in log shipping:

  • sp_add_log_shipping_database 

  • sp_add_log_shipping_plan 

  • sp_add_log_shipping_plan_database 

  • sp_add_log_shipping_primary 

  • sp_add_log_shipping_secondary 

  • sp_change_monitor_role 

  • sp_change_primary_role 

  • sp_change_secondary_role 

  • sp_create_log_shipping_monitor_account 

  • sp_define_log_shipping_monitor 

  • sp_delete_log_shipping_database 

  • sp_delete_log_shipping_monitor_info 

  • sp_delete_log_shipping_plan 

  • sp_delete_log_shipping_plan_database 

  • sp_delete_log_shipping_primary 

  • sp_delete_log_shipping_secondary 

  • sp_get_log_shipping_monitor_info 

  • sp_remove_log_shipping_monitor 

  • sp_resolve_logins 

  • sp_update_log_shipping_monitor_info 

  • sp_update_log_shipping_plan 

  • sp_update_log_shipping_plan_database 

For complete details on the stored procedures, including the specific parameters associated with each one, see SQL Server Books Online.

log_shipping_monitor_probe User

It is recommended that you use integrated security with your SQL Server installation. However, if integrated security is not used, and mixed mode is, the installation of log shipping creates the user log_shipping_monitor_probe. This account is used by the primary and secondary servers to update the log_shipping_primaries and log_shipping_secondaries tables when a transaction log backup, copy, or restore operation occurs.

Log Shipping Process

First, make a full backup of the database to be log shipped. This backup is then applied to any secondary server that will receive the transaction logs. When you want to restore the database, choose among the following restore options: WITH STANDBY (making it read-only, but able to restore transaction logs), WITH NORECOVERY (making it available to restore transaction logs only), and, depending on the configuration, KEEP_REPLICATION (preserving replication settings on a particular database when log shipping is employed).

Once the backup is applied, the transaction log shipping may commence. Transaction logs are backed up on the primary server at a specified interval, generally 1 to 15 minutes apart. That file is then copied across the network to the secondary server and applied. This process would continue indefinitely unless manually shut down, or there is a primary server failure. The transaction log filename is databasename_tlog_YYYYMODDHHMI.TRN, where YYYY is the four-digit year, MO is the month, DD is the day, HH is the hour, and MI is the minute. When the transaction log is applied, SQL Server needs exclusive access to the database on the secondary server. No users can be in the database, active or inactive, and a process to terminate the users automatically can be configured. Otherwise, it will need to be done manually.


After configuration, the log shipping process is implemented by using a database maintenance plan (for each database log shipped). Use this plan with corresponding SQL Server Agent jobs that all invoke the command line utility sqlmaint through the extended stored procedure xp_sqlmaint:

  1. Back up the transaction log. The job is located on the primary server; if the secondary server is configured as a potential primary server, the job also exists on the secondary server as well. It is named "Transaction Log Backup Job for DB Maintenance Plan 'Configured Plan Name' ". 

  2. Copy the transaction log file. This job is located on the secondary server, and is named "Log Shipping Copy for PRIMARYSERVER.databasename_logshipping". Currently, the transaction log is copied to the secondary server using a standard filesystem copy, and no compression is used. 

  3. Restore the transaction log file. This job is located on the secondary server, and is named "Log Shipping Restore for PRIMARYSERVER.databasename_logshipping". 

Two additional SQL Server Agent jobs are configured on the primary server: an alert job for the backup process reported in the log shipping monitor, and an alert job for the restore process as well.

Bringing a Secondary Server Online as a Primary

The need to create a redundant copy of your data exists for one main purpose: In the event that the primary server goes down, either planned or unplanned, you want to have access to the data and continue to be up and running. This is why it is vital to understand how to bring the designated secondary database online.

A major part of the switch process is to ensure that all logins from the primary exist on the secondary. A Data Transformation Services (DTS) package needs to be created on the primary server to copy the logins from the primary to the secondary. A SQL Server Agent job should back up the syslogins table, and copy this backup file to a location accessible by the secondary. Once this is done, the DTS package should be executed. If the primary server is completely unavailable, and the logins were never transferred, the warm standby cannot be brought online.

It is now time to initiate the role change to make the secondary server the new primary server. This is achieved by executing four stored procedures that should be configured as individual steps in a SQL Server Agent job, ensuring that there will be no human error when entering syntax and if the need arises to bring the secondary online.

For complete details on creating the DTS package to copy logins and on how to change server roles, see "How to set up and perform a log shipping role change (Transact-SQL)" in SQL Server Books Online.

The final step is to repoint any applications, direct end users, and other things, such as middle-tier servers like Component Services, available in Microsoft Windows® 2000, and ODBC Datasource Names (DSNs), to the "new" primary server. This is potentially the most time consuming part of the process. Utilizing a Network Load Balanced may help; consider evaluating this for your production environment.

Configuring Log Shipping

Cc917640.spacer(en-us,TechNet.10).gif Cc917640.spacer(en-us,TechNet.10).gif

Now that you have an understanding of how log shipping works, it is time to implement it. This section will list some of the important considerations to take into account when configuring your solution. For complete installation instructions, see "How to configure log shipping (Enterprise Manager)" in SQL Server Books Online.

Keeping the Data in Sync

This is the most important aspect of configuring log shipping. How close should the secondary server be behind the primary? Or, from a business standpoint, how many transactions can we afford to lose? A few factors go into making this decision:

  • How many transactions are generated per hour? This number should be measured in some way before implementing log shipping. One possible way is to load test the application in a lab situation. 

  • How big is the transaction log file generated, and how long does it take to copy and apply to the secondary server? 

By default, transaction logs are backed up every 15 minutes. You can set this interval to be as frequent as every 1 minute, or as infrequent as, say, every 30 or 60 minutes. Each environment and use will be different. On a highly utilized OLTP database, such as one that would power an e-commerce site, transactions are more important, so the backup frequency may drop to every 5 minutes to ensure a smaller file size.

In conjunction with determining the frequency of transaction log file backups, devise a plan as to how often the files will be copied to the secondary server(s), and if there will be any delay in applying them. If the load delay is set to 0, which is the default, the transaction log file will be applied as soon as it is copied. For more information, see the "Best Practices" section later in this chapter.


This section will detail where to place your servers for log shipping, and some tips on connectivity.


The location of the log shipped servers is crucial to a successful implementation. If the primary and secondary are located next to each other in the same data center, and a failure occurs, you may have lost both your main and backup servers. So the point of having redundancy is negated.

Log shipping allows you to place servers in any geographic location. This is the recommended practice. For example, a company has data centers in Boston, New York, Tokyo, London, and Sydney. The primary HR server is located in New York, but is log shipped to servers in each location. If it is not possible to spread the primary and secondary servers to different locations, then other precautions should be met: The primary and secondary should be, at a minimum, on different power grids, and other similar high availability principles should be applied. For more information, see Chapter 16, "Five Nines: The Ultimate in High Availability."

The log shipping pair should never exist on the same server. Even though this is possible due to the multiple instances support of SQL Server 2000, the reason for not having them on the same box is that the server becomes a single point of failure.


The log shipping pair must be able to communicate with each other. This may mean that both servers are part of the same domain, or that proper login credentials are provided so the servers have the proper access. Ensure that all secondary servers also have connectivity to the public network so all users and applications can connect to it in the event of a switch.

Basic connectivity is not the only thing to take into consideration. Since log shipping does not utilize any form of compression on the transaction log files or the initial backup made by the Database Maintenance Plan Wizard, the files copied over the network have the potential of being large. As these files grow, they may take longer to copy, and slow down the log shipping process. So having a properly designed network with low latency is important. You can compensate for the size of the files and the corresponding latency if the load delay is altered.

To alleviate this potential problem without having to alter load delay after log shipping is implemented, do one or both of the following:

  • Configure a private LAN between the log shipping pair servers to create a direct pipe into each server, instead of sharing network bandwidth over the public network, and potentially over the same network card used for all SQL Server connections. 

  • Use higher bandwidth network cards (provided your network infrastructure can handle it). SQL Server 2000 supports Giganet as a built-in feature, and Compaq's Servernet II technology on Compaq hardware is supported by using an update located online at:


Keeping Old Transaction Log Files

Even though the transaction logs are being copied and applied, it is important to back them up in case a manual restore becomes necessary. SQL Server can be configured to automatically delete the older transaction log files after a certain period of time to conserve disk space. Every environment is different, but backing up transaction logs to an offline tape once a week may be a reasonable timeframe for archiving and freeing up disk space.


The DBA maintaining the SQL Server environment needs to know if log shipping becomes too far out of sync and/or may be failing. The two parameters to configure are: backup alert threshold and out of sync alert threshold. The alerts are triggered by error messages 14420 and 14421, respectively, and can be changed. After log shipping is installed, access the parameters by selecting the properties of the log shipping pair listed under the Log Shipping Monitor.

Installation Considerations

  • If the option Back up the database as part of the maintenance plan is checked on the Specify the Database Backup Plan dialog box, it will create a SQL Server Agent job named "DB Backup Job for DB Maintenance Plan 'Configured Plan Name' ". Selecting this option may not be necessary if a backup plan already exists for the database.

  • Configuring the directory to backup and store the transaction logs is done first on the Specify Transaction Log Backup Disk Directory dialog box and, immediately following, on the Specify the Transaction Log Share dialog box. It is extremely important to ensure that the values specified for each of these are the same location, and that they are a valid universal naming convention (UNC) share (for example, \\servername\dirname) that is accessible by the log shipping pair. If they are not, the file copies will not occur, and subsequently, the transaction logs will not be applied to the warm standby.

  • A database that is being log shipped cannot have more than one log shipping database maintenance plan. To add additional secondaries, modify the existing database maintenance plan. 

  • If a load delay is set, adjust the Out of sync threshold to compensate for this so the Log Shipping Monitor will not display a false error. 

  • When running the Database Maintenance Plan Wizard to configure log shipping, only one database can be selected. If more than one is selected, the wizard will disable the log shipping check box. 

  • Make sure the default local backup directory on the primary has enough room. This is important because when log shipping copies a file, it is copied from where the backup was made to the default directory, and then to the secondary. If there is not enough room, or a backup file exists with the same filename (for example, if a full database backup was done and the wizard also attempts to do one), errors will occur.

    Backup, copy, and restore the database to the secondary prior to running the Database Maintenance Plan Wizard. While the Database Maintenance Plan Wizard can do the initial backup, copy, and restore of the selected database if you choose, it is not the recommended practice due to the following reasons:

    • The backup, copy, and restore process can encounter errors. For example, if Perform a full database backup now is selected, it uses the default backup directory, so if there is either not enough room on the hard drive, or a backup file exists with the same filename (if a previous backup was done), log shipping will encounter errors and not be configured—thereby forcing a manual backup, copy, and restore.

    • Similarly, if "Use most recent backup file" is selected, a variation on the previous bullet point happens: Although the backup file exists and may be on another drive, it utilizes the default backup directory for the instance during the copy process. Should this fail, a manual backup, copy, and restore will be necessary. 

  • If using the wizard to do the database backup, make sure either the Leave database non-operational but able to restore additional transaction logs or Leave database read-only and able to restore additional transaction logs option is selected on the Options screen for restoring a database. If doing it through Transact-SQL, use the WITH STANDBY or WITH NORECOVERY options.

  • If a backup file is created for use with the wizard, place it in a separate directory different from the one being used as the share for log shipping to prevent a possible configuration failure. 

Preparation Worksheet

Below is a worksheet that can be copied and placed in a Run Book (for more information on a Run Book, see Chapter 16, "Five Nines: The Ultimate in High Availability") or a configuration document. It is done in the order of the Database Maintenance Plan Wizard. This worksheet is designed for you to enter the values used in your environment for the configuration of log shipping. It will not only enable log shipping to be able to be easily reconfigured if servers need to be rebuilt, but can also serve as an excellent resource for anyone who needs to know how your SQL Server environment is configured.




Primary Server Name



Secondary Server Name(s)



Database to Log Ship (on Primary)

Primary server


Directory to Store the Backup File (should be a valid UNC name)

One accessible by primary and secondary


Create a subdirectory under the UNC for each database

N/A – UNC file path


Delete transaction log files older than a certain time period

N/A – UNC file path


Backup File Extension (default is TRN)



Network Share Name for Backup directory

One accessible by primary and secondary

Same as directory to store Backup File

Transaction Log Destination Directory (should be valid UNC on secondary server)

Secondary server


Create and Initialize New Database

Secondary server

If No, name of existing database:
If Yes:
Name of database:
Data path:
Log path:

Database Load State

Secondary server

No recovery/Standby

Terminate User Connections in Database

Secondary server


Allow Database to Assume Primary Role

Secondary server

If Yes:
Transaction log backup directory:
(should be the same as directory to store backup file)

Perform a Full Backup (if not using an existing DB)



Use Most Recent Backup File (if not using an existing DB)


If Yes, what file:

Transaction Log Backup Schedule (default is every 15 minutes)



Copy/Load Frequency (default is 15 minutes)



Load Delay (default is 0 minutes)



File Retention Period (default is 24 hours)



Backup alert threshold



Out of sync alert threshold



Log Shipping Monitor server



Authentication mode for Monitor Server

Monitor server

Windows/SQL Server

Generate a Report


If yes:
Delete files after:
E-mail report to:

Limit number of history entries in the sysdbmaintplan_history table


If Yes:
Number of rows:

Log Shipping Tips and Best Practices

Cc917640.spacer(en-us,TechNet.10).gif Cc917640.spacer(en-us,TechNet.10).gif

In addition to understanding the fundamentals of log shipping, configuration, and installation issues, you may find it useful to keep these tips and best practices in mind.

Secondary Server Capacity

The secondary server ought to have the same capacity as the primary server. This ensures that the application will perform as expected. However, it is not mandatory the secondary server be exactly the same as the primary server. In some cases, the secondary server may need more capacity. It is acceptable to have multiple databases log shipped to a single secondary server. Before configuring multiple databases to one secondary, consider the following: What if the different applications share a user, with different rights? Does the application require a specific service pack level (either SQL Server or operating system) that the secondary server may not have? Do you have enough server processor and memory capacity to run all the databases? What if the application eats up disk space too quickly and affects the others? What if the application has a memory leak?

Generating Database Backups from the Secondary

Since the log shipped database is only a small increment of time out of sync with the primary database, it is a good idea to use it to generate the full database backups. This would keep the primary production server free of the potential overhead and contention incurred by a daily (or scheduled) backup.

It is also important to backup the msdb database on all servers involved with log shipping, including the log shipping monitor. This database contains all the configuration and status information for log shipping.

Keeping Logins in Sync

The DTS package that is created to copy the logins for the database from the primary to the secondary needs to be created for each secondary server that is part of the log shipping plan. This package should be scheduled to run on a regular basis. Another SQL Server Agent job should be created for the backing up of the syslogins table, and have the job copy the file to the secondary. As with the DTS package, this job must either have multiple job steps to copy the file to all secondary servers, or have multiple jobs.

When log shipping is first configured, these jobs should be run to ensure that the secondary is in sync with the primary; then it can be run manually or scheduled at a regular interval (scheduled is the recommended way). If the primary server is completely unavailable, and the logins were never transferred, the warm standby cannot be brought online. If the logins were scheduled to be applied, the worst-case scenario is that the secondary would be a bit out of sync if users are added on a regular basis and some would need to be added manually.

Monitoring Log Shipping

After log shipping is configured, check its status from time to time to ensure that it is functioning properly. The status can be viewed in the Log Shipping Monitor. You can view the history of the transaction log backups, the history of the copy and restore process, and various configuration properties for the log shipping pair.

Modifying or Removing Log Shipping

Sometimes it may be necessary to modify your log shipping configuration, or to completely remove it. For more information, see "Modifying Log Shipping" and "How to remove log shipping (Enterprise Manager)" in SQL Server Books Online.

Log Shipping Interoperability Between SQL Server 7.0 and SQL Server 2000

SQL Server allows transaction logs to be manually shipped from a SQL Server 7.0 installation with Service Pack 2 (SP2). However, you cannot send transaction logs from SQL Server 2000 to SQL Server 7.0. For log shipping to work, the option pending upgrade must be set to TRUE on the SQL Server 7.0 SP2 server. A backup of a database from SQL Server 7.0 cannot be restored in SQL Server 2000. Therefore, an alternative method, such as bulk copy (bcp) or Data Transformation Services (DTS), will need to be used for creating the initial database to be log shipped to SQL Server 2000. Log shipping between SQL Server 7.0 and SQL Server 2000 will not be tracked by the monitor server. A custom solution needs to be implemented to track the status. Some uses for log shipping from SQL Server 7.0 to SQL Server 2000 are:

  • Using it to upgrade to SQL Server 2000 by phasing out the SQL Server 7.0 installation. 

  • In a development or test environment, keeping data concurrent when SQL Server 7.0 is the current development/production platform, and SQL Server 2000 is the future platform for the application. 

Note When log shipping from a SQL Server 7.0 SP2 server, the pending upgrade option should always be set to FALSE for any SQL Server 7.0 system that is not interoperating with SQL Server 2000. If pending upgrade is set, users cannot create indexes or statistics in the database. Attempts to create indexes or statistics will generate an error message. .

For more information, see SQL Server Books Online.

Using the Log Shipped Database to Check the Health of the Production Database

A benefit of having a secondary server, or multiple secondaries, is that—depending on how the copy and load delays are set—routine maintenance tasks, such as DBCCs, can be run against the log shipped database. As with the backup processes, it alleviates overhead and reduces potential downtime on the primary. Some recommended consistency checks are: DBCC CHECKALLOC, DBCC CHECKDB or DBCC CHECKTABLE (depending on what your environment dictates; doing a check table by table may be the only option on a log shipped server if the tables are large), and DBCC SHOWCONTIG.

Using the Log Shipped Database for Reporting

Since the secondary database is read-only, it can be used for reporting, such as in a DSS environment. With log shipping, by default, the secondary database is not available for reporting unless you restore with the STANDBY option and even then the database may only be available for intermittent read-only access. One limitation to be aware of is that SQL Server needs exclusive use of the database to apply the transaction logs. The secondary database can be made available for continuous read-only access only if you apply log backups when you do not need to run queries; if you do this, however, the data being queried will become more and more out of sync with the primary. This will potentially lower your high availability if there are large amounts of transactions.

Combining Log Shipping and Snapshot Backups

Log shipping can be employed in conjunction with a third party snapshot backup solution, which mirrors disks in a remote location that are attached to another server. In this scenario, the primary would log ship to the secondary, and the snapshot backup would mirror the secondary server, providing extra redundancy.

Terminating User Connections in the Secondary Database

SQL Server requires exclusive use of the database to apply the transaction log file, so both active and inactive connections must be terminated for log shipping to complete its process. Two scenarios are outlined below:

  • Terminate user connections in database selected 

    Bob is using the log shipped database to do his accounting reports. He knows that every 15 minutes the system needs exclusive use of the database; otherwise it will be out of sync. However, Bob forgets to close the reporting tool with a connection into the database before he goes to lunch. Since log shipping was configured to terminate the user connections, Bob's connection will be terminated at the next interval. 

  • Terminate user connections in database deselected 

    During configuration, the option to terminate user connections was not selected. Log shipping is set to apply a transaction log every 15 minutes, and delete any transaction log files older than two hours. If Bob is running end of quarter reports all day, he winds up tying up the database for eight hours. Since the older transaction log files are deleted after two hours, not only will the database be out of sync, but also it could not be synchronized any other way besides a full database backup since the transaction log files no longer exist. 

Warm Standby Role Change

It is critical when a high availability method is employed as part of a disaster recovery scenario that you contemplate and document how to respond in the event of a failure.

  • Determine the amount of time to wait before deciding to fail over to the warm standby server. 

  • Do you have to switch ODBC Datasource Names (DSNs) to another server for your application or Web server? Or do you repoint a DNS and change a TCP/IP address on the secondary server? These must be determined before implementing log shipping. 

  • If the last transaction log did not get applied from the production server due to a failure, and the system is still alive from an operating system perspective, copy over and apply that final transaction log before bringing up the warm standby as your new primary. If the primary server is completely unavailable, check the log shipping monitor to see what the last backup applied was. 

  • Make sure new users are added and applied before bringing the warm standby online. For more information, see "How to set up and perform a log shipping role change (Transact-SQL)" in SQL Server Books Online.

  • Tests should be conducted at semi-regular intervals to determine if a failover scenario to a warm standby server is successful. 

  • Failovers can be planned to perform typical system maintenance on the primary. If the secondary node is configured to become the primary, transaction logs can be built up, and then applied to the original primary to bring it back in sync when it comes online. 

For more information on disaster recovery, see Chapter 16, "Five Nines: The Ultimate in High Availability."

Failback to Primary

After bringing a secondary server online as the primary, devise a strategy to bring the original primary server back online if it is necessary. Keep in mind that if the secondary server is equal from a hardware and capacity standpoint, there may be no need to switch back to the primary. This should be a decision determined by the business prior to implementing log shipping. After the failover to the secondary, it is recommended that you diagnose the problem on the primary to ensure that the failure does not occur again in your environment.

Here are two examples:

Example One
  1. Make sure the original primary is online and functional. 

  2. Bring the application/Web server down. 

  3. Apply any transaction logs that were generated by the current primary database. 

  4. Switch the log shipping role back to the original primary, ensuring all new logins are applied to the server. 

Example Two
  1. Make sure the original primary is online and functional. 

  2. Bring the application/Web server down. 

  3. Stop log shipping on the current primary server. 

  4. Make a full backup of the database, and restore it on the original primary. 

  5. Make the original primary the "live" database by switching the log shipping role, and restart log shipping. 

Network Load Balancing and Log Shipping

When Network Load Balancing (NLB) and log shipping are used together, both servers are set to inactive at startup. This requires the use of a private network between the log shipping pair, and works well in a Web-based environment. The primary node is started manually. When NLB is used for read-only SQL Servers (for example, reporting or catalog usage), all servers can be set in auto-start mode. In dual-homed configurations, the network cards must be non-routable to each other. And although direct IP communication can be achieved, the preferred method is to use a WINS server entry (LMHosts is another good alternative). Manual intervention is required to ensure client connectivity, but the problem is that clients cannot depend on one SQL Server name. A potential remedy is to have the IIS server point directly to the SQL Server instead of having the clients access the SQL Server directly.

Log Shipping and Replication

Both transactional and merge replication work with log shipping. The main reason for using log shipping with replication is to provide protection in the event of a Publisher failure. Make sure to use the KEEP_REPLICATION option on the restore of the last transaction log as part of the failover process that involves recovering the secondary database. KEEP_REPLICATION tells the recovery process to keep replication settings intact, since these are normally cleaned up as part of the restore process. As mentioned earlier, msdb is important to backup for log shipping, but it is equally important to back up for replication. For more information, see the topics "Strategies for Backing Up and Restoring Merge Replication," "Transactional Replication and Log Shipping," and "Strategies for Backing Up and Restoring Transactional Replication" in SQL Server Books Online.

Log Shipping and Application Code

Do not hard code a server name into an application when using log shipping. If the primary server fails, the application would not be able to utilize the secondary server, and would therefore render log shipping impossible.

Log Shipping and Failover Clustering

Many businesses use log shipping in conjunction with another feature of SQL Server 2000 Enterprise Edition—failover clustering. This is a solution that offers extreme high availability. Cost may be a consideration, though, since additional servers to ensure that the application could run with the acceptable level of performance could be expensive. The hardware requirements for log shipping are not as stringent as they are for failover clustering.

Note All hardware employed in a Windows environment should be on the Hardware Compatibility List to ensure complete compatibility and supportability. To see the Hardware Compatibility List, go to http://www.microsoft.com/whdc/hcl/default.mspx.

For more information on failover clustering, see Chapter 12, "Failover Clustering." For more information on high availability, including a more detailed comparison of methods such as failover clustering and log shipping, see Chapter 16, "Five Nines: The Ultimate in High Availability."

Monitor Server

While the primary or the secondary can be configured as the log shipping monitor server, this is not recommended due to the fact that it stores the state of the log shipping process, and should the primary or secondary be lost, so will the history. This becomes important especially if a reporting tool is used to generate status documents for external people to read.

Using Full-Text Search with a Log Shipped Database

The full-text search feature will fail when the primary system fails. Another copy of the search indexes must also exist on the secondary server. As soon as the search service is restarted on the second computer, clients can use it again. While this can be automated, it is not an instantaneous failover, and should be part of the failover plan if full-text is used.


Cc917640.spacer(en-us,TechNet.10).gif Cc917640.spacer(en-us,TechNet.10).gif

Sometimes errors may occur after log shipping has been configured or during the configuration process. Below are some of the common problems, and how to attempt to solve them. For more information, refer to "Installation Considerations" in this chapter.

Q Log shipping does not seem to be working or I cannot get it configured. Where do I start to look? 

A First and foremost, make sure that SQL Server Agent is started on all SQL Server instances, especially the secondary. If log shipping is configured, SQL Server Agent should be set to start automatically. Also make sure that the servers are set up as linked servers.

Q Log shipping seems to be set up correctly, but no files get copied or applied. I just see first_file_000000000000.trn when I bring up the log shipping pair properties. 

A If different file locations were specified in setup (see "Installation Considerations" in this chapter), this is a common result when the secondary cannot access the file share.

Q I see an error message similar to the one below when a transaction log is applied: 

[Microsoft SQL-DMO (ODBC SQLState: 42000)] 
Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set 
begins at LSN 7000000026200001, which is too late to apply to the database. An earlier 
log backup that includes LSN 6000000015100001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.

A When the full database backup was restored, one that was too old was restored. The transaction logs must be applied sequentially, so if the full backup is out of date, log shipping will fail.


Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
© 2015 Microsoft