Solução de problemas de failover automático em ambientes de SQL Server Always On

Este artigo ajuda você a resolve problemas que ocorrem durante o failover automático no Microsoft SQL Server.

Versão original do produto: SQL Server
Número de KB original: 2833707

Resumo

SQL Server Always On grupos de disponibilidade podem ser configurados para failover automático. Se um problema de integridade for detectado na instância de SQL Server que está hospedando o réplica primário, a função primária poderá ser transicionada para o parceiro de failover automático (réplica secundário). No entanto, a réplica secundária nem sempre pode ser transicionada para a função primária. Em alguns casos, ela só pode ser transicionada para a RESOLVING função. Nesta situação, nenhum réplica terá a função primária, a menos que o réplica primário retorne a um estado saudável. Além disso, os bancos de dados de disponibilidade serão inacessíveis.

Este artigo lista algumas causas comuns de failover automático mal sucedido e discute as etapas que você pode tomar para diagnosticar a causa dessas falhas.

Sintomas se um failover automático for disparado com êxito

Quando um failover automático é disparado na instância de SQL Server que está hospedando o réplica primário, o réplica secundário faz a transição para a RESOLVING função e, em seguida, para a função primária. Embora o processo seja bem-sucedido, as entradas de erro são registradas no relatório de log SQL Server que se assemelham ao seguinte texto:

The state of the local availability replica in availability group '\<Group name>' has changed from 'RESOLVING_NORMAL' to 'PRIMARY_PENDING'  
The state of the local availability replica in availability group '\<Group name>' has changed from 'PRIMARY_PENDING' to 'PRIMARY_NORMAL'

Sintomas se um failover automático não tiver êxito

Se um evento de failover automático não for bem-sucedido, o réplica secundário não fará a transição com êxito para a função primária. Portanto, o réplica de disponibilidade informará que esse réplica está em um RESOLVING estado. Além disso, os bancos de dados de disponibilidade relatam que estão em um NOT SYNCHRONIZING estado e os aplicativos não podem acessar esses bancos de dados.

Por exemplo, na imagem a seguir, SQL Server Management Studio relata que o réplica secundário está em um RESOLVING estado porque o processo de failover automático não pôde fazer a transição do réplica secundário para a função primária.

As seções a seguir discutem vários possíveis motivos pelos quais o failover automático pode não ter êxito e como diagnosticar cada causa.

Caso 1: o valor "Falhas máximas no período especificado" está esgotado

O grupo de disponibilidade tem propriedades de recurso de cluster do Windows, como as Falhas Máximas na propriedade Período Especificado . Essa propriedade é usada para evitar a movimentação indefinida de um recurso clusterizado quando ocorrem várias falhas de nó.

Para investigar e diagnosticar se essa é a causa do failover mal sucedido, examine o log de cluster do Windows (Cluster.log) e, em seguida, marcar a propriedade.

Etapa 1: examinar os dados no log de cluster do Windows (Cluster.log)

1. Use Windows PowerShell para gerar o log de cluster do Windows no nó de cluster que está hospedando o réplica primário. Para fazer isso, execute o seguinte cmdlet em uma janela do PowerShell elevada na instância de SQL Server que está hospedando o réplica primário:

   Get-ClusterLog -Node <SQL Server node name> -TimeSpan 15
   

   

   [! ANOTAÇÕES]

   • O -TimeSpan 15 parâmetro nesta etapa pressupõe que o problema que está sendo diagnosticado ocorreu durante os 15 minutos anteriores.
   • Por padrão, o arquivo de log é criado em %WINDIR%\cluster\reports.

2. Abra o arquivo Cluster.log no Bloco de Notas para examinar o log de cluster do Windows.

3. No Bloco de Notas, selecione Editar>Localizar e pesquise a cadeia de caracteres "failoverCount" no final do arquivo. Nos resultados, você deve encontrar uma mensagem que se assemelha à seguinte mensagem:

   Não falhando no nome> do recurso do grupo<, failoverCount 3, failoverThresholdSetting <Number>, computedFailoverThreshold 2

   

Etapa 2: verificar as falhas máximas na propriedade Período Especificado

1. Iniciar o Gerenciador de Cluster de Failover.

2. No painel de navegação, selecione Funções.

3. No painel Funções , clique com o botão direito do mouse no recurso clusterizado e selecione Propriedades.

4. Selecione a guia Failover e selecione as Falhas Máximas no valor de Período Especificado .

   

   Observação

   O comportamento padrão especifica que, se o recurso clusterizado falhar três vezes em seis horas, ele deverá permanecer no estado com falha. Para um grupo de disponibilidade, isso significa que o réplica é deixado no RESOLVING estado.

Conclusão

Depois de analisar o log, você descobrirá que o valor failoverCount de 3 é maior que o valor computadoFailoverThreshold de 2. Portanto, o cluster do Windows não pode concluir a operação de failover do recurso do grupo de disponibilidade para o parceiro de failover.

Resolução

Para resolve esse problema, aumente as falhas máximas no valor de período especificado.

Caso 2: permissões insuficientes da conta NT Authority\SYSTEM

A DLL do recurso do Mecanismo de Banco de Dados SQL Server se conecta à instância de SQL Server que está hospedando o réplica primário usando o ODBC para monitorar a integridade. As credenciais de logon usadas para essa conexão são a conta de logon local SQL ServerNT AUTHORITY\SYSTEM. Por padrão, essa conta de logon local recebe as seguintes permissões:

• Alterar qualquer grupo de disponibilidade
• Conectar o SQL
• Exibir o estado do servidor

Se a NT AUTHORITY\SYSTEM conta de logon não tiver nenhuma dessas permissões no parceiro de failover automático (o réplica secundário), SQL Server não poderá iniciar a detecção de integridade quando ocorrer um failover automático. Portanto, o réplica secundário não pode fazer a transição para a função primária. Para investigar e diagnosticar se essa é a causa, examine o log de cluster do Windows. Para fazer isso, siga estas etapas:

1. Use Windows PowerShell para gerar o log de cluster do Windows no nó de cluster. Para fazer isso, execute o seguinte cmdlet em uma janela do PowerShell elevada na instância de SQL Server que está hospedando o réplica secundário que não fez a transição para a função primária:

   Get-ClusterLog -Node <SQL Server node name> -TimeSpan 15
   

   

2. Abra o arquivo Cluster.log no Bloco de Notas para examinar o log de cluster do Windows.

3. Localize a entrada de erro que se assemelha ao seguinte texto:

   Falha ao executar diagnóstico comando. O usuário não tem permissão para executar essa ação.

   

Conclusão

O arquivo Cluster.log informa que existe um problema de permissões quando SQL Server executa o comando diagnóstico. Neste exemplo, a falha foi causada pela remoção da permissão exibir estado NT AUTHORITY\SYSTEM do servidor da conta de logon na instância de SQL Server que está hospedando o réplica secundário de um par de failover automático.

Resolução

Para resolve esse problema, conceda permissões suficientes à NT AUTHORITY\SYSTEM conta de logon para a detecção de integridade da DLL do recurso do Mecanismo de Banco de Dados SQL Server.

Caso 3: os bancos de dados de disponibilidade não estão em um estado SINCRONIZADO

Para fazer failover automaticamente, todos os bancos de dados de disponibilidade definidos no grupo de disponibilidade devem estar em um SYNCHRONIZED estado entre o réplica primário e o réplica secundário. Quando ocorre um failover automático, essa condição de sincronização deve ser atendida para garantir que não haja perda de dados. Portanto, se um banco de dados de disponibilidade no grupo de disponibilidade estiver no estado ou NOT SYNCHRONIZED sincronização, o failover automático não fará a transição com êxito do réplica secundário para a função primária.

Para obter mais informações sobre as condições necessárias para um failover automático, consulte as condições necessárias para um Failover Automático e as réplicas de confirmação síncrona dão suporte a duas seções de configurações dos modos Failover e Failover (Always On Grupos de Disponibilidade).

Para investigar e diagnosticar se essa é a causa do failover mal sucedido, examine o log de erros SQL Server. Você deve encontrar uma entrada de erro que se assemelha ao seguinte texto:

Um ou mais bancos de dados não foram sincronizados ou não ingressaram no grupo de disponibilidade.

Para marcar se os bancos de dados de disponibilidade estavam no SYNCHRONIZED estado, siga estas etapas:

1. Conecte-se ao réplica secundário.

2. Execute o script SQL a seguir para marcar o is_failover_ready valor de todos os bancos de dados de disponibilidade no grupo de disponibilidade que não falharam.

   Observação

   Um valor zero para qualquer um dos bancos de dados de disponibilidade pode impedir o failover automático. Esse valor indica que o banco de dados de disponibilidade não SYNCHRONIZEDera .

   SELECT database_name, is_failover_ready FROM sys.dm_hadr_database_replica_cluster_states WHERE replica_id IN (SELECT replica_id FROM sys.dm_hadr_availability_replica_states)
   

   

Conclusão

Um failover automático bem-sucedido do grupo de disponibilidade exige que todos os bancos de dados de disponibilidade estejam no SYNCHRONIZED estado. Para obter mais informações sobre modos de disponibilidade, consulte Modos de disponibilidade em grupos de disponibilidade Always On.

Caso 4: a configuração "Force Protocol Encryption" é selecionada para os protocolos do cliente no réplica secundário (primário de destino), embora o réplica não esteja configurado para criptografia

Durante o failover, quando o servidor primário detecta um problema de integridade, a DLL do cluster no parceiro de failover (réplica secundário) tenta se conectar à réplica local para iniciar o monitoramento de integridade. Isso faz parte da transição para a função primária. Se o réplica secundário não estiver configurado para criptografia, mas a configuração de Criptografia de Protocolo de Força for inadvertidamente definida na configuração do cliente, a conexão falhará e o failover não poderá ocorrer.

Para marcar para essa configuração:

1. Inicie o SQL Server Configuration Manager.
2. No painel esquerdo , clique com o botão direito do mouse na Configuração do SQL Native Client 11.0 e selecione Propriedades.
3. Na caixa de diálogo, marcar a configuração de Criptografia de Protocolo de Força. Se estiver definido como Sim, altere o valor para Não.
4. Retize o failover.

Conclusão

SQL Server Always On monitoramento de integridade usa uma conexão ODBC local para monitorar SQL Server integridade. A Criptografia de Protocolo de Força deve ser habilitada na seção Configuração do Cliente do SQL Server Configuration Manager somente se SQL Server em si tiver sido configurada para forçar criptografias em SQL Server Configuration Manager no SQL Server Seção Configuração de Rede. Para obter mais informações, consulte Habilitar conexões criptografadas com o Mecanismo de Banco de Dados.

Caso 5: Problemas de desempenho em réplica ou nó secundários causam falha Always On verificações de integridade

Antes de falhar do réplica primário ao réplica secundário, SQL Server DLL de recurso do Mecanismo de Banco de Dados se conecta ao réplica secundário para verificar a integridade do réplica. Se essa conexão falhar devido a problemas de desempenho no réplica secundário, o failover automático não ocorrerá.

Para investigar e diagnosticar se essa é a causa, siga estas etapas:

1. Examine o log de cluster no réplica secundário para marcar para a mensagem de erro: "Não é possível concluir o processo de logon devido ao atraso na abertura da conexão do servidor".

   0000110c.00002bcc::2020/08/06-01:17:54.943 INFO  [RCM] move of group AOCProd01AG from CO2ICMV3SQL09(1) to CO2ICMV3SQL10(2) of type MoveType::Manual is about to succeed, failoverCount=3, lastFailoverTime=2020/08/05-02:08:54.524 targeted=true 
   00002a54.0000610c::2020/08/06-01:18:44.929 ERR   [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]Unable to complete login process due to delay in opening server connection (0) 
   00002a54.0000610c::2020/08/06-01:18:44.929 INFO  [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] Could not connect to SQL Server (rc -1) 
   00002a54.0000610c::2020/08/06-01:18:44.929 INFO  [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] SQLDisconnect returns following information 
   00002a54.0000610c::2020/08/06-01:18:44.929 ERR   [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0) 
   00002a54.0000610c::2020/08/06-01:18:44.931 ERR   [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] Failed to connect to SQL Server 
   00002a54.0000610c::2020/08/06-01:18:44.931 ERR   [RHS] Online for resource AOCProd01AG failed. 
   

   Essa situação poderá ocorrer se o failover for feito em um SQL Server réplica secundário que tenha uma carga de trabalho existente ocupada. Isso pode atrasar a resposta do SQL Server à tentativa de solicitação de conexão de integridade do HADR e impedir uma tentativa de failover bem-sucedida.

2. Para determinar se há pressão sobre os agendadores do sistema, use SQL Server Management Studio para executar o seguinte script no réplica secundário:

   USE MASTER 
   GO  
   WHILE 1=1 
   BEGIN 
   PRINT convert(varchar(20), getdate(),120) 
   DECLARE @max INT; 
   SELECT @max = max_workers_count 
   FROM sys.dm_os_sys_info; 
   SELECT GETDATE() AS 'CurrentDate',  
          @max AS 'TotalThreads',  
          SUM(active_Workers_count) AS 'CurrentThreads',  
          @max - SUM(active_Workers_count) AS 'AvailableThreads',  
          SUM(runnable_tasks_count) AS 'WorkersWaitingForCpu',  
          SUM(work_queue_count) AS 'RequestWaitingForThreads' 
          --SUM(current_workers_count) AS 'AssociatedWorkers' 
   FROM sys.dm_os_Schedulers 
   WHERE STATUS = 'VISIBLE ONLINE'; 
   wait for delay '0:0:15' 
   END
   

   Veja a seguir a saída de exemplo da consulta anterior:

   CurrentDate	TotalThreads	CurrentThreads	AvailableThreads	WorkersWaitingForCpu	RequestWaitingForThreads
   2020-10-06 01:27:01.337	1216	361	855	33	0
   2020-10-06 01:27:08.340	1216	1412	-196	22	76
   2020-10-06 01:27:15.340	1216	1304	-88	2	161
   2020-10-06 01:27:22.340	1216	1242	-26	21	185
   2020-10-06 01:27:29.343	1216	1346	-130	19	476
   2020-10-06 01:27:36.350	1216	1350	-134	9	630
   2020-10-06 01:27:43.353	1216	1346	-130	13	539
   2020-10-06 01:27:50.360	1216	1378	-162	5	328
   2020-10-06 01:27:57.360	1216	197	1019	0	0

   Valores altos relatados para WorkersWaitingForCpu e RequestWaitingForThreads indicam que a contenção de agendamento está ocorrendo e que SQL Server não pode atender a carga de trabalho atual em tempo hábil.

Resolução

Se você tiver esse problema, reequilibrar a carga de trabalho no réplica secundário ou considerar aumentar o poder de processamento (adicionar processadores) nos computadores que executam essas cargas de trabalho.

Solucionar problemas de outros eventos de failover com falha

Para monitorar a integridade do novo réplica primário durante o failover, você deve conectar localmente o monitoramento de integridade AlwaysOn à instância SQL Server que está em transição para a função primária.

Além dos motivos mais comuns que são discutidos neste artigo, há muitos outros motivos para que essa tentativa de conexão possa falhar. Para investigar uma tentativa de failover com falha ainda mais, examine o log do Cluster no parceiro de failover (o réplica que você não pôde fazer failover):

1. Use Windows PowerShell para gerar o log do Cluster do Windows no nó de cluster. Para fazer isso, execute o cmdlet a seguir em uma janela do PowerShell elevada na instância de SQL Server que está hospedando o réplica secundário que não fez a transição para a função primária. Um log de cluster será gerado para os últimos 60 minutos de atividade.

   Get-ClusterLog -Node <SQLServerNodeName> -TimeSpan 60
   

2. Para examinar o log do Cluster do Windows, abra o arquivo Cluster.log no Bloco de Notas.

3. Pesquise a cadeia de caracteres "Conectar-se a SQL Server" que cai durante o evento de failover mal sucedido.

4. Examine as mensagens de logon subsequentes usando a ID do thread (consulte a captura de tela a seguir) para correlacionar os eventos relacionados ao evento de logon. O exemplo a seguir mostra uma pesquisa de "Conectar-se a SQL Server". Ele também mostra o uso da ID do thread (lado esquerdo) para localizar os outros diagnóstico que descrevem por que a tentativa de conexão falhou.

   

Os exemplos a seguir mostram falhas de conexão com o novo réplica primário.

Exemplo Defina 1

[hadrag] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]SQL Server Network
Interfaces: No client protocols are enabled and no protocol was specified in the connection
string [xFFFFFFFF]. (268435455)

Resolução

Inicie SQL Server Configuration Manager e verifique se a Memória Compartilhada ou TCP/IP está habilitada em Protocolos de Cliente para a Configuração de Cliente Nativo do SQL.

Exemplo Set 2

[hadrag] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]SQL Server Network
Interfaces: Server doesn't support requested protocol [xFFFFFFFF]. (268435455)

Resolução

Inicie SQL Server Configuration Manager e verifique se a Memória Compartilhada ou TCP/IP está habilitada em Protocolos de Cliente para a Configuração de Cliente Nativo do SQL.

Exemplo Set 3

000010b8.00001764::2020/12/02-16:52:49.808 ERR [RES] SQL Server Availability Group : [hadrag]
ODBC Error: [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot alter the availability
group 'ag', because it does not exist or you do not have permission. (15151)
000010b8.00000fd0::2020/12/02-17:01:14.821 ERR [RES] SQL Server Availability Group: [hadrag]
ODBC Error: [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The user does not have permission to perform this action. (297)
000010b8.00001838::2020/12/02-17:10:04.427 ERR [RES] SQL Server Availability Group : [hadrag]
ODBC Error: [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user
'SQLREPRO\NODE2$'. Reason: The account is disabled. (18470)

Resolução

Examinar o caso 2: permissões insuficientes da conta NT Authority\SYSTEM.