May 1, 2018 at 2:37 pm
I am experimenting with log shipping in sql server 2014, and am log shipping to 2 secondaries. I synchronized all three instances by running the backup, copy and restore jobs so the log shipping report showed the last backup file had been copied and restored to the two secondaries, then disabled all the jobs. As an experiment, I removed one of the secondaries from the log shipping configuration. Then I readded it to the log shipping configuration with the No, the secondary database has been Initialized option. It allowed me to do it, but it didn't work at all. Why didn't it work? the secondary database was an exact copy of the primary (I checked through the log shipping report). Any help you could give me would be greatly appreciated. Am not sure why it didn't work okay.
May 1, 2018 at 4:34 pm
ceciliarenebaker - Tuesday, May 1, 2018 2:37 PMI am experimenting with log shipping in sql server 2014, and am log shipping to 2 secondaries. I synchronized all three instances by running the backup, copy and restore jobs so the log shipping report showed the last backup file had been copied and restored to the two secondaries, then disabled all the jobs. As an experiment, I removed one of the secondaries from the log shipping configuration. Then I readded it to the log shipping configuration with the No, the secondary database has been Initialized option. It allowed me to do it, but it didn't work at all. Why didn't it work? the secondary database was an exact copy of the primary (I checked through the log shipping report). Any help you could give me would be greatly appreciated. Am not sure why it didn't work okay.
What didn't work? What were the error messages?
Sue
May 2, 2018 at 8:33 am
Thanks for responding. I tried again, and got the same result. Basically, after I removed the secondary from the log shipping configuration and tried to add it again, it prompted me that the instance and database was already set up for log shipping and did I want to overwrite it so evidently removing the secondary did not remove log shipping in the msdb tables. Last time I chose to overwrite and it failed, so this time I removed the secondary using the stored procedures and then checked all the table to be sure there was no records for the instance on the primary and secondary and monitor. Then I tried to run the copy job and got the below error message:
Message
2018-05-02 09:19:59.37 *** Error: Could not retrieve copy settings for secondary ID '9b2d4ee9-4acd-4232-a05c-76ce18758559'.(Microsoft.SqlServer.Management.LogShipping) ***
2018-05-02 09:19:59.37 *** Error: The specified agent_id 9B2D4EE9-4ACD-4232-A05C-76CE18758559 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider)
***
And when I try to go into the log shipping configuration for the instance (in the secondaries area), in the Configure Log Shipping screen, I get this message:
Management Studio could not retrieve information about secondary database [LNAR-PC0FXMAQ].[AdventureWorks2014]
Any help you could give me would be greatly appreciated. I really don't understand this.
May 2, 2018 at 11:06 am
ceciliarenebaker - Wednesday, May 2, 2018 8:33 AMThanks for responding. I tried again, and got the same result. Basically, after I removed the secondary from the log shipping configuration and tried to add it again, it prompted me that the instance and database was already set up for log shipping and did I want to overwrite it so evidently removing the secondary did not remove log shipping in the msdb tables. Last time I chose to overwrite and it failed, so this time I removed the secondary using the stored procedures and then checked all the table to be sure there was no records for the instance on the primary and secondary and monitor. Then I tried to run the copy job and got the below error message:
Message
2018-05-02 09:19:59.37 *** Error: Could not retrieve copy settings for secondary ID '9b2d4ee9-4acd-4232-a05c-76ce18758559'.(Microsoft.SqlServer.Management.LogShipping) ***
2018-05-02 09:19:59.37 *** Error: The specified agent_id 9B2D4EE9-4ACD-4232-A05C-76CE18758559 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider)
***And when I try to go into the log shipping configuration for the instance (in the secondaries area), in the Configure Log Shipping screen, I get this message:
Management Studio could not retrieve information about secondary database [LNAR-PC0FXMAQ].[AdventureWorks2014]
Any help you could give me would be greatly appreciated. I really don't understand this.
Looks like some of the log shipping pieces were "orphaned" when you tried to removed the secondary,
On the secondary that seems to have the issues, try executing the following to clean up whatever pieces are left: DELETE FROM dbo.log_shipping_monitor_secondary
WHERE secondary_database = 'LogShippedDBName'
go
DELETE FROM dbo.log_shipping_secondary
WHERE primary_database = 'LogShippedDBName'
go
DELETE FROM dbo.log_shipping_secondary_databases
WHERE secondary_database = 'LogShippedDBName'
Sue
May 2, 2018 at 11:23 am
So, I started looking at the msdb log_shipping_tables on the secondary and there is no record for the database and instance in the log_shipping_secondary_databases table. And when I went to the monitor instance and looked in log_shipping_monitor_secondary there was no record for the instance there. The only place I found the instance was when I went to the primary and looked in log_shipping_primary_secondaries. Is this normal? I mean do I have to completely remove log shipping to use the No, the database is already initialzed option?
Well, to test that theory I completely redid the log shipping configuration and used the No, Database has already been initialized option and it worked fine so I guess for some reason, it won't allow me to remove and then add secondaries when I use that option. So, I'm guess it's probably a good idea to just redo everything instead of trying to make changes to the configuration
May 2, 2018 at 11:53 am
Thank you for responding. You are right the entries in the tables were incorrect. They weren't exactly orphans though since I removed everything by hand using the log shipping stored procedures before I readded the secondary. I think it's a flaw in the log shipping program. The reason I am removing/adding secondaries is because I want to try to switch between log shipping and mirroring, a technique I have read about where when a failover will be short-term, he recommends switching between log shipping and database mirroring because this technique requires much less work to set up than reversing log shipping. But it looks like I will have to completely redo log shipping (minus the backup and restore to initialize) when I switch back to log shipping. I don't know if it will save me that much time though, because reversing log shipping doesn't seem that much longer than dropping log shipping, setting up the mirroring session, failing over, and then failing back and redoing log shipping without the initialization. Also, if you're log shipping to multiple secondaries, switching to mirroring after log shipping is dropped won't work since only the mirrored instances will be kept in sync. I'm just trying different things to see what is possible.
May 2, 2018 at 11:55 am
ceciliarenebaker - Wednesday, May 2, 2018 11:23 AMSo, I started looking at the msdb log_shipping_tables on the secondary and there is no record for the database and instance in the log_shipping_secondary_databases table. And when I went to the monitor instance and looked in log_shipping_monitor_secondary there was no record for the instance there. The only place I found the instance was when I went to the primary and looked in log_shipping_primary_secondaries. Is this normal? I mean do I have to completely remove log shipping to use the No, the database is already initialzed option?Well, to test that theory I completely redid the log shipping configuration and used the No, Database has already been initialized option and it worked fine so I guess for some reason, it won't allow me to remove and then add secondaries when I use that option. So, I'm guess it's probably a good idea to just redo everything instead of trying to make changes to the configuration
No you don't need to completely remove log shipping. For the secondary you tried to remove it wouldn't be normal to see it in the primary_secondaries table. On the primary, you would execute sp_delete_log_shipping_primary_secondary - pass in the secondary server and the Name of the log shipped database on that secondary.
Not sure how you tried to delete it but whatever the process was, it didn't work correctly.
Sue
May 2, 2018 at 3:12 pm
I"m not very good an explaining things. I removed the log shipping secondary from the configuration, and because that doesn't remove log shipping from the msdb tables, I used the log shipping stored procedures to remove log shipping for that secondary, and then checked all the msdb tables on the primary, the secondary, and the monitor to make sure that every mention of that instance was gone. Then I readded that instance to the log shipping configuration, and I got the same errors I got the first time that I mentioned above. So, I queried the log shipping tables on the primary, the secondary and the monitor and I got the results that I posted above (apparently it didn't create the records it needed to create to get a valid logging shipping configuration for the secondary when I readded it in the log shipping configuration screen). Does that make sense? Thank you for taking the time to help me.
May 2, 2018 at 3:20 pm
I don't why it didn't add a record in the log_shipping_monitor_secondary on the monitor and log_shipping_secondary_databases on the secondary. So you think I am doing something wrong? I mean have you ever removed a secondary and then readded it with the database already initialized option selected in 2014?
May 2, 2018 at 4:24 pm
ceciliarenebaker - Wednesday, May 2, 2018 3:12 PMI"m not very good an explaining things. I removed the log shipping secondary from the configuration, and because that doesn't remove log shipping from the msdb tables, I used the log shipping stored procedures to remove log shipping for that secondary, and then checked all the msdb tables on the primary, the secondary, and the monitor to make sure that every mention of that instance was gone. Then I readded that instance to the log shipping configuration, and I got the same errors I got the first time that I mentioned above. So, I queried the log shipping tables on the primary, the secondary and the monitor and I got the results that I posted above (apparently it didn't create the records it needed to create to get a valid logging shipping configuration for the secondary when I readded it in the log shipping configuration screen). Does that make sense? Thank you for taking the time to help me.
I'm pretty sure I was/am following the course of things. Not everything was deleted though as you said when you went to add it back you were prompted to overwrite it. So it wasn't all out of the tables. I am referring to this:
I removed the secondary from the log shipping configuration and tried to add it again, it prompted me that the instance and database was already set up for log shipping and did I want to overwrite it so evidently removing the secondary did not remove log shipping in the msdb tables.
The error on the jobs of this:
Could not retrieve copy settings for secondary ID '9b2d4ee9-4acd-4232-a05c-76ce18758559'.(Microsoft.SqlServer.Management.LogShipping) ***
2018-05-02 09:19:59.37 *** Error: The specified agent_id 9B2D4EE9-4ACD-4232-A05C-76CE18758559 or agent_type 1 do not form a valid pair for log shipping monitoring processing
is from the server parameter in the job pointing to the secondary you tried to remove.
When you remove the secondary, it is removed from msdb tables. If you look at the code for sp_delete_log_shipping_primary_secondary which you execute on the primary, it does delete from msdb. However, you said that:
The only place I found the instance was when I went to the primary and looked in log_shipping_primary_secondaries.
So that stored procedure wasn't run on the primary - it deletes from that table. Most likely it was run on the secondary.
The process is -
Run sp_delete_log_shipping_primary_secondary on the primary
Run sp_delete_log_shipping_secondary_database on the secondary
On the secondary check for LS copy and restore jobs - disable or delete as needed.
The process usually gets mixed up when mixing up the primaries and secondaries when executing the stored procedures.
Sue
May 3, 2018 at 8:14 am
Thanks.
May 3, 2018 at 8:26 am
I really want to thank you for helping me. Teaching yourself is difficult. I didn't have any trouble with switching to different scenarios with mirroring, but setting up and breaking down log shipping configurations has proved a challenge. It's quite stinging that people say log shipping is easy to understand, set up and manage 🙂 I will keep at it, and try again with the things you have told me.
May 3, 2018 at 9:00 am
ceciliarenebaker - Thursday, May 3, 2018 8:26 AMI really want to thank you for helping me. Teaching yourself is difficult. I didn't have any trouble with switching to different scenarios with mirroring, but setting up and breaking down log shipping configurations has proved a challenge. It's quite stinging that people say log shipping is easy to understand, set up and manage 🙂 I will keep at it, and try again with the things you have told me.
Thanks a lot for coming back and saying that - I really appreciate it.
And no doubt it is really a challenge when teaching yourself. But with your attitude and wanting to keep at it, it will fall into place. It takes time so try not to beat yourself up about it all. Everyone has been there before and had those days of feeling lost or wondering how everyone else seems to "get it" when you feel like you don't. Feeling like something is wrong with the product and being convinced we did it all the right way and it still doesn't work. We've all stared at code where we are positive it is correct, keep getting an error and later see the missing comma, bracket, apostrophe, etc. It's all just part of the process.
You are going about it the right way, testing, playing with it, seeing what happens when you do different things, trying to figure out how it actually works. So you aren't blindly just implementing something without know what is really going on. You are ahead of the game,more than you realize!
Sue
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply