I have a setup where 3 servers combined into Availability Group
All 3 servers have local directly attached SSD drives, and user database files are hosted on these drives
But the system databases (master and msdb) of each server in AG, are hosted on a SAN device that is accessed over the network
Did not move those to local SSD drives yet
Questions:
In a hypothetical situation where network connection between any of the servers and the SAN device is lost (bad cable, bad NIC, some temporary network glitch etc.),
-
Will SQL Server service on that server go offline or stop working properly immediately ?
-
Or it continues to work for some time if master and msdb were cached in RAM before network went down ?
Best Answer
From documentation
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:
From a (close enough) lab test
master
andmsdb
data and log files on a pen-drive (drive D:) - for the sake of brevity I'm not gonna describe this process;Lab
;master
database I ranselect name, state_desc from sys.databases;
;Lab
- all fine, I even updated a table;CREATE DATABASE StorageOffline;
. I got the following error message:D:\
it didn't change the state of the databases nor took the instance offline;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.Conclusion
Based on that info, my thoughts are:
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.
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
master
ormsdb
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.