March 29, 2011 at 9:56 am
Hello,
I’m trying to imaging an extreme case when SQL server is in the middle of a transaction and the power is off or some sort of disaster happens, what will happen to the database file and log file? Will I still be able to access db and log files, to be exact to attach them to a different SQL server?
Our company is setting up new infrastructure with SQL 2008 and we are evaluating whether or not a clustered SQL engine would help in this kind of situation.
Your input is greatly appreciated.
awan
March 29, 2011 at 9:59 am
It really depends upon the type of behavior and your backup strategy.
If it is simply a power failure, the DB will rollback any uncommitted/unfinished transactions and you should be good to go.
If it is a hard drive failure, you will need to do a restore of your DB and all subsequent transaction logs to get it as close to time of failure as possible.
March 29, 2011 at 10:16 am
To stick with our case
Set-up I: two SQL engines set up as cluster and all accessing DB files on a separate SAN drive. One SQL engines dies the other kick in automatically.
Set-up II: only one SQL engine set up to access DB files on SAN drive. If this SQL engine dies, can I mount the db files to a different SQL engine without problem?
To compare these two set-ups, any special gains to go with I, other than safe ten minutes mounting time comparing to II?
Thanks!
March 29, 2011 at 10:55 am
awan-413156 (3/29/2011)
To stick with our caseSet-up I: two SQL engines set up as cluster and all accessing DB files on a separate SAN drive. One SQL engines dies the other kick in automatically.
Set-up II: only one SQL engine set up to access DB files on SAN drive. If this SQL engine dies, can I mount the db files to a different SQL engine without problem?
To compare these two set-ups, any special gains to go with I, other than safe ten minutes mounting time comparing to II?
Thanks!
There is no hard and fast answer. If the server in set-up II goes away and your files are intact, yes, you might be able to mount them to another server, maybe. It just depends on so much that it's really impossible to list it all. I would absolutely NOT count on that as my recovery mechanism. It's to prone to error and mishap. Instead, I would plan on backups and log backups as my recovery mechanism. It's much more standard, well known, well established, and easy to do.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 29, 2011 at 12:33 pm
Got it. Thank you!
March 29, 2011 at 12:48 pm
Typically yes, providing you have and use BOTH files, providing the IO subsystem didn't glitch in the failure (battery-backed write caches are not optional)
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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply