Implementing Log Shipping

  • Article

Updated : November 12, 2002

This chapter teaches you how to increase the availability of a Microsoft SQL Server data center by using built-in log shipping. After reading this chapter, you will be able to configure each component in this high-availability solution. For information about configuring log shipping using Network Load Balancing (NLB), see Solution Guide Chapter 4, "Implementing Network Load Balancing."

The implementation steps include annotation to help you understand the selected configuration choices. The configuration options presented in this chapter have been designed using input from the field, reviewed thoroughly by Microsoft Product Support Services (PSS), and tested by an independent team to ensure accuracy and functionality.

Log shipping is only part of achieving a highly available data center. To achieve a highly available data center, you must also follow the processes outlined in the accompanying Planning Guide.

On This Page

Understanding Log Shipping
Deploying Automatic Log Shipping
Changing the Role of a Standby Server

Understanding Log Shipping

Built-in log shipping is a high-availability solution provided with SQL Server 2000 Enterprise Edition that can be implemented with any server edition of Microsoft Windows 2000. If you want to use the client redirection capabilities of NLB, however, you must use either Windows 2000 Advanced Server or Windows 2000 Datacenter Server.

Log shipping is an inexpensive, high-availability solution that uses SQL Server jobs to periodically back up the production database transaction log on the primary server, and then restore these backups to a copy of the production database (the standby database) on one or more secondary servers (standby servers). With log shipping, proximity is not a limitation; you can place a standby server in a geographically remote location to protect against a catastrophic disaster. Standby servers do not need to be identical to the primary server, although differences in the amount of memory, the number and speed of the processors, and the robustness of the storage system can affect SQL Server performance after a role change.

Built-in log shipping was designed as a high-availability solution. It includes a role change mechanism to promote a standby server to assume the role of the primary server and demote the original server to become a standby server. Built-in log shipping also includes support for a third server (the monitoring server) to monitor log shipping and to notify the database administrator (DBA) team if log shipping fails to synchronize the standby database according to the automated schedule.

You can use the standby database on a standby server in place of the production database if the primary server fails or must be taken offline for planned maintenance. The standby database is always slightly out of synchronization with the production database. You can control the level of synchronicity — it can be as short as several minutes if a high-speed network is used, or as long as you choose. To minimize the transactional latency of the standby database, back up the transaction log on the primary server frequently, and restore it to the standby database with the same frequency.

Because log shipping relies on the SQL Server restore process, user connections to the standby database must terminate each time the transaction log is restored. As a result, if you also want to use a standby database for reporting or maintenance tasks, you must restore the transaction logs less frequently. Restoring the transaction logs less frequently sacrifices the currency of the standby database and increases the time required to bring the standby server online in case the primary server fails or is taken offline. Using multiple standby servers permits you to maintain a standby database with minimum transactional latency and another standby database with greater latency for reporting or maintenance tasks.

Changing the role of a standby server is a manual process that requires the DBA to perform a number of tasks to complete the role change, including the manual redirection of client requests to the promoted standby server. To point clients to the new primary server, use one of the following client redirection options:

  • Program an alternative server list into the client application

  • Update the client Data Source Name (DSN)

  • Update the Domain Name System (DNS)

  • Update the COM+ component

  • Update the NLB configuration.

If the primary server fails, any transactions that are not backed up and copied to the standby server are lost. During a planned role change, if the production database and the standby database are completely synchronized, no transactions are lost. Because changing server roles is a manual process, changing roles takes longer than changing nodes with failover clustering. Changing roles, however, is significantly faster than restoring a database to a secondary server from a cold backup.

To implement log shipping, you need to have certain things in place:

  • You need people in the following roles:

    • Database administrator

    • Network administrator

  • You need the following software:

    • Windows 2000 Server, Windows 2000 Advanced Server, or Windows 2000 Datacenter Server

    • SQL Server 2000 Enterprise Edition

  • You need systems and system components that are certified to work together and with the chosen edition of Windows 2000 operating system. Windows 2000 Datacenter Server has more stringent certification requirements than Windows 2000 Advanced Server. To find systems and system components that are certified by Microsoft, search the "Hardware Compatibility List" on the Microsoft Web site at https://support.microsoft.com/kb/131900

For more detailed information about log shipping, including setup and troubleshooting, see "How to Set Up Log Shipping" on the Microsoft Support Services Web site at https://support.microsoft.com/default.aspx?scid=https://support.microsoft.com/support/sql/content/2000papers/LogShippingFinal.asp.

For information about connectivity between geographically dispersed sites, see Publishing Data Over the Internet Using VPN in SQL Server Books Online, and Virtual private network (VPN) connections overview on the MSDN Web site at https://msdn2.microsoft.com/library/aa237159.aspx.

Deploying Automatic Log Shipping

Implementing log shipping is wizard-driven and easy to follow. You cannot install log shipping by using Transact-SQL scripts, and you cannot implement log shipping more than once for a single database.

This chapter assumes the following with respect to each participating server in the log-shipping solution:

  • Windows 2000 Server, Windows 2000 Advanced Server, or Windows 2000 Datacenter Server is installed.

  • The most recent operating system service pack is installed.

  • The server is a member of the same Microsoft Active Directory service forest as each other server.

  • At least one network interface card (NIC) is installed.

  • The server is connected to the corporate network.

  • SQL Server 2000 Enterprise Edition is installed and configured to use the same authentication mode as each other server.

  • The most recent SQL Server 2000 service pack is installed.

  • The SQL Server Agent service is configured to use the same domain user account used by the SQL Server Agent service on each other server.

  • The SQL Server Agent service is configured to start automatically.

  • The server is registered in SQL Server Enterprise Manager.

  • If e-mail notifications will be used, SQLAgentMail is configured.

This chapter also assumes that the production database on the primary server is configured to use either the bulk-logged recovery model or the full recovery model. The logical design of the log-shipping solution built in this chapter is described next, followed by the specific steps required to build this high-availability solution.

Understanding the Logical Design

Figure 2.1 shows the logical design of a log-shipping solution. This design illustrates the use of three standby servers. These servers can be geographically distributed, and the data restoration schedules can be different for each standby database.

Cc917705.haso0201(en-us,TechNet.10).gif

Figure 2.1: Logical design diagram of a log-shipping solution

After you set up the production database on the primary server, create a log-shipping database maintenance plan for the production database on the primary server. During log-shipping setup, restore a backup of the production database to each standby server. Log shipping setup creates a log-shipping job on the primary server that periodically backs up the production database transaction log. Log shipping setup also creates log shipping jobs on each standby server that copy the transaction log backups from the primary server and restore them to the standby database on the standby server. During restoration, specify either the STANDBY or the NORECOVERY option. Performing a restoration with either of these options permits additional transaction logs to be applied. The STANDBY option places the database in read-only mode. Finally, specify a monitoring server to monitor these log-shipping jobs and to notify the DBA if the log-shipping jobs fail to keep the standby database synchronized according to schedule. For example, if you configure log shipping to keep each standby database current to within 15 minutes of the production database, you can configure the monitoring server to notify you if a standby database is out of synchronization for more than 45 minutes.

Although no special networking is required, you must ensure that the bandwidth between the primary server and each standby server can handle the network traffic generated by copying the transaction log backup files. The copy of a transaction log backup must complete before the next one is ready to be copied. The traffic depends on the number and size of the transactions generated in the production database. If additional network performance is required, use a private network segment between the primary server and each standby server. On the primary server, ensure that the disks are fault tolerant to minimize the need for a failover because of a disk failure. On each standby server, use fault tolerant disks to ensure that the standby server remains available for a role change and to function as a replacement primary server if necessary.

The steps required to implement this logical design are below, followed by the steps in detail.

To implement log shipping with SQL Server

  1. Log on or connect to the primary server with a user account that is a member of the Administrators local group on that computer.

  2. Create and share a synchronization folder on the primary server and on each standby server.

  3. Create database folders on each standby server.

  4. Create log-shipping jobs, and initialize each standby database.

  5. Create and execute a login synchronization job.

  6. Script and copy jobs, alerts, and operators.

  7. Copy Data Transformation Services (DTS) packages.

These steps create a database maintenance plan with log-shipping jobs to log ship a single production database from the primary server to one or more standby servers. To log ship a second production database from the primary server, create a second database maintenance plan.

Note: You can use a standby server for multiple standby databases from multiple servers.

Creating and Sharing Synchronization Folders

On the primary server and on each standby server, create and share a synchronization folder. The synchronization folder on the primary server is used to store transaction log backup files, login information output by the bulk copy program (BCP) utility, the jobs script, the alerts script, and the operators script that you use to synchronize objects between the primary server and each standby server. The synchronization folder on each standby server is used to store transaction log backup files copied from the primary server and login information output by the BCP utility. This synchronization folder also stores the transaction log backup files created on a standby server after a role change. The SQL Server Agent service must have permission to read and write to these synchronization folders. On the primary server and each standby server, use a drive that is not used by the SQL Server data or log files, and mirror the drive for fault tolerance. Be sure only authorized users can access these shared folders.

To create a shared folder on the primary server

  1. Log on or connect to the primary server with a user account that is a member of the Administrators local group on that computer.

  2. On a drive not used by SQL Server, create a folder for storing transaction log backups.

  3. Grant full–control, file-level access to this folder to the domain user account that the SQL Server Agent service uses and to the Administrators local group. Remove the Everyone group from the file level permissions list.

  4. Share the new folder.

    Use a share name that has no spaces. If the share name has a space, you have to place double quotation marks around it when you reference it in command prompt utilities.

  5. Grant full control share level access through this share to the domain user account that the SQL Server Agent service uses and to the Administrators local group. Remove the Everyone group from the share-level permissions list.

To create a shared folder on a standby server

  1. Log on or connect to the standby server with a user account that is a member of the Administrators local group on that computer.

  2. On a drive not used by SQL Server, create a folder for storing transaction log backups.

  3. Grant full–control, share-level access to this folder to the domain user account that the SQL Server Agent service uses and to the Administrators local group. Remove the Everyone group from the permissions list.

  4. Share the new folder.

    Use a share name that has no spaces. If the share name has a space, you have to place quotation marks around it when you reference it in command prompt utilities.

  5. Grant full–control, share-level access through this share to the domain user account that the SQL Server Agent service uses and to the Administrators local group. Remove the Everyone group from the permissions list.

Note: Repeat this procedure on each standby server.

Creating Database Folders on Each Standby Server

Log shipping creates a copy of the production database on each standby server. Use an identical path for the data and log files between the primary server and a standby server to avoid having to edit the restoration script on the standby server. Use a mirrored drive to increase the availability of the standby database. In addition, grant full–control, file-level access to the data and log file folders to the domain user account that the SQL Server service uses and to the Administrators local group.

To create database folders on a standby server

  1. Log on or connect to the standby server with a user account that is a member of the Administrators local group on that computer.

  2. Create a folder for the standby database data file for the copy of the production database.

    Use the same drive letter and path for this data file as is used on the primary server.

  3. Grant full–control, file-level access to this folder to the domain user account that the SQL Server Agent service uses and to the Administrators local group. Remove the group Everyone from the permissions list.

  4. Create a folder for the standby database log file.

    Use the same drive letter and path for this log file as is used on the primary server.

  5. Grant full–control, file-level access to this folder to the domain user account that the SQL Server Agent service uses and to members of the Administrators local group. Remove the Everyone group from the permissions list.

Note: Repeat this procedure on each standby server.

Creating the Log-Shipping Jobs and Initializing the Standby Database

Use the Database Maintenance Plan Wizard in SQL Server Enterprise Manager to create the log-shipping jobs and to initialize the standby database on each standby server. You can run this wizard from any computer that has SQL Server Enterprise Manager installed and to which each participating server is registered; only registered servers are available for selection in the wizard when you are configuring log-shipping. Using the wizard, specify and configure the primary server, specify and configure each standby server, initialize each standby database, configure log-shipping job schedules and alert thresholds, and configure the monitoring server and report properties.

Note: Log shipping can be configured only by using the Database Maintenance Plan Wizard. Setting up log shipping using Transact-SQL scripts is not supported.

To specify and configure the primary server

  1. In SQL Server Enterprise Manager, connect to the production database on the primary server, and then expand Databases.

  2. Right-click the production database, point to All Tasks, and then click Maintenance Plan.

  3. On the Welcome to the Database Maintenance Plan Wizard page, click Next.

  4. On the Select Databases page, verify that only the check box for the production database is selected. Then select the Ship the Transaction Logs to Other SQL Servers (Log Shipping) check box, and click Next.

  5. On the Update Data Optimization Information page, click Next.

    Do not select any of the check boxes.

  6. On the Database Integrity Check page, click Next.

    Do not select any of the check boxes.

  7. On the Specify the Database Backup Plan page, clear the Back Up the Database as Part of the Maintenance Plan check box and then click Next.

    Develop a separate backup plan for full and differential database backups.

  8. On the Specify Transaction Log Backup Disk Directory page, click Use This Directory, and then browse to or type the path that you created for the transaction log files.

  9. Select the Remove Files Older Than check box, specify a retention time for the transaction log backup files according to your business archive policy, and then click Next.

    You must ensure that the primary server has sufficient space to hold the transaction log backup files for the retention time specified.

    Note: Archive these transaction log backup files that are used for log shipping as part of your regular backup strategy.

  10. On the Specify the Transaction Log Share page, browse to or type the path for the shared synchronization folder on the primary server, and then click Next.

To specify and configure standby servers

  1. On the Specify the Log Shipping Destinations page, click Add to specify a standby server (called a destination server in the wizard).

  2. In Add Destination Database, click a standby server in the Server Name list.

  3. In the Directory text box, browse to or type the path that you created on the standby server for the transaction log files.

    Change the path that the wizard suggests.

  4. In the For Data text box in Destination Database, browse to and type the path that you created for the data file on the standby server.

  5. In the For Log text box in Destination Database, browse to and type the path that you created for the log file on the standby server.

    Notice that the Database Name box completes automatically with the same name as the production database. Do not change the database name.

    Note: You can use an already existing database rather than having the wizard create a new one. If you want to use an existing database, you must restore the standby database from backup using either the NORECOVERY or the STANDBY option. You must also ensure that the standby database is synchronized with the production database before log shipping commences. The backup that is used must be taken when the database is using either the bulk-logged or full-recovery model.

  6. In DownloadState, click No Recovery Mode unless you plan to use the standby database for reporting and maintenance tasks.

    If you plan to use the standby database for reporting and maintenance tasks, click Standby Mode, and select the Terminate Users in Database check box.

  7. Select the Allow Database to Assume Primary Role check box. In the Directory text box, browse to or type the path for the synchronization folder on the standby server.

    Specify a path to a folder on the standby server. A common error is to specify a path to a folder on the local server.

  8. Click OK.

Note: Repeat this procedure for each standby server.

To initialize the production database copy on the standby servers

  1. After you have added all participating standby servers, click Next on Specify the Log Shipping Destinations.

  2. On the Initialize the Destination Databases page, click Perform a Full Database Backup Now, and then click Next.

    If you chose to use an existing database in the preceding procedure, this page will not appear. In this case, do not continue until you ensure that the existing database on the standby server has been initialized and is ready to accept transaction log backups from the production server.

    Note: If you have a current backup, click Use Most Recent Backup File, and then browse to or type the path and name of a recent backup file. If the backup is not current, either have the wizard create a new backup or manually initialize and synchronize the standby database using the existing database backup and all recent transaction log backups.

To configure the log-shipping job schedules and alert thresholds

  1. To modify the default frequency of transaction log backups on the primary server, on the Log Shipping Schedules page, click Change in Backup Schedule. Change the transaction log backup frequency, and then click OK in Edit Recurring Job Schedule.

    The default interval is 15 minutes. A shorter interval enables you to reduce the latency between the production database and a standby database but slightly increases the total overhead on the primary server. The smallest interval that you can specify is 1 minute.

  2. To modify the default frequency of the copy and load jobs, type or select a frequency in the Copy/Load Frequency box.

    The default frequency is 15 minutes. This frequency should match the backup schedule frequency so that each transaction log backup is copied from the primary server to each standby server immediately after it is complete, without delaying the copy process.

  3. To specify a delay between copying a transaction log backup and restoring it, type or select a delay in the Load Delay box.

    The default delay is 0. Set a delay if you want time to detect corruption to the production database before the corruption affects the standby database. Also set a delay if are using the standby server for reporting or maintenance tasks.

    Note: If you are configuring multiple standby servers and want a different load delay for a particular server, edit the database maintenance plan after completing the wizard, and adjust the load frequency for that server.

  4. In the File Retention Period box, type or select a time to keep the transaction log backup files on the standby server.

    Although these transaction log backup files are also archived at the primary site, you might want to archive them in the standby site in case a manual restore is required. Doing so is particularly important if the standby server is geographically remote from the primary server. Ensure that the standby server has sufficient space to hold the transaction log backup files shipped from the primary server for the retention period specified.

  5. Click Next.

  6. On the Log Shipping Thresholds page, in the Backup Alert Threshold box, click the maximum time allowed between successful transaction log backups on the primary server before an alert is issued.

    The default threshold is three times the backup frequency. Change this threshold according to your business needs. The backup alert is set to error message 14420. You can change which error message the alert uses after the wizard finishes by using the Log Shipping Monitor.

  7. In the Out Of Sync Alert Threshold box, type or select the maximum allowable time difference between the last transaction log backup of the production database on the primary server and the last restore of the transaction log to the production database copy on the secondary server.

    The default threshold is three times the backup frequency. Change this threshold according to your business needs. The threshold alert is set to error message 14421. You can change which error message the alert uses after the wizard finishes by using the Log Shipping Monitor.

  8. Click Next.

To configure the monitoring server and report properties

  1. On the Specify the Log Shipping Monitor Server Information page, in the SQL Server list, click the server you want to use to monitor the log-shipping jobs.

    Do not use the primary server as the monitoring server. If the primary server fails, you won’t know the current log-shipping status. If you have only two servers, use the standby server; otherwise, choose a third, independent server. A monitoring server can monitor many log-shipping servers.

  2. Specify the authentication method for the SQL Server Agent service on the monitoring server to use to connect to the primary server and each secondary server. Then click Next.

    Use Windows Authentication. If SQL Server Authentication is selected, SQL Server Agent uses the log_shipping_monitor_probe SQL Server login account. You must configure each participating server for mixed mode authentication and create this SQL Server login account on each participating server.

  3. On the Reports to Generate page, select the Write Report to a Text File check box, and then browse to or type the directory to be used to save the reports.

  4. Select the Delete Text Report Files Older Than check box, and then click a retention period appropriate to your business requirements.

  5. Select the Send E-Mail Report to Operator check box, and then click an operator from the list, or click New Operator to create a new operator. Click Next.

    To send e-mail notifications, configure SQLAgentMail.

  6. On the Maintenance Plan History page, click the maximum number of rows stored in the history table, or clear the Limit Rows in the Table check box to remove the limit completely.

  7. If you want to use a centralized reporting server, select the Write History to the Server check box, and then browse to or type the name of a centralized server.

    For example, you might want to have all log-shipping history centralized to the monitoring server.

  8. Click Next.

  9. On the Database Maintenance Plan Wizard Summary page, type a unique descriptive name for this database maintenance plan, and then click Next.

    Note: If you are log shipping to an existing database, be sure that you synchronize that database with the production database before you click Finish.

  10. On the Completing the Database Maintenance Plan Wizard page, click Finish.

Note: If the Database Maintenance Plan Wizard fails to complete due to an error, some changes are not completely rolled back. You must resolve the error and roll back all changes made by the wizard before running the wizard again. For more information, see article 298743, "All Changes May Not be Rolled Back when Log Shipping Maintenance Wizard Fails," in the Microsoft Knowledge Base at https://support.microsoft.com/default.aspx?scid=kb;en-us;298743&sd=tech.

Creating and Executing a Login Synchronization Job

After you have set up log shipping, you must synchronize logins between the primary server and each standby server. The master database stores login accounts. Log shipping the production database does not ensure that user accounts in the production database have corresponding login accounts in the master database on each standby server.

You must synchronize logins before clients can use the standby database. Synchronizing logins is a manual process requiring you to transfer logins from the primary server to each standby server, export login information from the syslogins table on the primary server to a file, and synchronize logins and user accounts on the standby server. The final synchronization of logins and user accounts occurs when the standby database is recovered during the role change process.

The procedures below create a login synchronization job for each standby server to ensure that the logins remain synchronized between the primary server and the standby server. Run this job when you initially set up each standby server and each time you change logins on the primary server. Server Roles are not synchronized between servers by these procedures, so all users in system roles should be managed manually at each server using a script created by you or directly in Enterprise Manager.

Important: Apply the changes specified in article 310882, "BUG: sp_resolve_logins Stored Procedure Fails If Executed During Log Shipping Role Change" in the Microsoft Knowledge Base at https://support.microsoft.com/default.aspx?scid=kb;en-us;310882&sd=tech to each server before proceeding. The article describes how you must edit the stored procedure sp_resolove_logins in the master database.

Locate the following text in the procedure:

SELECT   *
INTO     #sysloginstemp
FROM     syslogins
WHERE    sid = 0x00

Replace it with the following text:

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

To create a transfer logins DTS package for a standby server

Note: If you have created a login for SQL Server by using a local user account in Windows 2000, this transfer logins task will fail because a local login is not valid on any other server. In a high availability environment, only create logins for domain users, or create SQL Server logins.

  1. Using SQL Server Enterprise Manager, connect to the primary server.

  2. Right-click Data Transformation Services, and click New Package.

  3. On the Task menu, click Transfer Logins Task.

  4. On the Source tab in Transfer Logins Properties, browse to or type the name of the primary server in the Source Server box, and verify that Use Windows Authentication is selected.

  5. On the Destination tab, browse to or type the name of a standby server in the Destination Server box, and then verify that Use Windows Authentication is selected.

  6. On the Logins tab, click Logins for Selected Databases.

  7. In the Databases list, select the check box for the production database, and then click OK.

  8. On the Package menu, click Save As.

  9. In Save DTS Package, type Transfer_Logins in the Package Name box, and then click OK.

    The transfer logins package saves to the msdb database on the primary server.

  10. Close the transfer logins DTS package.

Note: Create a separate transfer logins package on the primary server for each standby server.

To create a login synchronization job for a standby server

  1. Using SQL Server Enterprise Manager, connect to the primary server.

  2. Expand Management, expand SQL Server Agent, right-click Jobs, and then click New Job.

  3. On the General tab in New Job Properties, type Synchronize Logins <standbyserver> in the Name box.

  4. In the Owner list, select the domain user account that the SQL Server Agent service uses.

To create a BCP job step for the synchronize logins job

  1. On the Steps tab, click New.

  2. On the General tab in New Job Step, type BCP Out in the Step Name box.

  3. In the Type list, select Operating System Command (CmdExec).

  4. In the Command text box, type the following command as follows:

    5. 



BCP master..syslogins out \<primary server>&lt;share name of
synchronization folder>\syslogins.dat /N /S <primary server> /T



**Note**: If the share name of the synchronization folder has a space in it, you must use double quotation marks around the entire path for the output file.



    

  1. Click OK.
    2. 



To create a copy file job step for the synchronize logins job


    

  1. On the Steps tab, click New.
    

    2. 

  2. On the General tab in New Job Step, type Copy File in the Step Name box.
    

    3. 

  3. In the Type list, select Operating System Command (CmdExec).
    

    4. 

  4. In the Command box, type the following command:
    

    5. 



copy <path to shared synchronization folder>\syslogins.dat
\<standby server>&lt;share name for synchronization folder>



    

  1. Click OK.
    2. 



Note: The log-shipping role change process uses this file to reconcile SQL Server logins with standby database user accounts.


To create a transfer logins job step for the synchronize logins job


    

  1. On the Steps tab, click New.
    

    2. 

  2. On the General tab in New Job Step, type Transfer Logins in the Step Name box.
    

    3. 

  3. In the Type list, select Operating System Command (CmdExec).
    

    4. 

  4. In the Command box, type the following command:
    

    5. 



DTSRun /S<primary server> /E /NTransfer_Logins



    

  1. Click OK.
    

    2. 

  2. Click OK to close the synchronize logins job.
    

    3. 



Note: Create a separate synchronize logins job on the primary server for each standby server.


To execute the synchronize logins job for a standby server


    

  1. Using SQL Server Enterprise Manager, connect to the primary server.
    

    2. 

  2. Expand Management, expand SQL Server Agent, and then expand Jobs.
    

    3. 

  3. Right-click Synchronize Logins <standby server>, and then click Start Job.
    

    4. 

  4. In the Start Job on < standby server >, click Start to execute all of the steps in the synchronize logins job.
    

    5. 



Note: Execute the synchronize logins job for each standby server whenever you change logins on the primary server.


Scripting and Copying Jobs, Alerts, and Operators


The msdb database, not the production database, stores SQL Server jobs, alerts, and operators. If you have jobs, alerts, or operators that run on the primary server on which your production database relies, manually copy these jobs, alerts, and operators to each standby server. You cannot simply back up the msdb database and restore it to a standby server. Doing so overwrites all jobs, alerts, and operators in the msdb database on the standby server, including all the log-shipping jobs. You can copy jobs, alerts, and operators by using SQL Server Enterprise Manager to generate Transact-SQL scripts for them and then applying the scripts to each standby server. You must complete these tasks before users connect to a standby server that is promoted to become the new primary server.


Use the following procedures to copy jobs, alerts, or operators that the production database needs to each standby server.


To script all jobs on the primary server


    

  1. In SQL Server Enterprise Manager, connect to the primary server, expand Management, and then expand SQL Server Agent.
    

    2. 

  2. Right-click Jobs, point to All Tasks, and then click Generate SQL Script.
    

    3. 

  3. In Generate SQL Script, browse to or type the path to the synchronization folder on the primary server, and then type a file name for the jobs script using the extension .sql.
    

    4. 

  4. Verify that the International Text (Unicode) check box is selected in the File Format box and that the Replace Job If It Exists check box is selected in the SQL Generation Options box.
    

    5. 

  5. Accept the default for the TSQL Batch Separator.
    

    6. 

  6. Click OK to create a Transact-SQL script for all jobs on the primary server.
    

    7. 

  7. Use SQL Query Analyzer to edit the jobs script to remove all log-shipping jobs.
    

    8. 



To execute the jobs script on each standby server


    

  1. Using SQL Query Analyzer, connect to SQL Server on a standby server.
    

    2. 

  2. On the File menu, click Open.
    

    3. 

  3. In Open Query File, type or browse to the jobs script in the shared synchronization folder on the primary server, and then open the jobs script.
    

    4. 

  4. Execute the jobs script, and then close SQL Query Analyzer.
    

    5. 

  5. In SQL Server Enterprise Manager, connect to the appropriate instance on the standby server, expand Management, expand SQL Server Agent, and then click Jobs.
    

    6. 

  6. In the details pane, review the jobs that were created. Disable those jobs until they are needed after a role change.
    

    7. 



Note: Repeat this procedure on each standby server.


To script all alerts on the primary server


    

  1. In SQL Server Enterprise Manager, connect to the primary server, expand Management, and then expand SQL Server Agent.
    

    2. 

  2. Right-click Alerts, point to All Tasks, and then click Generate SQL Script.
    

    3. 

  3. In Generate SQL Script, browse to or type the path to the synchronization folder on the primary server and then type a file name for the alerts script using the extension .sql.
    

    4. 

  4. Verify that the International Text (Unicode) check box is selected in the File Format box.
    

    5. 

  5. Verify that the following check boxes are selected in the SQL Generation Options box:
    

      

    • Replace Alert If It Exists
      

      • 

    • Include the Name of the Job Executed by the Alert
      

      • 

    

    6. 

  6. Clear the Include Notifications Sent by the Alert to the Operator check box.
    

    Note: Notifications are copied when the operators are copied to ensure that the operators exist prior to creating the notification.
    

    7. 

  7. Accept the default for the TSQL Batch Separator.
    

    8. 

  8. Click OK to create a Transact-SQL script for all alerts on the publisher.
    

    9. 



Note: A bug in the scripting routine exports all Net Send notifications as e-mail notifications. If you use Net Send in any notifications, edit the script file and change the notification method in each sp_add_notification statement from @notification_method =1 to @notification_method =4 to configure the notification to use Net Send.Note: If any alerts use user-defined messages, you must create those messages on the subscriber before applying this script. It is recommended that you always use a script to create user-defined messages so you can recreate the messages on any SQL Server instance.


To execute the alerts script on each standby server


    

  1. Using SQL Query Analyzer, connect to SQL Server on a standby server.
    

    2. 

  2. On the File menu, click Open.
    

    3. 

  3. In Open Query File, type or browse to the alerts script in the shared synchronization folder on the primary server, and then open the alerts script.
    

    4. 

  4. Execute the alerts script, and then close SQL Query Analyzer.
    

    5. 

  5. In SQL Server Enterprise Manager, connect to the appropriate instance on the standby server, expand Management, expand SQL Server Agent, and then click Alerts.
    

    In the details pane, review the alerts that were created.
    

    6. 



Note: Repeat this procedure on each standby server.


To script all operators on the primary server


    

  1. In SQL Server Enterprise Manager, connect to the primary server, expand Management, and then expand SQL Server Agent.
    

    2. 

  2. Right-click Operators, point to All Tasks, and then click Generate SQL Script.
    

    3. 

  3. In Generate SQL Script, browse to or type the path to the synchronization folder on the primary server and then type a file name for the operators script using the extension .sql.
    

    4. 

  4. Verify that International Text (Unicode) is selected in the File Format box.
    

    5. 

  5. Verify the following check boxes are selected in the SQL Generation Options box.
    

      

    • Replace Operator If It Exists
      

      • 

    • Include Notifications Sent by Alert to the Operator
      

      • 

    

    6. 

  6. Accept the default for the TSQL Batch Separator.
    

    7. 

  7. Click OK to create a Transact-SQL script for all operators on the primary server.
    

    8. 



To execute the operators script on each standby server


    

  1. Using SQL Query Analyzer, connect to SQL Server on a standby server.
    

    2. 

  2. On the File menu, click Open.
    

    3. 

  3. In Open Query File, type or browse to the operators script in the shared synchronization folder on the primary server, and then open the operators script.
    

    4. 

  4. Execute the operators script, and then close SQL Query Analyzer.
    

    5. 

  5. In SQL Server Enterprise Manager, connect to the appropriate instance on the standby server, expand Management, expand SQL Server Agent, and then click Operators.
    

    6. 

  6. In the details pane, review the operators that were created.
    

    7. 



Note: Repeat this procedure on each standby server.


Copying DTS Packages


DTS packages can be stored in the msdb database or in the file system. If you have DTS packages that run on the primary server on which your production database relies, you must manually copy these DTS packages to each standby server. You cannot simply back up the msdb database and restore it to a standby server. Doing so overwrites all jobs, alerts, operators, and DTS packages in the msdb database on the standby server. You can open DTS packages saved to the msdb database on the primary server and save them to each standby server. Simply copy DTS packages saved to the file system on the primary server to a folder on each standby server. Complete this task before users connect to a standby server that is promoted to become the new primary server.


Use the following procedure to copy DTS packages that the production database needs to each standby server.


To copy DTS packages


    

  1. For each DTS package stored in the file system on the primary server, copy the DTS package to each standby server using Windows Explorer.
    

    When copying DTS packages to each standby server, use the same drive and path that the primary server uses. Doing so eliminates potential path problems.
    

    2. 

  2. For each DTS package stored in the msdb database on the primary server, use SQL Server Enterprise Manager to open the DTS package, and then save it to the msdb database on each standby server.
    

    3. 



Note: To ensure DTS packages execute properly on a subscriber, use an alias or the dynamic properties task. For more information, see "Redirecting Client Network Traffic to a Promoted Secondary Server" in Planning Guide Chapter 5, "Minimizing Downtime by Using Redundant Components."


Changing the Role of a Standby Server


To change the role of a standby server when the primary server fails or is taken offline, disable the database restoration job on that server, execute the role change stored procedures, synchronize SQL Server logins and standby database user accounts, and then use the standby database as the new production database. Ensure all logins, jobs, alerts, operations, and DTS packages are synchronized, and then redirect clients to the promoted standby server. For more information about facilitating this client redirection task, see Planning Guide Chapter 5, "Minimizing Downtime by Using Redundant Servers."


In a planned role change, pause all applications that use the production database, and then place the production database in single user mode. Pausing the production database helps ensure that the standby database is transactionally current with the production database. Pausing the production database fails if applications do not disconnect from the database after each transaction. Be sure you don’t have open connections in Enterprise Manager, Query Analyzer, or other utilities that would cause the pause procedure to fail.


In an unplanned role change, recent transactions that are not backed up and copied to the standby server before the primary server fails are lost.


Note: Save the log-shipping role change Transact-SQL scripts shown in the procedures later in this chapter, and incorporate them into OSQL batch files to make role changing faster and less error prone.


To pause applications that use the production database


    

  1. Disconnect applications from the production database if the database is still available.
    

    The method for doing so varies by application. In a planned failover, allow open transactions to complete, but do not allow new ones to start.
    

    2. 

  2. If you implemented NLB to direct traffic to a specific server, issue a WLBS command to ensure that all transactions have completed:
    

    3. 



WLBS DrainStop <NLB cluster name>:<primary server> /PASSW <password>



Note: For more information about NLB, see Solutions Guide Chapter 4, "Implementing Network Load Balancing."


To place the production database in single user mode from a command prompt


    

  • Put the following command into a batch file so that it is ready when you need it:
    

    • 



OSQL –Q ALTER DATABASE <production database> SET SINGLE_USER WITH
ROLLBACK IMMEDIATE –S<primary server> -d master –E



Note: Ensure that no users are connected to the production database before continuing, including any connections you have through SQL Server Enterprise Manager.


To execute the sp_change_primary_role system stored procedure


    

  1. Using SQL Query Analyzer, connect to the primary server by using Windows Authentication.
    

    2. 

  2. In the query window, type and execute the following Transact-SQL script:
    

    3. 



Msdb..Sp_change_primary_role
@db_name=<production database>,
@backup_log=1,
@terminate=0,
@final_state=2,
@access_level=2



**Note**: You will not be able to run this system stored procedure if the primary server has failed. All transactions not previously backed up are lost and users have to recreate these lost transactions.

When the sp\_change\_primary\_role system stored procedure executes with these parameters, the system stored procedure performs the following actions:

  - Removes the production database from the log-shipping database maintenance plan.

  - Disables the log-shipping transaction log backup job on the primary server.

  - Backs up the tail end of the production database transaction log.

  - Terminates and rolls back all pending transactions in the production database.

  - Places the production database in single-user mode for the duration of the system stored procedure.

  - Sets the recovery state of the production database after the system stored procedure completes to **NO RECOVERY**, which allows the application of transaction log backups from the promoted standby database.

  - Sets the access level of the production database after the system stored procedure completes to restricted user mode.



Note: If this system stored procedure fails because SQL Server cannot obtain exclusive access to the production database, rerun this system stored procedure after closing any existing connections to the production database. If the first execution of this system stored procedure fails, the log-shipping transaction log backup job is still disabled.


To stop the log-shipping restore job on the standby server


    

  1. Using SQL Server Enterprise Manager, connect to SQL Server on the standby server.
    

    2. 

  2. Expand Management, and then click Jobs.
    

    3. 

  3. Right-click Log Shipping Restore for <primary server>.<production database>_logshipping, and then click Disable Job.
    

    Disabling the log-shipping restore job prevents its periodic execution from interfering with the execution of the sp_change_secondary_role system stored procedure.
    

    4. 



Note: Ensure that no users are connected to the standby database before continuing, including any connections you have through SQL Server Enterprise Manager.


To execute the sp_change_secondary_role system stored procedure


    

  1. Using SQL Query Analyzer, connect to the standby server by using Windows Authentication.
    

    2. 

  2. In the query window, type and execute the following Transact-SQL script:
    

    3. 



Msdb..sp_change_secondary_role
@db_name=<standby database>,
@do_load=1,
@final_state=1,
@access_level=1,
@terminate=0



When the sp\_change\_secondary\_role system stored procedure executes with these parameters, this system stored procedure performs the following actions:

  - Copies the final production database transaction log backup to the standby server along with any additional transaction log backups that were not previously copied.

  - Disables the log-shipping transaction copy job on the standby server.

  - Restores all transaction log backups not previously restored to the standby database and initiates the recovery of the standby database.

  - Terminates and rolls back all pending transactions.

  - Sets the access level of the standby database after completion of the system stored procedure to multiuser mode.

  - Creates a log-shipping database maintenance plan on the new primary server for the new production database. The SQL Server Agent job defined in this plan backs up the transaction log of the new production database to the folder location you specified when you set up log shipping.



Note: If this system stored procedure fails because SQL Server cannot obtain exclusive access to the standby database, rerun this system stored procedure after closing any existing connections to the production database. If the first execution of this system stored procedure fails, the log-shipping copy job is still disabled.


To synchronize SQL Server logins and database users


Note: Be sure you have applied the fix specified in article 310882, "BUG: sp_resolve_logins Stored Procedure Fails If Executed During Log Shipping Role Change" in the Microsoft Knowledge Base at https://support.microsoft.com/default.aspx?scid=kb;en-us;310882&sd=tech.


    

  1. Using SQL Query Analyzer, connect to SQL Server on the new primary server by using Windows Authentication.
    

    2. 

  2. Select the new production database.
    

    3. 

  3. In the query window, type and execute the following Transact-SQL script:
    

    4. 



sp_resolve_logins '<database name>', '\<new primary server>&lt;share
name of synchronization folder>', 'syslogins.dat'



Note: This script will not successfully synchronize mappings between SQL Server logins and database users in the standby database if you do not run the synchronize logins job for each standby server each time you change SQL Server logins or database users on the primary server.


To execute the sp_change_monitor_role system stored procedure


    

  1. Using SQL Query Analyzer, connect to SQL Server on the monitoring server by using Windows Authentication.
    

    2. 

  2. In the query window, type and execute the following Transact-SQL script:
    

    3. 



Msdb..Sp_change_monitor_role
@primary_server=<old primary server>,
@secondary_server=<promoted standby server>,
@database=<promoted standby database>,
@new_source=<transaction log backup folder for promoted standby database>



When the sp\_change\_monitor\_role system stored procedure executes with these parameters, the **msdb** database on the monitoring server updates to reflect the new primary server, the new standby server, and the new transaction log backup folder.



The former standby server is now the primary server. To maintain the database redundancy protection provided by log shipping, edit the database maintenance plan on the promoted standby server to enroll the original primary server as a standby server. If the tail of the transaction log for the production database on the original primary server was backed up and the database left in recovery mode, you can apply transaction logs from the new primary server without reinitializing the original production database. If the original primary server failed, initialize and synchronize the standby database on this server before adding it to the database maintenance plan.