Если у вас в инфраструктуре стоит Always On Availability Groups, вы наверняка бывали в такой ситуации: в SSMS всё зелёное, дашборд показывает «Synchronized», пользователи звонят с жалобами на тормоза. Смотришь на secondary — а там redo_queue_size 600 МБ, реплика отстаёт на полчаса. Ни одного алерта.

У нас это случилось на продуктивном кластере с 1С — бухгалтерия закрывала период, secondary молча отвалился в SYNCHRONIZING, а мы узнали только когда решили переключиться на него для планового обслуживания primary. Полтора часа redo queue. После этого стало понятно, что встроенный дашборд SSMS — это не мониторинг. Он не алертит, не хранит историю и не масштабируется. SQL Server Agent умеет слать письма, но это отдельная настройка на каждом экземпляре, и она ломается тихо.

Дальше — как мы это закрыли Zabbix'ом. T-SQL-запросы, UserParameters, шаблон, пороги триггеров. Всё это реально поставить за вечер.

Где в Always On копится задержка

Когда транзакция фиксируется на primary, она проходит такой путь до secondary: запись в лог на primary → отправка по сети → запись в лог на secondary → применение (redo) к базе данных.

Поток данных синхронной фиксации в SQL Server Always On Availability Groups
Поток данных синхронной фиксации в SQL Server Always On Availability Groups

Задержка накапливается в двух точках.

Log Send Queue — логи на primary, которые ещё не ушли на secondary. Растёт когда сеть между репликами перегружена, primary генерирует логи быстрее чем отправляет, или secondary временно недоступен.

Redo Queue — логи, которые secondary уже получил, но не успел применить к базе. Растёт при высокой нагрузке на secondary по CPU или I/O, когда redo-поток заблокирован долгим read-only запросом (если включён readable secondary), или при bulk-операциях и DDL на primary.

В терминах DR это напрямую связано с RPO и RTO. RPO — сколько данных теряете при failover. Считается как разница last_commit_time между primary и secondary для каждой базы. Отставание на 5 минут в asynchronous commit — это 5 минут потерянных транзакций при аварии. В synchronous commit данные не теряются, и штатный failover невозможен пока secondary не догнал primary. Принудительный FORCE_FAILOVER_ALLOW_DATA_LOSS — другое дело, но это аварийный инструмент, а не процедура.

RTO определяется размером redo queue: чем он больше, тем дольше secondary будет «догонять» после переключения.

Оба этих параметра видны через Dynamic Management Views. Их и будем мониторить.

DMV для мониторинга

Нам нужны три представления. Главное из них — sys.dm_hadr_database_replica_states. Оно возвращает строку для каждой базы на каждой реплике и содержит всё самое важное:

  • log_send_queue_size и redo_queue_size (обе в КБ);

  • скорости log_send_rate и redo_rate (КБ/сек);

  • synchronization_state_desc;

  • last_commit_time;

  • флаги is_local / is_primary_replica.

Важный нюанс: запрос с is_local=0 возвращает данные об удалённых репликах только на primary. На secondary видны только свои данные (is_local=1). Поэтому send queue мониторим на primary, redo queue — на каждом secondary.

Второе представление — sys.dm_hadr_availability_replica_states. Состояние подключения и синхронизации на уровне реплики: role_descconnected_state_descoperational_state_descsynchronization_health_desc. Отсюда берём информацию о disconnected и unhealthy репликах.

Третье — sys.dm_hadr_availability_group_states. Одна строка на группу доступности, агрегированный synchronization_health_desc. Это «верхнеуровневый» индикатор здоровья всей группы.

Что мониторить и пороги

Дальше — конкретика. Какие метрики собираем, какие пороги ставим и почему именно такие.

Метрика

Источник

Warning

Critical

Зачем

redo_queue_size (макс.)

dm_hadr_database_replica_states

100 МБ

500 МБ

При redo_rate ~50 МБ/сек: 100 МБ = 2 сек recovery, 500 МБ — уже заметно

log_send_queue_size (макс.)

dm_hadr_database_replica_states

100 МБ

500 МБ

Те же соображения + риск потери данных в async

RPO (задержка last_commit_time)

dm_hadr_database_replica_states

60 сек

300 сек

5 минут отставания — существенный риск

Состояние синхронизации

dm_hadr_availability_replica_states

PARTIALLY_HEALTHY

NOT_HEALTHY

Данные могут быть не защищены

Подключение реплики

dm_hadr_availability_replica_states

DISCONNECTED

Репликация остановлена

Не-HEALTHY реплик

dm_hadr_availability_replica_states

≥ 1

Любое отклонение требует внимания

Цифры 100/500 МБ — отправная точка для OLTP. Для нагруженных систем (та же 1С с сотней пользователей) порог предупреждения стоит снизить до 50 МБ. Для баз, в которые пишут раз в час — поднять. Ориентир — ваш нормальный redo_rate: разделите порог на него и получите время recovery. Если устраивает — порог правильный.

T-SQL-запросы

Каждый запрос должен возвращать ровно одно число — так работают UserParameters в Zabbix. Сначала проверяем в SSMS, потом несём на агент.

Максимальный redo_queue_size (выполнять на secondary):

SELECT ISNULL(MAX(redo_queue_size), 0)
FROM sys.dm_hadr_database_replica_states
WHERE is_local = 1
  AND is_primary_replica = 0;

Если реплика является primary или вообще не в AG — вернёт 0.

Максимальный log_send_queue_size (выполнять на primary):

SELECT ISNULL(MAX(drs.log_send_queue_size), 0)
FROM sys.dm_hadr_database_replica_states drs
WHERE drs.is_local = 0;

Только с primary видны данные об удалённых репликах.

RPO в секундах (выполнять на primary):

SELECT ISNULL(MAX(lag_seconds), 0)
FROM (
    SELECT
        drs_sec.database_id,
        DATEDIFF(
            SECOND,
            drs_sec.last_commit_time,
            drs_pri.last_commit_time
        ) AS lag_seconds
    FROM sys.dm_hadr_database_replica_states drs_sec
    JOIN sys.dm_hadr_availability_replica_states ars_sec
         ON ars_sec.replica_id = drs_sec.replica_id
    JOIN sys.dm_hadr_database_replica_states drs_pri
         ON  drs_pri.database_id        = drs_sec.database_id
         AND drs_pri.is_primary_replica  = 1
    WHERE ars_sec.role_desc = 'SECONDARY'
      AND drs_sec.is_local  = 0
) AS per_db_lag;

Здесь важно считать задержку попарно — для каждой базы сравниваем last_commit_time её primary-копии с secondary-копией, потом берём максимум. Наивный вариант с MIN по всем secondary и MAX по всем primary сравнивал бы активно пишущую базу с практически мёртвой и показывал бы фейковую задержку в часы.

Если secondary на долю секунды опережает primary (бывает из-за рассинхронизации часов), DATEDIFF вернёт отрицательное — обработаем в preprocessing.

Количество не-HEALTHY реплик (на primary):

SELECT COUNT(*)
FROM sys.dm_hadr_availability_replica_states
WHERE synchronization_health_desc != 'HEALTHY'
  AND role_desc != 'PRIMARY';

Primary исключаем намеренно — интересует состояние вторичных.

Отключённые реплики (на primary):

SELECT COUNT(*)
FROM sys.dm_hadr_availability_replica_states
WHERE connected_state_desc = 'DISCONNECTED';

Состояние группы доступности:

SELECT CASE synchronization_health_desc
    WHEN 'HEALTHY'           THEN 1
    WHEN 'PARTIALLY_HEALTHY' THEN 2
    WHEN 'NOT_HEALTHY'       THEN 3
    ELSE 0
END
FROM sys.dm_hadr_availability_group_states;

Текст в число: 1 = норма, 2 = предупреждение, 3 = критично. В Zabbix потом накинем value mapping.

Учётка для мониторинга и проверка из консоли

Создайте отдельный login — не sa, не sysadmin:

USE master;
GO

CREATE LOGIN [zabbix_monitor] WITH PASSWORD = 'YourStrongPassword123!',
    CHECK_EXPIRATION = OFF,
    CHECK_POLICY     = ON;
GO

GRANT VIEW SERVER STATE TO [zabbix_monitor];
GO

На SQL Server 2022+ хватит VIEW SERVER PERFORMANCE STATE вместо VIEW SERVER STATE — более узкое право, но для DMV достаточно. Доступ к пользовательским базам не нужен.

Проверяем что sqlcmd отдаёт число:

sqlcmd -S localhost -U zabbix_monitor -P YourStrongPassword123! -h -1 -W -Q "SELECT ISNULL(MAX(redo_queue_size),0) FROM sys.dm_hadr_database_replica_states WHERE is_local=1 AND is_primary_replica=0"

Должно быть одно число без заголовков. Для именованного экземпляра: -S localhost\INSTANCENAME.

Настройка агента Zabbix

Длинные запросы в одну строку zabbix_agentd.conf — нечитаемо. Выносим в файлы:

mkdir C:\zabbix\scripts\mssql

Каждый запрос — отдельный .sql. В начало каждого файла добавляем SET NOCOUNT ON; — без этого sqlcmd выплёвывает (1 rows affected) и Zabbix не может распарсить число.

Содержимое файлов — те же запросы что выше, только с SET NOCOUNT ON; в первой строке. Их шесть: ag_redo_queue.sqlag_send_queue.sqlag_rpo_seconds.sqlag_unhealthy_replicas.sqlag_disconnected_replicas.sqlag_group_health.sql.

Кстати, в файле RPO-запроса используем упрощённую версию — ту же попарную логику, просто в .sql-файле удобнее читать многострочный запрос.

UserParameters в конфиге агента (или в отдельном .conf через Include):

# Always On Availability Groups
UserParameter=mssql.ag.redo_queue,sqlcmd -S localhost -U zabbix_monitor -P YourStrongPassword123! -h -1 -W -i "C:\zabbix\scripts\mssql\ag_redo_queue.sql"
UserParameter=mssql.ag.send_queue,sqlcmd -S localhost -U zabbix_monitor -P YourStrongPassword123! -h -1 -W -i "C:\zabbix\scripts\mssql\ag_send_queue.sql"
UserParameter=mssql.ag.rpo_seconds,sqlcmd -S localhost -U zabbix_monitor -P YourStrongPassword123! -h -1 -W -i "C:\zabbix\scripts\mssql\ag_rpo_seconds.sql"
UserParameter=mssql.ag.unhealthy_replicas,sqlcmd -S localhost -U zabbix_monitor -P YourStrongPassword123! -h -1 -W -i "C:\zabbix\scripts\mssql\ag_unhealthy_replicas.sql"
UserParameter=mssql.ag.disconnected_replicas,sqlcmd -S localhost -U zabbix_monitor -P YourStrongPassword123! -h -1 -W -i "C:\zabbix\scripts\mssql\ag_disconnected_replicas.sql"
UserParameter=mssql.ag.group_health,sqlcmd -S localhost -U zabbix_monitor -P YourStrongPassword123! -h -1 -W -i "C:\zabbix\scripts\mssql\ag_group_health.sql"

Для именованного экземпляра — -S localhost\INSTANCENAME. Насчёт пароля в конфиге — да, не идеал. Если есть возможность, лучше Windows Authentication: агент работает под доменной учёткой с нужными правами, в sqlcmd вместо -U/-P ставим -E.

Перезапустили агент — проверяем с сервера Zabbix:

zabbix_get -s <IP_сервера> -k mssql.ag.redo_queue
zabbix_get -s <IP_сервера> -k mssql.ag.group_health

Числа — хорошо. Пустота — проблема в sqlcmd или правах. ZBX_NOTSUPPORTED — проверьте AllowKey (Zabbix 5.0+).

Шаблон Zabbix

Создаём Template SQL Server Always On. Шесть элементов данных, все типа Zabbix agent, интервал 60 секунд:

  • mssql.ag.redo_queue — KB, Numeric (unsigned);

  • mssql.ag.send_queue — KB, Numeric (unsigned);

  • mssql.ag.rpo_seconds — s, Numeric (unsigned);

  • mssql.ag.unhealthy_replicas — Numeric (unsigned);

  • mssql.ag.disconnected_replicas — Numeric (unsigned);

  • mssql.ag.group_health — Numeric (unsigned), с Value Mapping: 1 → HEALTHY, 2 → PARTIALLY_HEALTHY, 3 → NOT_HEALTHY, 0 → UNKNOWN.

Для RPO добавляем preprocessing — JavaScript:

return Math.max(0, Number(value));

Защита от отрицательных значений при рассинхронизации часов.

Триггеры

Redo Queue:

  • Warning: last(/Template SQL Server Always On/mssql.ag.redo_queue) > 102400 (100 МБ)

  • High: last(/Template SQL Server Always On/mssql.ag.redo_queue) > 512000 (500 МБ)

Log Send Queue — аналогично, те же пороги.

RPO:

  • Warning: > 60 (минута)

  • High: > 300 (5 минут)

Не-HEALTHY реплики:

  • Average: last(…/mssql.ag.unhealthy_replicas) > 0

DISCONNECTED реплики:

  • High: last(…/mssql.ag.disconnected_replicas) > 0

Группа не HEALTHY:

  • Average: last(…/mssql.ag.group_health) > 1

Имеет смысл заменить жёсткие цифры в триггерах на макросы {$AG_REDO_WARN}{$AG_REDO_CRIT} и т.д. — тогда пороги можно переопределять на уровне хоста без копирования шаблона.

Графики

Два графика — минимум для разбора инцидентов. Первый: redo_queue + send_queue на одном (Line, Y axis в KB). Второй: rpo_seconds + unhealthy_replicas.

Куда вешать шаблон

На все узлы AG. На primary будут работать все элементы, но redo_queue вернёт 0 (primary — не secondary). На secondary — redo_queue и rpo_seconds покажут реальные данные, а send_queue вернёт 0. Это нормально: мониторим очередь там, где она копится.

Мониторинг кворума WSFC

Always On работает поверх Windows Server Failover Clustering. Состояние кластера через SQL DMV не видно — нужен PowerShell с модулем FailoverClusters.

Зачем это отдельно: представьте, один узел кластера потерял сетевое соединение. SQL-шный Always On может ещё показывать «connected» — а кластер уже потерял голос. Следующий узел, который уйдёт в оффлайн, обрушит кворум целиком. Или другая ситуация — плановое обслуживание одного из трёх узлов, кворум держится на двух. Это нормально, но хрупко: падение ещё одного = катастрофа. Мониторинг должен об этом предупреждать.

Шесть скриптов в C:\zabbix\scripts\wsfc\:

cluster_nodes_total.ps1:

(Get-ClusterNode).Count

cluster_nodes_up.ps1:

(Get-ClusterNode | Where-Object { $_.State -eq 'Up' }).Count

cluster_quorum_state.ps1:

$quorum = Get-ClusterQuorum
$resource = $quorum.QuorumResource
if ($null -eq $resource) {
    1
} else {
    switch ($resource.State) {
        'Online'  { 1 }
        'Offline' { 3 }
        'Failed'  { 3 }
        default   { 2 }
    }
}

Возвращает 1 (кворум есть), 2 (непонятно) или 3 (кворума нет). Когда кворум типа Node Majority — без свидетеля — QuorumResource будет $null. Это нормально: живость определяется голосами узлов, $null = кворум есть.

cluster_node_state.ps1:

(Get-ClusterNode | Where-Object { $_.State -ne 'Up' }).Count

cluster_quorum_votes.ps1:

$votes = 0
Get-ClusterNode | Where-Object { $_.State -eq 'Up' } | ForEach-Object { $votes += $_.DynamicWeight }
$q = Get-ClusterQuorum
if ($null -ne $q.QuorumResource -and $q.QuorumResource.State -eq 'Online') { $votes += 1 }
$votes

Считает активные голоса кворума: живые узлы (по DynamicWeight) плюс свидетель, если он в Online. Это важно — Get-ClusterNode возвращает только узлы, а File Share Witness или Cloud Witness — это отдельный голос, который не попадает в Get-ClusterNode. Без учёта свидетеля триггер хрупкого кворума будет врать: в двухузловом кластере с FSW три голоса, а не два.

cluster_quorum_votes_total.ps1:

$votes = 0
Get-ClusterNode | ForEach-Object { $votes += $_.NodeWeight }
$q = Get-ClusterQuorum
if ($null -ne $q.QuorumResource) { $votes += 1 }
$votes

Общее количество голосов в кластере — все узлы (по NodeWeight) плюс свидетель, если он сконфигурирован.

UserParameters:

# Windows Server Failover Cluster
UserParameter=wsfc.nodes.total,powershell -NonInteractive -NoProfile -ExecutionPolicy Bypass -File "C:\zabbix\scripts\wsfc\cluster_nodes_total.ps1"
UserParameter=wsfc.nodes.up,powershell -NonInteractive -NoProfile -ExecutionPolicy Bypass -File "C:\zabbix\scripts\wsfc\cluster_nodes_up.ps1"
UserParameter=wsfc.nodes.down,powershell -NonInteractive -NoProfile -ExecutionPolicy Bypass -File "C:\zabbix\scripts\wsfc\cluster_node_state.ps1"
UserParameter=wsfc.quorum.state,powershell -NonInteractive -NoProfile -ExecutionPolicy Bypass -File "C:\zabbix\scripts\wsfc\cluster_quorum_state.ps1"
UserParameter=wsfc.quorum.votes_up,powershell -NonInteractive -NoProfile -ExecutionPolicy Bypass -File "C:\zabbix\scripts\wsfc\cluster_quorum_votes.ps1"
UserParameter=wsfc.quorum.votes_total,powershell -NonInteractive -NoProfile -ExecutionPolicy Bypass -File "C:\zabbix\scripts\wsfc\cluster_quorum_votes_total.ps1"

В шаблон добавляем шесть элементов данных (wsfc.nodes.totalwsfc.nodes.upwsfc.nodes.downwsfc.quorum.statewsfc.quorum.votes_upwsfc.quorum.votes_total), Value Mapping для кворума (1 → QUORUM OK, 2 → UNKNOWN, 3 → NO QUORUM) и три триггера:

  • High: узлы не в Up: last(…/wsfc.nodes.down) > 0

  • Disaster: кворум потерян: last(…/wsfc.quorum.state) = 3

  • Average: хрупкий кворум — кластер работает на минимуме голосов: last(…/wsfc.quorum.votes_up) = (last(…/wsfc.quorum.votes_total) / 2 + 1). Ещё один голос потеряется — и всё.

Обратите внимание: триггер хрупкого кворума использует votes_up / votes_total, а не nodes.up / nodes.total. Разница принципиальна. В двухузловом кластере с File Share Witness три голоса (2 узла + FSW), и когда все живы — 3 ≠ (3/2+1) = 2, триггер молчит. Если один узел или свидетель упадёт — 2 = 2, триггер сработает. Без учёта свидетеля формула бы считала что голосов всего два и 2 = (2/2+1) = 2 — ложное срабатывание при полностью здоровом кластере.

Учётка агента должна быть в локальных администраторах или иметь Read на кластерный объект. На практике проще первый вариант — агент и так работает как сервис.

Когда триггер сработал: что делать

Мониторинг без плана действий — это просто будильник без смысла. Дальше — конкретные шаги, которые можно пройти за 5 минут.

Redo Queue растёт

Secondary получает логи, но не успевает применять. Причина — одна из трёх.

Подключаемся к secondary и смотрим на redo-поток:

SELECT r.session_id, r.command, r.wait_type, r.wait_time,
       r.blocking_session_id, DB_NAME(r.database_id) AS db_name
FROM sys.dm_exec_requests r
WHERE r.command IN ('PARALLEL REDO HELP TASK', 'PARALLEL REDO TASK',
                    'DB STARTUP');

LCK_* в wait_type — redo заблокирован read-only запросом. Ищем и убиваем долгий SELECT. PAGEIOLATCH_* — проблема с дисками. Строк нет вообще — redo-поток не активен, очередь должна сама рассосаться.

Если redo-поток работает, но очередь всё равно растёт — смотрим что происходит на primary. Может, там bulk insert или перестроение индексов:

SELECT r.session_id, r.status, r.command,
       SUBSTRING(st.text, 1, 200) AS query_text,
       r.total_elapsed_time / 1000 AS elapsed_sec
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.status = 'running'
ORDER BY r.total_elapsed_time DESC;

Log Send Queue растёт

Primary копит логи быстрее чем отправляет. Первым делом — сеть: пинг, traceroute, не идёт ли параллельно бэкап или репликация по тому же каналу.

Потом — CPU на primary. Если потоков не хватает, отправка тоже тормозит:

SELECT scheduler_id, cpu_id, current_tasks_count,
       runnable_tasks_count, active_workers_count
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
ORDER BY runnable_tasks_count DESC;

runnable_tasks_count больше 5–10 на ядро — это CPU pressure.

Ещё стоит проверить flow control — SQL Server иногда сам тормозит отправку, если считает что secondary не справляется:

SELECT counter_name, instance_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Availability Replica%'
  AND counter_name IN ('Flow Control/sec', 'Flow Control Time (ms/sec)');

Ненулевые значения — SQL Server жмёт на тормоз.

Важный момент для WAN-реплик на Windows Server 2016+: проверьте настройку TCP:

Get-NetTCPSetting | Select-Object SettingName, CwndRestart

Если CwndRestart = True — это может вызывать просадки. Установите False:

Set-NetTCPSetting -SettingName InternetCustom -CwndRestart False

Реплика DISCONNECTED

Данные не реплицируются вообще. Смотрим Event Log на обоих серверах:

Get-WinEvent -LogName 'Microsoft-Windows-FailoverClustering/Operational' -MaxEvents 50 | Where-Object { $_.LevelDisplayName -ne 'Information' } | Format-List TimeCreated, Message
Get-WinEvent -LogName 'Application' -MaxEvents 50 | Where-Object { $_.ProviderName -eq 'MSSQLSERVER' -and $_.LevelDisplayName -ne 'Information' } | Format-List TimeCreated, Message

На SQL Server — детали подключения:

SELECT ar.replica_server_name, ars.connected_state_desc,
       ars.operational_state_desc,
       ars.last_connect_error_description,
       ars.last_connect_error_number,
       ars.last_connect_error_timestamp
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar ON ar.replica_id = ars.replica_id;

last_connect_error_description обычно прямо говорит что произошло: сертификат, файрвол, endpoint. Кстати, проверьте что endpoint жив:

SELECT name, state_desc, type_desc, port
FROM sys.tcp_endpoints
WHERE type_desc = 'DATABASE_MIRRORING';

Если state_desc не STARTED:

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

Узел кластера недоступен

Get-ClusterNode | Select-Object Name, State, NodeWeight

Посмотрите на голосование — почему кворум ещё держится и насколько это хрупко:

Get-ClusterNode | Select-Object Name, NodeWeight, DynamicWeight

Если нужно вывести узел планово — используйте Suspend-ClusterNode -Name "NODE2" -Drain. Он переведёт роли на другие узлы перед приостановкой.

Порядок внедрения

Весь процесс — часа на два, если sqlcmd и PowerShell уже на месте. Начинаете с SQL-серверов: создаёте login zabbix_monitor с VIEW SERVER STATE, раскладываете шесть .sql-файлов по C:\zabbix\scripts\mssql\, добавляете UserParameters, перезапускаете агент. На кластерных узлах — то же самое с шестью .ps1-скриптами в C:\zabbix\scripts\wsfc\. В Zabbix создаёте шаблон с 12 элементами, 12 триггерами, двумя графиками, вешаете на все узлы AG. Проверяете zabbix_get'ом — если числа пошли, всё работает.

Для проверки боем: запустите тяжёлый запрос на secondary — redo_queue должен поползти вверх. Остановите агент на одном из узлов — должен сработать триггер на disconnected.

Что дальше

Это базовый мониторинг, который закрывает процентов 90 ситуаций. Если хочется большего — есть несколько направлений.

Для нескольких групп доступности имеет смысл сделать Low-Level Discovery через sys.availability_groups, чтобы элементы создавались автоматически. Можно мониторить историю failover'ов из SQL Server Event Log и sys.dm_xe_sessions. Можно считать RTO на лету — redo_queue_size / redo_rate даёт время восстановления в секундах, это информативнее голого порога по размеру очереди.

Ну и Grafana. Данные из Zabbix отлично ложатся туда через datasource-плагин, и исторические графики очередей помогают ловить паттерны — например, что каждый день в 22:00 redo queue вырастает до 200 МБ из-за ночного отчёта. С такими данными уже можно не тушить пожар, а предотвращать.