Microsoft SQL Server 2000
How to Setup Log
Shipping
Microsoft Product Support Services White Paper
Written by Purna Gathani
Additional contributions by Richard Waymire, Arvind Krishnan, Rick Gutierrez Published on May 24, 2001
Abstract
The Log Shipping feature that is included in Microsoft® SQL Server 2000 Enterprise Edition is an automated process that sends transaction logs from one server to another. You can use Log Shipping to create a warm standby server for your production server. This white paper is for Database Administrators who have never used Log Shipping before and are interested in exploring Log Shipping as a strategy for disaster recovery. This white paper outlines the following:
• The steps to configure Log Shipping between two or more servers that are running SQL Server 2000 Enterprise Edition.
• The steps to configure Log Shipping between Microsoft SQL Server 7.0 Service Pack 2 (SP2), or later, and Microsoft SQL Server 2000 Enterprise Edition.
• A brief comparison between Log Shipping and the other high availability-solutions that SQL Server provides.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
The example companies, organizations, products, people and events depicted herein are fictitious. No association with any real company, organization, product, person or event is intended or should be inferred.
2000 Microsoft Corporation. All rights reserved.
Microsoft, SQL Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.
CONTENTS
How to Setup Log Shipping 1
MICROSOFT PRODUCT SUPPORT SERVICES WHITE PAPER...1
CONTENTS...3
INTRODUCTION...1
COMMON TERMS...2
PREPARING TO SET UP LOG SHIPPING...3
HOW TO SET UP LOG SHIPPING BETWEEN SQL SERVER 2000 SERVERS...5
HOW TO MONITOR LOG SHIPPING...14
HOW TO MODIFY OR DELETE LOG SHIPPING...18
PRIMARY ROLE CHANGE...25
HOW TO SET UP LOG SHIPPING BETWEEN SQL SERVER 7.0 SERVICE PACK 2, OR LATER, AND SQL SERVER 2000...31
COMPARISON WITH OTHER HIGH-AVAILABILTY SOLUTIONS...36
High-Availability Solutions 36 Failover Clustering 36 Replication 37 Log Shipping 37 FOR MORE INFORMATION...39
INTRODUCTION
Log Shipping is an automated process that sends transaction logs from one server to another. The transaction logs are backed up periodically on the production server and copied to warm standby servers where they are restored sequentially. If the current production server stops working, you can upgrade one of the standby servers to a production server
The Log Shipping feature is only available in the Enterprise Edition of SQL Server 2000.
The following diagram represents a simple Log Shipping scenario: Monitor Server
Primary Server Secondary Server
Backup Copy Restore
COMMON TERMS
• Primary or source server: This server contains the source database that is
configured for Log Shipping. The initial database backup and subsequent transaction log backups are taken on this server. Usually, this is the production or development server.
• Secondary or destination server: The initial database and subsequent
transaction log backups are restored on this server. Usually, this is a warm standby server.
• Monitor server: This server is used to monitor Log Shipping. The monitor
server contains all relevant information regarding the status of Log Shipping.
• Transfer Logins Task: You can use this Data Transformation Services (DTS)
task to transfer logins from the source server to the destination server. Microsoft recommends that you not use the same server as both the monitor server and the source server because the monitor server maintains critical information regarding the Log Shipping system. This critical information is lost if the primary server stops working. Also, because the monitoring activity adds some server overhead, keep the monitor server separate to yield better performance. Like all other SQL Server servers, the monitor server must be backed up regularly. For example, although not recommended, you can configure the source and destination servers to be the same physical computer with
PREPARING TO SET UP LOG SHIPPING
As you prepare to set up Log Shipping, follow these guidelines:
• The user who configures Log Shipping must be a member of the sysadmin server role to have permission to modify the
database to log ship.
• The login that you use to start the MSSQLServer and SQLServerAgent services must have administrative access to the Log Shipping plan jobs, the source server, and the destination server.
• Use SQL Server Enterprise Manager on the primary server to register all the servers that will participate in Log Shipping.
• The database that you set up for Log Shipping must use
either the bulk-logged or full recovery model. A database that uses the simple recovery model cannot be log shipped because the simple recovery model does not allow transaction log backups.
• If you use the Database Maintenance Plan Wizard to configure Log
Shipping, you can only log ship to disks. If you want to use the backup-to-tape option, you must manually configure Log Shipping with custom scripts for copying and restoring the transaction logs.
• Create a network share on the primary server where the transaction log backups will be stored. For example:
\\primary_computername\sharename
If you use a directory that is different from the default backup location, you must share that directory so that it can be accessed by the Log Shipping jobs. See the Specify Transaction Log Backup Disk Directory dialog box in Figure 2.
Figure 2
If you select Use this directory, and then specify the F:\BACKUP folder, the F:\BACKUP folder must be shared
.
• If you plan to select the Allow database to assume primary role option when you configure Log Shipping, create a network share on the standby server where the transaction log backups will be stored. For example, \\secondary_computername\sharename.
• Ensure network connectivity between the primary and secondary servers.
HOW TO SET UP LOG SHIPPING BETWEEN SQL SERVER 2000 SERVERS
You can set up Log Shipping between SQL Server 2000 servers by using the Database Maintenance Plan Wizard. To access the Database Maintenance Plan Wizard, open SQL Server Enterprise Manager, and then click Database Maintenance Planner on the Tools menu. Use these steps to set up Log Shipping:
1. Open the Database Maintenance Plan Wizard.
2. Select the name of the database that you want to log ship.
Figure 3
NOTE: To set up Log Shipping, you must configure each database separately. If you select multiple databases, you cannot set up Log Shipping. If you have selected only one database and cannot select the Ship the transaction logs to other SQL Servers (log shipping) check box, make sure that the database is using the Full Recovery model.
The Select Databases dialog box in Figure 3 is followed by the database maintenance dialog boxes:
Pay special attention to the Specify the Transaction Log Backup Plan dialog box. If you do not set up the transaction log backup plan correctly, Log Shipping may fail. If hard disk space is a factor, you may want to consider using the Remove files older than option. The Remove files older than option deletes backup files from the primary server after a specified amount of time has elapsed.
The configuration of Log Shipping starts with the Specify the Transaction Log Share dialog box shown in Figure 4.
3. In the Specify the Transaction Log Share dialog box, enter the share name on the primary server (for example,
\\primary_computername\sharename) where the transaction log backups will
be stored. (This is the same share that you specified in the Preparing To Set Up Log Shipping section.)
4. For each secondary server, you can specify the configuration options in the Add Destination Database dialog box as shown in Figure 5.
The following table provides more information about the Add Destination Database dialog box.
Option Description
Server Name Select the secondary server from the drop-down list box. The server must be registered in the SQL Server Enterprise Manager before you start the Database Maintenance Plan Wizard.
Transaction Log
Destination Directory
Select the destination directory on the secondary server where transaction logs will be copied and subsequently restored.
Create and initialize new database
Create a new database on the secondary server with the same name as the database on the primary server. You can use an existing database backup or create a new one at the end of the Database Maintenance Plan Wizard.
Use existing
database The database already exists on the secondary server.
No Recovery mode
The database remains in No Recovery mode on the
secondary server. No users are able to access the database until the database is recovered.
Standby mode The database remains in Standby mode, and the users
have read–only access. Terminate
users
Microsoft recommends that you select this option. This option terminates all user connections that are using this database before a restore operation occurs. If user connections exist during a restore, the restore operation fails.
Allow database to assume primary role
When this option is enabled, this secondary server becomes the primary server if there are any problems on the original primary server. If you select this option, you must provide a share where the transaction logs are stored when the server assumes the role of a primary server (for example, (\\secondary_computername\sharename).
5. The Initialize the Destination Databases dialog box only appears if you have chosen to create a new database. You can create a new backup or use an existing backup.
Figure 6
NOTE: If you choose to use an existing backup, the system only lists the backup on the current primary server. When you use an existing backup, the file must reside in a directory other than the one you are using to store the Log Shipping backup.
Although you can specify a UNC path to back up the database, Microsoft does not recommend this because it adds the overhead of copying the database backup files to the primary server before they are copied to the secondary server during the final phase of configuration.
6. After you define the secondary server properties, specify the properties of the transaction log backup schedules.
Figure 7
The following table provides more information about the Log Shipping Schedules dialog box.
Option Description
Backup schedule This option sets the frequency that the transaction logs are backed up on the primary server.
Copy/load frequency
This option sets the frequency that the transaction logs are copied from the primary server to the secondary server, and then loaded on the secondary server. If Load delay is 0, zero (0) is also the frequency with which the transaction logs are restored.
Load delay This is the length of time that the secondary server waits
before loading logs after the file is copied to the secondary server. The default for this option is zero (0) minutes, which indicates that the secondary server should
immediately restore any transaction log backups after they are copied. In situations where there is a problem on the primary server, a time delay allows the user to try and correct the problem before the problem log is restored onto the secondary server.
File retention period The length of time that the transaction logs are retained on the secondary server before they are deleted.
7. Specify the thresholds for generating alerts. By default, the alert is written to the SQL Server error log and the Microsoft Windows NT Event Log. After you have successfully configured Log Shipping, you can modify the thresholds for the alerts in the job properties.
Figure 8
Here is a description for each of the Log Shipping Thresholds options:
Option Description
Backup alert threshold
This is the maximum elapsed time since the last transaction log backup occurred on the primary server.
Out of sync alert This is the maximum elapsed time between the last transaction log backup on the primary server and the last transaction log restore on the secondary server.
NOTE: The default numbers that are provided for the preceding dialog box are based on the frequencies that you selected in step 6.
8. Specify the server name and the security context that you want to use to monitor Log Shipping as shown in Figure 9.
Figure 9
You can use either Microsoft Windows NT authentication or SQL Server authentication depending on the security configuration for your server; however, Microsoft recommends that you use Windows NT authentication. If you use SQL Server authentication, you can only use the
log_shipping_monitor_probe login to monitor Log Shipping. If you use the log_shipping_monitor_probe login for other Database Maintenance Plans, you must use the same password. If this is the first time you are using the log_shipping_monitor_probe login, you can specify a new password. The log_shipping_monitor_probe account is used by the primary and secondary servers to update the msdb.dbo.log_shipping_primaries and msdb.dbo.log_shipping_secondaries tables when a transaction log is backed up, copied, or restored. The Specify Log Shipping Monitor Server Information dialog box is the last dialog box you use to configure Log Shipping.
The next two dialog boxes are a part of the Database Maintenance Plan Wizard:
• Reports to Generate
9. In the Database Maintenance Plan Wizard Summary dialog box, click Next to complete the plan.
Figure 10
NOTE: If you are configuring Log Shipping for a large database, this process may take a considerable amount of time. The average time is approximately 12 seconds for an 8-megabyte (MB) database with a single processor [PIII-500] computer that is using only one secondary server.
HOW TO MONITOR LOG SHIPPING
The monitor server is defined as part of the Database Maintenance Plan. After you have successfully created the plan, in the SQL Server Enterprise Manager Management folder for the monitor server, the Log Shipping Monitor appears.
Figure 11
A log shipping pair is assigned for each secondary server per database. For example, if use Log Shipping to send a database from a primary server to a secondary server, there is one log shipping pair. If you use Log Shipping to send two databases from a primary server to a secondary server, there are two log shipping pairs.
You can use the Log Shipping Monitor to check the status of Log Shipping and to edit specific information that pertains to the primary and secondary server. For each log shipping pair, the Log Shipping Monitor shows the time of Last Backup, Backup Threshold, time of Last Copy, time of Last Restore, Sync
Threshold, Alerts Enabled, and Status. When the log shipping pair is not synchronized, the icon displays an “X.” The log shipping pairs do not
auto-refresh. Therefore, you must manually refresh SQL Server Enterprise Manager to obtain the current status.
The options in the following table are available when you right-click a log shipping pair.
Option Description
Disable Backup Alert This option prevents SQL Server from
raising a backup alert.
Disable out-of-sync alert This option prevents SQL Server from
raising an out-of-sync alert.
View Backup History This option displays the Backup
History dialog box.
View Copy/Restore History This option displays the
Copy/Restore History dialog box.
Properties This option displays the Monitor Pair
Property dialog box.
Enable Backup Alert This option enables the SQL Server
backup alert.
Enable out-of-sync alert This option enables the SQL Server
In addition to the Log Shipping Monitor, you can also use Transact-SQL to query the system tables on the different servers to obtain status and configuration information about Log Shipping. There are seven Log Shipping system tables in the msdb database on each participating server, but only the following tables are used on each server, depending on the role played by that server (primary, secondary or monitor).
Table Description
Msdb..log_shipping_databases This table contains a list of all the
databases that are currently being log shipped and their corresponding plan_ids. This table is present on the primary server.
Msdb..log_shipping_monitor This table contains the name of the
monitor server and the type of
authentication that is used for the monitor server. This table is relevant on both the primary and the secondary servers.
Msdb..log_shipping_plans -and-
msdb..log_shipping_plan_databa ses
These tables provide information about the Log Shipping configuration on the secondary server (steps 4 through 8 in the How To Set Up Log Shipping Between SQL 2000 Servers section). This table is
only relevant on the secondary server.
Msdb.. log_shipping_plan_history This table contains the history of Log
Shipping plans. This table is only relevant on the secondary server.
Msdb.. log_shipping_primaries If this table is on a monitor server, it
contains a list of all the primary servers for which this server is a monitor server. On a primary server, this table contains information about the primary server.
Msdb... log_shipping_secondaries If this table is on a monitor server, it
contains a list of all the secondary servers for which this server is a monitor server. On a secondary server, this table contains information about the secondary server.
NOTE: When you configure a database for Log Shipping, a Database Maintenance Plan is created and a row is inserted in the
msdb..sysdbmaintplans system table. The following jobs are also created:
• One job for database backup
• One job for transaction log backup
• Two jobs for Log Shipping alerts
Additionally, two more jobs are created on the secondary server for copying and loading the transaction log. The entries for these jobs are made in the
msdb..sysjobs system table. If you have a backup primary server, there is a transaction log backup job, but it is disabled until you execute a role change.
HOW TO MODIFY OR DELETE LOG SHIPPING
After you configure Log Shipping, you can add or edit the properties of the secondary servers. To do this, open SQL Server Enterprise Manager on the primary server, right-click the Database Maintenance Plan that is used by Log Shipping, and then click Properties. In the Properties dialog box, click the Log Shipping tab.
Figure 12
NOTE: If you do not know which Database Maintenance Plan is being used by Log Shipping, run the following SQL statement from the SQL Server Query Analyzer:
SELECT database_name, plan_name FROM msdb..sysdbmaintplans as a, msdb..log_shipping_databases as b
The following table provides more information about the Database Maintenance Plan dialog box.
Option Description
Add Add another secondary server to an existing primary server.
Delete If this is the only log shipping pair, Log Shipping is completely removed. Otherwise, only that specific log shipping pair is deleted. Edit Edit the properties of an existing secondary server. You can edit all of
the options that you originally configured during the creation of the Database Maintenance Plan.
Remove Log Shipping
This option removes Log Shipping completely.
The Edit Destination Database dialog box has three tabs:
• General
• Initialize
• Threshold
Figure 15
Tab Name Description
General View or change the
Transaction Log Destination and share name for the secondary server (backup primary server).
Figure 16
Tab Name Description
Initialize Make changes to the
secondary database status and to the copy or load frequency.
Figure 17
Tab Name Description
Threshold Make changes to the Out of Sync
Threshold, Load Time Delay, File Retention Period and History Retention Period.
The Edit Destination Database dialog box is the only place where you can make changes to the History Retention Period, which is used to determine how much information is retained in the Log_shipping_plan_history table on the secondary server.
You can also remove a secondary server from your Log Shipping configuration in SQL Server Enterprise Manager. To do this, on the secondary server, right-click the log shipped database, and then click Properties.
Because this database participates in Log Shipping, the Properties dialog box contains some additional Log Shipping information about the role of this server and the name of the monitor server. Click Details to view the Log Shipping Details dialog box.
Figure 19
If you click Remove Log Shipping to remove Log Shipping for this database, the corresponding log shipping pair is also deleted. If this is the only pair, Log Shipping is completely removed.
PRIMARY ROLE CHANGE
If the primary or production server has a problem, you can upgrade your
secondary server to be the current primary server provided that you selected the Allow database to assume primary role option for that secondary server during the initial configuration.
To prepare the primary or production server for disaster recovery, do the following:
1. Create a DTS package with the Transfer Logins task. Specify the source or primary server and the destination or secondary server, and then select one of the two login options shown in Figure 20.
Figure 20
2. Create a job that is owned by the system administrator (sa) on the current primary server or create a login with sysadmin rights to both servers by using the following job steps:
a. BCP Out: This step should be of Type = “Operating System Command (CmdExec).” The command will read as follows (note that the switches are case sensitive)
bcp master..syslogins out {localpath}\syslogins.dat /N
b. Copy File: This step should be of Type = “Transact-SQL Script (T-SQL)”. The step should execute in Database = master. The command should be as follows:
exec xp_cmdshell 'copy {localpath}\syslogins.dat {destination share}'
c. Transfer Logins: This step should be of Type = “Operating System Command (CmdExec).” The command should be as follows (note that the switches are case sensitive):
DTSRun /Sserver_name /Uuser_nName /Ppassword /Npackage_name/Mpackage_password
For the preceding job, create a job schedule so that it runs once or on a recurring basis. Microsoft recommends that the job run as close as possible to the time of the role change, so that the job gets the most current login information from the primary server.
To perform a server role change, use these steps:
NOTE: You must be a SQL Server Administrator to perform a server role change. You must always specify the parameters explicitly when you run the following stored procedures, and never assume that a parameter uses a default value.
3. Run the following stored procedure on the instance of SQL Server that is marked as the current primary server:
Exec msdb..sp_change_primary_role @db_name sysname, @backup_log BIT = 1, @terminate BIT = 0, @final_state SMALLINT = 1, @access_level SMALLINT = 1
The following table has more information about the parameters.
4. Run the following stored procedure on the instance of SQL Server that is marked as the current secondary server (the future primary server):
Exec msdb..sp_change_secondary_role @db_name sysname, @do_load BIT = 1, @force_load BIT = 1, @final_state SMALLINT = 1, @access_level SMALLINT = 1, @terminate BIT = 1,
Parameter Options Field Type Description/Options
db_name Sysname Database name. Make sure that
you enclose the database name in single quotes.
Backup_log 0 or 1 Bit 0 = Do not back up the log.
1 = Back up the log (back up the end of the log).
Terminate 0 or 1 Bit 1 = Terminate user.
0 = Do not terminate user.
Final_State 1, 2 or 3 SmallInt 1 = Recovery
2 = No Recovery 3 = Stand by (Recommended) Access Level 1, 2, or 3 SmallInt 1 = Multi-user 2 = DBO 3 = Single user
This table has more information about the parameters.
Parameter Options Field Type Description/Options
db_name Sysname Database name. Enclose the
database name in single quotes.
Do_load 0 or 1 BIT 1 = Try to load available
transaction logs before you perform the switch.
0 = Do not load available transaction logs before you perform the switch.
Force_Load 0 or 1 BIT 1 = Load everything available,
and ignore the load_delay option that you specified during the configuration of log shipping. This option is ignored unless Do_load is set to 1.
Final_State 1, 2 or 3 SMALLINT 1 = Recovery
2 = No Recovery 3 = Standby
Access_Level 1, 2 or 3 SMALLINT 1 = Multi-user
2 = DBO
3 = Single User
Terminate 0 or 1 BIT 0 = Do not terminate user.
1 = Terminate user.
If the new primary database is set up as Standby, you must use the Terminate User option. Otherwise, the stored procedure fails.
Keep_Replication 0 or 1 BIT 0 = False
1 = True
Stopat DATETIME Enter a valid date or time as
5. Run the following stored procedure on the instance of SQL Server that is marked as the monitor server:
exec msdb..sp_change_monitor_role @primary_server sysname, @secondary_server sysname, @database sysname,
@new_source Nvarchar (128) The following table provides more information.
Parameter Field Type Description/Options
Primary_Server Sysname This is the primary server name.
You may need to use brackets [ ] depending on the server name.
Secondary_Server Sysname This is the new primary (old
secondary) server name. You may need to use brackets [ ] depending on the server name.
Database Sysname Database name.
New source Nvarchar This is the new primary server
share name. Use single quotes around the share name.
6. Run the following stored procedure on the instance of SQL Server that is marked as the new primary server (the old secondary server):
EXEC sp_resolve_logins databasename, destination_path, filename
The following table provides more information.
Parameter Field Type Description/Options
dest_db Sysname This is the database name. Enclose the
NOTE: Neither the sp_resolve_logins stored procedure nor Data
Transformation Services (DTS) restores a login’s server role membership. After the logins are transferred, you must manually apply the role membership. This is scheduled to be fixed in Microsoft SQL Server 2000 Service Pack 1 so that the login’s server role membership is also automatically transferred.
The former secondary server now functions as the current primary server. The former primary server is no longer part of a log shipping pair. You can add the former primary server to the new primary server as a secondary server if you want to establish a log shipping pair between the two databases.
HOW TO SET UP LOG SHIPPING BETWEEN SQL SERVER 7.0 SERVICE PACK 2, OR LATER, AND SQL SERVER 2000
You can manually configure Log Shipping between Microsoft SQL Server 7.0 Service Pack 2 and SQL Server 2000, with the SQL Server 7.0 server as the primary server and the SQL Server 2000 server as the secondary server. SQL Server 7.0 Service Pack 2 introduces the new database option pending
upgrade, which must be set to TRUE in order to set up Log Shipping. However, if you enable the pending upgrade option on a database in SQL Server 7.0, the following restrictions occur:
• Users cannot create indexes or statistics in the database. Attempts to create indexes or statistics cause the following 1931 error message to occur:
“Create index/Create statistics is disallowed when the database has pending upgrade enabled.”
If you set the pending upgrade option to TRUE while a clustered index is being created, and there are no existing non-clustered indexes, the index creation succeeds. However, if non-clustered indexes already exist when the creation of a clustered index is initiated, and the pending upgrade option is set to TRUE while the index creation takes place, the index creation may fail, rolling back the entire operation. The pending upgrade option must always be set to FALSE for any SQL Server 7.0 system that is not inter-operating with SQL Server 2000.
In addition to the restrictions on SQL Server 7.0, you must consider the following when you set up Log Shipping:
• No graphical interface exists to set up or monitor Log Shipping.
• In the event that you need to upgrade the secondary server to a
primary server, there is no automatic way to change roles. Changing roles is a manual procedure.
• The database on the secondary server cannot be restored by using the STANDBY option because the database requires an upgrade.
2. Create a share on the primary server to store the transaction log backups. 3. Create a database maintenance plan on the primary server to take database
backups and recurring transaction log backups. Store the backups of the transaction log on the share created in step 2.
4. Restore the database on the secondary server with the No Recovery mode. 5. Create a Log Shipping plan on the secondary server by executing the
following stored procedure:
EXEC msdb.dbo.sp_add_log_shipping_plan @plan_name,
@description, @source_server, @source_dir, @destination_dir, @history_retention_period, @file_retention_period,
@copy_frequency, @restore_frequency The following table provides additional information.
Parameter Description
@plan_name This is the plan name.
@description This is the description of the plan.
@source_server This is the source or primary server name.
@source_dir This is the primary server share where the
Transaction Log is stored. The transaction logs are copied from this share to the secondary server.
@destination_dir This is the folder where transaction logs are stored
on the secondary server. @history_retention_perio
d
This is the length of time (in minutes) that the history of this plan is retained.
@file_retention_period This is the length of time (in minutes) that the transaction logs are stored on the secondary server.
@copy_frequency This is the frequency (in minutes) at which
transaction logs are copied from the primary server to the secondary server.
@restore_frequency This is the frequency (in minutes) at which the copied transaction logs are restored on the secondary server.
6. Add a database to the Log Shipping plan that you previously created on the secondary server by executing the following stored procedure:
EXEC msdb.dbo.sp_add_log_shipping_plan_database [@plan_id | @plan_name], @source_database, @destination_database,
@recover_db, @load_delay, @load_all, @copy_enabled, @load_enabled
The following table has more information.
Parameter Description
@plan_id -or-
@plan_name
This is the ID or name of the plan that wascreated in step 5.
@source_database This is the database name on the primary
server. @destination_databas
e
This is the database name on the secondary server.
@recover_db This value specifies the state of the
database. The value of one (1) means restore the logs with STANDBY; zero (0) means restore the logs with NORECOVERY. This value can only be 0 because of the reason mentioned in the restrictions section.
@load_delay This is the length of time (in minutes) to
wait after the transaction log is copied, but before it can be restored on the secondary server.
@load_all This parameter specifies that all newly
copied transaction logs must be loaded when the job is run. If the value is set to zero (0), only one transaction log is loaded when you run the job. If the value is one
Parameter Description
The default value of one (1) means a copy should be performed; zero (0) means no copy is made.
@load_enabled This parameter specifies if the transaction
logs should be loaded, load_enabled is a bit datatype. The default value of one (1) means a load should be performed; zero (0) means no load is performed.
NOTE: You should see two jobs created in the msdb..sysjobs system table:
• One for copying the transaction logs from the primary server to the secondary server.
-and-
• One to restore the transaction logs on the secondary server.
You can only monitor Log Shipping by viewing the job history for copy or restore jobs. You cannot set up the Log Shipping Monitor to monitor Log Shipping
between SQL Sever 7.0 Service Pack 2 and SQL Server 2000.
You can modify the log shipping information for existing secondary servers. You can add new secondary servers, or add existing ones that have been deleted. Use the commands in the following table to perform these operations.
Command Description
sp_add_log_shipping_secondary Add a secondary server.
sp_update_log_shipping_plan Update any parameters for an
existing Log Shipping plan.
sp_update_log_shipping_plan_database Update any parameters of an
existing plan for a database.
sp_delete_log_shipping_plan_database Remove a database from a Log
Shipping plan.
sp_delete_log_shipping_plan Delete a Log Shipping plan.
For more information about the parameters that are required for each of these commands, refer to Microsoft SQL Server 2000 Books Online.
If you want to upgrade the secondary server to a primary server, perform these steps:
• Manually copy and restore the transaction logs that have not been applied.
• Restore the database with recovery.
It is beneficial to create a recurring job to bcp out syslogins and copy the output file to the secondary server, which is to be used to synchronize the logins when the secondary server is upgraded to a primary server. The steps for this are outlined in the Primary Role Change section of this white paper.
COMPARISON WITH OTHER HIGH-AVAILABILTY SOLUTIONS
Log shipping is one of several high-availability solutions that Microsoft provides for a SQL Server environment. Failover clustering and replication are two other popular options. To determine if log shipping is ideal for your environment, consider the following questions:
How much availability do I need? Availability is defined as the ratio
of time that the server is actually available to the time that the server should be available.
How much work can I afford to lose? Can I afford to re-create
committed transactions that have already made it to the primary server?
How much downtime can I afford during the disaster recovery
phase?
How much money am I willing to spend on my solution?
Understanding and answering these questions is the first of several steps in planning a high-availability solution. The next section outlines the pros and cons of different high-availability solutions that SQL Server provides.
High-Availability Solutions
Failover Clustering
Failover clustering is a good enterprise solution. Failover clustering provides the highest availability because it immediately fails over to the second node. Furthermore, this failover is transparent to the end-user. Failover clustering provides protection against SQL Server failures, SQL Agent failures,
Windows operating system crashes and errors, full text resource failures, and hardware failures. However, failover clustering requires special hardware. Also, failover clustering uses a shared disk subsystem, and therefore, the computers must be physically located in the same data center, unless you plan to implement Distance Clustering. It is important to note that failover clustering does not protect you against a failure in the disk subsystem and the data loss that results because of the hardware failure.
Replication
You can use replication as a high-availability solution even though that is not its intended purpose. The database administrator must take extra care to transfer the metadata changes that are not otherwise replicated. Moreover, replication does not synchronize all the objects in the database, unless
explicitly requested. Replication works well for read-only data. For example, it may be a good idea to replicate a copy of your production database to
another server for reporting purposes, but it involves a lot of work to upgrade the reporting server to a production server, if and when the need arises.
Log Shipping
Log shipping is a good option because it not only provides high-availability, but also ensures your data against hardware failures. For example, if one of the disks on the primary server stops responding, you can still restore the saved transaction logs on the secondary server and upgrade the secondary server to a primary server, with little or no loss of work. Additionally, Log Shipping does not require that the servers be in close proximity. Also, Log Shipping supports sending transaction logs to more than one secondary server and allows you to offload some of the query processing and reporting needs to the secondary servers. Before you make the decision to implement Log Shipping, consider the following questions:
Where is the standby server physically located? The physical
location of the secondary server becomes significant when it is time to upgrade the standby server to be the primary server.
How good is the network connectivity between the primary
server and the secondary servers? The faster the connection, the better, as network connectivity impacts the performance of Log Shipping considerably.
What is the plan for switching to the secondary or standby server?
How much work can you afford to lose? In some situations, the end
of the current transaction log may not be recoverable, for example, due to a disk failure. If a disk failure occurs, you are only able to recover the database up to the last valid transaction log backup,
What is the capacity (CPU, memory, and so on) of your secondary
server? Is the capacity of the secondary server comparable to the primary server? Can the secondary server take the load of the primary server if it is upgraded to be the primary server?
After you upgrade your standby server to a primary server, are you
going to switch back to your primary server. If so, how?
Log shipping is not as transparent as failover clustering because the user is aware of the downtime. In addition, the user or the application must be aware of the name and location of the standby or secondary server when it is upgraded to be the new primary server.
For added fault-tolerance, you can combine log shipping with replication and/or failover clustering to overcome the potential disadvantages that these solutions bring when they are implemented separately.
FOR MORE INFORMATION
• Microsoft SQL Server 2000 Books Online
• For the latest information about Microsoft SQL Server 2000, refer to the following resources at:
