The Caveats section of the Availability group database level health detection failover option doc has some info that might improve our guesses on the question:
It is important to note that the Database Level Heath Detection option
currently does not cause SQL Server to monitor disk uptime and SQL
Server does not directly monitor database file availability. Should a
disk drive fail or become unavailable, that alone will not necessarily
trigger the availability group to automatically failover.
As an example, when a database is idle with no active transactions,
and with no physical writes occurring, should some of the database
files become inaccessible, SQL Server may not do any read or write IO
to the files, and may not change the status for that database
immediately, so no failover would be triggered. Later, when a database
checkpoint occurs, or a physical read or write occurs for fulfilling a
query, then SQL Server may then notice the file issue, and react by
changing the database status, and subsequently the availability group
with database level health detection set on would failover due to the
database health change.
As another example, when the SQL Server database engine needs to read
a data page to fulfill a query, if the data page is cached in the
buffer pool memory, then no disk read with physical access may be
required to fulfill the query request. Therefore, a missing or
unavailable data file may not immediately trigger an automatic
failover even when database health option is enabled, since database
status is not immediately.
From a (close enough) lab test
- I placed the
msdb data and log files on a pen-drive
(drive D:) - for the sake of brevity I'm not gonna describe this
- Started the instance and ran some DML on a my lab database
- Connected do the
master database I ran
select name, state_desc from sys.databases;;
- Unplugged the pen-drive (no Safely Remove Hardware and Eject
Media, just pulled it from desktop);
- Ran some more DML on a my lab database
Lab - all fine, I even updated a table;
- SQL Server only noticed the problem when I tried to run
CREATE DATABASE StorageOffline;. I got the following error message:
Msg 823, Level 24, State 2, Line 4 The operating system returned error
21(The device is not ready.) to SQL Server during a read at offset
0x0000000041c000 in file 'D:\MSSQL\master.mdf'. Additional messages
in the SQL Server error log and operating system error log may provide
more detail. This is a severe system-level error condition that
threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error
can be caused by many factors; for more information, see SQL Server
- After I got the error, I repeated step 3 and the output was the
same: the state for all databases were still ONLINE. So, despite
the fact that SQL Server was aware of a problem on the file of the
D:\ it didn't change the state of the databases nor took the
I kept using the
Lab database with no (apparent) major problem for a few minutes and the instance only stopped working while I was writing this answer. Of course it's not a reliable state to keep working in production, but it took sometime to go offline.
Based on that info, my thoughts are:
Will SQL Server service on that server go offline or stop working
I'd say no. I haven't worked with availability groups yet, but if the feature is meant to keep important databases online and it doesn't monitor disk uptime or database file availability for databases that are actively being monitored, it won't notice the problem faster on databases that are not part of the availability group.
Or it continues to work for some time if master and msdb were cached
in RAM before network went down?
Yes, but it depends on how busy your environment is. The databases will keep online until SQL Server tries to read or write something on the
msdb database files.
But I agree with J.D., you should not rely on that situation to give you enough time to take any action that would avoid your instance from going offline.