O SQL Server ficará offline se perder a conectividade de rede com a SAN onde residem os bancos de dados do sistema master e msdb?

Você não pode depender dos bancos de dados do sistema sendo armazenados em cache na memória, o mais provável é que eles não sejam devido ao acesso menos frequente em relação aos bancos de dados do usuário.

Acho que você terminará em um estado quase funcional, onde seus bancos de dados de usuários ainda estarão acessíveis, mas certos recursos da instância do servidor que dependem mastere msdblançarão alguns erros estranhos, dependendo do que mais seu servidor estiver fazendo. O serviço para sua instância do SQL Server deve continuar online (estado "iniciado"). Por exemplo, se você tiver algum trabalho de agente agendado , eu apostaria meu dinheiro (mas não poderia dizer com certeza sem testar) que eles encontrariam erros (silenciosamente ou aparentemente) ao tentar executar, pois a maioria de seus metadados é armazenados no banco de msdbdados.

Se isso ocorrer, é melhor restaurar o acesso a esses bancos de dados do sistema o mais rápido possível para garantir 100% de confiabilidade em todos os recursos e funções.

Da documentação

A seção Advertências do documento de opção de failover de detecção de integridade no nível de banco de dados do grupo de disponibilidade tem algumas informações que podem melhorar nossas suposições sobre a questão:

É importante observar que a opção Database Level Heath Detection atualmente não faz com que o SQL Server monitore o tempo de atividade do disco e o SQL Server não monitora diretamente a disponibilidade do arquivo de banco de dados. Caso uma unidade de disco falhe ou fique indisponível, isso por si só não necessariamente acionará o grupo de disponibilidade para failover automático.

Por exemplo, quando um banco de dados está ocioso sem transações ativas e sem ocorrência de gravações físicas, se alguns dos arquivos do banco de dados se tornarem inacessíveis, o SQL Server pode não fazer nenhuma E/S de leitura ou gravação nos arquivos e não pode alterar o status para esse banco de dados imediatamente, para que nenhum failover seja acionado. Mais tarde, quando ocorrer um ponto de verificação de banco de dados ou uma leitura ou gravação física para atender a uma consulta, o SQL Server poderá notar o problema do arquivo e reagir alterando o status do banco de dados e, posteriormente, o grupo de disponibilidade com detecção de integridade no nível do banco de dados definida em faria failover devido à alteração da integridade do banco de dados.

Como outro exemplo, quando o mecanismo de banco de dados do SQL Server precisa ler uma página de dados para atender a uma consulta, se a página de dados estiver armazenada em cache na memória do pool de buffers, nenhuma leitura de disco com acesso físico poderá ser necessária para atender à solicitação de consulta. Portanto, um arquivo de dados ausente ou indisponível pode não acionar imediatamente um failover automático mesmo quando a opção de integridade do banco de dados estiver habilitada, pois o status do banco de dados não é imediato.

De um teste de laboratório (próximo o suficiente)

1. Coloquei os dados mastere msdbos arquivos de log em um pen-drive (drive D:) - por uma questão de brevidade, não vou descrever esse processo;
2. Iniciei a instância e executei alguns DML em um banco de dados do meu laboratório Lab;
3. Conectado faço o masterbanco de dados que rodei select name, state_desc from sys.databases;;
4. Desconecte o pen-drive (sem Remover Hardware com Segurança e Ejetar Mídia , apenas puxá-lo da área de trabalho);
5. Executei um pouco mais de DML em um banco de dados do meu laboratório Lab- tudo bem, até atualizei uma tabela;
6. O SQL Server só percebeu o problema quando tentei executar o CREATE DATABASE StorageOffline;. Recebi a seguinte mensagem de erro:

   Msg 823, Level 24, State 2, Line 4 O sistema operacional retornou o erro 21(O dispositivo não está pronto.) para o SQL Server durante uma leitura no deslocamento 0x0000000041c000 no arquivo 'D:\MSSQL\master.mdf'. Mensagens adicionais no log de erros do SQL Server e no log de erros do sistema operacional podem fornecer mais detalhes. Essa é uma condição de erro grave no nível do sistema que ameaça a integridade do banco de dados e deve ser corrigida imediatamente. Conclua uma verificação completa de consistência do banco de dados (DBCC CHECKDB). Esse erro pode ser causado por vários fatores; para obter mais informações, consulte os Manuais Online do SQL Server.

7. Depois que recebi o erro, repeti a etapa 3 e a saída foi a mesma: o estado de todos os bancos de dados ainda era ONLINE . Assim, apesar do SQL Server estar ciente de um problema no arquivo da unidade D:\, ele não alterou o estado dos bancos de dados nem colocou a instância offline;

Continuei usando o Labbanco de dados sem grandes problemas (aparentes) por alguns minutos e a instância só parou de funcionar enquanto eu escrevia esta resposta. É claro que não é um estado confiável para continuar trabalhando em produção, mas levou algum tempo para ficar offline.

Conclusão

Com base nessas informações, meus pensamentos são:

O serviço do SQL Server nesse servidor ficará offline ou deixará de funcionar corretamente imediatamente?

Eu diria que não. Ainda não trabalhei com grupos de disponibilidade, mas se o recurso se destina a manter bancos de dados importantes online e não monitora o tempo de atividade do disco ou a disponibilidade do arquivo de banco de dados para bancos de dados que estão sendo monitorados ativamente , ele não notará o problema mais rapidamente em bancos de dados que não fazem parte do grupo de disponibilidade.

Ou continua a funcionar por algum tempo se master e msdb foram armazenados em cache na RAM antes da rede cair?

Sim, mas depende de quão ocupado o seu ambiente está. Os bancos de dados permanecerão online até que o SQL Server tente ler ou gravar algo nos arquivos de banco de dados masterou .msdb

Mas eu concordo com JD, você não deve confiar nessa situação para lhe dar tempo suficiente para tomar qualquer ação que evite que sua instância fique offline.

Depende de que tipo de offline. Eu fiz com que ele entrasse em um estado em que não tinha ideia de quais transações foram confirmadas porque o modo de falha que estava vendo era que as gravações no banco de dados estavam falhando nos níveis de bloco. Ele enviou spam ao log muito bem, mas não conseguiu se recuperar até que eu o devolvesse manualmente, pois acreditaria que a cópia na memória estava correta após atingir o erro de E/S.

Tenho certeza que alguém vai passar por aqui e dizer que isso é loucura. Concordo. É um comportamento terrível. Mas eu observei in loco. Ao trazer o servidor de volta, apareceu de um SELECTobservador que o banco de dados foi revertido. Observe que, embora qualquer pessoa que esteja executando um COMMITerro, outras SELECTinstruções possam ver os resultados dos commits com falha como se tivessem sido bem-sucedidos lendo-os com SELECTinstruções até que eu os reciclasse manualmente.

Que nojo.