Все описанное в этом посте будет актуально для 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" мы попадаем на предпоследний экран мастера настройки.
Здесь мы можем настроить:
- Account Retry Attempts - количество попыток отправки письма с использованием конкретной учтеной записи (помните, что мы можем добавить в профиль несколько учетных записей? Вот, сначала SQL Server попробует отправить письмо от имени учетной записи с приоритетом 1 столько раз, сколько мы укажем, а потом, если письмо так и не уйдет - будет перебирать менее приоритетные учетные записи)
- Account Retry Delay (seconds) - именно столько секунд SQL Server будет ждать прежде чем повторить попытку отправить письмо
- Maximum File Size (Bytes) - SQL Server может добавлять файлы-вложения в письмо. Этим параметром можно ограничить размер такого вложения
- Prohibited Attachment File Extensions - запрещенные разрешения для вложений, чтобы пользователь не мог сгененерировать и отправить файл .vbs, например
Database Mail Executable Minimum Lifetime (seconds) - Database Mail представляет из себя отдельный файл DatabaseMail(90-110).exe - этот параметр определяет через какой промежуток времени SQL Server "прибьет" запущенный процесс при отсутствии активности - 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", т.е. уведомления я получу только если задание завершится с ошибкой. Однако, там можно выбрать еще два варианта - когда задание завершится успешно, либо когда задание завершится вообще хоть как-то - с ошибкой, либо без ошибок.
Настройки сделаны, запускаем задание:
Отлично, путь, естественно не найден, бэкап не сделан. Проверяю почтовый ящик:
Письмо доставлено. В нем можно увидеть:
Когда и какое задание выполнялось (JOB RUN: 'Backup With Errors' was run on 19.11.2011 at 19:35:12)
Сколько времени выполнялось задание до того как обнаружилась ошибка (DURATION: 0 hours, 0 minutes, 1 seconds)
Итог выполнения (STATUS: Failed) - хотя, собственно, при успешном выполнении задания, письмо бы и не пришло
Сообщение из журнала событий этого задания (MESSAGES: The job failed. The Job was invoked by User KATE-HOME\rancid. The last step to run was step 1 (test).), включающее в себя имя пользователя, выполнявшего задание
Этой информации хватит для того чтобы понять что, когда и где не выполнилось и поможет в определении вариантов решения возникшей проблемы.
Хэппи энд
Надеюсь эта информация была хоть кому-нибудь полезной и возможно даже интересной. Если у кого-нибудь возникнет интерес - я могу написать о других способах использования Database Mail. В частности: внутри планов обслуживания, для создания предупреждений (Alerts), а так же о хранимой процедуре sp_send_dbmail, используемой для отправки электронных сообщений с использованием Database Mail.