2

I'm testing the new database level detection option in 2016 SQL Enterprise SP2 CU7 edition, and it does not seem to be working as expected. We have a 2 node setup, synchronous commit, automatic failover on both nodes. Database level health detection option is checked. On the primary node, I took a drive offline that contained one of the data files of the DB that is in the AG. I ran a select * from a table which read from the missing disk, and got the expected 823 error, which was logged in the error log. I ran it a few times, and the error log recorded the 823 multiple times.

The availability group did NOT fail over as it was supposed to when this happened. I waited for about 3 minutes to see if a failover would occur, and it never did. How can i find out how often the DB level health check routine is set to run? I understand this needs to see the issue in 4 consecutive runs according to this article: enhanced database level failover

I checked the health check timeout value in the AG, and it was 30 seconds.

I also reviewed the failure condition level on the server and it is set to On CriticalServerErrors, but as I understand it, this setting is completely independent of the database level health check, and either of them should be able to trigger a failover on their own. Is this correct?

The only thing I can think of that is preventing this is the pending timeout in the WSFC manager. this has a value of 3 minutes before it will take the cluster resource offline.

Any idea where else I should be looking for why this did not fail over?

3

On the primary node, I took a drive offline that contained one of the data files of the DB that is in the AG. I ran a select * from a table which read from the missing disk [...]

Since you're on 2016, the database level health checks are checking that the database is online (which taking secondary file offline won't change) and that we can write to the transaction log. Since both of these are true, your test passes. That's how it works in 2016.

Any idea where else I should be looking for why this did not fail over?

Yes, see above. This was changed to encompass more in 2017.

| improve this answer | |
  • Thanks for the feedback. I should have been a bit more specific, the drive i took offline contained the .mdf and 1 .ndf file. it did not contain the log file, so that would not trigger it, but wouldn't the .mdf file trigger it since its a primary file? – DBA Greg14 Jul 31 '19 at 17:38
  • @DBAGreg14 Let me answer that with a question, Did you see the database state change? – Sean Gallardy - Retired User Jul 31 '19 at 17:51
  • it changed to not synchronizing, but did not go offline – DBA Greg14 Jul 31 '19 at 19:55
  • @DBAGreg14 Correct :) Which is why you didn't have the failover occur. Now, try messing with the log or having checkpoints fail (which you could also simulate) and you should see the failover occur. – Sean Gallardy - Retired User Jul 31 '19 at 23:36
  • Thanks again. I'll research how to simulate a checkpoint failure, haven't come across that before. Need to be able to prove what will fail over the AG, and what won't. Appreciate the help! – DBA Greg14 Aug 1 '19 at 17:19

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.