November 15, 2012 at 7:43 am
The platform is 2008 R2 though I'm not sure that really matters for this particular question. I have two instances I setup on my machine to perform some test and just to do it I disabled the log backups and did a restore with recovery on the secondary which brought the database in to "normal" mode. All that was just a test to makes user everything shipped as expected and I could failover to the secondary as expected and all went just fine.
But I don't really want to failover, just test so that means I need to put the secondary back in to stand by/read only. I've done some searching and from what I've read it seems I can't do this. I can switch roles and make the secondary the primary and reverse the way shipping was setup but if I want to put things back the way they were I actually have to completely reinitialize the shipping configuration and part of that means restoring the secondary database, setup the log backup, copy and restore process.
I could see this as a requirement under certain circumstances but let's assume I didn't add any new data to the secondary, changed no table structure or created new objects. I just want to go back to the prior scheme. Everything I can find tells me I can't do that. What if the secondary target DB was several terabytes and the secondary server was on a WAN? That presents a big problem.
So, is it a fact that I can't just put the secondary back in to stand by/read only, enable the related Agent jobs and move on? I would actually need to switch roles and then switch roles again to get back to where I was?
Cheers
November 15, 2012 at 7:50 am
You can restore the logs in a standby/readonly method when creating the logshipping plan, just ensure you enable the disconnect users option to kick anyone out when it tries to do the next restore of the logs.
November 15, 2012 at 8:09 am
I'm aware of all of that as I found out the hard way when I left a session open to the secondary and didn't check the disconnect option when configuring shipping. My questions surrounds taking the secondary out of read only/stand by to test failover. I now want to reverse that and carry on with log shipping as it was but it seems like I can't put the database back in that state without doing another restore of the secondary. That's fine with testing on my laptop but what if the DB backup was huge and had to be sent over a network or even mailed on overnight on a secure drive? That is unattractive to say the least.
Cheers
November 15, 2012 at 8:18 am
How are you wanting to test failover? Is it just point your client applications at the server and see if it can connect and read data or will you want to modify the data as well?
If you just want to ensure that you can connect and read, assuming your using the standby method then you could just stop the restore and copy jobs on the secondary to prevent anything from being shipped and restored. Then as long as your logins on the secondary server have been created with the same SID's, permissions etc will all match allowing them to connect and read, then you can simply just start the jobs again and log shipping will resume once you have finished testing.
I do feel your pain in shipping large backups between servers to do initialisations for log shipping, working in a secure environment in the past for a past client, they only had a 10Mb link between their sites, if anything was to happen to log shipping it was a copy the backup file to a USB drive and then drive to the DR site to reinitialise log shipping due to the amount of time it took to ship the BAK file down over the wire. It is a nightmare I do agree with that.
November 15, 2012 at 8:46 am
so I guess the answer is to never take the secondary out of stand by/read only unless you are serious. Once it happens and you want to go back you must either completely reinitialize the process including the restoration of the database to the secondary. Additionally, if one intended to try to put things back by reversing roles (secondary to primary) you still can't escape the fact that the db must be restored?
Cheers
November 15, 2012 at 8:52 am
If you have a real requirement to fail back to the primary server as quickly as possible, then perhaps you should use mirroring instead of log shipping. And I understand that, especially if there are web/app servers involved. There are tradeoffs with mirroring as well, so you have to figure out which gives you what you are looking for, with the least amount of pain when it is needed.
November 15, 2012 at 8:52 am
jfogel (11/15/2012)
so I guess the answer is to never take the secondary out of stand by/read only unless you are serious. Once it happens and you want to go back you must either completely reinitialize the process including the restoration of the database to the secondary. Additionally, if one intended to try to put things back by reversing roles (secondary to primary) you still can't escape the fact that the db must be restored?
The moment your recover the secondary database in a Log Shipping setup, a restore is required to re-start the session again.
November 15, 2012 at 8:54 am
on the primary manually take a last log backup with norecovery, apply that to the secondary with recovery to bring it online. Then when you want to switch back backup log on the new primary with norecovery, and apply that to the original primary with recovery. Both databases will be in synch LSN wise that way and you will be able to restart your logshipping jobs.
I wouldn't do that in live except as part of a planned DR test where you wanted to keep data updated on the failover, but play with it on a test setup.
---------------------------------------------------------------------
November 15, 2012 at 12:01 pm
SQLSACT (11/15/2012)
The moment your recover the secondary database in a Log Shipping setup, a restore is required to re-start the session again.
this is incorrect, role switching in log shipping involves putting the primary into norecovery state and recovering the secondary using the tail log backup from the primary.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 15, 2012 at 12:14 pm
Perry Whittle (11/15/2012)
SQLSACT (11/15/2012)
The moment your recover the secondary database in a Log Shipping setup, a restore is required to re-start the session again.this is incorrect, role switching in log shipping involves putting the primary into norecovery state and recovering the secondary using the tail log backup from the primary.
Thanks
I wasn't aware of this
November 15, 2012 at 12:29 pm
This stuff throws a wrench in how I'm used to backing up my DBs and transaction log backup schedules too. Gotta get more practice with this stuff.
Cheers
November 15, 2012 at 1:06 pm
the best bit is, providing the differential base LSN is in sync between the primary and secondary you can use a differential backup to bridge LSN gaps when resuming log shipping
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 15, 2012 at 1:54 pm
jfogel (11/15/2012)
This stuff throws a wrench in how I'm used to backing up my DBs and transaction log backup schedules too. Gotta get more practice with this stuff.
These are the steps i took, follow these and you'll get an idea of what happens during the role reversals for Log Shipping
Create the Primary and Secondary databases and get them synchronised
Switch the Primary and Secondary databases and get them synchronised and add a new table
Now switch the roles back again
Take a tail log backup using the following
BACKUP LOG [LSTest] TO
DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL\Backup\lstest_tail.trn'
WITH NO_TRUNCATE , NORECOVERY
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 15, 2012 at 2:02 pm
Thank you. I will work with this tonight.
Cheers
November 15, 2012 at 2:28 pm
I would clarify Perrys tail log backup script, it depends why you are failing over. If you are failing over because the primary is damaged but the log file is still available you would use the no_truncate option, if it is a planned failover or role swap with the primary intact just the norecovery option is required.
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply