March 10, 2010 at 2:52 am
Hi All
Thanks for reading considering this is my first post here! There seem to be allot of Logshipping related queries here but few about moving into a DR scenario and none about this particular problem I can find but I apologise if this is a duplicate.
A bit of background - We use 2 servers a Primary and a backup with the SQLServer 2008 installed on Server 2003. We use the built in logshipping for SQLserver 2008 which works nicely.
Right now the wierd bit.
This is what I am doing to move to DR and the problem.
I stop the backup, copy and restore jobs and run the copy and restore jobs once more (waiting until the copy has finished before running restore of course). I then do a tail of the database (we actually have 36 DBs so I will need to script this!) with norecovery.
But then when i try to import this tail log it tells me it is too early to apply!
How can this log possibly be too early?
Thanks in advance
Chris
March 10, 2010 at 3:56 am
Just realised this is not in the correct sub-forum
March 10, 2010 at 5:54 am
For all databases or just some?
you must have missed out a log taken via logshipping. Check in the errorlog or logshipping tables that the latest log applied is the latest log backup taken.
if you have a delay set in the restore the load job may have run successfully but not applied a log.
---------------------------------------------------------------------
March 10, 2010 at 6:55 am
Hi and thanks for responding.
I am currently just testing it with one database but will need it to scale to all 36 databases once I have got it working enough to script it all up properly. I thought I must have missed a log too but I had run both the copy and restore jobs without errors. Also surely once the final built in log-shippings backup job has completed the next one should start with a later not earlier LSN number? I am just creating a new database and going to try it on that. The only errors I get are about the LSN being too early to apply to the database, the agent jobs all say succeeded and don't seem to be that much more helpful!
Do you think I am going about the whole process wrong and should be looking at a DR migration plan another way? I am not sure how well its going to scale to lots of databases! I would cluster but only have a 1mb up / down connection available between DR and Primary site and I am assuming mirroring is a no no!
Thanks
Chris
March 10, 2010 at 7:06 am
Your plan is fine although note you only strictly need the tail of the log if you intend to point logshipping back the other way on failover. In a TEST Dr you need to ascertain whether changes done whilst in DR will be kept and hence if you need to recover them. Quiescing the app and taking one last logship backup might be enough.
the error points to a log backup having been missed out. The restore job can report success but not have actually loaded any log files. Easiest way to be sure is to look in the errorlog of the secondary and see what tranlogs have been restored. My gut feeling is your last logship tranlog has not been loaded yet and you need to run your logship restore job once more before manually loading the tail log backup.
---------------------------------------------------------------------
March 10, 2010 at 7:41 am
Thats good to hear I am on track. For testing purposes (and the fact we have such a slow connection) we definitely need to be able to just 'reverse' the logshipping. In a fire or some other misfortune I am not too fussed what happens and would just bring up the secondary databases.
My only confusion is why would sqlserver say the transaction log is too early for the database and not too late? Have I mis-understood a concept?
Perhaps i will try with 2 new databases as I have been playing with these ones all day.
March 10, 2010 at 7:51 am
can you post the exact error?
Are you sure all logs have been applied at the secondary?
---------------------------------------------------------------------
March 11, 2010 at 3:16 am
No need! Your help solved the problem 🙂 I just deleted the database in the end and started again and have not encountered the same problem (other than when the copy job doesn't copy all of the logs.)
I haven't managed to get the timing correct on my script yet. Looks like for ease of use i will have to run the copy and restore jobs twice to make sure that the logs are all copied correctly otherwise my script just falls apart. I also can't get my linked servers to work as I want because I am not allowed to refer to them by name (only ip) across sites. Do you by chance know how to execute a script on a remote server using the ip? I can link it if I use an IP but then seem unable to refer to it.
Chris
March 11, 2010 at 5:26 am
good to hear you have made progress.
you may have a timing issue with the logshipping jobs. Adjust the schedules so backups run on the hour, copy 2 mins after and load 2 mins after that, or something similar. also check you haven't specified a time in 'delay restoring backups at least' box.
linked server query by I.P.? No not sure there I am afraid. MAybe better off doing the script in SSIS, though being a simple person I would just have a prepared script that reverse engineers the SQL and run that manually on the actual servers.
---------------------------------------------------------------------
March 11, 2010 at 7:29 am
Thanks, Ive been making Mountains out of Mole hills! We used to use our own logshipping scripts which because I wrote them I knew exactly how they worked! I changed because to doing it this way because I am a very simple person and I like pretty check boxes and menus! I have adjusted all our databases to have a delay between the jobs as you suggest and reduced the time between backups to 10 mins from 30 to reduce the potential size of logs.
Shame about the linked servers, I think I am going to just put a request in to have this so it resolves to a name. Looks like the best way. I will let you know my progress soon hopefully.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply