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

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

  • Статья

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

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

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

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

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

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

Снимок экрана: реплики отчетов Always On панели мониторинга в состоянии

При просмотре журналов ошибок 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. Для наблюдения за этим расширенным событием необходимо запустить последние накопительные обновления.

Запрос Always On распределенных административных представлений

Дополнительные сведения о состоянии подключения реплика можно запросить 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

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

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

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

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

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

  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_diagnostics, проверяются примерно в указанное время, в частности, при проверке TrackingNonYieldingScheduler точки данных в выходных query_processing данных компонента. Выходные данные сообщают о том, что неурожайный планировщик отслеживался (в виде шестнадцатеричного значения, отличного от нуля) на серверных SQL19AGN2 (в 07:24:33) примерно в то время, когда время ожидания реплика подключения было сообщено SQL19AGN1 (в 07:24:31).

Примечание.

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

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

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

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

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

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

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

  4. Соберите ключевые счетчики производительности для системных ресурсов, включая Processor::% Processor Time, Memory::Available MBytes, Logical Disk::Avg Disk Queue иLogical Disk::Avg Disk/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);