Changing a Log Shipping Configuration
You can use your database maintenance plan's Properties dialog box to change your log shipping configuration. The Transaction Log tab offers options for changing the configuration of the log shipping transaction-log backups. The Log Shipping tab shows you the log shipping pair you originally created for the plan, as well as any other pairs you might have added to the plan. This tab also contains options to add a new destination database (thereby creating a new log shipping pair), to delete a selected log shipping pair, to edit current log shipping pair properties, and to remove log shipping entirely.
When you choose Edit from the Log Shipping tab, you open the Edit Destination Database dialog box. On the dialog box's General tab, you can view and change the secondary-server transaction-log directory and future primary-server file share locations that you chose during setup. On the Initialize tab, you can change the recovery mode and the secondary server's copy and restore frequencies. On the Thresholds tab, which Figure 4 shows, you can set threshold durations. The Out of Sync Threshold sets the maximum permitted duration (between the latest source database transaction-log backup and the most recent transaction-log restore) before the Log Shipping Monitor will generate an alert. (You can also set this parameter from the Log Shipping Monitor.) The Load Time Delay, File Retention Period, and History Retention Period values all relate to the secondary server.
The monitor server plays an important role in these configuration options. The Log Shipping tab depends on the monitor server for much of its information, so you can't change log shipping configuration values when the monitor server is unavailable. When I ran SQL Server 2000 Profiler on the monitor server, I found that the primary server connects to the monitor server to retrieve existing log shipping plan information from the log shipping tables on the monitor server. Therefore, to change a log shipping plan's settings, you must make sure that the monitor server is available to Enterprise Manager.
Inspecting and Monitoring Log Shipping
If you're accustomed to SQL Server 7.0 log shipping or you've created your own log shipping system, you probably monitor log shipping activity from the secondary server. However, SQL Server 2000 log shipping also provides a special Log Shipping Monitor utility that you typically place on a separate monitor server.
Where does SQL Server 2000 store log shipping information? A total of seven log shipping tables reside in the msdb database in SQL Server 2000 Enterprise and Developer editions:
- log_shipping_plans
- log_shipping_plan_databases
- log_shipping_databases
- log_shipping_plan_history
- log_shipping_monitor
- log_shipping_primaries
- log_shipping_secondaries
Each of these tables exists on the primary, secondary, and monitor servers. Each server uses only some of the tables to store data, depending on the role the server plays in log shipping.
Inspecting log shipping on the primary server. From Enterprise Manager, you can connect to the primary server and inspect and monitor log shipping. If a database is involved in log shipping, the General tab of that database's Properties dialog box tells you the database's role (i.e., source or destination) as well as which server houses the Log Shipping Monitor. You can view the state and history of the log shipping transaction-log backup job in Enterprise Manager's SQL Server Agent, Jobs node.
The primary server populates only two of the msdb log shipping tables. In log_shipping_databases, SQL Server inserts a row linking the database maintenance plan ID to the source log shipping database. In the log_shipping_monitor table, SQL Server inserts a row containing the monitor server name and type of login.
Inspecting log shipping on the secondary server. The log shipping plan resides on the secondary server. From that server, you can monitor the SQL Agent jobs that copy transaction-log files to the server and restore those logs to the destination database. You can also inspect the destination database's Properties dialog box to determine the database's role in the log shipping process.
On the secondary server, SQL Server uses four of the msdb log shipping tables. After SQL Server creates the log shipping plan, it inserts a row into the log_shipping_plans table, specifying the primary and secondary server names, file locations, and the copy and restore job IDs (which it gets from the sysjobs system table on the secondary server). In the log_shipping_plan_databases table, SQL Server links the plan to the source and destination database names and stores information about the last files copied and loaded. The log_shipping_plan_history table records each log shipping plan's copy and restore event, along with information such as whether the job succeeded. In the log_shipping_monitor table, SQL Server also inserts a row that references the monitor server.
If you chose (when you set up log shipping in the Database Maintenance Plan Wizard) to let the destination database assume a primary role, you'll see one important additional item on the secondary server: another database maintenance plan with the same name as the plan you created but without log shipping enabled. You'll also see a disabled SQL Agent job for making transaction-log backups of that database. You might find these additional items confusing. Despite having the same name, this additional plan isn't the same maintenance plan as the one you created. SQL Server simply holds this second plan in reserve in case you make a primary- and secondary-server role change.
Monitoring log shipping on the monitor server. After log shipping is in place, SQL Server enables Enterprise Manager's Log Shipping Monitor utility on the monitor server. In addition, SQL Server creates two SQL Agent alert jobs: one for backup and the other for out-of-sync conditions.
Prev. page
1
2
[3]
4
next page 
