Configuring and Administering the Built-In Functionality Using SQL Server 2000 Enterprise Edition


Microsoft recommends that whenever possible, you should use the log shipping functionality provided by SQL Server 2000 Enterprise Edition (or Developer Edition if you are in a development or testing environment). This feature is not present in any other edition of SQL Server 2000; if you want to use log shipping with another edition, you need to code your own solution. This section walks you through the components of what you get when using the built-in log shipping, how to configure it, and how to administer the solution. Figure 7-2 illustrates the workflow of the log shipping feature found in SQL Server 2000 Enterprise Edition.

Important

For the primary server that is the source of the data as well as any secondary server that will have the transaction logs applied to it, you must use SQL Server 2000 Enterprise Edition or SQL Server 2000 Developer Edition. However, Developer Edition cannot be used in a production environment.

click to expand
Figure 7-2: Diagram of the built-in log shipping feature s workflow.

Log Shipping Components

The built-in log shipping functionality is comprised of a database maintenance plan, stored procedures, SQL Server Agent jobs, and tables in msdb. There might be differences between the primary, secondary (or secondaries), and the Log Shipping Monitor about which objects exist and which edition of SQL Server is used.

Caution

The stored procedures, tables, and jobs used by log shipping are listed here for informational purposes only. Just because they exist does not mean you should or can use them, as they are used by SQLMAINT behind the scenes. What you can use is detailed later in this section.

Stored Procedures

The following list of stored procedures includes all stored procedures that are used by log shipping. Most are located in msdb, and some are in master.

  • sp_add_log_shipping_database

  • sp_add_log_shipping_monitor_jobs

  • sp_add_log_shipping_plan

  • sp_add_log_shipping_plan_database

  • sp_add_log_shipping_primary

  • sp_add_log_shipping_secondary

  • sp_can_tlog_be_applied

  • sp_change_monitor_role

  • sp_change_primary_role

  • so_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_monitor_jobs

  • 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_log_shipping_get_date_from_file

  • sp_log_shipping_in_sync

  • sp_log_shipping_monitor_backup

  • sp_log_shipping_monitor_restore

  • sp_remove_log_shipping_monitor

  • sp_remove_log_shipping_monitor_account

  • sp_resolve_logins

  • sp_update_log_shipping_monitor_info

  • sp_update_log_shipping_plan

  • sp_update_log_shipping_plan_database

Tables

Table 7-1 shows the tables used only for log shipping and which server has them populated . The tables are located in msdb.

Note

If the monitor is configured on SQL Server 2000 Standard Edition, the only log-shipping-specific tables that will exist are log_shipping_primaries and log_shipping_secondaries.

Table 7-1: Table Usage for Log Shipping

Table

Primary

Secondary

Monitor

log_shipping_databases

Used

Not used (unless this is also a primary)

Not used (unless this is also a secondary)

log_shipping_monitor

Used

Used

Not used (unless this is also a secondary)

log_shipping_plan_databases

Not used (unless this is also a secondary)

Used

Not used (unless this is also a secondary)

log_shipping_plan_history

Not used (unless this is also a secondary)

Used

Not used (unless this is also a secondary)

log_shipping_plans

Not used (unless this is also a secondary)

Used

Not used (unless this is also a secondary)

log_shipping_primaries

Not used (unless this is also a monitor)

Not used (unless this is also a monitor)

Used

log_shipping_secondaries

Not used (unless this is also a monitor)

Not used (unless this is also a monitor)

Used

log_shipping_databases

Used

Not used (unless this is also a primary)

Not used

Jobs

The following SQL Server Agent job exists on the primary for each database being log shipped:

  • Transaction Log Backup Job for DB Maintenance Plan ˜ database maintenance plan name This is a job created as part of the database maintenance plan to back up the transaction log of the primary database on a scheduled basis. It also exists on the secondary if you select the Allow Database To Assume Primary Role option during configuration.

The following SQL Server Agent jobs exist on the secondary for each database being log shipped:

  • Log shipping copy for SQLSERVERPRIMARYSERVERNAME.databasename_logshipping This job exists on the primary server and copies the transaction log files from the primary.

  • Log shipping Restore SQLSERVERPRIMARYSERVERNAME.databasename_logshipping This job exists on the secondary server and restores the transaction logs.

The following SQL Server Agent jobs exist on the monitor server for each database being log shipped:

  • Log Shipping Alert Job ”Backup

  • Log Shipping Alert Job ”Restore

Log Shipping Monitor

One of the best reasons to use the functionality provided with SQL Server is the Log Shipping Monitor, which provides status about the log shipping process through Enterprise Manager. It is located on a server designated by the person configuring log shipping.

Note

Although the primary and secondary servers for log shipping require SQL Server 2000 Enterprise Edition or Developer Edition, the Log Shipping Monitor can be placed on any version of SQL Server 2000, including SQL Server 2000 Standard Edition. You can use one Log Shipping Monitor to monitor multiple log shipping pairs, so if you have a dedicated server as recommended here, you do not need to worry about configuring a separate Log Shipping Monitor for each pair. However, keep in mind that you need to ensure that the Log Shipping Monitor itself is available or that you know how to move it.

Configuring Log Shipping

This section takes you through the process of configuring log shipping with SQL Server 2000 Enterprise Edition.

On the CD

Use the file SQL_Server_2000_Log_Shipping_Worksheet.doc when planning your installation. This document corresponds to Table 7-2. There is also a checklist to use to verify all preconfiguration tasks in the file Preconfiguration_Log_Shipping_Checklist.doc.

Step 1: Restoring the Initial Backup on the Secondary

Although you can use the Database Maintenance Plan Wizard to do the initial backup, copy, and restore of the databases for log shipping, you should do each of these outside of the wizard. The main reason for this procedure is that the way log shipping is coded, it uses your default backup drive (which is usually C$) for the backup. Therefore, if you are trying to initialize a large database, you could run out of space on your drive, causing the operation to fail. Because this is not detected immediately, you could waste time if you get an error two hours into a backup operation. That said, if your database is small to medium sized and you know you have plenty of hard disk room, the wizard might work for you, although it is still probably better to do it on your own.

There is also a known issue with the Database Maintenance Plan Wizard such that even if you select the NORECOVERY mode during the install and have the wizard perform your backup, copy, and restore, the database is restored with STANDBY. However the database is switched to NORECOVERY mode after the application of the first transaction log backup. Therefore, if you need your database to start out with NORECOVERY (even though it will be corrected once logs are applied), the only way to do this is to restore the database manually.

If you do not restore the database with STANDBY or NORECOVERY, you will see an error similar to the following:

 Error on loading file c:\backups\Log_Source_tlog_200010090015.TRN : [Microsoft  SQL-DMO (ODBC SQLState: 42000)] Error 4306: [Microsoft][ODBC SQL Server  Driver][SQL Server]The preceding restore operation did not specify WITH  NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH  NORECOVERY or WITH STANDBY for all but the final step. [Microsoft][ODBC SQL  Server Driver][SQL Server]Backup or restore operation terminating abnormally. 

One reason for this is that sometimes mistakenly the database might have been placed within a database that is already recovered, or active, into read-only mode. Read-only mode of an active database is just that; you cannot apply a transaction log to the database. Visually, this appears similar to the way a database restored with STANDBY appears in Enterprise Manager (see Figure 7-3). To see if your database is set to read-only, look at the Options tab in the Properties dialog box for the specific database, as shown in Figure 7-4.


Figure 7-3: How a database that is restored with the STANDBY option appears in Enterprise Manager.
click to expand
Figure 7-4: The Options tab for a database.
Caution

Please ensure that you are applying the proper database backup to the secondary server. For example, if you apply the wrong point-in-time full backup file, you will not be able to restore transaction logs. A sample error message follows :

 [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. 

Step 2: Running the Database Maintenance Plan Wizard

Once the point-in-time full backup is restored, you can run the Database Maintenance Plan Wizard. Table 7-2 lists all the parameters associated with configuring log shipping using the Database Maintenance Plan Wizard.

For complete installation instructions using the Database Maintenance Plan Wizard, see the file Configuring_Log_Shipping.doc.

On the CD
Table 7-2: Log Shipping Parameters and Recommended Values

Parameter

Server Affected

Value or Recommendation

Back up database as part of the maintenance plan

Primary

By default, this is selected in the Specify The Database Backup Plan dialog box. It creates a SQL Server Agent job named DB Backup Job for DB Maintenance Plan ˜Configured Plan Name . You might not have to select this option if a backup plan already exists for the database or you are just configuring log shipping and not an overall Database Maintenance Plan.

Primary server name

Primary

Name of the primary server.

Secondary server names

Secondary

Names of the secondary servers to log ship.

Database to log ship (on primary)

Primary

This is the database from which the transaction log files will be generated.

Directory to store the backup file

Primary

This can be an explicit path (such as C:\Tranlogs) or a Universal Naming Convention (UNC) file path . This directory is used by the primary to back up transaction logs, and although it can be located on the primary, it might be smarter from a high availability perspective to place it elsewhere. Some might make this the same as Network share name for backup directory.

Create a subdirectory under the UNC for each database

Primary

Set this to Yes, as it avoids confusion and makes it easier to find files if you have configured log shipping for more than one database.

Delete transaction log files older than a certain time period

Primary

The value you choose for this parameter depends on how long you need to keep older backup files. Prior to deletion, back these files up to a medium that can be stored offsite for archival purposes.

Backup file extension (default is .trn)

All

Leave as .trn.

Network share name for backup directory

Primary and secondary

This directory, usually a UNC name, needs to be accessible by both the primary and the secondary. This is where the transaction logs are accessed from the secondary. This is basically the same directory as Directory to store backup file, but with a share associated with it.

Transaction log destination directory (should be a valid UNC on secondary server)

Secondary

As with Directory to store the backup file, this can be an explicit path (such as C:\Tranlogs) or a UNC file path. This directory is used to copy the transaction logs from the network share to this location on the secondary.

Create and initialize new database

Secondary

Choose No and do the initial backup, copy, and restore yourself.

Database load state

Secondary

Set to No Recovery if you want to just apply transaction logs for high availability, or to Standby if you want to make it a read-only reporting server.

Terminate user connections in database

Secondary

 Set to Yes only if Standby is selected for Database load state and you are using the secondary for read-only purposes. If No is selected and you have selected Standby, you have to set up a job to terminate the connections, otherwise the logs will never be applied. The syntax is as follows: 
 ALTER DATABASE  databasename  SET SINGLE_USER  WITH ROLLBACK IMMEDIATE 
If you are using your log-shipped database for reporting, you must reset it to allow users to have access after the transaction log is applied with the following:
 ALTER DATABASE  databasename  SET MULTI_USER 

Allow database to assume primary role

Secondary

Set this to Yes to allow the secondary to become the primary. Set the Transaction log backup directory to the same one as Directory to store backup file if it was not located on the primary or set it to the same as Directory to store backup file as you did on the original primary.

Perform a full backup (if not using an existing database)

Primary

No.

Use most recent backup file (if not using an existing database)

Primary

No.

Transaction log backup schedule (default is every 15 minutes)

Primary

This should be set to a lower number for higher volume sites and smaller files. If you set this to a larger number, the files might be large and your standby will not be as close in time to the primary.

Copy/load frequency (default is 15 minutes)

Primary/ secondary

The smaller the number, the closer match the log shipping pair will be. If you are using the secondary as a reporting server, use a higher value, as the user sessions would otherwise have to be terminated more frequently.

Load delay (default is 0 minutes)

Secondary

This is the amount of time the load process waits after the copy process is complete to restore the transaction log. The smaller the number, the closer match the log shipping pair will be. However, if the transaction log files are large, you might need to adjust this to allow time for the file to copy. If you are using the secondary as a reporting server, you might want to set this higher to stack the log files and allow users to do reporting. The default of 0 means that the transaction log will be loaded immediately.

File retention period

The default value is 24 hours. Configure this to match your corporate archival scheme.

Backup alert threshold

If the backups are large, adjust this number accordingly so you do not get false errors. The rule of thumb is to set this to three times the frequency of the backup job.

Out of sync alert threshold

Same as the backup alert threshold. If the file takes 15 minutes to copy because it is 3 GB and takes 45 minutes to apply, set this number accordingly; an hour or even two might be appropriate in this case. The rule of thumb is to set this to three times the amount of the slowest copy and restore job (if you are doing multiple secondaries).

Log Shipping Monitor server

N/A

This should be on a completely separate server than either the primary or secondary server.

Authentication mode for monitor server

Monitor

Set to Microsoft Windows, if possible, but if you change the password for the account that the SQL Server runs under, you must change it on all servers defined in log shipping only if all SQL Servers are starting under the same Windows account.

If you choose SQL Server, it creates the log_shipping_monitor_probe user, for which you enter a password. Do not set this to a blank password.

Generate a report

Primary

This is optional, and you would need to configure a directory to hold the reports and configure how long to retain the reports or whether to e-mail them using SQL Mail.

Limit number of history entries in the sysdbmaintplan_history table

Log shipping is verbose. This can enlarge your table quickly if you generate frequent transaction logs. Adjust accordingly. Also, ensure that if you allow unlimited growth, msdb is set to autogrow. To clear out the entries, you need to execute the sp_delete_backuphistory stored procedure.

Also, log shipping puts entries in the Application Event Log, so that might need to be backed up and cleared out from time to time.

Warning

There is a known issue documented in Knowledge Base article 311801, BUG: Error 3154 Reported in Log Shipping Restore Job Sporadically. If you configure log shipping for more than one database, back up all databases to the same share, and the transaction logs only differ by _tlog in name, this causes failures on the RESTORE job on the secondary. To fix the problem, you need to ensure each database gets its own directory for backups. See the article for any other relevant information.

Step 3: Post-Wizard Configuration Tasks

After you configure log shipping with the Database Maintenance Plan Wizard, your configuration is not complete. There are a few remaining tasks that must be performed. The four tasks involve the following:

  • Creating the DTS package for transferring database users to the secondary.

  • Creating a job to regularly bcp out syslogins for the system-level logins.

    Remember, as discussed earlier, there are two levels of logins that you need to worry about. All database logins are linked to some system-level login. Without the ability to properly synchronize them, you create what are known as orphans, and after the role change the users will be unable to connect to the database. An orphan happens because each database user does not have a corresponding server- level login in SQL Server.

  • Creating jobs to eventually run the stored procedures to perform the role change.

  • Modifying sp_resolve_logins.

In the event of a problem, you do not want to be worried about syntax. These steps remove that worry. If you do not want to make each procedure a SQL Server Agent job, you can also create the scripts for each server based on the information provided here, and then put them in a well-known place to have them ready for execution.

Task 1: Configure Transfer Logins Task DTS Package

In this exercise, you create the DTS package that transfers any database users from the primary to the secondary.

Note

Remember, DTS does not exist for 64-bit editions of SQL Server 2000. You must create DTS packages on a 32-bit instance to run against your 64-bit instances.

  1. In Enterprise Manager, expand the Data Transformation Services control tree.

  2. Right-click Local Packages and choose New Package from the shortcut menu.

  3. In the DTS Package window, go to the Task menu and select Transfer Logins Task.

  4. In the Transfer Logins Properties dialog box, click the Source tab. In the Source Server text box, enter the name of the primary server. Select Use Windows Authentication.

  5. On the Destination tab, select the name of the secondary server. Select Use Windows Authentication.

  6. On the Logins tab, select Logins For Selected Databases and then select the database that is being log shipped.

  7. Click OK.

  8. In the DTS Package window, go to the Package menu and choose Save As.

  9. In the Package Name text box, type Copy DBTOLOGSHIP Users or another name that makes sense. In the Server drop-down list box, select the name of the primary server. Select Use Windows Authentication.

  10. Click OK.

  11. Close the DTS Package window.

  12. In the Local Packages window, right-click Copy DBTOLOGSHIP Users and click Schedule Package.

  13. Schedule the package to run as often as required.

  14. Modify the job Copy DBTOLOGSHIP Users by changing the owner to the name of the owner of the primary database.

    Warning

    If you are transferring large numbers of logins with the transfer logins task, it could be slow. Check Knowledge Base article 311351, BUG: Transfer Login Task Is Slow with a Large Number of Logins, for updates.

Task 2: Set Up a Job to bcp out SYSLOGINS

In this task, you create a new job on the primary server to back up the syslogins table.

  1. In Enterprise Manager, expand the Management control tree on the primary server.

  2. Expand SQL Server Agent.

  3. Right-click the Jobs icon and select New Job.

  4. On the General tab, in the Name text box, type Backup SYSLOGINS or another name that makes sense to you. Set the owner to a user who has privileges to access the system tables.

  5. On the Steps tab, click New.

  6. In the New Job Step dialog box, click the General tab. In the Step Name text box, type BCP Out or something that makes sense.

  7. In the Type drop-down list box, select Operating System Command (CmdExec).

  8. In the Command text box, type the following command:

      BCP master..syslogins out    pathforfile    \    syslogins.dat    /N /S name_of_current_primary_server /U sa / P sa_password  
    • Pathforfile is the path where the file will be created. This should be the same location where the transaction logs are copied because it is accessible by both servers. It also eliminates the problem of one server being a single point of failure.

    • Syslogins.dat is the file containing the login information. You can name this anything you want.

  9. Click OK.

  10. On the Schedules tab, create a new schedule called Transfer Syslogins. Schedule this job to run as often as required. It should also be synchronized to some degree with the job to DTS out the database logins.

  11. Click OK to close the New Job Properties dialog box.

Task 3: Configure the Role Change SQL Server Agent Jobs for Manual Execution

In this exercise, you create the jobs on the proper servers that will contain the stored procedures used in the role-change process.

Subtask A: Create a Job to Demote the Primary

On the primary server, create a SQL Server Agent job named Change Primary Role. Set the owner to the owner of the database or one that has the appropriate privileges. This job executes the sp_change_primary_role stored procedure, which has the following options:

  • @db_name This parameter is the name of the primary database currently being log shipped. The value is enclosed in single quotes.

  • @backup_log This parameter tells SQL Server to back up the transaction log once more before changing the state of the database. This is set to either 0, which tells SQL Server not to make a final transaction log backup, or 1, which instructs SQL Server to make a final transaction backup. The default is 1.

  • @terminate This parameter tells SQL Server to immediately roll back any active transactions and puts the database in single-user mode for the duration of this stored procedure execution. This is set to either 0, which tells SQL Server not to roll back any pending transactions, or 1, which instructs SQL Server to do an immediate rollback of transactions and put the database in single-user mode. The default is 0.

  • @final_state This parameter sets the final state of the primary database after the stored procedure is run. The values that can be configured are 1, which leaves the database in recovery mode (available for writes and reads, but no longer the primary); 2, which leaves the database in no recovery mode (maps to the NORECOVERY function of the RESTORE statement) and allows the database to accept transaction logs but not be available for read-only access; and 3, which leaves the database in standby mode (maps to the NORECOVERY function of the RESTORE statement) and allows the database to accept transaction logs and also makes it available for read-only access. The default is 1, and if you want to eventually use the database again as a secondary in log shipping, you should set it to 2 or 3.

  • @access_level This parameter sets the accessibility of the primary database after the stored procedure is run. The values that can be configured are 1, which leaves the database accessible for multiple users; 2, which allows access only by restricted users (maps to the RESTRICTED_USER function of the RESTORE statement) and is used only when @final_state is set to 1 and you want only members of db_owner, dbcreator, or sysadmin roles to have access; and 3, allowing only one user to access the database. The default value, if not specified, is 1.

The following is an example for this stored procedure with all of the parameters. If you do not put this in the job step, you must use msdb.dbo prior to sp_change_primary_role . If this is in a job step, you do not need msdb.dbo prior to sp_change_primary_role.

EXEC msdb.dbo.sp_change_primary_role @db_name = ' mylogshipdb ',@backup_log = 1 ,@terminate = ,@final_state = 2 ,@access_level = 1

Here are the full steps to create the job:

  1. In Enterprise Manager, expand the Management control tree on the primary server.

  2. Expand SQL Server Agent.

  3. Right-click the Jobs icon and select New Job.

  4. On the General tab, in the Name text box, type a name that is easily understood , such as Run sp_change_primary_role . Set the owner to the owner of the primary database.

  5. On the Steps tab, click New.

  6. In the New Job Step dialog box, click the General tab. In the Step Name text box, enter a name that makes sense to you.

  7. In the Type drop-down list box, select Transact -SQL Script (TSQL).

  8. Enter the syntax for the stored procedure (such as the example just shown).

  9. Click OK.

  10. Do not create a schedule for the job on the Schedules tab. You want to execute this job only on demand.

  11. Click OK to close the New Job Properties dialog box.

Subtask B: Create a Job to Promote the Secondary

On each secondary server, create a SQL Server Agent job named Change Secondary Role. Set the owner of the job to the owner of the database or one who has the appropriate permissions. Further define the job to have one Transact-SQL step named Run sp_change_secondary_role and to use the database msdb. Do not schedule this, but allow it to be executed on demand. This step executes the sp_change_secondary_role stored procedure, which has the following options:

  • @db_name This parameter is the name of the secondary database that will be promoted to the primary. The value is enclosed in single quotes.

  • @do_load This parameter forces all remaining transaction logs that are pending to be copied and restored prior to recovering the database. This is set to either 0, which tells SQL Server to force the copy and restore, or 1, which tells it to make a final transaction backup. The default is 1.

  • @force_load This parameter forces SQL Server to restore the pending transaction logs. This only works if @do_load is set to 1. This is set to either 0, which tells SQL Server not to force a restore, or 1, which tells SQL Server to force the restore. The default is 1.

  • @final_state This parameter sets the final state of the primary database after the stored procedure is run. The values that can be configured are 1, which leaves the database in recovery mode (available for writes and reads, but no longer the primary); 2, which leaves the database in no recovery mode (maps to the NORECOVERY function of the RESTORE statement) and allows the database to accept transaction logs but not be available for read-only access; and 3, which leaves the database in standby mode (maps to the NORECOVERY function of the RESTORE statement) and allows the database to accept transaction logs and be available for read-only access. The default is 1, and it would make no sense to set it to 2 or 3 if this is to be the new active database.

  • @access_level This parameter sets the accessibility of the primary database after the stored procedure is run. The values that can be configured are 1, which leaves the database accessible for multiple users; 2, which allows access only by restricted users (maps to the RESTRICTED_USER function of the RESTORE statement) and is used only when @final_state is set to 1 and you only want members of db_owner, dbcreator, or sysadmin roles to have access; and 3, allowing only one user to access the database. The default value if not specified is 1, and it would make no sense to set it to 2 or 3 because this will be the new primary.

  • @terminate This parameter tells SQL Server to immediately roll back any active transactions and puts the database in single-user mode for the duration of this stored procedure execution. This is set to either 0, which tells SQL Server not to roll back any pending transactions, or 1, which makes it perform an immediate rollback of transactions and put the database in single-user mode. The default is 1.

  • @keep_replication This parameter specifies that if replication was in use on the original primary, the settings will be preserved when restoring any pending transaction logs. This option is ignored if you do not set @do_load to 1. The default is 0.

    Caution

    If you do not set @do_load to 1 and @keep_replication to 1, you will lose all your replication settings when the database is recovered after the last transaction log loads.

  • @stopat This parameter sets the accessibility of the primary database after the stored procedure is run. This option is ignored if you do not set @do_load to 1. The default value is NULL.

The following is an example for this stored procedure with all of the parameters. If you do not put this in the job step, you must use msdb.dbo prior to sp_change_primary_role . If this is in a job step, you do not need msdb.dbo prior to sp_change_primary_role.

EXEC msdb.dbo.sp_change_secondary_role @db_name = ' mylogshipdb ',@do_load = 1 ,

@force_load = 1 ,@final_state = 1 ,@access_level = 1 @terminate = 1 @keep_replication = 1 @stopat = NULL

Follow the steps listed under Subtask A to create the SQL Server Agent job, renaming appropriately and inserting the proper syntax for this stored procedure.

Subtask C: Create a Job to Resolve the Logins

On the secondary server, create a SQL Server Agent job, which should be executed by someone with sysadmin privileges to execute sp_resolve_logins and to use the database master. This stored procedure has the following variables :

  • @dest_db This parameter is the name of the database where logins will be synchronized. The value is enclosed in single quotes.

  • @dest_path This parameter is the location of the BCP file you have been creating. The value is enclosed in single quotes.

  • @filename This parameter is the exact file name that you have been creating.

The following is an example for this stored procedure with all of the parameters. If you do not put this in the job step, you must use master.dbo prior to sp_resolve_logins . If this is in a job step, you do not need master.dbo prior to sp_resolve_logins.

EXEC master.dbo.sp_resolve_logins @dest_db = ' mylogshipdb ',@dest_path = ' h:\mydirectory\ ',@dest_filename = ' syslogins.dat '

Follow the steps listed under Subtask A to create the SQL Server Agent job, renaming appropriately and inserting the proper syntax for this stored procedure.

Subtask D: Create a Job to Change Primaries at the Log Shipping Monitor

On the server containing the Log Shipping Monitor, create a SQL Server Agent job, which should be executed by someone with sysadmin privileges to execute sp_change_monitor_role and to use the database msdb. This stored procedure has the following variables:

  • @primary_server This parameter is the name of the original primary server. The value is enclosed in single quotes.

  • @secondary_server This parameter is the name of the secondary server that was converted to the new primary server. The value is enclosed in single quotes.

  • @database This parameter is the name of the database promoted to the new primary. The value is enclosed in single quotes.

  • @new_source This parameter is the path where the new primary will be putting its transaction logs. The value is enclosed in single quotes.

The following is an example for this stored procedure with all of the parameters. If you do not put this in the job step, you must use msdb.dbo prior to sp_resolve_logins . If this is in a job step, you do not need msdb.dbo prior to sp_resolve_logins.

EXEC sp_change_monitor_role @primary_server = ' primarysrv ',@secondary_server = ' secondarysrv '@database = ' mylogshipdb ',@new_source = ' \\newprisrv1\tlogs\ '

Follow the steps listed under Subtask A to create the SQL Server Agent job, renaming appropriately and inserting the proper syntax for this stored procedure.

Step 4: Verifying and Testing the Log Shipping Pair

The easiest way to verify that the process is working properly is to open the Log Shipping Monitor after the process has had some time to do some copies and restores on the secondary. It is located on the Management tab of Enterprise Manager on the server you designated during configuration.

More Info

For more information on the Log Shipping Monitor, see the section Administering Log Shipping later in this chapter.

Before this goes into production, you should also test the role-change process. This means you might have to reconfigure the log shipping pair (if you do not set the secondary to also become the primary at some point, and so on), but you can also have confidence that everything you have set up is correct. For a disaster recovery drill, it is absolutely crucial that you have this down pat.

On the CD

For a test plan to test the log shipping role-change process, see the file Log_Shipping_Test_Plan.xls. There is also a checklist to easily verify all postconfiguration tasks in the file Postconfiguration_Log_Shipping_Checklist.doc.

Task 4: Modify sp_resolve_logins

As documented in Knowledge Base article 310882, BUG: sp_resolve_logins Stored Procedure Fails If Executed During Log Shipping Role Change, there is a known issue with the sp_resolve_logins stored procedure that requires a manual fix. This stored procedure exists in the master database. Here s the problem. The code currently contains

SELECT *INTO #sysloginstempFROM sysloginsWHERE sid = 0x00

which is incorrect. This incorrectly uses the syslogins table because it does not qualify it. The new statement should be manually corrected to this:

SELECT *

INTO #sysloginstemp FROM master.dbo.syslogins WHERE sid = 0x00

Tip

Back up your system databases before performing a modification to a system-stored procedure such as this one.

Troubleshooting Log Shipping

There are some steps that you can follow to troubleshoot an installation that is not behaving as expected:

  • Make sure the SQL Server Agent is started on the primary and the secondary.

  • Make sure you have no other transaction log backup jobs or processes configured or running for the database.

  • Make sure that no operations (such as changing the database back to simple recovery model) are breaking or have broken the LSN chain.

  • If you are only seeing first_file_000000000000.trn in the Log Shipping Monitor, it could mean one of a few things:

    • You have not given log shipping enough time to complete a full cycle.

    • The secondary has no rights or just cannot access the share that was configured during installation.

    • If last_file_loaded and last_file_copied still reflect first_file_000000000000.trn , even if the copy is occurring, the tables driving the GUI might not be getting updated. Last_file_loaded and last_file_copied are driven by msdb.dbo.log_shipping_secondaries, and last_backup_file is driven by msdb.dbo.log_shipping_primaries. This usually happens when you have configured log shipping to use Windows Authentication only and the SQL Server Agent service startup account of the primary or secondary does not have enough privileges to update the table on the monitor. It also could be an indication that the primary_server_name column of log_shipping_primaries or the secondary_server_name column of log_shipping_secondaries does not reflect the proper names. If it turns out to be a permissions problem, grant the UPDATE and SELECT rights for the appropriate account. If it is the server name issue, update the tables appropriately.

  • If you upgraded from SQL Server 2000 Standard Edition to Enterprise Edition and could not configure log shipping because the components seem to be missing (you will most likely see Errors 208 and 2812), run the file Instls.sql, which is found in the Install directory of the SQL Server 2000 Enterprise Edition installation CD.

    Warning

    If you have access to the Enterprise Edition CD, do not run this script on any other version of SQL Server. It is not supported.

  • Make sure that you applied the right point-in-time backup and that nothing could have broken the LSN chain. Fixes for those errors more than likely will involve a full reconfiguration of log shipping, which means you must remove the current install first. The error messages for these types of root causes were detailed earlier in this chapter.

    Note

    If you need to reconfigure log shipping, you will have to delete the current configuration and also possibly remove the Database Maintenance Plan that was created. This is documented later in the section Removing Log Shipping. If for some reason you are still having problems, you might have to manually check each log shipping table in msdb and delete the offending rows.

Administering Log Shipping

After configuring log shipping, you need to understand how to administer log shipping, including how to monitor the process.

Removing Log Shipping

Use the following steps to remove log shipping from a Database Maintenance Plan:

  1. Open the Database Maintenance Plan for the database on the primary server.

  2. Select the Log Shipping tab and click Remove Log Shipping.

  3. You are prompted with the question, Are you sure you want to remove log shipping? as shown in Figure 7-5. Answer Yes or No, and SQL Server will do your bidding.


    Figure 7-5: Removing log shipping from a Database Maintenance Plan.

    Caution

    If you choose Yes, you remove all pairs participating in log shipping. If you have multiple secondaries and only want to remove one, follow the instructions in the next section, Deleting a Secondary

Deleting a Secondary

If you choose this option, you delete only one of the secondaries if multiple secondary servers are configured. The following are the steps to delete a single secondary from the log shipping definition:

  1. Open the Database Maintenance Plan for the database on the primary server.

  2. Click the Log Shipping tab and click Remove Log Shipping.

  3. You are prompted with the question Are you sure you want to remove log shipping? as shown in Figure 7-6. Answer Yes, and SQL Server will remove the secondary. The only confirmation you receive is that the secondary no longer appears in the Database Maintenance Plan.

    click to expand
    Figure 7-6: Removing a secondary from a Database Maintenance Plan.

    Caution

    If you have only one secondary, performing this operation would be the same as clicking Remove Log Shipping, as shown in the previous section. You would need to reconfigure log shipping from scratch.

Monitoring Log Shipping

To view information about log shipping, you have five options:

  • Log Shipping Monitor The Log Shipping Monitor is the first place you should look to see the status of the log shipping process for the log shipping pair. It is located on the Management tab of Enterprise Manager on the server you designated during configuration. Figure 7-7 shows what the Log Shipping Monitor displays in Enterprise Manager.

    click to expand
    Figure 7-7: The basic Log Shipping Monitor.

    If you select a log shipping pair in the right-hand pane of the Log Shipping Monitor and double-click it, you bring up more information about the pair, as shown in Figure 7-8. The Status tab of the Log Shipping Monitor is the most important one from a monitoring standpoint ”it tells you the last file backed up on the primary, the last file copied, and the last file applied, with the appropriate time deltas. The other two tabs are discussed in the section Changing Log Shipping Parameters After Configuration later in this chapter.


    Figure 7-8: Detailed status information from the Log Shipping Monitor.

    Important

    The Log Shipping Monitor is not automatically refreshed. You must refresh it each time you want to see the updated status of log shipping, as shown in the left-hand pane of Figure 7-7.

  • SQL Server Agent jobs history Each SQL Server Agent job related to log shipping contains a status history, like any other normal job. This is a good place to look when trying to determine why things are failing.

  • Event Viewer Log shipping status is also logged to the Application Log of the Windows Event Viewer.

    Warning

    If you do frequent transaction log backups, this could fill up your Event Viewer quickly and cause alerts to go off unnecessarily in a monitoring center. Figure 7-9 shows the message that will pop up on your server. Please monitor your Event Viewer and maintain it as necessary.

    click to expand
    Figure 7-9: An error message indicating that the Event Viewer is full.

  • Database Maintenance Plan on the primary The Database Maintenance Plan contains all of the information about the log shipping configuration. Database Maintenance Plans are located under the Management control tree of Enterprise Manager. There are two tabs to be concerned with: Log Shipping and Transaction Log Backup. An example of the Log Shipping tab is shown in Figure 7-10.

    click to expand
    Figure 7-10: Log Shipping tab of a Database Maintenance Plan.

  • Querying the log shipping tables You can also code your own queries against the tables listed earlier (Table 7-1) to create your own monitoring scheme.

Changing Log Shipping Parameters After Configuration

You can change the parameters for log shipping postinstallation. Parameters are not centralized in one place to be changed.

Database Maintenance Plan

The Database Maintenance Plan is generally the first place you would go to tweak parameters for log shipping.

Log Shipping Tab

When you select the Log Shipping tab in the Destination Server Information window (see Figure 7-10), select the secondary you would like to modify and then click Edit. You are presented with three tabs: General, Initialize, and Thresholds.

  • General tab The General tab, shown in Figure 7-11, allows you to tweak a few options: where the transaction logs are copied to on the secondary and if the secondary will be able to assume the role of the primary.


    Figure 7-11: General tab of a Log Shipping secondary in a Database Maintenance Plan.

  • Initialize tab The Initialize tab, shown in Figure 7-12, allows you to tweak the state of the secondary database after the transaction log loads, if the log shipping process will automatically terminate the users in the database, and the copy and load frequencies.


    Figure 7-12: Initialize tab of a Log Shipping secondary in a Database Maintenance Plan.

  • Thresholds tab The Thresholds tab, shown in Figure 7-13, is important. It controls when SQL Server alerts you if you are too far out of sync as well as the load delay, file retention, and history retention. You have to update the number for the Out Of Sync Threshold if you change your transaction log frequency. If you feel the number is too high or too low for your environment, change it.


    Figure 7-13: Thresholds tab of a Log Shipping secondary in a Database Maintenance Plan.

Transaction Log Backup Tab

If you want to change anything relating to the transaction log backup scheme you must do it in the Transaction Log Backup tab, as shown in Figure 7-14. The parameters you can tweak are also shown in Figure 7-14.

Important

Changing any values here affects all secondary servers. Also, you might need to tweak the Out Of Sync Threshold value if you change the frequency of the transaction log backups.

click to expand
Figure 7-14: Transaction Log Backup tab of a Database Maintenance Plan.
Note

There is no way to change the backup network share using the Enterprise Manager GUI once you have log shipping configured. This must be done using one of the two following methods :

  1. If the new destination folder lives on the same server as the old folder, remove the old share and rename the new share to the old share s name.

  2. If the new destination folder is on a different computer, which is the more likely scenario, run the query listed here to update the location. You should back up msdb prior to executing the query.

     UPDATE msdb.dbo.log_shipping_plans       SET source_dir = '  \new_computer_name\new_sharename  '       WHERE source_dir = '  \old_computer_name\old_sharename  ' 
Log Shipping Monitor

The Log Shipping Monitor is also used to change some parameters associated with log shipping. The two tabs you use are Source and Destination.

Source Tab

The Source tab, shown in Figure 7-15, allows you to control various settings for when you want to be notified about when backups fail.

Tip

If you are performing intrusive maintenance regularly, or even once, on the primary, configure the day and times under Suppress Alert Generation Between. For example, if you do a full index rebuild every Sunday night between midnight and 3 A.M., set that time. That way, those monitoring it will not see false errors.


Figure 7-15: Source tab of the Log Shipping Monitor.
Destination Tab

The Destination tab, shown in Figure 7-16, allows you to control settings in terms of when you want to be notified about how out of sync the secondary is.


Figure 7-16: Destination tab of the Log Shipping Monitor.
Tip

Both the primary server and the secondary servers must be registered in the Enterprise Manager where the monitor instance is registered. If this is not the case, you will not be able to tweak the parameters just listed. See Figures 7-17 and 7-18 for the error messages you would see.

click to expand
Figure 7-17: Error message if the primary is not registered in Enterprise Manager.
click to expand
Figure 7-18: Error message if the secondary is not registered in Enterprise Manager.

Moving the Log Shipping Monitor

Moving the Log Shipping Monitor functionality to another SQL Server 2000 instance is not documented elsewhere. You would need to do this if the server containing the Log Shipping Monitor fails or you want to move it to another server.

Tip

Because you need to get information from the current Log Shipping Monitor to re-create another one, you should gather the information directly after configuring log shipping.

Step 1: Configure the New Log Shipping Monitor

You need to insert information into the database so that the Log Shipping Monitor knows about the primary. Repeat this for the primary in each log shipping pair by following these steps:

  1. Get the information needed to populate the Log Shipping Monitor. On the current monitor server, run the following query:

     SELECT primary_id, primary_server_name, primary_database_name,  maintenance_plan_id, backup_threshold, threshold_alert, threshold_alert_enabled,  planned_outage_start_time, planned_outage_end_time, planned_outage_weekday_mask FROM msdb.dbo.log_shipping_primaries WHERE primary_database_name = '  mydbname  ' 
    On the CD

    This query, saved as a Transact-SQL script, is on the CD for you to use. The file name is Monitor_Primary_Info.sql.

  2. Run the stored procedure sp_add_log_shipping_primary on the new Monitor server, which uses the information from the preceding query. An example is shown here:

     EXEC msdb.dbo.sp_add_log_shipping_primary  @primary_server_name = 'MyPrimaryServer',  @primary_database_name = 'logshipdb',  @maintenance_plan_id = '9B4E380E-11D2-41FC-9BA5-A8EB040A3DEF',  @backup_threshold = 15,  @threshold_alert = 14420,  @threshold_alert_enabled = 1,  @planned_outage_start_time = 0,  @planned_outage_end_time = 0,  @planned_outage_weekday_mask = 0 

    Conversely, you could also insert the information directly into the msdb.dbo.log_shipping_primaries table on the Log Shipping Monitor.

You now need to insert information into the database so that the Log Shipping Monitor knows about the secondary. Repeat this for each secondary in each log shipping pair using the following steps:

  1. Get the information needed to populate the Log Shipping Monitor. On the monitor server, run the following query:

     SELECT primary_id, secondary_server_name, secondary_database_name,  secondary_plan_id, copy_enabled, load_enabled, out_of_sync_threshold,  threshold_alert, threshold_alert_enabled, planned_outage_start_time,  planned_outage_end_time, planned_outage_weekday_mask FROM msdb.dbo.log_shipping_secondaries WHERE secondary_database_name = 'mysecondarydbname' 
    On the CD

    This query, saved as a Transact-SQL script, is on the CD for you to use. The file name is Monitor_Secondary_Info.sql.

  2. Run the stored procedure sp_add_log_shipping_secondary , which uses the information from the preceding query. An example is shown here:

     EXEC msdb.dbo.sp_add_log_shipping_secondary  @primary_id = 1,  @secondary_server_name = 'MySecondaryServer',  @secondary_database_name = 'logshipdb',  @secondary_plan_id = 'B5C330FF-1081-4FCB-83D0-955DDFB56BA5',  @copy_enabled = 1,  @load_enabled = 1,  @out_of_sync_threshold = 15,  @threshold_alert = 14421,  @threshold_alert_enabled = 1, 
     @planned_outage_start_time = 0,  @planned_outage_end_time = 0,  @planned_outage_weekday_mask = 0,  @allow_role_change = 0 
    Important

    Make sure that the value for @primary_id matches the one inserted into the log_shipping_primaries table, as it is an automatically generated value. If you do not, you will get a message similar to this:

     Server: Msg 14262, Level 16, State 1, Procedure sp_add_log_      shipping_secondary, Line 20      The specified primary_id ('msdb.dbo.log_shipping_primaries') does not exist. 

Example Transact-SQL to get the new primary_id from the inserted row on the new monitor is:select primary_id

from log_shipping_primaries where maintenance_plan_id = 'CE6960C2-F51F-4585-B79B-172E35AF8B4B'

Conversely, you could also insert the information directly into the msdb.dbo.log_shipping_secondaries table on the Log Shipping Monitor.

  1. Create the jobs and alerts that are necessary for the Log Shipping Monitor. The easiest way to do this is to script out the current alerts configured on the current monitor and then modify them as necessary.

Step 2: Update the log_shipping_monitor Table

On the primary server and all secondary servers that will be using the new Log Shipping Monitor, execute the following Transact-SQL on each server to change the monitor defined in the log_shipping_monitor table:

EXEC msdb.dbo.sp_define_log_shipping_monitor@monitor_name = 'GRANDILLUSION',@logon_type = 1, -- Use a @logon_type of 2 for SQL Server authentication that is using the log_shipping_monitor_probe user

--@password = 'password'--Only use the @password if @logon_type = 2@delete_existing = 1

On the original monitor server, also execute the following. Do not do this on the new monitor server.

delete from log_shipping_primaries where primary_id = n delete from log_shipping_secondaries where primary_id = n

where n is the original primary_id. You must also delete the alert jobs from the old Log Shipping Monitor.

Note

In some cases, the existing Log Shipping Monitor will still exist, but it will no longer be updated. All updates should now be done at the newly defined Log Shipping Monitor. Use Step 3 (below) to verify this.

Step 3: Verify the New Log Shipping Monitor

To verify that the newly configured Log Shipping Monitor has been set up properly, check for the following:

  • The Log Shipping Monitor should now display the information for all log shipping pairs defined in Step 2.

  • Wait for some time to ensure that all log shipping pairs are now functioning properly and in sync, or in sync according to the delta that you set.

  • Check the status of all the SQL Server Agent jobs on the server hosting the Log Shipping Monitor; no errors should be found.

Step 4: Delete Old Monitor History and Entries

There are now system tables that have orphaned rows or old rows. You can delete the relevant rows from the tables if you want. The tables can include the following:

  • msdb.dbo.log_shipping_plan_history

  • msdb.dbo.sysdbmaintplan_databases

  • msdb.dbo.sysdbmaintplan_history

  • msdb.dbo.sysdbmaintplan_jobs

  • msdb.dbo.sysdbmaintplans

  • msdb.dbo.sysjobs

  • msdb.dbo.sysjobschedules

  • msdb.dbo.backupfile

  • msdb.dbo.backupmediafamily

  • msdb.dbo.restorefile

  • msdb.dbo.restorefilegroup

  • msdb.dbo.restorehistory

  • msdb.dbo.backupset

  • msdb.dbo.backupmediaset

    Caution

    Do not delete data from system tables without thinking first. Chances are you should qualify by using a WHERE clause so as not to delete more than you want to.

Adding Additional Secondaries

Adding another secondary database to the Database Maintenance Plan is simple. Keep in mind that it utilizes the same transaction log backup schedule; you cannot customize it for each secondary. However, you can customize certain variables, such as how often you copy and load the transaction log.

To add a new secondary, click Add on the Log Shipping tab of the Database Maintenance Plan. You are then presented with three tabs ”General, Initialize, and Thresholds ”that are basically the same as the ones you use when editing an existing secondary. These are shown in Figures 7-19, 7-20, and 7-21. The parameters are also basically the same as those you use when configuring a secondary in the wizard. When you are finished, click OK. The only confirmation you see is that the new secondary has been added to the Database Maintenance Plan.

To verify that things are working properly, refresh the Log Shipping Monitor. The new pair is then displayed. Wait until one cycle is finished and see that the monitor reflects that the secondary is receiving and applying the transaction logs.


Figure 7-19: General tab when adding a new secondary.

Figure 7-20: Initialize tab when adding a new secondary.

Figure 7-21: Thresholds tab when adding a new secondary.

Role Changes

A role change is the process of promoting the current secondary to the new primary. This process might or might not include the demotion of the current primary, depending on its availability and status. There are different types of role changes and drivers for each type that influence the process.

Types of Role Changes

The two types of role changes are simple: planned and unplanned .

  • A planned role change is exactly what it sounds like ”you will have planned downtime. This is the easiest one when you know you will be doing something like performing the role change due to maintenance on the primary because it is the easiest to plan for.

  • An unplanned role change is the most common scenario for log shipping. This is when a problem occurs on the primary and you need to switch to the secondary.

Performing a Role Change

The following steps show how to switch the roles of the primary and the secondary servers.

Tip

Before you proceed to the next step, where appropriate check the job history of each stored procedure run, which will tell you if it was a success or a failure.

  1. Notify any users or anyone who needs to know that there will be an interruption of service. How you do this and who you notify should be defined in your SLA.

  2. If there is a catastrophic failure (triggering an unplanned role change) on the primary server, skip directly to Step 5. This also means that you will not be able to back up the tail of the log and therefore will be a larger delta of time off of the primary. If the primary server is still available and there are connections into the current primary database, allow them to complete but do not allow new transactions. Make sure that a final transaction log backup is made and copied in this case.

  3. Before proceeding, you might want to consider putting the primary database (if it is still available) into single-user mode once activity winds down to ensure no other transactions or connections interfere with the role change process. This can be done using Enterprise Manager or Transact-SQL in the ALTER DATABASE command. If the primary database is not available or you set the @terminate option of sp_change_primary_role to 1, this is not necessary.

  4. On the primary server, execute the SQL Server Agent job you configured to run sp_change_primary_role .

  5. On the secondary server, execute the SQL Server Agent job you configured to run sp_change_secondary_role .

  6. On the secondary server, execute the SQL Server Agent job you configured to run sp_resolve_logins .

    Warning

    sp_resolve_logins only resolves typical SQL Server logins. If you have remote logins configured on the primary, they need to be manually re-created on the secondary. Also, if you do not configure the Transfer Logins Task DTS package and the bcp of the syslogins table to be run, execution of sp_resolve_logins will fail.

    More Info

    Running sp_resolve_logins is not necessary if you use the methods provided in Knowledge Base article 303722.

  7. On the server containing the Log Shipping Monitor, execute the SQL Server Agent job you configured to run sp_change_monitor_role .

  8. If you need to rerun a service pack to upgrade the promoted database on the secondary, do it now.

  9. Test to ensure that the new primary server functions properly, and then redirect client applications to the new server using your technology of choice.

    • If it is ODBC, change the server name (and subsequent entries) for the proper ODBC data source name (DSN). The tool Data Sources (ODBC) is located in the Start menu under Administrative Tools. Figure 7-22 shows the screen where you would select the new server.

      click to expand
      Figure 7-22: Changing server names in ODBC.

    • If you are using Network Load Balancing under Windows 2000, execute the following in a command window. It ensures that all traffic will be redirected to the new primary server.

       WLBS drainstop NLB_cluster_name:primary_server_name /PASSW password_for_NLB_cluster 
    • If you are using Windows Server 2003, use Network Load Balancing Manager to issue the drainstop.

    • Make sure Network Load Balancing is now started on the new Network Load Balancing cluster node using either wlbs on Windows 2000 or Network Load Balancing Manager under Windows Server 2003.

      More Info

      For more information on Network Load Balancing, see Chapter 5.

    • If you are using any other method, such as a custom switch in an application, make sure it is done to point users and applications to the new primary.

  10. Notify end users and others that the database is now ready for use and provide them with contact information should any problems be encountered after the role change.

  11. If you selected the Allow Database To Assume Primary Role option during the configuration of log shipping, configure it properly and start the appropriate SQL Server Agent job to start log shipping again.

    Important

    The Allow Database To Assume Primary Role option only sets up the proper maintenance plan on the secondary to start performing the transaction log backups. You need to add all secondaries and enable the jobs to start the log shipping process again once you have promoted the database on the secondary.

    Tip

    As noted earlier, the SQL Server Agent jobs make life easier in a role change. If you have these scripted, run the scripts in the order listed previously. Do not under any circumstances allow your DBA staff to enter syntax at the time of a failure. This only raises tensions and leaves room for errors and other failures.

Switching Back to the Original Primary

If you want to switch back to the original primary server, you have two options. However, make sure you need to go back to the primary server. If you are up and running on the secondary with no problems, is it worth causing another interruption in service, especially if you have SLAs that will be affected? You have the following options:

  • You can initiate the process from the start from the new primary to the old primary. Specifically, do a new backup of the current primary, apply it to the old primary, configure log shipping from the primary to what would be the new secondary, and finally pick a point in time to do a manual role change. Because you will probably want the server to assume the secondary role again, you should leave the database in NORECOVERY or STANDBY mode.

  • If you selected the Allow Database To Assume Primary Role option during the configuration of log shipping, the appropriate Database Maintenance Plans and SQL Server Agent jobs should have been created for you. You would just need to add secondary databases and create the transfer login tasks and the role-change procedures on the proper servers with the correct syntax reflecting the new roles of each server. If you left the primary database in NORECOVERY or STANDBY mode and the last transaction log from the original was applied to the secondary before it was brought online and no other transactions occurred in the primary database, you should now be able to reverse the process because the two databases were left in the same state.




Microsoft SQL Server 2000 High Availability
Microsoft SQL Server 2000 High Availability
ISBN: 0735619204
EAN: 2147483647
Year: 2006
Pages: 137

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net