10.10.25

Контроль состояния распределённой группы доступности: T-SQL и Zabbix

Автор: Pablo Echeverria, Distributed Availability Group Health: T-SQL and Zabbix

После создания распределённой группы доступности по шагам из моей статьи «SQL Server 2022: Распределённая группа доступности без кластера», как проверить, что синхронизация между дата-центрами в порядке?

В SQL Server Management Studio, если щёлкнуть правой кнопкой по распределённой группе доступности, вы заметите, что нет панели мониторинга, подтверждающей корректную синхронизацию данных между дата-центрами:

Даже если проверять панели первичного и вторичного дата-центров по отдельности, вы не увидите, есть ли проблема «между ними».

Чтобы решить это, можно выполнить запрос на первичном узле первичного дата-центра:

CREATE TABLE #Info (
  AG_Name SYSNAME, is_distributed BIT, is_chained BIT, replica_server_name VARCHAR(256), [Database] SYSNAME,
  role_desc VARCHAR(10), synchronization_health TINYINT, synchronization_health_desc VARCHAR(20), log_send_queue_size BIGINT, log_send_rate BIGINT,
  redo_queue_size BIGINT, redo_rate BIGINT, suspend_reason_desc VARCHAR(30), last_sent_time DATETIME, last_hardened_time DATETIME,
  last_redone_time DATETIME, last_commit_time DATETIME, secondary_lag_seconds BIGINT);
INSERT INTO #Info
SELECT ag.name AG_Name, ag.is_distributed, 0 'is_chained', ar.replica_server_name AS [AG], dbs.name AS [Database],
       ars.role_desc, drs.synchronization_health, drs.synchronization_health_desc, drs.log_send_queue_size, drs.log_send_rate,
       drs.redo_queue_size, drs.redo_rate, drs.suspend_reason_desc, drs.last_sent_time, drs.last_hardened_time,
       drs.last_redone_time, drs.last_commit_time, drs.secondary_lag_seconds
  FROM sys.databases dbs
 INNER JOIN sys.dm_hadr_database_replica_states drs ON dbs.database_id = drs.database_id
 INNER JOIN sys.availability_groups ag ON drs.group_id = ag.group_id
 INNER JOIN sys.dm_hadr_availability_replica_states ars ON ars.replica_id = drs.replica_id
 INNER JOIN sys.availability_replicas ar ON ar.replica_id = ars.replica_id
UNION ALL
SELECT ag.name AG_Name, 1 'is_distributed', 1 'is_chained', ar.replica_server_name AS [AG], dbs.name AS [Database],
       ars.role_desc, drs.synchronization_health, drs.synchronization_health_desc, drs.log_send_queue_size, drs.log_send_rate,
       drs.redo_queue_size, drs.redo_rate, drs.suspend_reason_desc, drs.last_sent_time, drs.last_hardened_time,
       drs.last_redone_time, drs.last_commit_time, drs.secondary_lag_seconds
  FROM [VSRVSQLON].master.sys.databases dbs
 INNER JOIN [VSRVSQLON].master.sys.dm_hadr_database_replica_states drs ON dbs.database_id = drs.database_id
 INNER JOIN [VSRVSQLON].master.sys.availability_groups ag ON drs.group_id = ag.group_id
 INNER JOIN [VSRVSQLON].master.sys.dm_hadr_availability_replica_states ars ON ars.replica_id = drs.replica_id
 INNER JOIN [VSRVSQLON].master.sys.availability_replicas ar ON ar.replica_id = ars.replica_id;
UPDATE i
   SET i.last_sent_time = i2.last_sent_time, i.secondary_lag_seconds = i2.secondary_lag_seconds, i.synchronization_health = i2.synchronization_health
  FROM #Info i
 INNER JOIN #Info i2 ON i2.[Database] = i.[Database] AND i2.is_distributed = 1 AND i2.is_chained = 0 AND i.is_distributed = 1 AND i.role_desc='PRIMARY';
DELETE
  FROM #Info
 WHERE is_distributed = 1 AND is_chained = 0;
ALTER TABLE #Info DROP COLUMN is_chained;
SELECT AG_Name, is_distributed, replica_server_name, [Database], role_desc,
       synchronization_health, synchronization_health_desc, log_send_queue_size, log_send_rate, redo_queue_size,
       redo_rate, suspend_reason_desc, last_sent_time, last_hardened_time, last_redone_time,
       last_commit_time, secondary_lag_seconds
  FROM #Info;

Этому запросу требуется линк на первичный узел вторичного дата-центра, для которого нужно задать безопасность «Be made using the login’s current security context». Обратите внимание, что мы копируем сведения о распределённой группе доступности (которые возвращает первичный дата-центр) в соответствующую запись (первичный узел вторичного дата-центра); это гарантирует, что мы вернём по одной строке на сервер.

Вот пример вывода для одной базы данных:

По мере роста числа баз данных вручную высматривать проблемы синхронизации становится невозможно; именно поэтому так полезны средства мониторинга вроде Zabbix:


С помощью Zabbix можно создать панель, показывающую состояние синхронизации каждой базы на всех серверах, и мгновенно сообщать о любых перерывах в синхронизации. Это также помогает не только администраторам БД, но и даже людям без технической подготовки — просто наблюдать.

Можно либо скачать готовый шаблон из моего репозитория на GitHub и адаптировать его, либо следовать подробному 10‑шаговому руководству ниже и создать свой.

Шаг 1: Создайте папку для пользовательских SQL‑запросов

Если вы установили Zabbix в «C:\Program Files\Zabbix Agent 2\», создайте папку: C:\Program Files\Zabbix Agent 2\Custom Queries\MSSQL

Затем добавьте файл с именем «AGDistributed.sql» и вставьте в него приведённую выше SQL‑команду.

Шаг 2: Настройте плагин Zabbix MSSQL

В «C:\Program Files\Zabbix Agent 2\zabbix_agent2.d\» откройте «mssql.conf» и добавьте строку:

Plugins.MSSQL.CustomQueriesDir=C:\Program Files\Zabbix Agent 2\Custom Queries\MSSQL

Шаг 3: Перезапустите агента Zabbix и проверьте подключения

Перезапустите агент после изменения конфигурации и после правки любого SQL‑файла:

net stop "Zabbix Agent 2"

Сначала проверьте подключение к базе данных, подставив теги реальными значениями без кавычек (см. в моей предыдущей статье «Monitor SQL Server using Zabbix», раздел про Macros):

& "C:\Program Files\Zabbix Agent 2\zabbix_agent2.exe" -t mssql.ping[{$MSSQL.URI}, {$MSSQL.USER}, {$MSSQL.PASSWORD}]

Затем проверьте работу пользовательского запроса (без расширения «.sql»):

& "C:\Program Files\Zabbix Agent 2\zabbix_agent2.exe" -t mssql.custom.query[{$MSSQL.URI}, {$MSSQL.USER}, {$MSSQL.PASSWORD}, AGDistributed]

И наконец, запустите службу:

net start "Zabbix Agent 2"

Шаг 4: Создайте новый шаблон в Zabbix

В Zabbix откройте Data Collection --> Templates. Нажмите Create template (в правом верхнем углу):

Заполните поля и нажмите Add для сохранения:

  • Template name: AG Distributed
  • Template groups: Templates/Database

Этот шаблон будет хранить все элементы, правила и панели.

Шаг 5: Добавьте элемент шаблона

В списке шаблонов найдите AG Distributed, затем нажмите Items:

Нажмите Create item (в правом верхнем углу) и заполните поля, затем нажмите Add:

  • Name: AG Distributed
  • Type: Zabbix agent (active)
  • Key: mssql.custom.query[{$MSSQL.URI},{$MSSQL.USER},{$MSSQL.PASSWORD},AGDistributed]
  • Type of information: Text
  • Update interval: 1m (одна минута; при необходимости увеличьте позже)

Этот элемент будет принимать результаты запроса и предоставлять их в формате JSON.

Шаг 6: Привяжите шаблон к узлу

Перейдите в Monitoring --> Hosts, выберите узел, где запущен экземпляр SQL Server, в разделе Configuration откройте Host:

В разделе Templates введите «AG Distributed» или используйте кнопку Select для поиска, затем нажмите Update:

Перейдите в Monitoring --> Hosts, выберите узел с экземпляром SQL Server, в разделе View выберите Latest data. В поле Name найдите «AG Distributed». Наведите курсор на колонку Last value — вы должны увидеть JSON‑вывод SQL‑скрипта:

Если в последней колонке вы видите значок Info, значит, есть проблема со сбором данных.

Шаг 7: Добавьте правило обнаружения (discovery rule)

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

В списке шаблонов найдите AG Distributed, затем нажмите Discovery:

Нажмите Create discovery rule и заполните поля:

  • Name: AG Distributed
  • Type: Dependent item
  • Key: ag.distributed
  • Master item: выберите «AG Distributed» (элемент, созданный на шаге 5)

Переключитесь на вкладку LLD macros (LLD — Low Level Discovery). Добавьте три макроса, однозначно идентифицирующих каждую запись:

  • {#AGNAME}: имя группы доступности
  • {#REPLICASERVERNAME}: имя экземпляра SQL Server
  • {#DATABASE}: имя базы данных


Пример: для базы данных DB1 в двух двухузловых группах доступности (SQLPRODAG и SQLPREMAG) вы получите:

  • SQLPRODAG CLOUD1 DB1
  • SQLPRODAG CLOUDDR2 DB1
  • SQLPREMAG ONPREMISE1 DB1
  • SQLPREMAG ONPREMISEDR2 DB1

LLD‑макросы — это ключи, которые Zabbix создаёт при получении данных. Они должны быть в верхнем регистре, без пробелов, без подчёркиваний и без кавычек. Выберите любые осмысленные имена — вы будете ссылаться на них далее.

JSONPath — это запрос, возвращающий значение; он должен точно соответствовать имени в JSON (названиям столбцов, определённых в нашем SQL‑запросе): регистр важен, пробелы и кавычки не допускаются.

После сохранения перейдите в Monitoring --> Hosts, выберите узел с экземпляром SQL Server и в разделе Configuration откройте Discovery. Убедитесь, что новое правило обнаружения активно, и каждый раз при изменении его или связанных объектов проверяйте колонку Info на наличие ошибок:

Шаг 8: Добавьте прототип элемента (item prototype)

В списке шаблонов найдите AG Distributed, затем нажмите Discovery. В правиле обнаружения нажмите Item prototypes:

Нажмите Create item prototype и введите данные:

  • Name: Synchronization health of {#AGNAME} {#REPLICASERVERNAME} {#DATABASE}
  • Type: Dependent item
  • Key: избегайте запятых, укажите synchronization_health[{#AGNAME}-{#REPLICASERVERNAME}-{#DATABASE}]
  • Type of information: Numeric (unsigned)
  • Master item: выберите «AG Distributed»

Перейдите на вкладку Preprocessing и нажмите Add для добавления шага предобработки:

  • Name: JSONPath
  • Parameters: $[?(@.AG_Name == "{#AGNAME}" && @.replica_server_name == "{#REPLICASERVERNAME}" && @.Database == "{#DATABASE}")].synchronization_health.first()

Эта предобработка ищет в JSON три ключа, определённые ранее в LLD‑макросах, затем получает поле synchronization_health и динамически создаёт элементы; например: Synchronization health of SQLPRODAG CLOUD1 DB1.

В разделе Monitoring --> Hosts --> Items поищите имя «synchronization health of» и убедитесь в отсутствии проблем в колонке Info у обнаруженных элементов:

Перейдите в Monitoring --> Hosts, выберите узел с экземпляром SQL Server и в разделе View выберите Latest data. Найдите по имени «synchronization health of», чтобы увидеть полученные значения, и убедитесь в отсутствии проблем в Info:

Шаг 9: Добавьте trigger prototype

В списке шаблонов найдите AG Distributed, затем нажмите Discovery. В правиле обнаружения нажмите Trigger prototypes:

Нажмите Create trigger prototype и введите данные:

  • Name: Synchronization health of {#AGNAME} {#REPLICASERVERNAME} {#DATABASE}
  • Severity: Disaster
  • Expression: нажмите Add и задайте:
    • Item: нажмите Select prototype и выберите Synchronization health of {#AGNAME} {#REPLICASERVERNAME} {#DATABASE}
    • Condition: <> (не равно)
    • Value: 2 (здорово; см. официальную документацию)

После добавления вы увидите оповещения в разделе Current problems или в проблемах выбранного узла:

Если у вас настроены почтовые уведомления для уровня Disaster, вы также получите письмо.

Шаг 10: Создайте панель (dashboard)

Наконец, визуализируем всё. В списке шаблонов найдите AG Distributed, затем нажмите Dashboards:

Нажмите Create dashboard (в правом верхнем углу), введите имя (например, «AG Distributed») и нажмите Apply. На полотне добавьте первый виджет в левый верхний угол:

  • Type: Graph
  • Name: введите имя дата‑центра и сервера, например, SQLPRODAG CLOUD1
  • Data set #1 item patterns: введите Synchronization health of SQLPRODAG CLOUD1 ***, подставив реальные имена дата‑центра и сервера; не забудьте про звёздочку
  • Draw: points
  • Во вкладке Problems включите show problems

Добавьте дополнительные виджеты по необходимости. Каждый виджет отображает до пятидесяти элементов, поэтому создавайте несколько — в зависимости от количества групп доступности, узлов и баз данных. По завершении нажмите Save changes (в правом верхнем углу). Теперь перейдите в Monitoring --> Hosts, выберите узел с экземпляром SQL Server. В разделе View откройте Dashboards и выберите AG Distributed, чтобы увидеть состояние синхронизации.

На этом настройка завершена. Благодаря этой панели вы получите ясное, почти в реальном времени представление о состоянии распределённой группы доступности. Обнаружение и устранение проблем синхронизации станет гораздо проще, а интерфейс достаточно понятен как для администраторов БД, так и для пользователей без технической подготовки.





Комментариев нет:

Отправить комментарий