June 9, 2009 at 1:48 pm
You could use the high-performance (asynchronous) mode. This would ensure that all transactions are committed locally.
You still have the possibility of some lost transaction with your current t-log process evry minute. Due to a hardware failure (loss of the t-log) you could loss up to 1 minute of work.
Just throwing out other ideas here.
June 9, 2009 at 2:05 pm
usman.tanveer (6/9/2009)
The problem with mirroring would be, in HA mode it will not commit the transaction on primary untill commited on secondary, which could be a performance hit as the secondary server is at a remote location. Also if it failed to commit on secondary it will roll back the transaction on primary BUT we cannot aford transaction failure AND transaction delay on primary due to any HA architecture.
If your hardware is good and the network between the two is fast (dedicated) there shouldn't be a problem. Rather than mirroring to the remote secondary, get a second local server for the mirror and leave the remote as a log shipping destination. At least with mirroring you can have 0 data loss if the primary fails.
Test it out in a lab, see how well it works under your production load.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2009 at 2:12 pm
I don't think a 1 minute Tlog interval is too much. I've run that before and it works OK. HOWEVER, it doesn't mean you won't lose data. The important thing is to get the files off the server ASAP, which is a copy somewhere immedaitely. You can log restore (in log shipping) after 5-10 minutes (and I'd recommend a delay), and also send the files to remote locations.
However, the same log transactions go in log shipping or mirroring, with arguably more overhead in log shipping with the file copies. I'd think non-synchronous would be the way to move for DR, but keep local log backups and your full/diff strategy on another local machine as well.
June 9, 2009 at 2:23 pm
Tanveer,
Yes. Performance and IO is the big concern if you have very active db. I think Gail already asked u the question on how much data you could afford to lose if disaster hit. That is usually the question we ask for our client if we want to setup tlog backup interval. 5-10m interval should be sufficient in your case. you might also consider to reduce the frequency of diff backup. any full or diff backup will block tlog backup.
June 9, 2009 at 2:49 pm
djjwu (6/9/2009)
Any full or diff backup will block tlog backup.
Not in SQL 2005 or above they do not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2009 at 2:49 pm
djjwu (6/9/2009)
Tanveer,Yes. Performance and IO is the big concern if you have very active db. I think Gail already asked u the question on how much data you could afford to lose if disaster hit. That is usually the question we ask for our client if we want to setup tlog backup interval. 5-10m interval should be sufficient in your case. you might also consider to reduce the frequency of diff backup. any full or diff backup will block tlog backup.
Full and Differential backups no longer block t-log backups starting with SQL Server 2005.
June 9, 2009 at 4:53 pm
usman.tanveer (6/8/2009)
Hi,"
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 149014000003456800006, which is too recent to apply to the database. An earlier log backup that includes LSN 148750000003326500001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
"
So, did you get the database restored?
Greg
June 9, 2009 at 5:10 pm
Sorry Greg i should have provided the update on the actuall post too:-).
But yes problem fixed - by mistake i was using restore LOG instead of restore DATABASE for the differential backup, and as ALWAYS Gila was right on top of it by pointing out the exact thing.
Thanks everyone who responded.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply