Log Shipping Monitor - changing where it runs
-
Anyone know how to change the server that the log shipping monitor resides on?
We currently have logs shipping setup & running, but the person who set it up put the monitor on the wrong server, so we want to shift it.
It looks like it could possibly be done with a couple of stored procs on the intended monitor server & some editing of a couple of tables on the primary & secondary to point to the new monitor, but I haven't found anything definitive yet.
I guess if it can't be done then we will just have to setup log shipping again.
Thanks.
Scott DuncanMARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare -
Finally found something. Haven't tried it yet. It was on http://www.experts-exchange.com, but is now only in Google cache as the question has been removed.
You might want to test this before implementing
(and make backups of msdb)
1. Script the "Log Shipping Alert - Backup" and "Log Shipping Alert -
Restore" jobs from the current monitor server
2. 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
3. Execute the scripts created in Step 1 on the new monitor server (in MSDB)
4. Execute the following stored proc on the new monitor server -
sp_add_log_shipping_primary
@primary_server_name = '' ,
@primary_database_name = ''
,@maintenance_plan_id = ''
,@backup_threshold =
,@threshold_alert = '14420'
,@threshold_alert_enabled = 1
,@planned_outage_start_time = 0
,@planned_outage_end_time = 0
,@planned_outage_weekday_mask = 0
5. Execute the following select statement to get the Primary_ID -
select primary_id from msdb.dbo.log_shipping_primaries where
primary_server_name =
'' and primary_database_name = ''
6. Execute the following stored procedure on the new monitor server -
sp_add_log_shipping_secondary @primary_id =
, @secondary_server_name = ''
, @secondary_database_name = ''
, @secondary_plan_id = ''
, @copy_enabled = 1
, @load_enabled = 1
, @out_of_sync_threshold =
, @threshold_alert = '14421'
, @threshold_alert_enabled = 1
, @planned_outage_start_time = 0
, @planned_outage_end_time = 0
, @planned_outage_weekday_mask = 0
7. Execute the following statement on the Primary and secondary servers to
get the current monitor login information. Verify that you have 1 for logon
type before proceeding with step 8. If you do not have this field set to 1,
then for the time being we will change the authentication mode to NT and
then we can change this later on once the monitor is completely migrated -
select * from msdb.dbo.log_shipping_monitor
go
8. Execute the following command on both Primary and all secondary servers
to change the monitor information -
delete from msdb.dbo.log_shipping_monitor
go
sp_define_log_shipping_monitor '', 1, NULL, 1
go
9. This should complete the migration. Test that the monitor server icon
thing appears under Management tree (Enterprise Manager). When you click
this Log Shipping Monitor, there should be the new pair that you just
added. Once the jobs are working successfully the last copied/loaded file
field will be updated to reflect the changes.
Scott DuncanMARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare -
Did you try this thing?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply