July 28, 2010 at 2:06 pm
I have a production server (primary), a mirrored server (secondary/asynch), and an offsite server which is log shipped (tertiary)
In testing out various failover conditions I have found that if the primary server crashes, I can successfully switch from primary to secondary server. When the primary server is back online I can successfully, switch the mirror back to the primary.
However, the tertiary server, the one maintained via log shipping, becomes out of synch. Does anyone know why this would be? I was assuming that when the primary server came back on line, the log shipping would pick up from where it left off, but that does not seem to be the case. Is there a certain order of steps that I need to take to ensure that the log file is created properly after primary server comes back online?
As a separate issue, I would also like to know how I can bring the primary server back online when both the primary and secondary servers are unavailable and I have changed the role on the log shipping server to be the principle. Is the only option to do a full restore on the primary? Is there a way to synch the log shipping database back to primary?
Thanks.
July 28, 2010 at 2:42 pm
Did you set up both nodes of the mirroring to log ship? You have to fail to the mirror and set it up as a primary, THEN fail back to the primary in order for the mirror to pick up log shipping when the primary fails. Sounds like the mirror picked up and moved ahead on the LSN numbers while not shipping its logs, which would then be too far ahead to continue when failing back to the primary.
Look up "Database Mirroring and Log Shipping" on BOL for details.
If you DID set up the mirror for log shipping, then the behavior you are seeing is not expected and we need to dig deeper.
July 28, 2010 at 3:42 pm
Does log shipping have to be set up from the mirror server? I guess that makes sense if it does. So when would that have to happen? At point that I set the mirror as the primary? Then, would I need to remove log shipping from the mirror when production server comes back online?
I currently have log shipping done from production server only.
July 28, 2010 at 8:05 pm
Books Online has a good writeup on this. Basically you have to set up log shipping on both mirror sites BEFORE you have a failover in production. When you set up log shipping in the first place, you set it up after mirroring. Then you start on the primary server doing a log shipping. Fail it over to the mirror and set up another log shipping job on the mirror (this has to be done by script and not GUI -- again, check BOL). Then you fail back to the primary and let it go live for production. If you don't, once you fail over the LSN and the log backups on the mirror server go beyond what is on the log shipping. And so when the primary is restored log shipping is broken.
July 28, 2010 at 8:58 pm
Jeff is giving you the right idea. Basically when you fail to the mirror, then it starts receiving transactions, and I assume, making log backups.
Those log backups need to go to the tertiary server. If not, then when you fail back to the primary, the logs it generates are out of sync with the last log restored on the log shipping server.
You might need to enable the log shipping jobs on the mirror server manually when you have failed over. Have you set up log backups on the mirror server?
July 29, 2010 at 10:03 am
Thanks for the help. I have not set up log shipping on the mirrored site. I will review BOL more to get details on making this work. I assumed I could not set up log shipping on mirrored site since GUI wouldnt allow it.
Thanks again!
July 29, 2010 at 5:19 pm
I believe this white paper from the SQL CAT team is exactly what you are looking for. It discusses setting up log shipping in conjunction with mirroring. Pages 4-6 should help.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply