Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
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.
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.
It depends, since there are multiple points where a crash can happen.
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.
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.
