8.2.2. Работа с Database Mail

Database Mail (SQLiMail) в SQL Server 2005, отправка электронной почты по протоколу SMTP, общие (public) и частные (private) профили Database Mail, роль DatabaseMailUserRole, хранимая процедура sp_send_dbmail

Database Mail (другое название — SQLiMail) — это новая подсистема для организация взаимодействия SQL Server 2005 и SQL Server Agent с почтовым сервером. Именно эту подсистему рекомендуется использовать с SQL Server 2005. По сравнению с традиционной подсистемой SQLMail, которая использовалась в предыдущих версиях и оставлена в SQL Server 2005 для обеспечения обратной совместимости, Database Mail обладает рядом преимуществ:

q      Database Mail использует для взаимодействия с почтовым сервером стандартный протокол SMTP. Это значит, что теперь SQL Server стандартными средствами может взаимодействовать с любым сервером электронной почты, установленным на предприятии, а не только с Exchange Server, как это было с SQL Server 2000;

q      Database Mail работает в отдельном процессе по отношению к процессу SQL Server 2005. Это значит, что если с этой подсистемой произойдут какие-то неполадки, на обычную работу пользователей с SQL Server они не повлияют;

q      Database Mail использует асинхронный режим доставки. То есть скрипт Transact-SQL сможет дать команду на отправку электронной почты и, не дожидаясь ее завершения, продолжить работу;

q      для Database Mail можно настроить и использовать несколько учетных записей электронной почты и несколько почтовых серверов. Это существенно повышает удобство работы и надежность;

q      Database Mail полностью совместима с кластерами Microsoft. В случае каких-то неполадок с основным сервером работа этой подсистемы продолжится на втором сервере кластера, как и работа самого SQL Server;

q      подсистема Database Mail предусмотрена не только для 32-разрядных версий SQL Server, но и для 64-разрядных версий;

q      средствами Database Mail вы можете настроить максимальный размер файлов во вложении или запретить работу с вложениями определенных типов;

q      все настройки Database Mail хранятся на самом SQL Server. Теперь нет необходимости настраивать внешние почтовые профили в операционной системе;

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

Настройка Database Mail обычно начинается с мастера настройки Database Mail Configuration Wizard. Вы можете запустить его при помощи команды Configure Database Mail (Настроить Database Mail) из контекстного меню для контейнера Management | Database Mail (Управление | Database Mail) в SQL Server Management Studio. Кратко опишем работу этого мастера.

На экране Select Configuration Task (Выбрать задачу по настройке) в вашем распоряжении есть три варианта:

q      Set up Database Mail (Настроить Database Mail);

q      Manage Database Mail accounts and profiles (Управление учетными записями и профилями Database Mail);

q      View or change system parameters (Просмотреть или изменить системные параметры).

Для первоначальной настройки электронной почты вам потребуется установить переключатель в верхнее положение — Set up Database Mail. По умолчанию подсистема Database Mail на SQL Server 2005 отключена, и поэтому появится окно сообщения с предложением ее включить. Также включить Database Mail можно при помощи утилиты SQL Server Surface Area Configuration.

Затем откроется экран New Profile (Новый профиль), в котором вам потребуется настроить параметры почтового профиля для работы с Database Mail. Нужно будет указать имя почтового профиля, его описание, а также настроить учетные записи для работы по протоколу SMTP. Для одного почтового профиля вы вполне можете настроить несколько почтовых профилей SMTP. В параметрах почтового профиля можно определить используемый SQL Server адрес электронной почты, почтовый сервер (и номер порта для подключения по протоколу SMTP), а также режим аутентификации при подключении к почтовому серверу (рис. 8.7).

Рис. 8.7. Настройка учетной записи SMTP для Database Mail

На следующем экране Database Mail Wizard, который называется Manage Profile Security (Управление безопасностью профилей), вы можете сделать созданные вами почтовые профили общими (public) или личными (private). Общие почтовые профили доступны для всех пользователей базы данных (по умолчанию для всех пользователей базы данных msdb, но подсистему Database Mail можно настроить и для любой пользовательской базы данных — тогда общий профиль станет доступным для всех пользователей этой базы данных). Личные почтовые профили доступны только тем пользователям, которым явно назначены эти профили на вкладке Private Profiles (Личные профили) этого экрана мастера.

Как общий почтовый профиль, так и личный почтовый профиль можно назначить профилем по умолчанию (default). Общие почтовые профили станут профилями по умолчанию для всех пользователей, а личные — только для тех пользователей, которым они назначены.

На следующем экране Configure System Parameters (Настройка системных параметров) вы можете настроить системные параметры работы Database Mail. Список параметров, которые можно настроить, выглядит так:

q      Account Retry Attempts (Количество повторов для учетной записи) — сколько раз SQL Server будет пытаться повторно обратиться к почтовому серверу, определенному для учетной записи, если он по каким-то причинам оказался недоступным. По умолчанию установлен 1 повтор;

q      Account Retry Delay, seconds (Задержка повторов для учетной записи, в секундах) — время, которое SQL Server будет ждать перед повторным обращением к почтовому серверу. По умолчанию — 60 секунд;

q      Maximum File Size (Максимальный размер файла) — имеется в виду размер всех файлов вложения. По умолчанию установлен примерно 1 Мбайт;

q      Prohibited Attachment File Extensions (Запрещенные расширения файлов для вложений) — можно запретить пользователям отправлять почтовые сообщения средствами Database Mail с определенными типами вложений. По умолчанию запрещено отправлять файлы exe, dll, vbs, js;

q      Database Mail Executable Minimum Lifetime, seconds (Время жизни для исполняемого файла Database Mail, в секундах) — сколько времени будет оставаться в памяти процесс Database Mail после завершения отправки всех сообщений в очереди. По умолчанию — 600 секунд;

q      Logging Level (Уровень протоколирования) — насколько подробно будет протоколироваться работа подсистемы Database Mail. В вашем распоряжении есть три варианта:

·                Normal (Обычный) — будут записываться только ошибки;

·                Extended (Расширенный) — будут записываться ошибки, предупреждения и информационные сообщения (этот вариант выбирается по умолчанию);

·                Verbose (Самый подробный) — наиболее полное протоколирование, будет записываться максимум информации о работе Database Mail.

После этого мастер настроит систему Database Mail на сервере.

Такую же настройку при желании можно выполнить при помощи специальных хранимых процедур базы данных msdb (их названия начинаются с префикса sysmail_...), однако при помощи мастера производить первоначальную настройку и последующее изменение параметров намного проще.

Следующее, что нужно сделать — предоставить пользователям, которым (или от имени которых) будет отправляться электронная почта средствами Database Mail, необходимые права. Для этого нужно назначить им специальную роль DatabaseMailUserRole в базе данных msdb.

После завершения всех настроек у вас появится возможность использовать хранимые процедуры и представления Database Mail. Хранимые процедуры предназначены для выполнения определенных действий, а представления — для получения информации о работе Database Mail. И хранимые процедуры, и представления находятся в базе данных msdb и принадлежат схеме dbo. Далее представлен перечень хранимых процедур Database Mail с пояснениями:

q      sp_send_dbmail — это наиболее часто используемая хранимая процедура Database Mail. Она предназначена для отправки электронной почты. Параметров у этой хранимой процедуры очень много, и разбирать их здесь не будем. Приведем лишь простой пример использования этой хранимой процедуры для отправки электронной почты:

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'MailProfile1', @recipients = 'admininstrator@nwtraders.msft', @subject = 'Заголовок сообщения', @body = 'Текст сообщения';

q      sysmail_delete_mailitems_sp — эта хранимая процедура позволяет удалить ранее отправленные (успешно или неуспешно) сообщения электронной почты из таблиц Database Mail в базе данных msdb. Если вы активно работаете с Database Mail, рекомендуется периодически запускать эту хранимую процедуру, чтобы база данных msdb не разрослась слишком сильно;

q      sysmail_delete_log_sp — эта хранимая процедура также позволяет очистить таблицы базы данных msdb. Но при ее использовании удаляются не сами сообщения, а протокол их отправки;

Остальные хранимые процедуры относятся к настройке параметров или к диагностике Database Mail.

Database Mail можно использовать и для получения электронных сообщений службой SQL Server с указанного почтового ящика. Но специальных хранимых процедур для этого не предусмотрено. Для получения электронной почты придется использовать программные средства Service Broker.

Конечно, Database Mail можно использовать и для отправки электронной почты службой SQL Server Agent. Чтобы настроить SQL Server Agent для использования электронной почты, необходимо выполнить следующие действия:

q      включить Database Mail и создать необходимый профиль и учетную запись, как это было описано ранее;

q      назначить логину, от имени которого SQL Server Agent подключается к SQL Server, роль DatabaseMailUserRole в базе данных msdb;

q      сделать данный профиль профилем по умолчанию для логина SQL Server Agent;

q      открыть свойства SQL Server Agent и на вкладке Alert System установить флажок Enable mail profile. Затем в списке Mail system нужно выбрать систему Database Mail, а в списке почтовых профилей — созданный профиль;

q      последнее, что осталось сделать, — перезапустить SQL Server Agent, чтобы изменения вступили в силу.

После этого SQL Server Agent сможет средствами Database Mail отправлять информацию о сработавших оповещениях или, например, о результатах выполнения заданий.

Отметим, что в этапах заданий вы можете использовать обходные пути для работы с электронной почтой. Например, из этапа типа Transact-SQL Script вы можете напрямую использовать хранимую процедуру sp_send_dbmail, а из этапа типа ActiveX Script вообще использовать для отправки почты программный объект CDO.Message. Конечно, при использовании этих методов специально настраивать SQL Server Agent для использования Database Mail нет необходимости.

Теперь остановимся на том, как можно получить информацию о работе Database Mail. Вне зависимости от того, удалось ли отправить сообщение, хранимая процедура sp_send_mail вернет стандартное сообщение "Mail queued" (т. е. письмо поставлено в очередь на отправку). Узнавать о результатах отправки придется другими способами.

Первый способ — просмотреть журналы Database Mail. Для этого можно воспользоваться командой View Database Mail Log (Просмотреть журнал Database Mail) контекстного меню контейнера Management | Database Mail в Management Studio. Изучив записи в журнале, можно понять, удалось ли отправить сообщение, и если нет, то по какой причине.

Второй способ — воспользоваться специальными представлениями Database Mail в базе данных msdb:

q      sysmail_allitems — главное представление Database Mail. С его помощью можно получить информацию о всех письмах, отправленных средствами Database Mail. Здесь находится информация о получателе, заголовок, начало текста и другие свойства сообщения, время отправки и статус сообщения (отправлено, еще не отправлено, произошел сбой и т. п.). Большинство других представлений показывает только часть записей, которые содержатся в sysmail_allitems;

q      sysmail_faileditems — это представление содержит только те письма, при отправке которых произошел сбой;

q      sysmail_sentitems — только успешно отправленные письма;

q      sysmail_unsentitems — только письма, которые еще не были отправлены, но стоят в очереди на отправку;

q      sysmail_mailattachments — только письма с вложениями;

q      sysmail_event_log — в этом представлении находится информация из журнала Database Mail (которую можно получить при помощи команды View Database Mail Log).

Получить учебные материалы по этому курсу