Solução de problemas de tempos limite de conexão intermitente entre réplicas do grupo de disponibilidade
Este artigo ajuda você a diagnosticar tempos limite de conexão intermitentes relatados entre réplicas do grupo de disponibilidade.
Sintomas e efeitos de tempos limite intermitentes de conexão de réplica do grupo de disponibilidade
A consulta de réplicas primárias e secundárias retorna resultados diferentes
As cargas de trabalho somente leitura que consultam réplicas secundárias podem consultar dados obsoletos. Se ocorrerem tempos limite de conexão de réplica intermitente, as alterações nos dados no banco de dados de réplica primária ainda não serão refletidas no banco de dados secundário quando você consultar os mesmos dados. Para obter mais informações, consulte a seção Latência de dados na réplica secundária.
Grupo de disponibilidade do relatório de diagnóstico não sincronizado
O painel Always On no SQL Server Management Studio pode relatar um grupo de disponibilidade não íntegro que tem réplicas em um estado Não Sincronizado . Você também pode observar que as réplicas de relatório do painel Always On estão no estado Não sincronizando .
Ao examinar os logs de erros do SQL Server dessas réplicas, você pode observar mensagens como as seguintes que indicam que houve um tempo limite de conexão entre as réplicas no grupo de disponibilidade:
Log de erros da réplica primária
2023-02-15 07:10:55.500 spid43s Always On availability groups connection with secondary database terminated for primary database 'agdb' on the availability replica 'SQL19AGN2' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.
Log de erros da réplica secundária
2023-02-15 07:11:03.100 spid31s A connection time-out has occurred on a previously established connection to availability replica 'SQL19AGN1' with id [<replicaid>]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.
2023-02-15 07:11:03.100 spid31s Always On Availability Groups connection with primary database terminated for secondary database 'agdb' on the availability replica 'SQL19AGN1' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.
Problemas de conexão intermitente podem afetar a preparação de failover de uma réplica secundária
Se você configurar o grupo de disponibilidade para failover automático e o parceiro de failover de confirmação síncrona for desconectado intermitentemente do primário, o failover automático poderá não ser bem-sucedido.
Você pode consultar sys.dm_hadr_database_replia_cluster_states para determinar se o banco de dados do grupo de disponibilidade está pronto para failover naquele momento. Aqui está um exemplo dos resultados se o ponto de extremidade de espelhamento foi interrompido na réplica secundária:
SELECT drcs.database_name, drcs.is_failover_ready, ar.replica_server_name, ars.role_desc, ars.connected_state_desc,
ars.last_connect_error_description, ars.last_connect_error_number, ar.endpoint_url
FROM sys.dm_hadr_availability_replica_states ars JOIN sys.availability_replicas ar ON ars.replica_id=ar.replica_id
JOIN sys.dm_hadr_database_replica_cluster_states drcs ON ar.replica_id=drcs.replica_id
WHERE ars.role_desc='SECONDARY'
O failover automático pode não colocar o grupo de disponibilidade online na função primária no computador do parceiro de failover se o failover coincidir com um tempo limite de conexão de réplica.
O que indicam os erros de tempo limite de conexão?
O valor padrão é de 10 segundos para a configuração de réplica do grupo de disponibilidade, SESSION_TIMEOUT. Essa configuração é definida para cada réplica. Ele determina quanto tempo a réplica aguarda para receber uma resposta de sua réplica de parceiro antes de relatar um tempo limite de conexão. Se uma réplica não obtiver resposta da réplica do parceiro, ela relatará um tempo limite de conexão no log de erros do Microsoft SQL Server e no log de aplicativos do Windows. A réplica que relata o tempo limite imediatamente tenta se reconectar e continuará a tentar a cada cinco segundos.
Normalmente, o tempo limite da conexão é detectado e relatado por apenas uma réplica. No entanto, o tempo limite da conexão pode ser relatado por ambas as réplicas ao mesmo tempo. Há diferentes versões dessa mensagem, dependendo se o tempo limite da conexão ocorreu usando uma conexão estabelecida anteriormente ou uma nova conexão:
Message 35206 A connection timeout has occurred on a previously established connection to availability replica '<replicaname>' with id [<replicaid>]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.
Message 35201 A connection timeout has occurred while attempting to establish a connection to availability replica '<replicaname>' with id [<replicaid>]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.
A réplica do parceiro pode não detectar um tempo limite. Em caso afirmativo, ele pode relatar a mensagem 35201 ou 35206. Caso contrário, ele relatará uma perda de conexão para cada um dos bancos de dados do grupo de disponibilidade:
Message 35267 Always On Availability Groups connection with primary/secondary database terminated for primary/secondary database '<databasename>' on the availability replica '<replicaname>' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.
Aqui está um exemplo do que o SQL Server relata ao log de erros: Se você interromper o ponto de extremidade de espelhamento na réplica primária, a réplica secundária detectará um tempo limite de conexão e as mensagens 35206 e 35267 serão relatadas no log de erros da réplica secundária:
2023-02-15 07:11:03.100 spid31s A connection timeout has occurred on a previously established connection to availability replica 'SQL19AGN1' with id [<replicaid>]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.
2023-02-15 07:11:03.100 spid31s Always On Availability Groups connection with primary database terminated for secondary database 'agdb' on the availability replica 'SQL19AGN1' with Replica ID:[<replicaid>]. This is an informational message only. No user action is required.
Neste exemplo, a réplica primária não detectou nenhum tempo limite de conexão porque ainda podia se comunicar com o secundário e relatou a mensagem 35267 para cada banco de dados do grupo de disponibilidade (neste exemplo, há apenas um banco de dados, 'agdb'):
2023-02-15 07:10:55.500 spid43s Always On Availability Groups connection with secondary database terminated for primary database 'agdb' on the availability replica 'SQL19AGN2' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.
Causas de tempos limite de conexão de réplica
Problema de aplicação
O SQL Server pode estar ocupado por vários motivos e não atende à conexão do ponto de extremidade de espelhamento dentro do período do grupo SESSION_TIMEOUT de disponibilidade. Isso causa o tempo limite da conexão. Algumas dessas razões são:
O SQL Server experimenta 100% de utilização da CPU. Isso significa que o SQL Server ou algum outro aplicativo está conduzindo a CPU por segundos de cada vez.
O SQL Server experimenta eventos de agendador não produtivos. Os threads do SQL Server são responsáveis por ceder o agendador (CPU) a outros threads para concluir seu trabalho se um thread não for renderizado em tempo hábil.
O SQL Server experimenta esgotamento de thread de trabalho, problemas de memória insuficiente ou problemas de aplicativo que afetam sua capacidade de atender à conexão do ponto de extremidade de espelhamento.
Problema de rede
Isso requer que você colete logs de rastreamento de rede nas réplicas primária e secundária quando o erro for disparado. Para fazer isso, você pode examinar a latência da rede e os pacotes descartados.
Como diagnosticar tempos limite de conexão de réplica
Para o problema de problemas de aplicativo que impedem o SQL Server de atender a conexão com a réplica do parceiro, esta seção explica como analisar os logs do SQL Server. Essas dicas podem ajudá-lo a identificar a causa raiz dos tempos limite de conexão da réplica. Esta seção termina com diretrizes mais avançadas sobre como coletar rastreamentos de rede quando os tempos limite de conexão ocorrerem para que você possa verificar o status da rede.
Avaliar o tempo e o local dos tempos limite de conexão de réplica
Revise o histórico, a frequência e as tendências dos tempos limite de conexão. Usar as mensagens encontradas no log de erros do SQL Server é uma ótima maneira de fazer isso. Onde os tempos limite de conexão são relatados? Eles são relatados consistentemente na réplica primária ou secundária? Quando os erros ocorreram? Eles ocorreram em uma determinada semana do mês, dia da semana ou hora do dia? Outras manutenções programadas ou processamento em lote correspondem aos horários em que os tempos limite de conexão são observados? Essa avaliação pode ajudá-lo a definir o escopo e correlacionar os tempos limite de conexão para identificar a causa raiz.
Examinar a AlwaysOn_health sessão de evento estendido
A AlwaysOn_health sessão de evento estendido foi aprimorada para incluir o evento, que é disparado ucs_connection_setup quando uma réplica está estabelecendo uma conexão com sua réplica de parceiro. Isso pode ser útil ao solucionar problemas de tempo limite de conexão.
Observação
O ucs_connection_setup evento estendido foi adicionado às atualizações cumulativas mais recentes do SQL Server. Você deve estar executando as atualizações cumulativas mais recentes para observar esse evento estendido.
Consultar DMVs (exibições de gerenciamento distribuído) Always On
Você pode consultar DMVs Always On para obter mais informações sobre o estado conectado da réplica. Essa consulta relata apenas o estado conectado e quaisquer erros associados ao tempo limite da conexão no momento em que os problemas ocorrem. Se os problemas de conexão forem intermitentes, a consulta poderá não capturar o estado desconectado facilmente.
SELECT ar.replica_server_name, ars.role_desc, ars.connected_state_desc,
ars.last_connect_error_description, ars.last_connect_error_number, ar.endpoint_url
FROM sys.dm_hadr_availability_replica_states ars JOIN sys.availability_replicas ar ON ars.replica_id=ar.replica_id
O exemplo a seguir mostra um estado desconectado sustentado porque o ponto de extremidade de espelhamento na réplica primária foi interrompido. Ao consultar a réplica primária, a DMV Always On pode relatar a réplica primária e todas as réplicas secundárias (o ponto de extremidade está desabilitado na réplica primária).
Ao consultar a réplica secundária, as DMVs Always On relatam apenas a réplica secundária.
Examinar a sessão de evento estendido Always On
Conecte-se a cada réplica usando o Pesquisador de Objetos do SSMS (SQL Server Management Studio) e abra os arquivos de
AlwaysOn_healtheventos estendidos.No SSMS, vá para Abrir Arquivo>e selecione Mesclar Arquivos de Eventos Estendidos.
Selecione o botão Adicionar.
Na caixa de diálogo Abrir Arquivo, navegue até os arquivos no diretório \LOG do SQL Server.
Pressione Control e selecione os arquivos cujo nome começa com 'AlwaysOn_healthxxx.xel'.
Selecione Abrir e, em seguida, selecione OK.
Você deve ver uma nova janela com guias no SSMS que mostra os eventos AlwaysOn.
A captura de tela a seguir mostra os
AlwaysOn_healthdados da réplica secundária. A primeira caixa destacada mostra a perda de conexão depois que o ponto de extremidade na réplica primária é interrompido. A segunda caixa destacada mostra a falha de conexão que ocorre na próxima vez que a réplica secundária tentar se conectar à réplica primária.
Verifique se os eventos não produtivos estão causando tempos limite de conexão
Um dos motivos mais comuns pelos quais uma réplica de disponibilidade não pode atender à conexão de réplica do parceiro é um agendador não produtivo. Para obter mais informações sobre agendadores sem rendimento, consulte Solucionando problemas de agendamento e rendimento do SQL Server.
O SQL Server rastreia eventos de agendador não produtivos que são tão curtos quanto 5 a 10 segundos. Ele relata TrackingNonYieldingScheduler esses eventos no ponto de dados na saída do sp_server_diagnostics query_processing componente.
Para verificar se há eventos não produtivos que possam causar tempos limite de conexão de réplica, siga estas etapas:
Crie um trabalho do SQL Agent que registre
sp_server_diagnosticsa cada cinco segundos.Agende esse trabalho no servidor que não relata o tempo limite da conexão. Ou seja, se a réplica do Servidor A relatar o tempo limite de conexão da réplica em seu log de erros, configure o trabalho do SQL Agent na réplica do parceiro, o Servidor B. Como alternativa, se você estiver vendo tempos limite de conexão em ambas as réplicas, crie o trabalho em ambas as réplicas.
Execute o arquivo em lotes a seguir para criar um trabalho que é executado
sp_server_diagnosticsa cada cinco segundos, acrescenta a saída a um arquivo de texto e inicia o trabalho. O comando no exemplosp_server_diagnostics 5a seguir é executado a cada cinco segundos. Portanto, não há necessidade de agendar este trabalho para ser executado a cada cinco segundos, basta iniciar o trabalho e ele será executado até ser interrompido, a cada cinco segundos:USE [msdb] GO DECLARE @ReturnCode INT SELECT @ReturnCode = 0 DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Run sp_server_diagnostics', @owner_login_name=N'sa', @job_id = @jobId OUTPUT /****** Object: Step [Run SP_SERVER_DIAGNOSTICS] Script Date: 2/15/2023 4:20:41 PM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run SP_SERVER_DIAGNOSTICS', @subsystem=N'TSQL', @command=N'sp_server_diagnostics 5', @database_name=N'master', @output_file_name=N'D:\cases\2423\sp_server_diagnostics_output.out', @flags=2 EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' EXEC sp_start_job 'Run sp_server_diagnostics'Observação
Nesses comandos, altere
@output_file_namepara um caminho válido e forneça um nome de arquivo.
Analisar os resultados
Quando um tempo limite de conexão for relatado, observe o carimbo de data/hora do evento de tempo limite mostrado no log de erros do SQL Server. Para as réplicas no exemplo a seguir, SQL19AGN1 estava relatando os tempos limite de conexão da réplica. Portanto, um trabalho do SQL Agent foi criado na SQL19AGN2réplica do parceiro. Em seguida, um tempo limite de conexão foi relatado no log de SQL19AGN1 erros às 07:24:31.
Em seguida, a saída do trabalho do SQL Agent que é executado sp_server_diagnostics é verificada em torno do tempo relatado, revisando especificamente o TrackingNonYieldingScheduler ponto de dados na saída do query_processing componente. A saída relata que um agendador não produtivo foi rastreado (como um valor hexadecimal diferente de zero) no SQL19AGN2 do servidor (às 07:24:33) na hora em que o tempo limite da conexão da réplica foi relatado em SQL19AGN1 (às 07:24:31).
Observação
A saída a seguir sp_server_diagnostics é concatenada para mostrar o (carimbo de data/hora) e query_processing TrackingNonYieldingScheduler os create_time resultados.
Investigar um evento do agendador não produtivo
Se você verificou nas etapas de diagnóstico anteriores que um evento não produtivo causou o tempo limite da conexão da réplica:
Identifique as cargas de trabalho que estão em execução no SQL Server no momento em que os eventos não produtivos são executados.
Semelhante aos tempos limite de conexão de réplica, procure tendências nesses eventos durante o mês, dia ou semana em que ocorrem.
Colete o rastreamento do monitor de desempenho no sistema no qual o evento não produtivo foi detectado.
Colete os principais contadores de desempenho para recursos do sistema, incluindo Processador::% Tempo do Processador, Memória::MBytes Disponíveis, Disco Lógico::Comprimento Médio da Fila de Disco e Disco Lógico::Disco Lógico s/Transferência.
Se for necessário, abra um incidente de suporte do SQL Server para obter mais assistência para encontrar a causa raiz desses eventos não produtivos. Compartilhe os logs que você coletou para análise posterior.
Coleta avançada de dados: coletar rastreamento de rede durante o tempo limite da conexão
Se o diagnóstico anterior do aplicativo SQL Server não tiver produzido uma causa raiz, você deverá verificar a rede. A análise bem-sucedida da rede requer que você colete um rastreamento de rede que cubra o tempo limite da conexão.
O procedimento a seguir inicia um rastreamento de rede do Windows netsh nas réplicas nas quais os tempos limite de conexão são relatados nos logs de erros do SQL Server. Uma tarefa de evento agendada do Windows é disparada quando um dos erros de conexão do SQL Server é registrado no log do aplicativo. A tarefa agendada executa um comando para interromper o rastreamento de netsh rede para que os principais dados de rastreamento de rede não sejam substituídos. Essas etapas também pressupõem um caminho de *F:* para os logs de lote e rastreamento. Ajuste esse caminho ao seu ambiente.
Inicie um rastreamento de rede, conforme mostrado no snippet de código a seguir, nas duas réplicas nas quais ocorrem os tempos limite de conexão:
netsh trace start capture=yes persistent=yes overwrite=yes maxsize=500 tracefile=f:\trace.etlCrie tarefas agendadas do Windows que interrompam o
netshrastreamento nos eventos 35206 ou 35267. Você pode criar estas tarefas em uma linha de comando administrativa:schtasks /Create /tn Event35206Task /tr F:\stoptrace.bat /SC ONEVENT /EC Application /MO *[System/EventID=35206] /f /RL HIGHEST schtasks /Create /tn Event35267Task /tr F:\stoptrace.bat /SC ONEVENT /EC Application /MO *[System/EventID=35267] /f /RL HIGHESTDepois que o evento ocorrer e os rastreamentos de rede forem interrompidos e capturados, você poderá excluir as
ONEVENTtarefas:PS C:\Users\sqladmin> Schtasks /Delete /tn Event35206Task /F PS C:\Users\sqladmin> Schtasks /Delete /tn Event35267Task /F
A análise do rastreamento de rede está fora do escopo desta solução de problemas. Se você não puder interpretar o rastreamento de rede, entre em contato com a equipe de suporte do Microsoft SQL Server e forneça o rastreamento junto com outros arquivos de log solicitados para análise de causa raiz.
O que mais posso fazer para mitigar os tempos limite de conexão?
O grupo de disponibilidade padrão, SESSION_TIMEOUT, é configurado para 10 segundos. Você pode atenuar os tempos limite de conexão ajustando a propriedade de réplica SESSION_TIMEOUT do grupo de disponibilidade. Essa configuração é por réplica. Ajuste-o para a réplica primária e secundária afetada. Aqui está um exemplo da sintaxe. O valor padrão SESSION_TIMEOUT é 10. Portanto, você pode usar 15 como o próximo valor.
ALTER AVAILABILITY GROUP ag
MODIFY REPLICA ON 'SQL19AGN1' WITH (SESSION_TIMEOUT = 15);