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
/S {Servername} /U sa /P {sa password}
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):
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
necessary.
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 database name in quotes.
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.
To set up Log Shipping between SQL Server 7.0 Service Pack 2 (primary) and
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
@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:
http://www.microsoft.com/technet/showcase/itops/availsql.asp