Поделиться через

Устранение неполадок с временным временем ожидания подключения между репликами группы доступности

  • Статья

В этой статье показано, как диагностировать временные ожидания подключения, сообщаемые между репликами группы доступности.

Симптомы и последствия временных истечений времени ожидания подключения к реплике группы доступности

Запросы первичных и вторичных реплик возвращают разные результаты

Рабочие нагрузки только для чтения, запрашивающие вторичные реплики, могут запрашивать устаревшие данные. Если временное время ожидания подключения реплики возникает, изменения данных базы данных первичной реплики еще не отражаются в базе данных-получателе при запросе одних и таких же данных. Дополнительные сведения см . в разделе задержки данных во вторичной реплике .

Группа доступности отчета диагностики не синхронизирована

Панель мониторинга AlwaysOn в SQL Server Management Studio может сообщить о неработоспособной группе доступности, которая содержит реплики, которые должны находиться в состоянии не синхронизации . Вы также можете наблюдать, как реплики отчетов о панелях мониторинга AlwaysOn будут находиться в состоянии "Не синхронизировать ".

Снимок экрана: реплики отчетов панели мониторинга AlwaysOn в состоянии not Synchronizing.

При просмотре журналов ошибок SQL Server этих реплик можно наблюдать такие сообщения, как показано ниже, что между репликами в группе доступности произошло время ожидания подключения:

Журнал ошибок из первичной реплики

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.

Журнал ошибок из вторичной реплики

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.

Временные проблемы подключения могут повлиять на готовность отработки отказа вторичной реплики

Если вы настраиваете группу доступности для автоматической отработки отказа, а партнер по отработки отказа синхронной фиксации периодически отключается от основного, автоматическая отработка отказа может быть неудачной.

Вы можете запросить sys.dm_hadr_database_replia_cluster_states , готова ли база данных группы доступности к отработку отказа в данный момент. Ниже приведен пример результатов, если конечная точка зеркального отображения остановлена на вторичной реплике:

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'

Снимок экрана: конечная точка зеркального отображения остановлена на вторичной реплике.

Автоматическая отработка отказа может не привести группу доступности в режим "в сети" на главном компьютере партнера отработки отказа, если отработка отказа совпадает с временем ожидания подключения реплики.

Какие ошибки времени ожидания подключения указывают?

Значение по умолчанию — 10 секунд для параметра SESSION_TIMEOUTреплики группы доступности. Этот параметр настроен для каждой реплики. Он определяет, сколько времени реплики ожидает получения ответа от реплики партнера, прежде чем сообщить о времени ожидания подключения. Если реплика не получает ответа от реплики партнера, он сообщает время ожидания подключения в журнале ошибок Microsoft SQL Server и журнале приложений Windows. Реплика, которая сообщает время ожидания немедленно пытается повторно подключиться, и будет продолжать пытаться каждые пять секунд.

Как правило, время ожидания подключения обнаруживается и сообщается только одной репликой. Однако время ожидания подключения может быть сообщено обеими репликами одновременно. Существуют разные версии этого сообщения в зависимости от того, произошло ли время ожидания подключения с помощью ранее установленного подключения или нового подключения:

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.

Реплика партнера может не обнаружить время ожидания. Если это так, он может сообщить о сообщении 35201 или 35206. Если это не так, он сообщает о потере подключения к каждой из баз данных группы доступности:

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.

Ниже приведен пример того, что SQL Server сообщает журналу ошибок: если остановить конечную точку зеркального отображения на первичной реплике, вторичная реплика обнаруживает время ожидания подключения, а сообщения 35206 и 35267 передаются в журнал ошибок вторичной реплики:

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.

В этом примере первичная реплика не обнаружила время ожидания подключения, так как оно по-прежнему может взаимодействовать со вторичной и сообщило сообщение 35267 для каждой базы данных группы доступности (в этом примере существует только одна база данных , 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.

Причины времени ожидания подключения реплики

Проблема с приложением

SQL Server может быть занят по нескольким причинам и не обслуживает подключение конечной точки зеркального отображения в течение периода группы SESSION_TIMEOUT доступности. Это приводит к истечении времени ожидания подключения. Ниже приведены некоторые из следующих причин:

  • SQL Server использует 100 процентов загрузки ЦП. Это означает, что SQL Server или другое приложение управляет ЦП в течение нескольких секунд.

  • SQL Server обеспечивает неозначение событий планировщика. Потоки SQL Server отвечают за получение планировщика (ЦП) другим потокам, чтобы завершить работу, если поток не дает своевременно.

  • SQL Server испытывает проблемы с рабочим потоком, проблемы с нехваткой памяти или приложения, которые влияют на возможность обслуживания подключения зеркальной конечной точки.

Неполадка сети

Для этого требуется собирать журналы трассировки сети на первичных и вторичных репликах при активации ошибки. Для этого можно проверить задержку сети и удаленные пакеты.

Диагностика времени ожидания подключения реплики

Проблемы с приложениями, которые не позволяют SQL Server обслуживать подключение с репликой партнера, в этом разделе объясняется, как анализировать журналы SQL Server. Эти советы помогут определить первопричину времени ожидания подключения реплики. В этом разделе приводятся более подробные рекомендации по сбору трассировок сети при возникновении времени ожидания подключения, чтобы проверить состояние сети.

Оценка времени и расположения времени ожидания подключения реплики

Просмотрите журнал, частоту и тенденции времени ожидания подключения. Использование сообщений, которые вы найдете в журнале ошибок SQL Server, является отличным способом сделать это. Где сообщается время ожидания подключения? Они постоянно сообщаются о первичной или вторичной реплике? Когда произошли ошибки? Они произошли в определенной неделе месяца, дня недели или времени дня дня? Соответствуют ли другие запланированные операции обслуживания или пакетной обработки времени ожидания подключения? Эта оценка поможет вам определить основную причину и сопоставить время ожидания подключения.

Просмотрите расширенный сеанс событий AlwaysOn_health

Расширенный AlwaysOn_health сеанс событий был улучшен, чтобы включить ucs_connection_setup событие, которое активируется при установке реплики с ее репликой партнера. Это может быть полезно при устранении неполадок времени ожидания подключения.

Примечание.

Расширенное ucs_connection_setup событие было добавлено в последние накопительные обновления SQL Server. Для наблюдения за этим расширенным событием необходимо запустить последние накопительные обновления.

Запрос представлений распределенного управления AlwaysOn (ДИНАМИЧЕСКИЕ административные представления)

Для получения дополнительных сведений о состоянии подключения реплики можно запрашивать динамические административные представления Always On. Этот запрос сообщает только подключенное состояние и все ошибки, связанные с временем ожидания подключения. Если проблемы с подключением периодически возникают, запрос может не записывать отключенное состояние легко.

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

В следующем примере показано устойчивое состояние отключения, так как конечная точка зеркального отображения на первичной реплике остановлена. Запросив первичную реплику, dmV AlwaysOn может сообщать о первичной и всех вторичных репликах (конечная точка отключена на первичной реплике).

Снимок экрана: устойчивое состояние отключения, так как конечная точка зеркального отображения на первичной реплике остановлена.

Запросив вторичную реплику, динамические административные представления Always On сообщают только о вторичной реплике.

Снимок экрана, показывающий устойчивое состояние отключения, так как конечная точка зеркального отображения на вторичной реплике остановлена.

Просмотр расширенного сеанса событий AlwaysOn

  1. Подключитесь к каждой реплике с помощью обозреватель объектов SQL Server Management Studio (SSMS) и откройте расширенные AlwaysOn_health файлы событий.

  2. В SSMS перейдите в раздел "Открыть файл>", а затем выберите "Объединить расширенные файлы событий".

  3. Нажмите кнопку Добавить.

  4. В диалоговом окне "Открыть файл" перейдите к файлам в каталоге SQL Server \LOG.

  5. Нажмите клавиши Control, а затем выберите файлы, имя которых начинается с "AlwaysOn_healthxxx.xel".

  6. Нажмите кнопку "Открыть" и нажмите кнопку "ОК".

    В SSMS должно появиться новое окно с вкладками, в которое отображаются события AlwaysOn.

    На следующем снимку экрана показаны AlwaysOn_health данные из вторичной реплики. В первом описанном поле отображается потеря подключения после остановки конечной точки на первичной реплике. Во втором описанном поле показан сбой подключения, который возникает при следующем попытке вторичной реплики подключиться к первичной реплике.

    Снимок экрана: AlwaysOn_health данные из вторичной реплики.

Убедитесь, что события, не являющиеся результатом ожидания подключения

Одной из наиболее распространенных причин, по которым реплика доступности не может обслуживать подключение реплики партнера, является планировщиком без предоставления. Дополнительные сведения о неурожающих планировщиках см. в разделе "Устранение неполадок с планированием и получением SQL Server".

SQL Server отслеживает события планировщика, не возвращающие значения 5–10 секунд. Он сообщает об этих событиях в точке TrackingNonYieldingScheduler данных в выходных sp_server_diagnostics query_processing данных компонента.

Чтобы проверить наличие неурожающих событий, которые могут привести к истечении времени ожидания подключения реплики, выполните следующие действия.

  1. Создайте задание агента SQL, записывающее sp_server_diagnostics каждые пять секунд.

  2. Запланируйте это задание на сервере, который не сообщает время ожидания подключения. То есть если сервер A реплики сообщает время ожидания подключения реплики в журнале ошибок, настройте задание агента SQL на реплике партнера, Сервер B. Кроме того, если во обеих репликах отображаются время ожидания подключения, создайте задание на обоих репликах.

  3. Выполните следующий пакетный файл, чтобы создать задание, которое выполняется sp_server_diagnostics каждые пять секунд, добавляет выходные данные в текстовый файл, а затем запускает задание. Команда в следующем примере sp_server_diagnostics 5 выполняется каждые пять секунд. Таким образом, не нужно планировать выполнение этого задания каждые пять секунд, просто запустить задание, и он будет выполняться до остановки каждые пять секунд:

    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'

    Примечание.

    В этих командах измените @output_file_name допустимый путь и укажите имя файла.

Анализ результатов

Когда сообщается время ожидания подключения, обратите внимание на метку времени ожидания события времени ожидания, отображаемого в журнале ошибок SQL Server. Для реплик в следующем примере SQL19AGN1 сообщалось о времени ожидания подключения реплики. Поэтому задание агента SQL было создано в SQL19AGN2реплике партнера. Затем в журнале ошибок сообщалось время ожидания подключения в SQL19AGN1 07:24:31.

Снимок экрана: время ожидания подключения, указанное в журнале ошибок SQL19AGN1.

Затем выходные данные задания агента SQL, выполняющегося sp_server_диагностика, проверяются в течение указанного времени, в частности, при TrackingNonYieldingScheduler проверке точки данных в выходных query_processing данных компонента. Выходные данные сообщают о том, что планировщик без выходных данных отслеживался (как шестнадцатеричное значение ненулевого значения) на сервере SQL19AGN2 (в 07:24:33) примерно в то время, когда время ожидания подключения реплики было сообщено в SQL19AGN1 (в 07:24:31).

Примечание.

sp_server_diagnostics Следующие выходные данные объединяются для отображения create_time (метки времени) и query_processing TrackingNonYieldingScheduler результатов.

Снимок экрана: sp_server_диагностика выходные данные были сцеплены.

Изучение события планировщика без получения

Если вы проверили на предыдущих шагах диагностики, что событие, не возвращающее значение, вызвало время ожидания подключения реплики:

  1. Определите рабочие нагрузки, выполняемые в SQL Server во время выполнения событий без получения.

  2. Как и время ожидания подключения реплики, найдите тенденции в этих событиях в течение месяца, дня или недели, которые они происходят.

  3. Сбор трассировки монитора производительности в системе, в которой обнаружено событие, не являющееся результатом.

  4. Соберите ключевые счетчики производительности для системных ресурсов, включая процессор::%Процессор, память::Доступные MBytes, логический диск::Avg Disk Queue Length и Логический диск::Avg Disk sec/Transfer.

  5. Если это необходимо, откройте инцидент поддержки SQL Server, чтобы получить дополнительную помощь в поиске первопричин для этих неурожающих событий. Поделитесь журналами, собранными для дальнейшего анализа.

Расширенная сбор данных: сбор трассировки сети во время ожидания подключения

Если предыдущий диагноз приложения SQL Server не дал первопричину, следует проверить сеть. Для успешного анализа сети требуется собрать трассировку сети, которая охватывает время ожидания подключения.

Следующая процедура запускает трассировку сети Windows netsh на репликах, на которых сообщается время ожидания подключения в журналах ошибок SQL Server. Задача запланированного события Windows активируется при записи одной из ошибок подключения SQL Server в журнале приложений. Запланированная задача выполняет команду, чтобы остановить netsh трассировку сети, чтобы данные трассировки сети ключей не были перезаписаны. Эти действия также предполагают путь *F:* для журналов пакетной и трассировки. Настройте этот путь к вашей среде.

  1. Запустите сетевую трассировку, как показано в следующем фрагменте кода, на двух репликах, на которых происходит время ожидания подключения:

    netsh trace start capture=yes persistent=yes overwrite=yes maxsize=500 tracefile=f:\trace.etl

  2. Создайте запланированные задачи Windows, которые останавливают netsh трассировку событий 35206 или 35267. Эти задачи можно создать в административной командной строке:

    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 HIGHEST

  3. После возникновения события и остановки и записи трассировок сети можно удалить ONEVENT задачи:

    PS C:\Users\sqladmin> Schtasks /Delete /tn Event35206Task /F
PS C:\Users\sqladmin> Schtasks /Delete /tn Event35267Task /F

Анализ трассировки сети выходит за рамки этого средства устранения неполадок. Если вы не можете интерпретировать трассировку сети, обратитесь в службу поддержки Microsoft SQL Server и предоставьте трассировку вместе с другими запрошенными файлами журнала для анализа первопричин.

Что еще можно сделать для устранения времени ожидания подключения?

Группа SESSION_TIMEOUTдоступности по умолчанию настраивается в течение 10 секунд. Возможно, вы сможете уменьшить время ожидания подключения, изменив свойство реплики SESSION_TIMEOUT группы доступности. Этот параметр имеет значение для каждой реплики. Настройте его для первичной и каждой затронутой вторичной реплики. Ниже приведен пример синтаксиса. Значение по умолчанию SESSION_TIMEOUT — 10. Таким образом, можно использовать 15 в качестве следующего значения.

ALTER AVAILABILITY GROUP ag
MODIFY REPLICA ON 'SQL19AGN1' WITH (SESSION_TIMEOUT = 15);