Настройка уведомлений по почте MS SQL SERVER

Все описанное в этом посте будет актуально для Microsoft SQL Server 2005 и старших версий (2008, 2008 R2, Denali). Так же, в рамках этого поста, словосочетание «SQL Server» будет обозначать только Microsoft SQL Server, исключая MySQL, PostgreSQL, FireBird и прочие СУБД, которые, теоретически, можно назвать SQL Server.

Что это за штука такая, Database Mail?

Database Mail появился в SQL Server 2005, заменив собой компонент SQLMail. Database Mail (как ранее SQLMail) используется для отправки сообщений электронной почты самим SQL Server’ом (точнее, его компонентом Database Engine). Коренным отличием Database Mail от SQLMail является то, что последний, для отправки сообщений, использовал стороний клиент (Microsoft Outlook), который должен был быть установлен на машине с SQL Server’ом, а Database Mail сам общается с почтовым сервером по протоколу SMTP.

Для чего это надо?

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

• каждое утро менеджер хочет получать на почту итоги вчерашнего дня: сколько и какой продукции было продано, какой клиент сделал наибольшую заявку, какие клиенты имеют задолженность и прочее. Иными словами — результаты любого запроса могут быть отправлены с помощью Database Mail;
• администратор баз данных хочет получать на почту информацию о том какие задачи(job’ы) или планы обслуживания завершились с ошибкой (или наоборот, выполнились успешно);
• администратор баз данных хочет получать на почту информацию об ошибках определенного уровня серьезности, или с определенным номером, произошедших на сервере;
• администратор баз данных хочет получать на почту информацию о том, что размер базы данных вплотную приближается к размеру жесткого диска и пора уже трясти шефа на предмет покупки нового обрудования;
• администратор баз данных хочет получать на почту что-то еще.

Таким образом, Database Mail, на мой взгляд, будет наиболее полезен именно администраторам баз данных SQL Server, а учитывая, что сотовые операторы предоставляют услугу email-to-sms (честно говоря не силен в ОПСОСах, может это и редкость, но у моего такая есть и является абсолютно бесплатной), может стать еще и очень оперативным средством информирования.

Что нам понадобится?

Итак, в первую очередь нам нужнен SQL Server версии 2005 или старше. У меня стоит SQL Server 2008 R2, но, в данном случае, разницы в настройках не будет никакой. Database Mail, по мнению Microsoft, доступен во всех редакциях, за исключением Express Edition (пруф), однако, вот здесь есть подробное описание, как этот компонент можно включить. Второе, без чего Database Mail работать не будет — это почтовый сервер, доступный с машины с SQL Server’ом и учетная запись на нем, от которой будут отправляться письма. В моем примере роль такого сервера будет выполнять mail.ru. И третье — членство в роли sysadmin у вашей учетной записи в SQL Server, поскольку настройку могут производить только члены этой роли.

Поехали

В первую очередь, подключимся к нашему SQL Server’у с помощью SQL Server Management Studio. Естественно, все действия по настройке можно выполнить с помощью предопределенных хранимых процедур, но я не стремлюсь делать запросам все что возможно, тем более, если для этого существуют удобные мастера. Раскрываем ветку Management, выбираем пункт Database Mail, тыкаем по нему правой кнопкой мыши и выбираем «Configure Database Mail». Приветственный экран мастера можно сразу отметить галкой «Больше никогда не показывать мне эту дрянь», поскольку полезной информации на нем нет. И вот, мы уже стоим перед выбором. Теперь мы можем: настроить Database Mail, изменить профили и аккаунты Database Mail, изменить безопасность профилей и, наконец, изменить конфигурацию системы. Поскольку Database Mail мы ранее не использовали — изменять нам пока нечего, выбираем первый пункт «Set up Database Mail» и нажимаем «Next». А вот и первый сюрприз. SQL Server услужливо сообщает, что вообще-то такая фича как Database Mail отключена и уточняет — желаем ли мы ее включить? Поскольку именно за этим мы сюда и пришли, отвечаем «Yes» и попадаем на следующий экран. Создадим новый профиль с именем My First DBMail Profile — именно он будет использоваться для отправки почты о невыполненных заданиях (job’ах) и добавим в него одну учетную запись (Account) — для чего нажмем кнопку «Add» (справа от пока пустого списка учетных записей). Перед написанием поста я зарегистрировал почту TestDBMail@mail.ru как раз для проведения экспериментов. Естественно, все эти поля вам нужно будет заполнить «под себя». Например, наш внутренний почтовый сервер не требует авторизации для отправки писем и, соответсвенно, можно выбрать пункт «Anonymous authentication». Display Name на этом экране — это то, что будет стоять в поле «From» у полученного письма, а Account name — это внутреннее имя учетной записи на SQL Server. Обратите внимание, что на этом этапе нет возможности проверки соединения с сервером и нет возможности отправить тестовое письмо — т.е. если при заполнении будет допущена ошибка, ее поиск, возможно, займет какое-то время. Будьте внимательны. Итак, у нас есть профиль, к нему добавлена учетная запись. Жмем «Next» и смотрим что дальше. Глядя на этот экран, а в особенности на вкладку Private Profiles, должен отпасть вопрос, на который я не обратил внимания раньше — для чего нужна возможность создания нескольких профилей. Каждому пользователю msdb, включенному в роль DatabaseMailUserRole, можно назначить свой профиль. Или даже несколько. Для этого необходимо установить галку «Access». Default Profile — если стоит «Yes», при использовании хранимой процедуры sp_send_dbmail, имя профиля по-умолчанию можно не указывать, туда будет подставлено имя профиля, отмеченного Default для этого пользователя. Однако же, я вернусь на закладку Public Profiles и установлю для свежесозданного профиля признаки Default = «Yes» и Public = «Yes». Теперь этот профиль смогут использовать все пользователи msdb включенные в роль DatabaseMailUserRole (и пользователи серверной роли sysadmin). После нажатия «Next» мы попадаем на предпоследний экран мастера настройки. Здесь мы можем настроить:

1. Account Retry Attempts — количество попыток отправки письма с использованием конкретной учтеной записи (помните, что мы можем добавить в профиль несколько учетных записей? Вот, сначала SQL Server попробует отправить письмо от имени учетной записи с приоритетом 1 столько раз, сколько мы укажем, а потом, если письмо так и не уйдет — будет перебирать менее приоритетные учетные записи)
2. Account Retry Delay (seconds) — именно столько секунд SQL Server будет ждать прежде чем повторить попытку отправить письмо
3. Maximum File Size (Bytes) — SQL Server может добавлять файлы-вложения в письмо. Этим параметром можно ограничить размер такого вложения
4. Prohibited Attachment File Extensions — запрещенные разрешения для вложений, чтобы пользователь не мог сгененерировать и отправить файл .vbs, например
5. Database Mail Executable Minimum Lifetime (seconds) — Database Mail представляет из себя отдельный файл DatabaseMail(90-110).exe — этот параметр определяет через какой промежуток времени SQL Server «прибьет» запущенный процесс при отсутствии активности
6. Logging Level — этот параметр определяет как много информации будет писаться в лог, при работе Database Mail.

На последнем экране мы увидим какие учетные записи и профили будут созданы, а так же каким пользователям будет добавлена возможность использовать созданный профиль. Смело жмем «Finish». SQL Server быстро закончит настройку и покажет какие пункты и по какой причине ему выполнить не удалось (если-таки что-то не удалось). Убедившись, что у нас все в порядке, закрываем окно. Проверим, что настройки Database Mail сделаны правильно. Снова идем в Management, нажимаем правой кнопкой на Database Mail и выбираем пункт «Send Test E-Mail». Как мы видим, в качестве профиля уже выбран только что созданный профиль, поля «Тема» и «Текст сообщения» автоматически заполнены. Естественно, все это можно изменить, но меня значения по-умолчанию устраивают. Осталось только указать адрес на который уйдет письмо, не мудрствуя лукаво — пишу тот же адрес, с которого SQL Server будет отправлять письмо (прошу простить мою лень — зачем создавать два ящика, когда и одного более чем достаточно? благо, SQL Server’у абсолютно наплевать на входящие письма, он их читать не умеет). И нажимаю кнопку «Send Test E-Mail». … спустя 15 секунд, во входящих: Бинго! Письмо пришло. Фактически, можно считать, что задача выполнена. Но здесь я сделаю небольшое отступление для бедолаг, которые безуспешно будут проверять почту, а нужного письма так и не увидят. Во-первых, посмотрите журнал Database Mail. В SSMS зайдите в Management, ткните в Database Mail правой кнопкой мыши и выберите там Database Mail Log. Возможно вы просто неправильно указали параметры подключения к серверу — это, как ни странно, довольно-таки распространенная ошибка. Второе, что нужно проверить — это то, что в базе данных msdb включен Service Broker. Именно он используется для отправки сообщений. Создайте новый запрос и выполните там:SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' Если возвращаемое значение отличается от единицы, Service Broker выключен. Вы можете включить его так:ALTER DATABASE msdb SET ENABLE_BROKER Если же Service Broker включен, все настройки сделаны верно — чем черт не шутит — проверьте почту еще раз. Письма нет? Тыкайте в кнопочку «Troubleshoot», которая есть в окне, появившемся после нажатия кнопки «Send Test E-Mail» и посмотрите типовые ошибки в справке от Microsoft. Будем считать, что теперь Database Mail у нас настроена и тестовое письмо пришло.

А дальше, дальше-то что?

А дальше начинается самое интересное. Теперь мы наконец-то можем использовать Database Mail для того чтобы получать уведомления от SQL Server’а о том, что у него что-то не так (или же наоборот, что у него все замечательно). SQL Server может прислать уведомление о том, что задание выполнилось успешно (или наоборот — завершилось с ошибкой). Точнее это может сделать SQL Server Agent, собственно, и выполняющий задания. Для того, чтобы он смог это сделать нужно сделать следующее. В первую очередь — создать оператора. То есть лицо, получающее уведомления. Для этого мы открываем SQL Server Agent, выбираем Operators и тыкаем «New operator» В появившемся окне заполним имя (у меня это Database Administrator) и адрес электронной почты на которую будут приходить уведомления (я указываю все тот же многострадальный ящик на mail.ru). Жмем «Ок» и оператор создастся за доли секунды. Теперь у нас есть настроенный профиль Database Mail, оператор получающий уведомления, осталось настроить SQL Server Agent. Для этого жмем на него (SQL Server Agent) правой кнопкой мыши, выбираем пункт Properties и переходим на вкладку Alert System. Здесь ставим галку «Enable Mail Profile» Сразу после этого становятся активными элементы Mail System и Mail Profile. В Mail System мы можем выбрать Database Mail, или как злобные некрофилы SQL Mail (но ее надо настраивать отдельно), в Mail Profile мы можем выбрать какой профиль Database Mail будет использоваться SQL Server Agent’ом для отправки сообщений. Это может быть публичный профиль, либо приватный, но в этом случае у пользователя Windows, под которым запущен SQL Server Agent, должен быть создан логин в SQL Server, а так же пользователь в msdb, входящий в роль DatabaseMailUserRole. Не желая заморачиваться с профилями, я соглашаюсь с выбором публичного профиля и жму «Ок». Все. Теперь обязательно надо перезапустить SQL Server Agent. Это можно сделать из SSMS, можно из SQL Server Configuration Manager. Выбирайте сами — откуда вам удобнее. После перезапуска проверим, что все настроено правильно и сообщения действительно приходят. Для этого я делаю job, пытающийся создать бэкап одной из баз данных по заведомо несуществующему пути. Захожу в свойства созданного задания, перехожу на вкладку Notifications, ставлю галку E-mail и выбираю недавносозданного оператора по имени Database Administrator. Третью колонку я оставляю без изменения — там стоит «When the job fails», т.е. уведомления я получу только если задание завершится с ошибкой. Однако, там можно выбрать еще два варианта — когда задание завершится успешно, либо когда задание завершится вообще хоть как-то — с ошибкой, либо без ошибок. Настройки сделаны, запускаем задание: Отлично, путь, естественно не найден, бэкап не сделан. Проверяю почтовый ящик: Письмо доставлено. В нем можно увидеть:

1. Когда и какое задание выполнялось (JOB RUN: ‘Backup With Errors’ was run on 19.11.2011 at 19:35:12)
2. Сколько времени выполнялось задание до того как обнаружилась ошибка (DURATION: 0 hours, 0 minutes, 1 seconds)
3. Итог выполнения (STATUS: Failed) — хотя, собственно, при успешном выполнении задания, письмо бы и не пришло
4. Сообщение из журнала событий этого задания (MESSAGES: The job failed. The Job was invoked by User KATE-HOMErancid. The last step to run was step 1 (test).), включающее в себя имя пользователя, выполнявшего задание

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

Хэппи энд

Надеюсь эта информация была хоть кому-нибудь полезной и возможно даже интересной. Если у кого-нибудь возникнет интерес — я могу написать о других способах использования Database Mail. В частности: внутри планов обслуживания, для создания предупреждений (Alerts), а так же о хранимой процедуре sp_send_dbmail, используемой для отправки электронных сообщений с использованием Database Mail.

P.S.

На хабре появился ещё один пост, посвящённый настройке Database Mail. Там написано как можно настроить Database Mail с помощью скриптов.

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

МЕХАНИЗМ ОПОВЕЩЕНИЙ

Настройку оповещений можно разделить на 4 этапа:

1. Включение и настройка профиля компонента Database Mail
2. Создание оператора оповещений и настройка почты агента SQL Server
3. Включение триггеров и задач оповещений
4. Проверка работоспособности

НАСТРОЙКА ПРОФИЛЯ КОМПОНЕНТЫ DATABASE MAIL

Включить компонент работы с почтой DATABASE MAIL нужно с помощью следующего запроса:

Для того чтобы настроить учетную запись компонента Database Mail необходимо в дереве «Обозревателя Объектов» (Object Explorer) выбрать настраиваемый экземпляр SQL Server, перейти в «Управление» (Management), далее «Компонент Database Mail» (Database Mail). Щелкнув правой клавишей мыши на данном пункте, будет открыто контекстное меню, в котором необходимо выбрать «Настроить…» (Configure…), после чего будет открыто окно мастера настройки.

Первую страницу можно пропустить, поэтому перейдем сразу ко второй. Здесь нам необходимо выбрать первый пункт «Установить компонент Database Mail…» (Setup Database Mail…) и нажать «Далее» (Next). На третьей странице задаем имя и описание профиля, затем нажимаем кнопку «Добавить» для добавления учетной записи SMTP. В открывшемся окне необходимо заполнить данные учетной записи (e-mail адрес, сервер и порт SMTP, а так же параметры аутентификации).

На следующей странице необходимо назначить безопасность профиля: указать открытый это профиль или частный (и для каких пользователей), а также можно указать является ли профилем по умолчанию. Для наших целей достаточно сделать профиль открытым и профилем по умолчанию. На пятой странице оставляем все по умолчанию, на последней странице жмем «Готово» (Finish)

После того как профиль настроен, его надо проверить, для этого в контекстном меню пункта «Компонент Database Mail»(вызываемым щелчком правой мыши по данному пункту) надо выбрать «Отправить тестовое сообщение» (Send Test E-Mail). В открывшемся окне следует заполнить поле «Кому» (To) и нажать «Отправить …» (Send …)

Если все сделано правильно, тогда в ближайшее время на почту будет доставлено тестовое письмо.

Если нет, то выполните настройки системных параметров по картинке:

ДОБАВЛЕНИЕ ОПЕРАТОРА ОПОВЕЩЕНИЙ

Операторы — это псевдонимы людей или групп, которые могут получать электронные уведомления о завершении задач, заданий или предупреждения. Для добавления нового оператора оповещений необходимо в дереве «Обозревателя Объектов» (Object Explorer) выбрать настраиваемый экземпляр SQL Server, перейти в «Агент SQL Server» (SQL Server Agent), далее «Операторы» (Operators). Щелкнув правой клавишей мыши на данном пункте, выбрать «Создать оператора» (New Operator), после чего будет открыто окно свойств оператора оповещений.

Настройки оператора находятся на закладке «Общие» (General). Здесь необходимо заполнить «Имя» (Name), указать состояние «Включено» (Enabled), ввести адрес электронной почты. В целом, существуют альтернативные способы оповещения помимо электронной почты: с помощью команды net send или сообщением на пейджер.

На этом настройка оператора завершена, перейдем к следующему шагу.

НАСТРОЙКА ПОЧТЫ АГЕНТА SQL SERVER

Данный пункт необходим для рассылки уведомлений по электронной почты агентом SQL Server. Такая рассылка происходит, например, для уведомления о статусе выполнения задания. Для настройки почты агента необходимо в дереве «Обозревателя Объектов» (Object Explorer) выбрать настраиваемый экземпляр SQL Server, перейти в «Агент SQL Server» (SQL Server Agent), из контекстного меню выбрать «Свойства» (Properties).

В открывшемся окне перейдем на вкладку «Система предупреждений» (Alert System), установим флажок «Включить почтовый профиль» (Enable mail profile), в качестве почтовой системы оставим «Компонент Database Mail» (Database Mail) и выберем ранее созданный профиль в соответствующем поле.

ВКЛЮЧЕНИЕ ТРИГГЕРОВ И ЗАДАЧ ОПОВЕЩЕНИЯ

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

Итак, откроем наш план обслуживания, в нем выделим субплан «UpdateStatistics». На рабочую область субплана перетащим задачу «Уведомление оператора» (Notify Operator Task). Протянем стрелку от задачи «Обновление статистики». Двойным кликом по задаче «Уведомление оператора» откроем ее свойства, отметим созданного оператора, а так же введем «Тему»(Subject) и «Текст» (Message) письма. Для того чтобы изменить условие оповещения (по умолчанию стрелка вида «Успешное завершение») надо щелкнуть правой клавишей мыши на стрелке и выбрать ее вид: успешное выполнение/ошибка/выполнение.

Теперь перейдем к настройке оповещений в «Заданиях» (Jobs). Откроем свойства нашего задания и перейдем на вкладку «Уведомления» (Notifications). Установим галки рядом с видами уведомлений, которые мы хотим использовать (у меня это только электронная почта), далее выберем оператора и условие оповещения (успешное завершение/завершение с ошибкой/любое завершение).

ПРОВЕРКА РАБОТОСПОСОБНОСТИ ОПОВЕЩЕНИЙ

Выполним ручной запуск нашего задания, для этого щелкнем правой клавишей мыши на нем и выберем «Запустить задание на шаге» (Start Job at Step). В результате должно прийти на почту 2 письма: одно (с установленными нами темой и текстом) соответствует задаче «Уведомления оператора» в плане обслуживания; второе — информирует о выполнении задания в целом.

Если не пришло ни одного письма тогда, возможно:

1. Ошибка в параметрах учетной записи SMTP. Попробуйте отправить тестовое сообщение, если оно не отправляется — перепроверьте параметры
2. Ошибка в правах безопасности профиля. Перепроверьте установлены ли права как описано ранее. Если все установлено верно, а результата нет — попробуйте указать все права: открытый и все частные, а так же сделать профилем по умолчанию для каждого права

Если не пришло только письмо с результатом выполнения задания: проверьте что почта SQL Server’а настроена в соответствии с вышеизложенным.

Используемые источники:

• https://habr.com/post/132902/
• https://lepkov.ru/ms-sql-server-email-notify/