October 23, 2017 at 4:01 am
I've two SQL Servers both running SQL Server 2012 and currently set up transaction log shipping as a DR solution.
I've undertaken a DR test and switched roles between primary and secondary and back again without issue. However, when I look at the transaction log shipping report on the primary, it is showing the old copy and restore entries on there.
What I've tried to do so far is the following
use master
go
execute sp_help_log_shipping_secondary_database @secondary_database = 'DB_Name'
get the ID of the database and then run
use master
go
exec sp_refresh_log_shipping_monitor
@agent_id = 'Secondary_id,
@agent_type = '1' ,
@database = 'db_name',
@mode = 2
The report when refreshed still shows the old alert. It's not a massive issue but when you have 52 db's appearing, it's not greatto look at
Any help apprecaited
Thanks
October 23, 2017 at 4:42 am
So, after reading a few more articles I found that the way to do it was to remove any references to the old entries in the following tables
[MSDB].[dbo].[log_shipping_monitor_secondary]
[MSDB].[dbo].[log_shipping_monitor_primary]
There may be a safer and cleaner way of doing it but it's now removed all the old entries from my transaction log shipping report
October 23, 2017 at 7:45 am
kirk.hughes - Monday, October 23, 2017 4:42 AMSo, after reading a few more articles I found that the way to do it was to remove any references to the old entries in the following tables[MSDB].[dbo].[log_shipping_monitor_secondary]
[MSDB].[dbo].[log_shipping_monitor_primary]There may be a safer and cleaner way of doing it but it's now removed all the old entries from my transaction log shipping report
That is a bit odd. When you execute the stored procedures to switch the roles, those tables are cleaned up by sp_MSprocesslogshippingmonitorprimary and sp_MSprocesslogshippingmonitorsecondary when executing sp_change_log_shipping_primary_database and sp_change_log_shipping_secondary_database.
Did you execute both of those stored procedure on the correct servers without any errors?
The steps are listed in this documentation:
Change Roles Between Primary and Secondary Log Shipping Servers (SQL Server)
Sue
October 23, 2017 at 8:08 am
Hi Sue,
Thanks for your response.
Looking back over my scripts to do the failover from one to the other, I did run the two SP's "sp_change_log_shipping_primary_database" and "sp_change_log_shipping_secondary_database"
However, I can't guarantee 100% that they were executed on the correct primary and secondary at the time. The results of the failover was as expected and worked OK. I managed to fail over to the secondary and during the same process, make the old primary the new secondary so I didn't need to back up, restore and re set up logshipping between the two servers.
Looking back over my implementation plan, I know the following code was run
Server A
EXEC master.dbo.sp_change_log_shipping_secondary_database @database=N'DBNAME'
Server B
EXEC master.dbo.sp_change_log_shipping_primary_database @database=N'DBNAME'
I think some where along the role switch, the SP's were run in the wrong order.
Therefore, I think that's the reason why I was still seeing the old alerts
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply