H

Hi
How do I change the log shipping monitor to another server
without setting up log shipping again?
Thanks
HThese steps are probably worth a shot. I'd however carefully test it in a
test environment first:
The information about the monitoring of the log shipping is stored to the
following locations:
- On the Primary server, in the msdb.dbo.log_shipping_monitor table
- On the Standby server, in the msdb.dbo.log_shipping_monitor table
- On the log shipping Monitor, in the msdb.dbo.log_shipping_primaries and
the msdb.dbo.log_shipping_secondaries tables.
1. Each time a log shipping related job is executed, its completion status
is updated into the tables of the Monitor server. Obviously, if the log
shipping monitor server needs to be changed, the information about it must
be updated in all of the locations. Beware that in log shipping,should not
be allowed to specify different monitor servers if secondary has monitor
already defined.
2. Script the "Log Shipping Alert - Backup" and "Log Shipping Alert -
Restore" jobs from the current monitor server To do this in the Enterprise
manager go to SQL Server Agent -->Jobs --> "Log shipping Alert-Backup"
-->Right click-->Generate SQL Script.Run these scripts on the New Monitor
Server to create the Alerts there.
3. Run the following query on current monitor server and save the output
select maintenance_plan_id from msdb.dbo.log_shipping_primaries
select secondary_plan_id from msdb.dbo.log_shipping_secondaries
4. On the new log shipping Monitor, there are a couple of alternatives:
- Use Query Analyzer or other similar tool to execute
sp_add_log_shipping_primary and sp_add_log_shipping_secondary stored
procedures on the new Monitor server to define the monitored servers. This
alternative is suitable if the Monitor role is moved to another server.
- Restore the whole MSDB database to the new Monitor server. This
alternative is suitable if the server needs to be replaced or if MSDB
contains other crucial information that needs to be moved, too (DTS
packages, jobs, operators, alerts, etc.) Be aware about some complications
that may turn up in case the machine name is different than the original
one. For more information see KB article 281642.KB.EN-US: BUG: Error 14274
When Updating a SQL Agent Job After Renaming Windows Server
A sample execution of the sp_add_log_shipping_primary and
sp_add_log_shipping_secondary stored procedures may look like:
DECLARE @.myprimary_id int
SELECT @.myprimary_id = 1
EXEC msdb.dbo.sp_add_log_shipping_primary
@.primary_server_name = 'primary_server_name', -- the primary server name
@.primary_database_name = 'primary_database_name', -- the primary db name
@.maintenance_plan_id = 'C354D59F-FACF-43FA-8D52-CEE8DC62FCC2', -- ID of the
maintenance plan that backs up the transaction log
@.backup_threshold = 45, -- the length of time, in minutes, after the last
backup before a threshold_alert error is raised
@.threshold_alert = 14420, -- the error raised when the backup threshold is
exceeded
@.threshold_alert_enabled = 1, -- whether an alert will be raised when
backup_threshold is exceeded
@.planned_outage_start_time = 0, -- the time, in HHMMSS format, a planned
outage starts.
-- During a planned outage, alerts will not be raised if the backup
threshold is exceeded
@.planned_outage_end_time = 0, -- the time, in HHMMSS format, a planned
outage ends
@.planned_outage_weekday_mask = 0, -- the day of the week that a planned
outage occurs
@.primary_id = @.myprimary_id -- OUTPUT parameter, unique ID for the new
primary server and database pair
--Execute the following select statement on the New monitor Server to get
--the Primary_ID that needs to be used in the sp_add_log_shipping secondary
statement:
select primary_id from msdb.dbo.log_shipping_primaries where
primary_server_name = '<primary server name>' and primary_database_name ='<primary database name>'
EXEC msdb.dbo.sp_add_log_shipping_secondary
@.primary_id = @.myprimary_id, -- the ID of the primary server
@.secondary_server_name = 'secondary_server_name', -- the name of the
secondary server
@.secondary_database_name = 'seconday_database_name', -- the secondary
db name
@.secondary_plan_id = '616679D6-CA8A-4098-A694-9502C390D2CA', -- the
ID of the log shipping plan on the secondary server found in
msdb..log_shipping_plans table as obtained in Step 2.
@.copy_enabled = 1, -- whether the copy for the database is enabled on the
secondary server
@.load_enabled = 1, -- whether the load for the database is enabled on the
secondary server
@.out_of_sync_threshold = 45, -- the length of time, in minutes, after the
last load before an error is raised
@.threshold_alert = '14421', -- whether an alert will be raised when an
out-of-sync threshold is exceeded
@.threshold_alert_enabled = 1, -- whether an alert will be raised when
backup_threshold is exceeded
@.planned_outage_start_time = 0, -- the time, in HHMMSS format, a planned
outage starts.
-- During a planned outage, alerts will not be raised if the backup
threshold is exceeded
@.planned_outage_end_time = 0, -- the time, in HHMMSS format, a
planned outage ends
@.planned_outage_weekday_mask = 0 -- the day of the week that a
planned outage occurs
GOThe values for the parameters can be completed from the values in the
step 2 above
5. Execute the following statement on the Primary and secondary servers to
get the current monitor login information. Note down the Logon Type
information.
select * from msdb.dbo.log_shipping_monitor
go
6. Execute the following command on both Primary and all secondary servers
to change the monitor information. Use the logon type that was found in the
step above
sp_define_log_shipping_monitor @.monitor_name ='<New monitor server name>',
@.logon_type =logon_type, -- this was written down in the previous
step
@.password = NULL, -- if the logon type is 1 ( NT authentication) then
the password is ignored
@.delete_existing=1
go
7. On the old log shipping Monitor, delete the log shipping information
from the msdb.dbo.log_shipping_primaries and the
msdb.dbo.log_shipping_secondaries tables. Executing only the
sp_delete_log_shipping_primary stored procedure is sufficient to delete the
related entries from the msdb.dbo.log_shipping_secondaries table if the
@.delete_secondaries parameter is set. A sample execution of the procedure
may look like:
USE master
GO
EXEC sp_delete_log_shipping_primary
@.primary_server_name = 'primary_server_name', -- the primary server
name
@.primary_database_name = 'primary_database_name', -- the primary db name
@.delete_secondaries = 1 -- the delete action is also applied to
log_shipping_secondaries table. Bear in mind that the entries in the
Monitor's system tables are per server and also per database.
It is not recommended to do direct inserts into the
msdb.dbo.log_shipping_primaries and msdb.dbo.log_shipping_secondaries
tables. Direct updates of system tables is never recommended - use the
designated stored procedures instead. Note that the procedure is just the
same even if either the Primary or any of the Standby servers has been
assigned the Monitor role. Just treat it as a separate server and follow
the steps above.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.