Lesson 1: Using Standby Servers

3 4

A standby server can be maintained to take the place of the primary production server, if needed. In this lesson, you will learn about setting up and maintaining a standby server. This includes learning how to automate the maintenance of a standby server. You will also learn how to bring a standby server online, and how to bring the primary server back online.


After this lesson, you will be able to

  • Set up a standby server
  • Automate log shipping
  • Monitor log shipping
  • Bring a standby server online
  • Bring a primary server back online

Estimated lesson time: 45 minutes


Using a Standby Server

A standby server is a secondary SQL Server installation that is kept up-to-date with a primary SQL Server installation through the application of transaction log backup files. By using standby servers, a database administrator can minimize the length of time that users are unable to access one or more production database(s) in case of server failure or the need for server maintenance. The databases on a standby server can also be used as reporting servers to offload reporting and analysis tasks.

A standby server must maintain an exact copy of one or more production databases on the primary server. This is initially accomplished through the use of a full database backup on the primary server that is restored to the standby server using either the NORECOVERY or the STANDBY restoration options. Using one of these two options leaves the standby database in recovery mode, so that additional transaction logs can be applied to it.

Next, to maintain an exact copy on the standby server, regular transaction log backups are performed on the primary server and applied to the standby server (again leaving the standby server in recovery mode). The frequency with which transaction log backups are performed on the primary server and applied to the standby server determines the amount of work (and length of time) required to bring the standby server up-to-date and online in the event the standby server must be promoted.

To promote the standby server to become the new primary server, all unapplied transaction log backup files must be applied to the standby server. In addition, the active portion of the transaction log on the primary server must be backed up and applied to the standby server. This final restoration is performed using the RECOVERY option. Users can then use the database on the standby server, which contains all data from the primary server exactly as it was prior to its failure (other than uncommitted transactions, which are permanently lost).

Note


Using RAID for transaction log files is critical for ensuring that the active portion of the transaction log is available for backup and application to the standby server.

The active portion of the transaction log on the primary server can be backed up using the NORECOVERY backup option. This option leaves the primary database in recovery mode. When the primary server is ready to be brought back online, the transaction logs from the standby server (for the period that users were using the standby server as the primary server) can be applied to the primary server. This avoids the necessity of applying a complete database backup and all applicable transaction logs to restore the primary server. The result is a significant decrease in the time required to bring the primary server back online.

When the standby server is brought online for use in place of the primary server, either the standby server must be renamed using the name of the primary server or user processes must know to connect to the standby server (using the name of a standby server) rather than the primary server. All uncommitted transactions must be restarted. Uncommitted transactions cannot be maintained between the primary server and the standby server.

Note


Use the SQL Server Setup program to rename a SQL Server 2000 installation after renaming the server using Windows 2000 or Windows NT 4.0.

User logins must be created on the standby server prior to bringing the standby server online as the new primary server. This is generally accomplished using one of the following methods.

  • User logins from the primary server can be scripted and these scripts used to create the necessary logins on the standby server when needed.
  • The DTS Transfer Logins Task in DTS Designer can be used within a job to automate the process of backing up, copying, and restoring the contents of the sysxlogins system table from the primary server to the standby server.

If the standby server is only providing services to a single production server, you might want to create the logins on the standby server when the standby server is created. However, if the standby server is providing services to multiple databases from multiple instances of SQL Server, generally you will not create logins on the standby server until it is brought online as the primary server for a particular database.

Automating Log Shipping

You can automate the process of maintaining a standby server by creating backup, copy, and restore jobs that are periodically executed by SQL Server Agent on the primary server and on the standby server. This automated process is called log shipping. You can also designate a third server to monitor the execution of the log shipping jobs on the primary server and on the standby server; the third server is frequently used to monitor other pairs of log shipping servers as well.

Note


The log shipping feature is available only in the Enterprise, Enterprise Evaluation, and Developer Editions of SQL Server 2000.

You can create these jobs using the Database Maintenance Plan Wizard in SQL Server Enterprise Manager. You must be a member of the sysadmin server role to run the Database Maintenance Plan Wizard. To start the Database Maintenance Plan Wizard, click Database Maintenance Planner on the Tools menu, or right-click the Database Maintenance Plan container (in the Management container) and then click New Maintenance Plan. (The use of most of the Database Maintenance Planner features was covered in Chapter 13.)

In the Select Servers page, select the primary server. See Figure 16.1.

 figure 16.1 - selecting a primary server for log shipping.

Figure 16.1

Selecting a primary server for log shipping.

Next, in the Select Databases page, select the database for which you want to configure log shipping, and specify log shipping by selecting the Ship The Transaction Logs To Other SQL Servers (Log Shipping) check box. You can select only one database at a time. See Figure 16.2.

Next, advance to the Specify the Database Backup Plan and clear the Back Up The Database As Part Of The Maintenance Plan check box. See Figure 16.3.

 figure 16.2 - selecting a database for log shipping.

Figure 16.2

Selecting a database for log shipping.

 figure 16.3 - clearing the full database back up check box.

Figure 16.3

Clearing the full database back up check box.

Note


You do not need to perform a full database backup as part of a log shipping database maintenance plan, although you will need to either perform an initial full backup or use an existing full backup.

Next, in the Specify Transaction Log Backup Disk Directory page, specify the directory into which the transaction log backup files will be stored. Make sure this location has sufficient space to hold the shipped logs, or log shipping will fail. See Figure 16.4.

 figure 16.4 - selecting a storage location for transaction log backups.

Figure 16.4

Selecting a storage location for transaction log backups.

Next, in the Specify The Transaction Log Share page, specify the network share for the storage location for the transaction log backup files. See Figure 16.5.

 figure 16.5 - specifying the network share for the transaction log backup files.

Figure 16.5

Specifying the network share for the transaction log backup files.

You must create this network share for the directory in which the transaction log backup files are stored. The domain user account used by the SQL Server Agent service on the primary server must have read and write access to this network share, and the domain user account used by the SQL Server Agent service on the standby server must have read access to this network share.

Next, in the Specify The Log Shipping Destinations page, click the Add button to display the Add Destination Database dialog box, where you specify the standby server and configure its properties. The standby server is called the destination server in the wizard. In addition to using log shipping to create and maintain standby servers, you can also use log shipping to create read-only copies of one or more production server databases rather than using replication. Figure 16.6 illustrates the Specify The Log Shipping Destinations page after a log shipping destination has been specified.

 figure 16.6 - specifying the standby server.

Figure 16.6

Specifying the standby server.

In the Add Destination Database dialog box, there are a number of properties you can configure for the destination database. See Figure 16.7.

 figure 16.7 - specifying the properties of the destination database.

Figure 16.7

Specifying the properties of the destination database.

In the Destination Database group box, you can choose to specify a different database name for the destination database. This is useful for creating a destination read-only database for reporting purposes. If you want the destination database to be viewable for read-only queries, you must select the Standby Mode option button in the Database Load State group box. The default is the No Recovery Mode option button. If you choose to update and overwrite an existing database (rather than creating a new database), you should select the Terminate Users In Database (Recommended) check box to automatically terminate all users in the existing database. If you want to use the destination database as a standby server that may need to assume the role of the primary server, you must select the Allow Database To Assume Primary Role check box to specify that it can assume the primary role if necessary. When you select this check box, you must specify the directory, in the Directory text box, for storing transaction log backups during the period the standby server is functioning as the primary server.

Next, in the Initialize The Destination Databases page, you specify the backup file containing a full database backup to be used to initialize the destination database on the standby server (see Figure 16.8).

 figure 16.8 - specifying the backup file for initialization.

Figure 16.8

Specifying the backup file for initialization.

You can choose to perform a full backup immediately by selecting the Perform A Full Database Backup Now option button, or you can select the Use Most Recent Backup File option button to specify an existing recent backup file. If you specify a recent backup file, you must include the path and filename of the existing backup file in the Use Most Recent Backup File text box. This backup file will be placed in the log shipping share so that it is available to the standby server for initialization of the destination database.

Next, in the Log Shipping Schedules page, specify the log shipping schedule. See Figure 16.9.

In the Backup Schedule group box, the default frequency for backing up the transaction log on the primary server is 15 minutes. Click the Change button to display the Edit Recurring Job Schedule dialog box to modify this frequency. Next, in the Copy/Load Frequency spin box, specify the frequency with which the transaction log backup files are copied to the standby server and restored (the default is 15 minutes). In the Load Delay spin box, you can specify a delay between the copy and the load (restore) of the transaction log backup files (the default is no delay). Finally, in the File Retention Period spin box, you can specify the length of time the transaction log backup files are retained on the log shipping share (the default is 24 hours). If the standby server is unable to copy these files before the retention period expires, the destination database on the standby server will have to be reinitialized. The frequence of the transaction log backups and the length of the file retention period will affect the amount of disk space that the transaction log backup files will require.

Next, in the Log Shipping Thresholds page, specify the log shipping alert thresholds. See Figure 16.10.

 figure 16.9 - specifying the log shipping schedule.

Figure 16.9

Specifying the log shipping schedule.

 figure 16.10 - specifying the log shipping alert thresholds.

Figure 16.10

Specifying the log shipping alert thresholds.

The Backup Alert Threshold alert defines the maximum length of time between transaction log backups on the primary server (the default is 45 minutes or three times the interval between transaction log backups). If the defined length of time is exceeded, an alert will fire to notify an administrator of a problem with the automated log shipping process. The Out Of Sync Alert Threshold alert defines the maximum length of time between the most recent transaction log backup on the primary server and the restore of the transaction log backup to the standby server (the default is 45 minutes or three times the interval between transaction log restores).

Next, in the Specify The Log Shipping Monitor Server Information page, specify a log shipping monitor for monitoring log shipping jobs on the primary server and the standby server. See Figure 16.11.

 figure 16.11 - specifying a central monitoring server.

Figure 16.11

Specifying a central monitoring server.

This should generally be a server other than the primary or the standby server. The monitoring server can provide monitoring services to multiple log shipping pairs of servers. You must specify the authentication mode the SQL Server Agent service will use to connect to the monitoring server. Select either Windows authentication or SQL Server authentication. If SQL Server authentication is selected, you must use the SQL Server login account, log_shipping_monitor_probe.

The next two pages, the Reports to Generate page and the Maintenance Plan History page, were covered in Chapter 13. See Figures 13.48 and 13.49.

When you are finished configuring your maintenance plan, the Database Maintenance Plan Wizard Summary page appears. See Figure 16.12. Verify that the plan information is correct, and then click Next. Finally, click the Finish button in the Completing the Database Maintenance Plan Wizard page, as shown in Figure 16.13.

After the Database Maintenance Plan Wizard completes its task, the database selected for log shipping is restored in recovery mode (using either the NORECOVERY or STANDBY options) on the standby server. See Figure 16.14.

 figure 16.12 - database maintenance plan summary page.

Figure 16.12

Database Maintenance Plan Summary page.

 figure 16.13 - completing the database maintenance plan wizard page.

Figure 16.13

Completing the Database Maintenance Plan Wizard page.

 figure 16.14 - standby server with database in standby mode.

Figure 16.14

Standby server with database in standby mode.

Practice: Creating a Standby Server

In this practice you use the Database Maintenance Planner Wizard in SQL Server Enterprise Manager to automate log shipping.

To create a standby server

  1. Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
  2. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
  3. In the console tree, expand the Microsoft SQL Servers container, expand the SQL Server Group container, expand the default instance container, and then expand the Databases container.
  4. Right-click Databases, point to All Tasks, and then click Attach Database.

    The Attach Database - SelfPacedCPU dialog box appears.

  5. Click the ellipsis button to browse for the MDF file.

    The Browse for Existing File - SelfPacedCPU dialog box appears.

  6. Click LogShipDB.mdf in C:\SelfPacedSQL\CH_16 and then click OK.

    Notice that the LogShipDB database is about to be attached.

  7. In the Specify Database Owner drop-down box, click Sa.
  8. Click OK to attach the LogShipDB database.

    A SQL Server Enterprise Manager message box appears to inform you that attachment of the database has completed successfully.

  9. Click OK.

    Notice that the LogShipDB database appears in the list of databases.

  10. Right-click the LogShipDB container, point to All Tasks, and then click Maintenance Plan.

    The Welcome To The Database Maintenance Plan Wizard page appears.

  11. Click Next.

    The Select Servers page appears.

  12. Select the (Local) check box and then click Next.

    The Select Databases page appears with the LogShipDB check box selected.

  13. Select the Ship The Transaction Logs To Other SQL Servers (Log Shipping) check box and then click Next.

    The Update Data Optimization Information page appears.

  14. Click Next.

    The Database Integrity Check page appears.

  15. Click Next.

    The Specify The Database Backup Plan page appears.

  16. Clear the Back Up The Database As Part Of The Maintenance Plan check box and then click Next.

    The Specify Transaction Log Backup Disk Directory page appears.

  17. Verify that the Use The Default Backup Directory option button is selected.
  18. Select the Remove Files Older Than check box and then click 1 in the spin box and Hour(s) in the drop-down list.
  19. Click Next.

    The Specify The Transaction Log Share page appears.

  20. Open Windows Explorer and browse to C:\Program Files\Microsoft SQL Server\Mssql.
  21. Right-click Backup and then click Sharing.

    The Backup Properties dialog box appears.

  22. Click the Share This Folder option button, change the share name to ProductionBackup in the Share Name drop-down combo box, and then click OK.
  23. Browse to C:\Program Files\Microsoft SQL Server\Mssql \$MyNamedInstance.
  24. Right-click Backup and then click Sharing.

    The Backup Properties dialog box appears.

  25. Click the Share This Folder option button, change the name to StandbyBackup in the Share Name drop-down combo box, and then click OK.
  26. Close Windows Explorer.
  27. Switch to the Database Maintenance Plan Wizard.
  28. In the Network Share Name For Backup Directory text box, type \\SelfPacedCPU\ProductionBackup and then click Next.

    The wizard verifies that the specified share exists and the Specify The Log Shipping Destinations page appears.

  29. Click the Add button.

    The Add Destination Database dialog box appears.

  30. In the Server Name drop-down list, click SelfPacedCPU\MyNamedInstance.
  31. In the Database Load State group box, click the Standby Mode option button and then select the Terminate Users In Database (Recommended) check box.
  32. Select the Allow Database To Assume Primary Role check box.
  33. In the Directory text box, type \\SelfPacedCPU\StandbyBackup and then click OK.

    The wizard verifies that the specified share exists and the Specify The Log Shipping Destinations page reappears displaying the destination server and database.

  34. Click Next.

    The Initialize The Destination Databases page appears.

  35. Verify that the Perform A Full Database Backup Now option button is selected and then click Next.

    The Log Shipping Schedules page appears.

  36. Click the Change button to modify the backup schedule.

    The Edit Recurring Job Schedule dialog box appears. Note that there are two spin boxes, one for changing the number and one for specifying the increment of time.

  37. In the Daily Frequency group box, change the Occurs Every spin box from 15 to 1 (minute) and then click OK.

    The Log Shipping Schedules page reappears.

  38. Change the Copy/Load Frequency spin box from 15 (minutes) to 1.
  39. Change the File Retention Period spin box from 24 (hours) to 1 (hour(s)).
  40. Click Next.

    The Log Shipping Thresholds page appears. Notice that the Backup Alert Threshold is set to 5 minutes and the Out Of Sync Alert Threshold is set to 3 minutes.

  41. Click Next.

    The Specify The Log Shipping Monitor Server Information page appears.

  42. In the SQL Server drop-down list, verify that SelfPacedCPU is displayed and then click Next.

    The Reports To Generate page appears.

  43. Click Next.

    The Maintenance Plan History page appears.

  44. Click Next.

    The Database Maintenance Plan Wizard Summary page appears.

  45. In the Plan Name text box, type Log Shipping and then click Next.

    The Completing The Database Maintenance Plan Wizard page appears.

  46. Click the Finish button.

    Notice the progress of the steps the wizard is performing.

  47. Click OK to acknowledge a Database Maintenance Plan Wizard message box informing you that the maintenance plan was created successfully.
  48. In the console tree, expand the SelfPacedCPU\MyNamedInstance container and then expand the Databases container.
  49. Click the Databases container and then, on the toolbar, click the Refresh button.

    In the Databases container for the SelfPacedCPU\MyNamedInstance, notice that the LogShipDB database container appears containing a read-only copy of the LogShipDB database. If it does not appear, right-click the Databases container and then click Refresh.

  50. Leave SQL Server Enterprise Manager running.

Monitoring Log Shipping

To monitor the status of log shipping on the monitor server, expand the Management container of that server and then click Log Shipping Monitor (if you have just configured log shipping, right-click the Management container and then click Refresh). The status of all log shipping servers is displayed in the details pane. See Figure 16.15.

 figure 16.15 - log shipping monitor.

Figure 16.15

Log shipping monitor.

The log shipping monitor displays the date and time of the most recent transaction log backup on the primary server in the Last Backup column, the most recent copy of the transaction log backup file to the network share in the Last Copy column, the most recent restore of the transaction log backup file to the standby server in the Last Restore column, and the synchronization status in the Status column. Click the Show/Hide Console Tree/Favorites button on the toolbar to display only the details pane.

To view the backup history for the primary server, right-click the log shipping pair and then click View Backup History to display the Database Maintenance Plan History dialog box. See Figure 16.16.

 figure 16.16 - viewing the backup history for the primary server from the monitor server.

Figure 16.16

Viewing the backup history for the primary server from the monitor server.

To view the copy and restore history for the standby server, right-click the log shipping pair and then click View Copy/Restore History to display the Secondary Server Log Shipping History dialog box. See Figure 16.17.

To modify the properties of log shipping, right-click the log shipping pair and then click Properties to display the Log Shipping Pair Properties dialog box. Log shipping information is displayed on this page, including the last backup file, the last file copied, the last file loaded, and the times and deltas for each. See Figure 16.18.

Click the Source tab to modify alert properties for the primary server (see Figure 16.19). Click the View Backup Schedule button to view the backup schedule.

Click the Destination tab to modify the alert properties for the standby server (see Figure 16.20). Click the View Copy Schedule button to view how often database copies are made, or click the View Load Schedule button to view how often the database is restored.

 figure 16.17 - viewing the copy/restore history for the standby server from the monitor server.

Figure 16.17

Viewing the copy/restore history for the standby server from the monitor server.

 figure 16.18 - viewing log shipping pair properties.

Figure 16.18

Viewing log shipping pair properties.

 figure 16.19 - viewing the source tab of log shipping pair properties.

Figure 16.19

Viewing the Source tab of log shipping pair properties.

 figure 16.20 - viewing the destination tab of log shipping pair properties.

Figure 16.20

Viewing the Destination tab of log shipping pair properties.

Practice: Monitoring Log Shipping

In this practice you use the Log Shipping Monitor in SQL Server Enterprise Manager to monitor log shipping.

To monitor log shipping

  1. Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
  2. In the SQL Server Enterprise Manager console tree, expand the Management container in the default instance.
  3. Click the Management container and then, on the toolbar, click the Refresh button.
  4. In the Management container, click Log Shipping Monitor.

    The current status of the log shipping pair is displayed in the details pane.

  5. On the toolbar, click the Show/Hide Console Tree/Favorites button.

    Notice that the window now displays only the contents of the details pane (for easier viewing of all columns).

  6. Right-click the log shipping pair and then click View Backup History.

    The Database Maintenance Plan History - SelfPacedCPU dialog box appears displaying the history of the Log Shipping maintenance plan. Notice that the transaction log is being backed up every minute and each successful job has a blue check mark in the status column.

  7. Click the Close button.
  8. Right-click the log shipping pair and then click View Copy/Restore History.

    The Secondary Server Log Shipping History - SelfPacedCPU\MyNamedInstance dialog box appears. Notice that the copy job and the load job occur every minute. The load job restores all transaction log backup files that have been copied, and then the copy job connects to the primary server and copies any new transaction log backup files to the standby server.

  9. Click the Close button.
  10. Right-click the log shipping pair and then click Properties.

    The Log Shipping Pair Properties dialog box appears. Notice that Load Delta indicates that the synchronization delay between the primary database and the standby database is one minute (it will be two minutes on some slower computers).

  11. Click OK to close the Log Shipping Pair Properties dialog box.
  12. On the Tools menu, click SQL Query Analyzer.

    SQL Query Analyzer appears displaying a connection to the SelfPacedCPU default instance.

  13. On the toolbar, click the Load SQL Script button.

    The Open Query File dialog box appears.

  14. Open LogShipChangeData.sql in the C:\SelfPacedSQL\CH_16 folder.

    Notice that this script changes the ContactTitle field for one of the customers in the LogShipDB database.

  15. On the toolbar, click the Execute Query button to execute the query.

    Notice that the ContactTitle field for CustomerID ANATR is changed.

  16. Leave SQL Query Analyzer and SQL Server Enterprise Manager running.

Bringing a Standby Server Online

To bring a standby server online, you must run the following system stored procedures in order.

  1. Run sp_change_primary_role on the primary server. Use the @db_name argument to specify the appropriate database. Use the @backup_log argument to specify a backup of the current transaction log and the @terminate argument to specify a roll back of all incomplete transactions. You can specify the recovery state of the database after the completion of the stored procedure: RECOVERY, NO RECOVERY, or STANDBY (default is RECOVERY) with the @final_state argument. Finally, you can specify the access level of the database after the completion of the stored procedure: MULTI_USER, RESTRICTED_USER, or SINGLE_USER (default is MULTI_USER) with the @access_level argument.
  2. Run sp_change_secondary_role on the standby server. Use the @db_name to specify the appropriate database. Use the @db_name argument to specify the application of pending transaction log backup files to the standby database. Use the @db_name argument to convert the standby database to a primary database. You can specify the recovery state of the database after the completion of the stored procedure: RECOVERY, NO RECOVERY, or STANDBY (default is RECOVERY) with the @final_state argument. Finally, you can specify the access level of the database after the completion of the stored procedure: MULTI_USER, RESTRICTED_USER, or SINGLE_USER (default is MULTI_USER) with the @access_level argument.

    Note


    If the secondary server (the standby server) has a job running against this database at the same time the system stored procedure is run, you will receive a Transact-SQL error indicating that exclusive access to the database to restore the transaction log files could not be obtained. Either rerun the system stored procedure or complete the restore using SQL Server Enterprise Manager.

  3. Run sp_change_monitor_role on the log shipping monitor server. This system stored procedure updates the monitor server to reflect the changed log shipping roles, using the @primary_server argument to designate the primary server being replaced, the @secondary_server argument to designate the standby server being converted to a primary server, the @database argument to designate the standby database being converted to a primary database, and the @new_source argument to specify the network share the new primary server will use for storing its transaction log backup files.

In addition, you must verify that the new primary server contains all required logins. You can use a saved Transact-SQL script, or if you used the DTS Transfer Logins Task to create a Bcp output file containing these logins, you can use the sp_resolve_logins system stored procedure to input these logins from the Bcp output file.

After completion of these steps, the standby server is ready to function as the new primary server. The former primary server is no longer part of a shipping pair. You must add the original primary server or another server as a standby server to re-establish a shipping pair. To bring the original primary server back online, repeat the three-step process just described.

Practice: Bringing a Standby Server Online as the Primary Server

In this practice you use stored procedures to bring a standby server online as the primary server and deactivate the primary server and its log shipping jobs.

To bring a standby server online as the primary server

  1. Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
  2. In SQL Query Analyzer, verify that you are connected to the default instance of SQL Server on SelfPacedCPU and then click the Load SQL Script button on the toolbar.

    The Open Query File dialog box appears.

  3. Open DemotePrimary.sql in the C:\SelfPacedSQL\CH_16 folder.

    This script runs the sp_change_primary_role system stored procedure, which backs up the current transaction log for the LogShipDB database and sets the database to read-only.

  4. On the toolbar, click the Execute Query button to execute the DemotePrimary.sql script.

    The script executes and displays a message regarding the backup of the current transaction log.

    Note


    If you get an access denied permissions error, the SQL Server Agent service does not have sufficient permissions. Either verify all required permissions, or make the SQLService account a member of the local Administrators group (and restart all services), and then rerun this lab from the beginning.

  5. Switch to SQL Server Enterprise Manager.
  6. On the toolbar, click the Show/Hide Console Tree/Favorites button to show the console tree (if necessary).
  7. Expand the SelfPacedCPU\MyNamedInstance container, expand the Management container, expand the SQL Server Agent container, click the Jobs container, and then click Local Server Jobs.

    The current jobs are displayed in the details pane.

  8. In the details pane, right-click the Log Shipping Copy For SelfPacedCPU LogShipDB_Logshipping job and then click Disable Job.
  9. In the details pane, right-click the Log Shipping Restore For SelfPacedCPU LogShipDB_Logshipping job and then click Disable Job.
  10. Switch to SQL Query Analyzer.
  11. On the File menu, click Connect.

    The Connect To SQL Server dialog box appears.

  12. In the SQL Server drop-down list, select SelfPacedCPU\MyNamedInstance, verify that the Windows Authentication option button is selected, and then click OK.
  13. On the toolbar, click the Load SQL Script button.

    The Open Query File dialog box appears.

  14. Open PromoteSecondary.sql in the C:\SelfPacedSQL\CH_16 folder.

    This script runs the sp_change_secondary_role system stored procedure, which copies the current transaction log from the primary server and restores it to the LogShipDB database on the standby server. It also restores the database to multiuser mode and removes the read-only setting.

  15. On the toolbar, click the Execute Query button to execute the PromoteSecondary.sql script.

    Notice that the script copies the current transaction log (and any other logs it has not previously copied) and applies it to the database on the standby server. It deletes the copy and load jobs that were running on the former standby server and enables the transaction log backup job on the new primary server. If the second result set indicates that exclusive control of the database could not be obtained to perform the restore task, rerun the system stored procedure.

  16. Switch to the SQL query pane containing the connection to the default instance.
  17. On the toolbar, click the Load SQL Script button.

    The Open Query File dialog box appears.

  18. Open UpdateMonitorServer.sql in the C:\SelfPacedSQL\CH_16 folder.

    This script runs the sp_change_monitor_role system stored procedure, updating the monitor server regarding the change in the role of the primary and standby server. It also specifies the storage location for the transaction log backup files being created on the new primary (former standby) server.

  19. On the toolbar, click the Execute Query button to execute the UpdateMonitorServer.sql script.
  20. Switch to the SQL query pane containing the connection to SelfPacedCPU\MyNamedInstance.
  21. On the toolbar, click the Clear Window button.
  22. In the query pane, type SELECT * FROM LogShipDB.dbo.Customers WHERE CustomerID = 'ANATR ' and then click the Execute Query button on the toolbar.

    Notice that the ContactTitle change was captured in the transaction log and applied to the new primary server before it was restored.

  23. On the toolbar, click the Clear Window button.
  24. In the query pane, type UPDATE LogShipDB.dbo.Customers SET ContactTitle = 'Owner' WHERE CustomerID = 'ANATR ' and then click the Execute Query button on the toolbar.

    Notice that the query executes successfully. The new primary database is functioning successfully. If you receive an error stating that BEGIN TRANSACTION could not run because the database is read-only, you are connected to the default instance rather than to the named instance.

  25. Close SQL Server Enterprise Manager and SQL Query Analyzer.

Lesson Summary

Using a standby server enables the database administrator to quickly bring a server online in case of a server failure or to perform necessary maintenance on a primary server. Log shipping automates this process using jobs and alerts administered by SQL Server Agent service on the primary server and on the standby server. A monitoring server is used to monitor log shipping pairs of servers. Bringing a standby server online as the primary server requires the execution of three system stored procedures. In addition, the database administrator must ensure that the new primary server has the necessary logins for the databases being promoted. This can be accomplished using the DTS Transfer Logins Task or using Transact-SQL scripts.



MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
ISBN: N/A
EAN: N/A
Year: 2001
Pages: 126

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