Log shipping is used to establish one or more secondary databases that can be brought online manually if a primary database fails. Following is an overview of log shipping and then a discussion of how it is implemented.
Log shipping requires at least two separate SQL Server instances:
A primary server instance on which you have configured a database to act as the primary for log shipping
A secondary server instance on which you have configured a database to act as the secondary for log shipping
All administration of the log shipping configuration is performed on the primary server. You can extend log shipping in several ways:
By configuring multiple secondary databases, you can establish multiple standbys that can be brought online in case the primary database fails. You can think of each secondary as a cold standby.
By configuring an optional monitoring server, you can track the history and status of log shipping. The monitor server can also be configured to raise alerts if log shipping operations fail to occur as scheduled.
By configuring secondary servers for query processing, you can reallocate query processing from a primary server to one or more secondary servers.
Log shipping uses a backup folder for logs. Because all log shipping servers must have access to this folder, this folder should be on a network or distributed share and the secondary server proxy accounts (which are by default the SQL Server Agent accounts used on the secondary servers) must have Read and Write permissions on this folder.
SQL Server Agent is the essential ingredient that enables log shipping. A SQL Server Agent job is scheduled to copy the primary server’s transaction logs to the backup folder. This job is referred to as the backup job. Other SQL Server Agent jobs also are used to copy the transaction logs from the backup share to the secondary server and to restore the transaction logs on the secondary server. These jobs are referred to as copy and restore jobs. By default, the backup, copy, and restore jobs run every 15 minutes.
When you enable and configure log shipping, the related SQL Server Agent jobs are created automatically. There will always be only one of each job on a server, even if you have configured log shipping on multiple databases. You should never edit the job properties directly. Instead, edit the backup settings on the primary database for log shipping.
Real World
When a database is updated frequently, a short interval between backup, copy, and restore operations helps ensure that the secondaries are synchronized with the primary. In some situations, however, you might want to set a longer interval to reduce the workload and resource usage associated with the backup, copy, and restore operations. If the primary is overworked and has few resources available, you might want to set a longer interval for each of the jobs. If the primary database is updated infrequently, you might want to set a longer interval for each of the jobs as well.
When you configure log shipping, tables are created in the msdb database on the servers acting as primary, secondary, and monitoring servers. Stored procedures also are created to perform the necessary operations, cleanup, and monitoring. Related alerts are configured automatically to help you monitor log shipping.
Log shipping requires a very specific configuration to work properly. To prepare your servers for log shipping, follow these general steps:
Log shipping is set on a per database basis. Access the Database Properties dialog box for the primary database. On the Options page, ensure that the Recovery Model is set to Full or Bulk-Logged. Log shipping databases cannot use the Simple recovery model.
Create a shared resource to use as the backup folder for log shipping. Set permissions so that secondary server proxy accounts (which are by default the SQL Server Agent accounts used on the secondary servers) have Read and Write permissions on this folder.
Tip
A good resource for creating shared folders and setting shared folder permissions is Microsoft Windows Server 2003 Administrator’s Pocket Consultant, 2nd Edition (Microsoft Press, 2006). See the sections titled "Creating Shared Folders" and "Managing Share Permissions" in Chapter 14 of this reference.
Enable log shipping on the primary database as discussed in "Enabling Log Shipping on the Primary Database" later in this chapter.
Specify the log shipping secondary databases as discussed in "Adding Log Shipping Secondaries" later in this chapter.
Optionally, add a monitoring server to track job history and alerts. The monitoring server runs an alert job that generates alerts when backup operations have not completed successfully in the predefined intervals.
Unlike SQL Server 2000 log shipping, which is configured using maintenance plans, SQL Server 2005 log shipping is configured as part of the standard database properties. Because of this, you cannot directly update SQL Server 2000 log shipping to SQL Server 2005 log shipping. You can, however, migrate your SQL Server 2000 log shipping configuration to SQL Server 2005 log shipping.
To upgrade the log shipping configuration easily, complete the following steps:
Upgrade all secondary server instances to SQL Server 2005. When you upgrade the secondary server instances, any log shipping databases will remain SQL Server 2000 databases because they will be in an offline state.
Upgrade the primary server to SQL Server 2005. The primary database will be unavailable while the upgrade is in progress, and you will not be able to failover to a secondary server.
Enable the primary database for log shipping. To ensure that backup logs are applied properly, use the same backup share that you used with your SQL Server 2000 log shipping configuration.
Specify the secondary servers. In the Secondary Database Settings dialog box, you must select the option No, The Secondary Database Is Initialized during the configuration. The secondary database is upgraded automatically to a SQL Server 2005 database when you start shipping logs.
SQL Server 2005 does not use any of the log shipping tables used by SQL Server 2000. After the migration, you can remove the following SQL Server 2000 log shipping tables:
log_shipping_databases
log_shipping_monitor
log_shipping_plan_databases
log_shipping_plan_history
log_shipping_plans
log_shipping_primaries
log_shipping_secondaries
You can also delete any log shipping SQL Server Agent jobs created by SQL Server 2000.
You can enable log shipping by completing the following steps:
In SQL Server Management Studio, access the primary server in Object Explorer view.
Right-click the database you want to be the primary database and select Properties.
On the Transaction Log Shipping page, select Enable This As A Primary Database In A Log Shipping Configuration, as shown in Figure 15-20.
Click Backup Settings to display the Transaction Log Backup Settings dialog box shown in Figure 15-21.
Type the UNC path for the network share where the transaction logs are created on the primary server, such as \ENGSQLDataLogs.
If the network share location is an actual folder on the local server, you can set the local path for the primary server to use. Otherwise, leave the related box blank.
Use the Delete Files Older Than boxes to configure how long old transaction logs copied to the backup folder will be retained.
By default, the backup job runs every 15 minutes. If no backup occurs within a specified time, you can configure log shipping to generate an alert. Set Alert If No Backup Occurs Within to the time to wait before generating an alert for failed copy operations.
Click OK to complete the backup configuration. Click OK to close the database properties dialog box.
After you enable log shipping, you can add a log shipping secondary by completing the following steps:
In SQL Server Management Studio, access the primary server in Object Explorer.
Right-click the database you want to be the primary database and select Properties.
On the Transaction Log Shipping page, click Add under Secondary Databases. This displays the Secondary Database Settings dialog box shown in Figure 15-22.
Click Connect. Use the Connect To Server dialog box to connect to the secondary server.
To initialize the secondary, you must restore a full backup of the primary database on the secondary WITH NORECOVERY. This initializes the secondary. If you have already done this, select the option No, The Secondary Database Is Initialized. Otherwise, select one of the following options to initialize the secondary:
Yes, Generate A Full Backup... Creates a full backup of the primary and restores it WITH NORECOVERY on the secondary. Click Restore Options to set the folder paths for data and log files.
Yes, Restore An Existing Backup... Uses the full backup of the primary specified in the Backup File box. Click Restore Options to set the folder paths for data and log files.
On the Copy Files tab, specify the local folder to use as the destination folder for transaction log copy operations. A SQL Server Agent job running on the secondary server handles this copy task, and the SQL Server Agent service account must have access to the specified folder.
Use the Delete Copied Files After boxes to configure how long transaction log copies are retained. Typically, you will want to retain copies for at least 24 hours.
By default, the copy job runs every 15 minutes. Click Schedule to change the run schedule.
On the Restore Transaction Log tab, specify the database state when restoring backups as either No Recovery Mode or Standby Mode. With No Recovery Mode, the transaction logs are applied WITH NORECOVERY, and the database is left in a nonoperational state. With Standby Mode, the database is in an operational standby state.
By default, backups are restored whenever the restore job runs. If you want to delay restoring backups, you can set a specific delay in minutes, hours, or days. The delay should never be longer than the Delete Copied Files After setting (and be sure to account for the restore job run interval).
Set Alert If No Restore Occurs Within to the time to wait before generating an alert for failed restore operations.
Click OK to start the secondary configuration. The progress of the configuration is displayed. If an error occurs, click the related link to read the error message and take corrective action as necessary.
Click Close when the configuration is complete. Click OK to close the database properties dialog box.
By default, transaction log backups are created every 15 minutes. To change the frequency of backup creation, follow these steps:
In SQL Server Management Studio, access the primary server in Object Explorer view.
Right-click the database you want to be the primary database and select Properties.
On the Transaction Log Shipping page, select Backup Settings. This displays the Transaction Log Backup Settings dialog box.
Note the Job Name under Backup Job. Click Edit Job to display the Job Properties dialog box.
Click the Schedule button to display the Job Schedules Properties dialog box, and then select the schedule item for the current database you are configuring.
Set the run frequency for the backup job.
Click OK three times to close all open dialog boxes and apply the settings.
By default, transaction logs copied from backup and restore are performed every 15 minutes. To change the frequency of the copy and restore operations, follow these steps:
In SQL Server Management Studio, access the primary server in Object Explorer view.
Right-click the database you want to be the primary database and select Properties.
On the Transaction Log Shipping page, secondary servers and databases are listed by server instance and database name. Select the secondary you want to modify, and then click the related Properties (...) button. This displays the Secondary Database Settings dialog box.
On the Copy Files tab, click Schedule to display the Job Properties dialog box. Set the run frequency for the copy job, and then click OK.
On the Restore Transaction Log tab, click Schedule to display the Job Properties dialog box. Set the run frequency for the restore job, and then click OK.
In most cases, when you manually fail over from a primary database to a secondary database, the two databases will not be fully synchronized. This can occur because some transaction log backups created on the primary server may not yet have been copied or applied to the secondary server because changes to the databases on the primary server may have occurred since the last transaction log backup, or both. Because of this, before using a secondary database, you should synchronize the primary database with the secondary database and then bring the second server online. To do this, follow these steps:
Copy any remaining transaction log backup files from the backup share location to the copy destination folder on the secondary server. You can do this manually or by starting the copy job on each secondary server.
Apply any unapplied transaction log backups. You can do this manually or by running the restore job on the secondary server.
If possible, back up the active transaction log on the primary server with NO_TRUNCATE. When you are using the SQL Server Backup dialog box, this means you should select Back Up The Tail Of The Log on the Options page of the Back Up Database dialog box when performing the transaction log backup.
If you are able to back up the active transaction log, apply the log backup to the secondary server. This ensures that the secondary server has the most up-to-date version of the data. Regardless, uncommitted transactions from the primary are lost.
Make the secondary database available for use by recovering the database on the secondary server. Execute the RESTORE DATABASE statement using the WITH RECOVERY clause, such as:
RESTORE DATABASE Customer WITH RECOVERY GO
After you have failed over to the secondary server, you can configure the secondary database to act as the primary database. Then, you will be able to swap primary and secondary databases as needed. The steps required for the initial role change are different from those required on subsequent role changes.
The first time you want to fail over to the secondary database and make it your new primary database, you must:
Manually fail over from the primary database to a secondary database, as discussed previously.
Disable the log shipping backup job on the original primary server and the copy and restore jobs on the original secondary server.
Configure log shipping on the secondary database, which is now acting as the primary database. You must use the same backup share as the original primary server, add the original primary as a secondary in the log shipping configuration, and choose No, The Secondary Database Is Initialized.
After you have completed the steps for an initial role change, you can perform subsequent role changes by completing these steps:
Perform the manual failover steps and bring the secondary database online. When you are backing up the active transaction log on the primary server, you must use WITH NORECOVERY.
Disable the log shipping backup job on the original primary server and the copy and restore jobs on the original secondary server.
Enable the log shipping backup job on the secondary server, which is now acting as the primary server, and enable the copy and restore jobs on the primary server, which is now acting as the secondary server.