Alerts and notification

SQL Server. Alerts and notification

Не буду преуменьшать важность своевременного оповещения администраторов о том или ином событии сервера, особенно если администратору необходимо предпринять какие-либо связанные с событием действия. Предположим, необходимо отслеживать попытки пользователей получить неавторизованный доступ к объектам SQL Server. Разумеется, такие попытки будут зафиксированы в журнале событий SQL Server и администратор во время очередного просмотра этого журнала увидит это. Наверное увидит. Если другие 100034895 событий не замаскируют. Тем более, что заставлять админа просматривать журнал каждый день/час/минуту – дело неблагодарное.

Задействуем для этих целей связку Alert + Notification + Database Mail. Последний компонент необходим для того, чтобы оповещение о наступлении события отсылалось по почте.

Сначала – краткая информация о алертах. Алерт (alert) – это объект SQL Server. Даже и не хочу переводить или искать перевод терминов, несерьезно это :) В алерте определяется условие, при котором он срабатывает и действия, которые необходимо предпринять при срабатывании. В качестве действия можно определить либо запуск задания, либо оповещение оператора. Так как алерт – объект, у него есть свойства. Одно из свойств – номер, второе – уровень важности, третье – информационное сообщение. Эти свойства нам пригодятся при создании алерта.

Итак, план действий таков:

1. Создаем алерт. Задаем имя и выбираем условие, при котором этот алерт сработает. В нашем случае это все события с важностью 14 (недостаточно прав). В этом случае алерт будет срабатывать каждый раз, когда пользователю не будет хватать прав на запрашиваемые объекты. Можно настроить алерт более тонко, указав конкретный номер ошибки, появление которой необходимо отслеживать.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2. Далее необходимо определить, что следует сделать при срабатывании. Предположим, нам необходимо отправлять письмо администратору. Выбираем опцию “Notify Operator” и оператора, которого необходимо извещать. Если операторы не определены – кликаем на кнопке “New operator” и создаем его. Задаем имя и адрес электронной почты, куда следует отправлять письма. После создания оператора необходимо выбрать способ оповещения при срабатывании – доступно три опции (E-Mail, Pager, Net Send). Для того, чтобы выбранный способ работал, необходимо его сконфигурировать в свойствах оператора (для отсылки по E-Mail необходимо указать адрес, для Net Send – указать имя компьютера и т.д.). Microsoft не рекомендует использовать опцию Net Send и оставил ее только для обратной совместимости.

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

Все. Алерт создан и мы надеемся, что теперь он начнет работать. Проверим.

Для начала посмотрим на историю срабатываний. Правой кнопкой мыши на созданном алерте, “Properties”, “History”. Убеждаемся, что срабатываний не было, равно как и не было извещений о срабатывании.

 

 

 

 

 

 

 

 

 

 

 

3. Теперь необходимо сконфигурировать Database Mail с тем, чтобы алерт мог отослать письмо (конечно, если компонента Database Mail еще не сконфигурирована). Как сконфигурировать компоненту расскажу в другом посте, чтобы не утяжелять этот. Либо сами в интернетах найдете как :) Самое главное – убедитесь, что созданный профиль выбран в свойствах SQL Server Agent – в данном случае у меня стоит галка на “Enable mail profile” и выбран необходимый профиль из списка.

4. Наконец, пробуем наш алерт на вкус.

  • Создаем логин для чистого тестирования
    • CREATE LOGIN Test WITH PASSWORD=’TEST’;
    • b. CREATE USER Test FOR LOGIN Test;
  • 2. Создаем таблицу для теста
    • a. CREATE TABLE tblTest (id int)
    • b. INSERT INTO tblTest (id) VALUES (1)
  • 3. Для чистоты эксперимента отбираем права на select у пользователя Test
    • a. DENY SELECT ON tblTest TO Test
  • 4. Теперь логинимся под аккаунтом Test и пробуем заселектить из таблицы tblTest. Получаем сообщение
      Msg 229, Level 14, State 5, Line 1. The SELECT permission was denied on the object ‘tblTest’, database ‘MDM’, schema ‘dbo’.

5. Ага! Событие случилось и алерт должен сработать! Снова проверяем историю алерта. С удивлением обнаруживаем, что история пуста. Ноль срабатываний. Хм.. Странно… Да ничего странного, алерт вполне предсказуемо себя ведет. Дело в том, что механизм работы алерта такой: при старте SQL Server Agent регистрирует себя в качестве callback сервиса в журнале приложений Windows (Windows Application Log) и ждет сигнала от журнала о наступлении события. Следовательно, событие должно появиться в журнале приложений Windows. По-умолчанию, SQL Server пишет в журнал приложений только события с важностью 19 и выше, остальные пишутся только в журнал SQL Server. В нашем случае событие имеет важность 14 (Level 14), поэтому для того, чтобы наш алерт работал, необходимо заставить SQL Server писать событие в Windows Application Log.

Сделаем следующее:

6. Снова логинимся под учеткой администратора и убеждаемся, что событие (номер события – 229 – в сообщении об ошибке) не пишется в Application Log: SELECT * FROM sys.messages where message_id=229 – смотрим на значение поля is_event_logged. Запрос возвращает много записей, по одной строке для каждого зарегистрированного языка. Далее необходимо зарегистрировать ошибку с номером 229 для записи в журнал приложений. Для этого выполняем скриптsp_altermessage 229, 'WITH_LOG', 'true'. Снова SELECT * FROM sys.messages where message_id=229 и проверяем значение в поле is_event_logged. Единица? Отлично, то, что надо.

7. Опять логинимся под пользователем Test и провоцируем ошибку – select top 1 * from tblTest.

 

 

 

8. Логинимся под админом и смотрим свойства алерта.

Ура. Сработало. Чуть позже и письмо должно прийти, поверьте :)

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

1. Настроить компоненту Database Mail
2. Создать оператора, который будет извещаться о наступлении события;
3. Создать алерт для отлавливания специфической ошибки (либо по номеру ошибки, либо по семейству)
4. Если важность отслеживаемой ошибки ниже 19 – изменить свойства сообщения таким образом, чтобы информация об ошибке писалась в Windows Application Log (sp_altermessage)
5. Все.

Удачи.

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

Автор: KHANZHIN

***   ***   ***   ***   ***   ***   ***   ***   ***   ***   ***   ***
Полезные Скрипты

Рубрика Проверь себя

Ссылка на наш канал YouTube

Запись опубликована в рубрике Полезно и интересно с метками , . Добавьте в закладки постоянную ссылку.

Добавить комментарий

Войти с помощью: