September 24, 2014 at 9:14 pm
Hey Guru's,
Lets say you have DB's log shipping from A to B in stand-by-mode so the DB are readable inbetween the apply log jobs. Is it possible to then mirror those databases to a third server easily? It does not matter that the mirror cant be reported off as snapshots of the mirrors will be used for data extraction. Is this possible and reletively easy to set up?
OR
Would it be considered easier to Mirror the DBs from A?
Thank you for reading and any and all opinions and views are welcome.
Regards,
D.
September 25, 2014 at 12:50 am
You can configure the mirror for databases on Server A
But you cannot do the mirror for databases on Server B.
September 25, 2014 at 1:24 am
Hi Grasshopper,
Thanks for getting back, what I am wondering in that case is, in regards to backing up and restoring the transaction logs to the mirror (which are already getting backed up by the log shipping), how do I capture the right logs to match the production/principle database in order to apply them to the mirror and bring them in synch? Would I have to stop all traffic to the source databases in order to set up the mirroring?
Thanks again for your reply.
Regards,
D.
September 25, 2014 at 2:24 am
How often are the log backups running in the log shipping policy?
Do you have the option of pausing the log shipping, grabbing the last log backup and applying that (with the others since the last full backup) to the server where you mirrored database will be?
I have exactly the same setup as you. We have a primary server, a mirrored server, and a secondary server that has the primary server database's log shipped to. However we setup the mirroring sessions before the log shipping.
September 25, 2014 at 2:54 am
Duran (9/24/2014)
Hey Guru's,Lets say you have DB's log shipping from A to B in stand-by-mode so the DB are readable inbetween the apply log jobs. Is it possible to then mirror those databases to a third server easily? It does not matter that the mirror cant be reported off as snapshots of the mirrors will be used for data extraction. Is this possible and reletively easy to set up?
OR
Would it be considered easier to Mirror the DBs from A?
Thank you for reading and any and all opinions and views are welcome.
Regards,
D.
Mirroring would be applied to the Primary database configuration, this will give you DatabaseA mirrored from ServerA to ServerB
Once the mirror session is running you then logship from Primary DatabaseA on ServerA to Secondary DatabaseC which resides on ServerC
Once completed, you failover the mirror session from ServerA to ServerB and using T-SQL setup the new mirror session Principal as a new Log shipping Primary.
It's all detailed here
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 25, 2014 at 5:29 am
Hello Ten Centuries, thanks for responding.
Backups are the default 15 minutes.
Do you have the option of pausing the log shipping, grabbing the last log backup and applying that (with the others since the last full backup) to the server where you mirrored database will be?
I'm not sure, I guess I'd have a chance if this was all done on the weekend. Would I do this on Server A? The principle side, I guess I'd have to.
I have exactly the same setup as you. We have a primary server, a mirrored server, and a secondary server that has the primary server database's log shipped to. However we setup the mirroring sessions before the log shipping. As some of the databases are massive, I'd have to stop logging shipping for quite some time. Stop log shipping, (wait till all transactions have been committed?)copy the databases, restore with no recovery, if I cant wait for all the logs to have been committed I'll have to work out what portion of the logs were the last to be committed? This is the part I'm struggling with, getting the logs to restore to the mirror.
Yes, that's the kicker, I have seen a few articles, but its the Mirroring that was there first!
Perry, thanks for getting back.
I think what you are saying is start mirroring then apply log shipping, I had read the article before, but I am already log shipping, that's what make the whole thing so awkward.
Regards,
D.
September 25, 2014 at 6:09 am
Could you restore the full backup on your secondary server and leave log shipping running, then take a differential backup of your database apply it to the secondary, and then apply any logs afterwards?
September 25, 2014 at 8:51 am
Duran (9/25/2014)
I think what you are saying is start mirroring then apply log shipping, I had read the article before, but I am already log shipping, that's what make the whole thing so awkward.Regards,
D.
Never tried but you should still be able to set it up. I would pause the LS jobs while you do this though
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 25, 2014 at 5:46 pm
Hey, I really appreciate the time everyone has taken to answer this post.
Could you restore the full backup on your secondary server and leave log shipping running, then take a differential backup of your database apply it to the secondary, and then apply any logs afterwards?
Hello Ten,
Take a backup of the secondary (logged shipped?) server, I had actually considered that, I wondered what would happen to the apply jobs in the meantime, I guess they would just fail until the database became available again after the backup. I'm not sure that if I did that, whether I'd be able to synch up the mirror with the (Primary) principle if, while I was doing that, the would-be principle will have been written to in the meantime. But I see where your going with it.
Perry,
Yes, I think log shipping is just going to have to be stopped, perhaps I need to stop the log backup jobs first, take a backup of the databases, restore them to the other server with norecovery, then possibly apply the last transaction log backup and bring then to synch.
What if the after stopping the log shipping and backing up the DBs and restoring the DBs are at that point already in synch? I may not have to worry about a transaction log restore. Is there a way, or a SQL native tool I can use to see if the databases are the same at that point?
Regards,
D.
September 25, 2014 at 6:12 pm
Hey,
I just found this new SSDT add on, not sure if its a comparison tool or not though, and it could be an issue with the other database being in recovery. If it does not help me, it may be useful to you guys at some point.
Regards,
D
September 26, 2014 at 4:00 am
Duran (9/25/2014)
Hey, I really appreciate the time everyone has taken to answer this post.Could you restore the full backup on your secondary server and leave log shipping running, then take a differential backup of your database apply it to the secondary, and then apply any logs afterwards?
Hello Ten,
Take a backup of the secondary (logged shipped?) server, I had actually considered that, I wondered what would happen to the apply jobs in the meantime, I guess they would just fail until the database became available again after the backup. I'm not sure that if I did that, whether I'd be able to synch up the mirror with the (Primary) principle if, while I was doing that, the would-be principle will have been written to in the meantime. But I see where your going with it.
Perry,
Yes, I think log shipping is just going to have to be stopped, perhaps I need to stop the log backup jobs first, take a backup of the databases, restore them to the other server with norecovery, then possibly apply the last transaction log backup and bring then to synch.
What if the after stopping the log shipping and backing up the DBs and restoring the DBs are at that point already in synch? I may not have to worry about a transaction log restore. Is there a way, or a SQL native tool I can use to see if the databases are the same at that point?
Regards,
D.
What I meant was take a backup of your database that's being log shipped, restore it onto the server that's going to host your mirrored database with norecovery.
This means that you can leave log shipping running.
Then take a differential backup of your database, restore it over the database that's in recovery (again leaving log shipping running).
Then you would only have to apply the log backups taken after the differential to get the mirroring setup. It may be a few but you should be able to script it out.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply