0

FCI technology uses windows clustering to start-up the sql server service on the secondary node and provides the secondary node with the data and log disks.

Assuming the OS on node 1 crashed, and the FCI failover started-up the node 2, then what is the guarantee of data consistency and integrity? For example - it could be that the OS crashed while working on the sql data and log files thereby causing corruption.

3 Answers 3

2

To answer this question, let's look at what happens when a transaction is committed to the database.

When a DML statement is executed against SQL Server, a few events must happen.

  1. The transaction is written to the transaction log on disk.
  2. The transaction is next written to the corresponding data page in memory and the data page is then marked as dirty (I.E. it no longer matches what is on disk).
  3. At the next CHECKPOINT or LAZY WRITE, dirty pages will be written back to disk. Due to the frequency of when these run, it's actually possible for a single data page to receive several changes before the entire page is written back to disk.

Once the transaction is in the transaction log, it's essentially committed. This is the key point in the process. If the SQL Server were to crash prior to the change making it to the transaction log, SQL Server would have never acknowledged to the client that the transaction was successful.

If the SQL Server were to crash after the transaction log is updated, and prior to the page being updated in memory or on disk, the transaction could still be recovered from the transaction log. Once the SQL Server service starts on the new primary node, the database would go through recovery, and roll the database forward by applying the transaction from the transaction log that were not yet written to the data files.

6
  • I understand your point is there will be no transactional data loss. Where as my question is purely asking about the database data and log file integrity assuming the sql server was actively working on the data and log files when the crash happened. I believe there is scope for corruption in this situation?
    – variable
    May 13 at 12:10
  • It's impossible to say there could not be a scenario where there is data loss or corruption, though SQL Server does a pretty good job of preventing it. As for the data/log files being written to disk, SQL Server would pass those commands to the OS, which would then pass them to the shared storage system. Once the storage new of the data changes to write to disk, it wouldn't matter of the SQL Server or the OS went offline while the storage system was writing the data to disk. May 13 at 12:22
  • Think of it like a mail service. If I go put an envelope in my mailbox, and then turn around and something happens to me, the post office will still come check my mail and take it to the next destination. My part of the process has ended. May 13 at 12:27
  • So you mean the windows cluster will gracefully shutdown the sql server service's access to the data/log files at the time of crash? May not be always.
    – variable
    May 13 at 12:50
  • No, I'm saying Windows will provide the storage system enough information to write good data to disk by passing a full request. The storage system will either get a request it can do something with or it won't. You're looking for where control shirts from the OS to the storage system. If there OS crashed, there would be no SQL service still running, so a graceful shutdown would not be possible. May 13 at 12:56
2

It depends, since there are multiple points where a crash can happen.

  • SQL Server the process might crash
  • Windows OS might crash
  • VM host might crash
  • SAN/disk controller might crash

Now, if the SQL Server crashes, Brendan McCaffrey's answer outlines the process in detail.

What if it was Windows itself? Windows' NTFS is a journaled filesystem, so it should write whatever data SQL Server tells it to in consistent state, right? No, it does not guarantee that user data - which database files and transaction logs are - are intact. It guarantees that NTFS internal data structures are robust. So, even if NTFS tells SQL Server that a transaction is written, it might not be if crash happens meanwhile.

What if the VM host crashes? This is more tricky. From Windows' point of view, it's a bit like someone pulls the power cord. The big "but" here is that VM systems hide the IO, so that Windows thinks it has made a successful write operation, but it's still cached on the VM host. And now the host crashes and the I-thought-it-was-written thing just disappears. What's the actual state on (virtual) disk? No one can tell. This was not too uncommon a failure, say, 10 years ago when virtualization wasn't that mature a technology.

What if the disk controller crashes? All bets are off. There's no way to tell what the controller actually writes on the disk, if anything. There are no guarantees it makes any sense. I've seen a few of these cases on HP EVAs, but those were rare occurrences.

3
  • Yes that's what I was thinking to make a choice between FCI and synchronous AG with auto failover - the later won't have this problem. Will it?
    – variable
    May 13 at 12:52
  • 1
    If both nodes of the AG use the same SAN, and the controller write-caches, you might still get data corruption on SAN crash. Can't help but wonder if the business requirements really warrant worrying about this kind of issues, but YMMV.
    – vonPryz
    May 13 at 13:13
  • I'm just learning here really.
    – variable
    May 13 at 13:16
1

For example - it could be that the OS crashed while working on the sql data and log files thereby causing corruption.

You are talking about SQL FCI and OS has hardly role in data consistency here as the data and log files reside on shared drive. So basically if OS crashes and failover happens all infight transactions would be rolled back, committed transactions would be rolled forward and you would see that when DB comes online on other node.

1
  • Technically yes but given that os was working on the files has scope for corruption. No?
    – variable
    May 13 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.