MS SQL

Microsoft SQL Server

Расширение предназначено для удалённого мониторинга состояния и производительности экземпляров Microsoft SQL Server с помощью SQL-запросов, выполняемых контроллером расширений.

Идентификатор: ru.ruscomtech.extension.sql-server.

Расширение контролирует экземпляры и базы данных SQL Server, блокировки, память, транзакционные журналы, резервное копирование, задания SQL Server Agent и группы доступности Always On.

Возможности

Расширение выполняет:

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

Мониторинг выполняется удалённо. Устанавливать ЕдиныйАгент непосредственно на сервер SQL Server не обязательно.

Поддерживаемые системы

Официально поддерживаются:

• Microsoft SQL Server редакций Enterprise, Standard, Developer, Web и Express на Windows;
• Azure SQL Database;
• Azure SQL Managed Instance;
• группы доступности Always On.

Расширение рассчитано на версии SQL Server, находящиеся на основной или расширенной поддержке Microsoft.

Работа с SQL Server на Linux и AWS RDS возможна, но зависит от доступности используемых системных представлений.

Не рекомендуется одновременно контролировать один экземпляр с помощью нескольких конфигураций или разных основных версий расширения: это может привести к дублированию метрик и сущностей.

Схема работы

Microsoft SQL Server
        |
        | TCP, SQL-запросы
        v
АктивныйШлюз / EEC
        |
        | метрики, логи и топология
        v
Ключ-АСТРОМ

Все устройства выбранной группы АктивныхШлюзов должны иметь сетевой доступ к контролируемому экземпляру SQL Server.

Требования к подключению

Перед настройкой подготовьте:

• адрес сервера SQL Server;
• TCP-порт экземпляра, обычно 1433;
• имя базы данных;
• отдельную учётную запись мониторинга;
• группу АктивныхШлюзов, с которой доступен сервер;
• разрешение сетевого доступа между АктивнымШлюзом и SQL Server;
• доверенный сертификат, если используется шифрование TLS.

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

Для получения общей информации об экземпляре в качестве базы подключения рекомендуется использовать master.

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

Создание пользователя мониторинга

Набор разрешений зависит от включённых наборов функций. Не выдавайте учётной записи административные роли без необходимости.

Пример создания отдельной учётной записи:

USE [master];
GO

CREATE LOGIN [keyastrom_monitor]
WITH PASSWORD = 'ЗАМЕНИТЕ_НА_НАДЕЖНЫЙ_ПАРОЛЬ';
GO

CREATE USER [keyastrom_monitor]
FOR LOGIN [keyastrom_monitor];
GO

GRANT VIEW ANY DATABASE TO [keyastrom_monitor];
GRANT VIEW ANY DEFINITION TO [keyastrom_monitor];
GO

Для SQL Server 2019 и более ранних версий:

GRANT VIEW SERVER STATE TO [keyastrom_monitor];
GO

Для SQL Server 2022 и более новых версий:

GRANT VIEW SERVER PERFORMANCE STATE TO [keyastrom_monitor];
GO

Для чтения сведений о резервных копиях и заданиях создайте пользователя в базе msdb и предоставьте права чтения:

USE [msdb];
GO

CREATE USER [keyastrom_monitor]
FOR LOGIN [keyastrom_monitor];
GO

ALTER ROLE [db_datareader]
ADD MEMBER [keyastrom_monitor];
GO

Для сбора сессий и данных Query Store добавьте пользователя в контролируемую базу:

USE [имя_базы_данных];
GO

CREATE USER [keyastrom_monitor]
FOR LOGIN [keyastrom_monitor];
GO

GRANT VIEW DATABASE STATE TO [keyastrom_monitor];
GO

Если пользователь уже существует в нужной базе, повторно выполнять CREATE USER не требуется.

Для Azure SQL Database права назначаются с учётом используемой модели аутентификации и уровня службы. Для просмотра всех баз рекомендуется подключаться к базе master.

Подготовка Query Store

Сбор наиболее продолжительных запросов требует включённого Query Store.

Проверить его состояние можно запросом:

SELECT actual_state_desc
FROM sys.database_query_store_options;

При необходимости Query Store включается администратором базы данных:

ALTER DATABASE [имя_базы_данных]
SET QUERY_STORE = ON;

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

Установка и настройка

1. Загрузите пакет расширения в Ключ-АСТРОМ.
2. Откройте установленное расширение Microsoft SQL Server.
3. Создайте конфигурацию мониторинга.
4. Укажите имя конфигурации.
5. Выберите группу АктивныхШлюзов.
6. Добавьте конечную точку SQL Server.
7. Укажите адрес сервера и TCP-порт.
8. Укажите базу данных.
9. Введите имя пользователя и пароль либо выберите учётные данные из хранилища секретов.
10. При необходимости включите защищённое соединение.
11. Выберите наборы функций.
12. Сохраните и активируйте конфигурацию.

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

Параметр	Описание
Адрес	DNS-имя или IP-адрес сервера SQL Server.
Порт	TCP-порт экземпляра SQL Server. Стандартное значение — 1433.
База данных	База, используемая для подключения. Для общего мониторинга экземпляра рекомендуется master.
Имя пользователя	Учётная запись с правами чтения необходимых системных представлений.
Пароль	Пароль пользователя мониторинга или ссылка на запись в хранилище секретов.
Шифрование	Использование TLS при подключении к SQL Server.
Наборы функций	Группы метрик и логов, которые должна собирать конфигурация.
Longest queries timeout	Максимальное время выполнения запроса сбора наиболее продолжительных запросов. Значение по умолчанию — 120 секунд. Рекомендуемый максимальный предел — 290 секунд.

Наборы функций

Набор	Назначение
Always On	Группы доступности, реплики, состояние синхронизации, очереди отправки журналов и восстановления.
Backups	Возраст, размер, тип и состояние резервных копий.
Database files	Размер, занятое и свободное пространство файлов баз данных.
Jobs	Текущие задания SQL Server Agent и задания, завершившиеся с ошибкой.
Latches	Ожидания защёлок и их средняя продолжительность.
Locks	Блокировки, взаимоблокировки, ожидания и тайм-ауты.
Memory	Физическая, виртуальная и выделенная память, показатели буферного кеша.
Queries	Компиляции, повторные компиляции и наиболее продолжительные запросы.
Replication	Обмен данными с репликами SQL Server.
Sessions	Активные сессии по пользователям и доменам.
Transaction logs	Размер, заполнение, рост, усечение и ожидания сброса транзакционного журнала.

Основные показатели экземпляра и состояние баз данных собираются независимо от дополнительных наборов.

Большинство метрик обновляется раз в минуту. Информация о продолжительных запросах, файлах, резервных копиях и заданиях собирается раз в пять минут.

Основные группы метрик

Группа	Примеры метрик	Описание
Подключения и сессии	sql-server.general.userConnections sql-server.sessions	Пользовательские подключения и активные сессии.
Блокировки	sql-server.locks.deadlocks.count sql-server.locks.waits.count sql-server.locks.waitTime.count	Взаимоблокировки, ожидания и их продолжительность.
Память	sql-server.memory.physical sql-server.memory.virtual sql-server.memory.grantsPending	Использование памяти и процессы, ожидающие её выделения.
Буферный кеш	sql-server.buffers.pageLifeExpectancy sql-server.buffers.pageWrites.count	Время жизни страниц и операции записи.
Запросы	sql-server.sql.batchRequests.count sql-server.sql.compilations.count sql-server.sql.recompilations.count	Пакеты запросов, компиляции и повторные компиляции.
Базы данных	sql-server.databases.state sql-server.databases.transactions.count	Состояние баз данных и количество транзакций.
Транзакционные журналы	sql-server.databases.log.percentUsed sql-server.databases.log.filesSize sql-server.databases.log.growths.count	Использование, размер и рост журналов.
Резервное копирование	sql-server.databases.backup.age sql-server.databases.backup.size	Возраст и размер последней резервной копии.
Файлы базы данных	sql-server.databases.file.size sql-server.databases.file.usedSpace sql-server.databases.file.emptySpace	Общий, занятый и свободный объём файлов.

Сбор логов

Часть подробной информации передаётся в виде записей логов:

• наиболее продолжительные запросы;
• крупнейшие файлы баз данных;
• текущие задания;
• задания, завершившиеся с ошибкой;
• сведения об отдельных резервных копиях.

Для получения наиболее продолжительных запросов должны быть включены:

• набор Queries;
• Query Store в целевой базе;
• разрешение VIEW DATABASE STATE;
• приём логов расширения.

Значение Longest queries timeout определяет, сколько времени контроллер ожидает завершения служебного запроса. Увеличивайте его только при регулярных тайм-аутах сбора.

Always On

Набор Always On доступен для SQL Server 2016 и новее.

Расширение собирает:

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

Для работы набора требуются права чтения представлений sys.availability_* и sys.dm_hadr_*.

В версии 2.5.4 для реплик стандартного экземпляра используется значение MSSQLSERVER. Это позволяет корректно связывать реплику Always On с соответствующим экземпляром SQL Server.

Обнаруживаемые сущности

Расширение создаёт следующие сущности:

• SQL Server Host;
• SQL Server Instance;
• SQL Server Database;
• SQL Server Availability Group;
• SQL Server Availability Replica;
• SQL Server Availability Database.

Базы данных связываются с экземплярами, экземпляры — с хостами, а базы доступности — с соответствующими репликами и группами.

Для связывания экземпляра с хостом адрес, указанный в конфигурации, должен совпадать с IP-адресом хоста, контролируемого ЕдинымАгентом.

Обзорная панель

В состав расширения входит панель SQL Server Overview. Она содержит:

• наиболее загруженные экземпляры и базы данных;
• базы данных, находящиеся не в состоянии ONLINE;
• группы и реплики доступности;
• количество компиляций и повторных компиляций;
• состояние синхронизации Always On.

Шаблоны оповещений

Расширение содержит готовые шаблоны:

• SQL Server low buffer cache hit rate — низкий коэффициент попаданий в буферный кеш;
• SQL Server high log usage — высокий уровень заполнения транзакционного журнала.

После установки шаблоны необходимо проверить и адаптировать под рабочую нагрузку конкретной среды.

Проверка работы

После активации конфигурации проверьте:

1. состояние конфигурации расширения;
2. успешность подключения к конечной точке;
3. появление сущности экземпляра SQL Server;
4. обнаружение баз данных;
5. поступление метрик с префиксом sql-server;
6. наличие данных на панели SQL Server Overview.

Базовую доступность SQL Server можно проверить с хоста АктивногоШлюза:

sqlcmd -S tcp:<адрес>,<порт> -d master -U keyastrom_monitor -Q "SELECT @@SERVERNAME"

Проверить доступность основных системных представлений можно запросами:

SELECT TOP (1) * FROM sys.dm_os_sys_info;
SELECT TOP (1) * FROM sys.dm_os_performance_counters;
SELECT name, state_desc FROM sys.databases;

Устранение неисправностей

Проблема	Возможная причина и решение
Не удаётся подключиться к серверу	Проверьте адрес, порт, правила межсетевого экрана, работу TCP/IP в SQL Server Configuration Manager и доступность сервера с АктивногоШлюза.
Ошибка аутентификации	Проверьте тип аутентификации, имя пользователя, пароль и наличие пользователя в указанной базе данных.
Поступает только часть метрик	Проверьте выбранные наборы функций и права пользователя на соответствующие системные представления.
Не отображаются все базы данных	Подключайтесь к master и проверьте право VIEW ANY DATABASE.
Не поступают наиболее продолжительные запросы	Включите Query Store, набор Queries, приём логов и право VIEW DATABASE STATE.
Не поступают резервные копии или задания	Проверьте доступ пользователя к таблицам базы msdb и включение наборов Backups и Jobs.
Не собираются метрики Always On	Проверьте версию SQL Server, набор Always On и права на представления sys.availability_* и sys.dm_hadr_*.
Экземпляр не связывается с хостом	Убедитесь, что в конфигурации указан тот же IP-адрес, который обнаружен ЕдинымАгентом.
Служебный запрос завершается по тайм-ауту	Увеличьте Longest queries timeout. Значение должно быть целым числом и не должно превышать 290 секунд.

История изменений

Версия	Изменения
2.5.4	Начало поддержки расширения Для измерения availability.replica.instance добавлено значение по умолчанию MSSQLSERVER. Исправлено связывание реплик Always On со стандартными экземплярами SQL Server.