Сегодня в материале мы с Вами рассмотрим компонент Database Mail входящий в состав Microsoft SQL Server, мы узнаем, что это за компонент, как его настроить и, конечно же, как им пользоваться.
Что такое Database Mail в MS SQL Server?
Database Mail – это компонент Microsoft SQL Server, который используется для отправки электронных писем самим SQL сервером. Он появился в MS SQL Server 2005 и доступен в более поздних версиях. До появления Database Mail в SQL сервере существовал компонент SQLMail, который для отправки электронной почты использовал клиент Microsoft Outlook, не входящий в состав SQL Server. Компонент Database Mail взаимодействует с почтовым сервером (SMTP) уже напрямую.
Основные возможности Database Mail
К основным возможностям компонента Database Mail относятся:
- Отправка электронных писем без стороннего клиента;
- Возможность вставлять в письмо результаты запроса;
- Возможность вкладывать файлы в письмо;
- Можно указывать важность сообщения;
- Письма можно посылать сразу на несколько адресов, а также можно указывать адреса, на которые посылать копию;
- Возможность выбора формата письма TEXT или HTML;
- Аудит и ведение журнала отправленных писем;
- Возможность создания множества профилей Database Mail, а также указывать более одного SMTP-сервера;
- Взаимодействие с агентом SQL сервера;
- Возможность ограничивать размер вложений и указывать запрещенные к отправке расширения файлов.
Компонент Database Mail можно использовать в следующих случаях:
- Для отправки сообщений пользователям;
- Для уведомления самого себя, т.е. администратора о различных событиях, произошедших на SQL сервере (выполнение задач, возникновение ошибок).
Настройка компонента Database Mail в MS SQL Server
Для начала хотелось бы сказать, что настраивать Database Mail я буду на примере Microsoft SQL Server 2008 R2. Нижеописанный процесс настройки характерен и для других версий MS SQL сервера, начиная с MS SQL Server 2005.
Прежде чем переходить к настройке Database Mail, необходимо проверить следующее:
- У Вас нужной версии SQL сервер, 2005 или выше (в редакции Express компонента Database Mail нет);
- У Вас есть работающий SMTP сервер и соответствующие настройки этого сервера для отправки писем;
- Вы являетесь членом группы sysadmin на SQL сервере, так как только члены этой группы могут производить настройку Database Mail;
- Отправкой писем в SQL Server занимается программа DatabaseMail.exe (или DatabaseMail90.exe). По умолчанию она расположена в каталоге «C:\Program Files\Microsoft SQL Server\…\MSSQL\Binn\». Если у Вас настроен брандмауэр, то в нем необходимо для этой программы разрешить исходящий трафик;
- Также необходимо проверить, включен ли Service Broker на базе ‘msdb’, он необходим для создания очередей писем, используемых компонентом Database Mail, и в случае если он выключен включить его. Это можно сделать, выполнив следующую SQL инструкцию.
- IF (SELECT is_broker_enabled FROM sys.databases WHERE [name] = 'msdb') = 0 ALTER DATABASE msdb SET ENABLE_BROKER GO
- Настроить компонент Database Mail на SQL сервере можно разными способами, а именно с использованием среды SQL Server Management Studio и с помощью инструкций T-SQL, мы с Вами рассмотрим оба.
- Настройка Database Mail с помощью среды Management Studio
Шаг 1
Открываем Management Studio, в обозревателе объектов открываем объект «Управление» и нажимаем правой кнопкой мыши по пункту «Компонент Database Mail», затем в появившемся меню щелкаем по пункту «Настроить компонент Database Mail».
- Шаг 2
В итоге запустится мастер настройки компонента Database Mail, жмем «Далее».
- Шаг 3
Затем мы должны будем выбрать задачу по настройке, но настроек пока нет никаких, так как компонент Database Mail мы настраиваем впервые, соответственно выбираем пункт «Установить Database Mail, выполнив следующие задачи», жмем «Далее».
- После этого, скорей всего, SQL Server скажет нам, что компонент Database Mail выключен и предложит нам его включить, жмем «Да». Если данного сообщения не появилось, значит, Database Mail был включен ранее.
- Шаг 4
На этом шаге нам необходимо создать профиль, указываем его название (я его назвал TestProfile) и, если хотим, описание этого профиля. Также здесь нам нужно добавить учетную запись SMTP сервера, для этого используем кнопку «Добавить».
- Шаг 5
И так как учетных записей SMTP сервера в Database Mail у нас нет вообще, у нас сразу откроется окно создания новой учетной записи. Вводим настройки SMTP сервера и нажимаем «OK» (я для примера указал имя учетной записи TestAccount, остальные настройки на скриншоте несуществующие).
- Шаг 6
После чего в списке учетных записей SMTP отобразится наша только что созданная учетная запись (в моем случае TestAccount), жмем «Далее».
- Шаг 7
А далее мы попадаем в окно настроек безопасности. Здесь на вкладке «Личные профили» мы можем назначить каждому пользователю свой личный профиль. В моем случае этого делать не нужно, мы просто созданный нами ранее профиль делаем открытым и говорим, что он будет по умолчанию, жмем «Далее».
- Шаг 8
Затем мы можем настроить системные параметры компонента Database Mail. По названию этих параметров можно определить, за что они отвечают, при необходимости изменяем параметры, и жмем «Далее».
- Шаг 9
Это последний шаг, в котором мастер нам выводит список действий, которые будут сейчас выполнены, жмем «Готово».
- В итоге будут выполнены все запланированные действия и выведен лог этих действий, жмем «Закрыть».
- На этом настройка компонента Database Mail с помощью Management Studio закончена.
- Настройка Database Mail с помощью хранимых процедур языка T-SQL
Ниже я представил скрипт, который выполняет такие же действия, как и те действия, которые мы выполняли в графическом интерфейсе чуть ранее. В него я также добавил инструкцию, которая проверяет, включен ли Service Broker на базе msdb.
Также хотелось отметить, что я попытался задействовать все параметры хранимых процедур, с описанием того для чего они нужны, но многие параметры имеют значения по умолчанию и не являются обязательными. - --Проверка, включен ли Service Broker на базе msdb.
- IF (SELECT is_broker_enabled FROM sys.databases WHERE [name] = 'msdb') = 0
- ALTER DATABASE msdb SET ENABLE_BROKER;
- GO
- -- Включаем компонент Database Mail.
- EXECUTE sp_configure 'Database Mail XPs', 1;
- GO
- RECONFIGURE
- GO
- --Переменные для хранения идентификаторов (профиля и учетной записи).
- DECLARE @ProfileId INT, @AccountId INT;
- --Создаем новый профиль компонента Database Mail.
- EXECUTE msdb.dbo.sysmail_add_profile_sp
- -- Имя нового профиля.
- @profile_name = 'TestProfile',
- -- Описание нового профиля
- (NVARCHAR(256)).
- Необязательный.
- @description = N'Тестовый профиль',
- -- Параметр возвращает идентификатор нового профиля.
- Необязательный.
- @profile_id = @ProfileId OUTPUT;
- --Создаем в Database Mail SMTP-аккаунт для отправки писем.
- EXECUTE msdb.dbo.sysmail_add_account_sp
- -- Имя добавляемой учетной записи.
- @account_name = 'TestAccount',
- -- Адрес электронной почты, от имени которого отправляется сообщение.
- -- Имя, отображаемое в письме электронной почты в поле "От:".
- @display_name = N'Database Mail',
- -- Обратный адрес сообщений, отправленных с этой учетной записи.
- -- Описание учетной записи
- (NVARCHAR(256)).
- Необязательный.
- @description = N'Тестовая учётная запись SMTP сервера',
- -- Имя или IP-адрес почтового SMTP-сервера.
- @mailserver_name = 'TestServer',
- --Тип сервера электронной почты. По умолчанию 'SMTP'.
- @mailserver_type = 'SMTP',
- -- Номер порта SMTP-сервера. По умолчанию 25.
- @port = 25,
- -- Имя пользователя для входа на сервер электронной почты.
- @username = 'TestUser',
- -- Пароль для входа на сервер электронной почты.
- @password = 'Password',
- -- Указывает, посылать ли почту SMTP серверу с помощью учетных данных Database Engine. По умолчанию 0.
- @use_default_credentials = 0,
- -- Включение SSL. Если SMTP-сервер использует защиту SSL, то указываем 1. По умолчанию 0.
- @enable_ssl = 0,
- -- Параметр возвращает идентификатор новой учетной записи. Необязательный. @account_id = @AccountId OUTPUT;
- --Подключаем учетную запись к профилю компонента Database Mail.
- EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
- -- Идентификатор профиля, к которому добавляется учетная запись.
- @profile_id = @ProfileId,
- -- Имя профиля, к которому добавляется учетная запись.
- @profile_name = 'TestProfile',
- -- Можно указывать либо параметр profile_id, либо параметр profile_name.
- -- Идентификатор учетной записи, которая добавляется к профилю.
- @account_name = 'TestAccount',
- -- Имя учетной записи, которая добавляется к профилю.
- @account_id = @AccountId,
- -- Можно указывать либо параметр account_id, либо параметр account_name.
- -- Порядковый номер учетной записи в профиле.
- @sequence_number = 1;
- --Предоставление необходимых прав доступа к профилю Database Mail.
- EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
- -- Идентификатор пользователя или роли в базе данных msdb. 0-этот профиль становится открытым.
- @principal_id = 0,
- -- Имя пользователя или роли в базе данных msdb. 'public'-этот профиль становится открытым.
- @principal_name = 'public',
- -- Можно указывать либо параметр principal_id, либо principal_name.
- -- Идентификатор профиля DBMail, который следует ассоциировать с пользователем или ролью базы данных.
- @profile_id = @ProfileId,
- -- Название профиля DBMail, который следует ассоциировать с пользователем или ролью базы данных.
- @profile_name = 'TestProfile',
- -- Можно указывать либо параметр profile_id, либо profile_name.
- -- Указываем, что профиль является профилем по умолчанию.
- @is_default = 1;
- --Посмотрим на значения идентификаторов.
- SELECT @ProfileId AS ProfileId, @AccountId AS AccountId
- Отправка тестового сообщения
После того как настройка компонента Database Mail закончена, можно проверить его работоспособность путем отправки тестового сообщения. Для более быстрой проверки в среде Management Studio предусмотрен специальный функционал, а именно в обозревателе объектов щелкаем правой кнопкой мыши по компоненту Database Mail и выбираем «Отправить тестовое сообщение…». Затем указываем, кому и жмем «Отправить тестовое сообщение». Если сообщение придет, то это значит, что компонент Database Mail работает.
- Для того чтобы пользователь мог сам отправлять почту с помощью компонента Database Mail, необходимо чтобы он был членом роли DatabaseMailUserRole в базе данных msdb. Для добавления пользователю роли можно использовать процедуру sp_addrolemember, например
sp_addrolemember @rolename = 'DatabaseMailUserRole', @membername = '<имя_пользователя>';
Объекты SQL сервера для работы с Database Mail
Для работы с компонентом SQL Server Database Mail существует достаточно много полезных объектов на SQL сервере (процедур, представлений) с помощью которых мы можем: отправлять письма, смотреть журнал отправленных писем, а также настраивать сам компонент Database Mail.
Хранимая процедура для отправки писем msdb.dbo.sp_send_dbmail
И начну я, конечно же, с той процедуры, которая позволяет нам отправлять письма – это msdb.dbo.sp_send_dbmail. Ниже я представил SQL инструкцию, в которой отправляю тестовое сообщение, с описанием параметров.
EXECUTE msdb.dbo.sp_send_dbmail -- Указываем, какой профиль использовать. @profile_name = 'TestProfile', -- Адрес электронной почты получателя. -- Через точку с запятой можно указать нескольких получателей. @recipients = 'Test@TestClient. ru', -- Адрес электронной почты получателя копии письма. -- Через точку с запятой можно указать нескольких получателей. @copy_recipients = 'TestCopy@TestClient. ru', -- Адрес электронной почты получателя скрытой копии письма. -- Через точку с запятой можно указать нескольких получателей. @blind_copy_recipients = 'TestBlindCopy@TestClient. ru', -- Тема @subject = N'Тестовое письмо', -- Текст письма @body = N'Данное сообщение отправлено с помощью компонента SQL Server Database Mail.', -- Формат текста сообщения. Может содержать два значения TEXT и HTML. По умолчанию TEXT. @body_format = 'TEXT', -- Важность сообщения. Может содержать: Low, Normal и High. По умолчанию Normal. @importance = 'Normal', -- Файл, который мы прикрепляем к письму. Через точку с запятой можно указать несколько файлов. @file_attachments = 'C:\TestCatalog\Документ.txt', -- Запрос, результаты которого включатся в текст сообщения. @query = 'SELECT database_id AS IdDB, name AS NameDB FROM sys.databases';
Хранимые процедуры для настройки системных параметров Database Mail: sysmail_configure_sp и sysmail_help_configure_sp
Помните, на этапе настройки компонента Database Mail через Management Studio, у нас была возможность настройки системных параметров, так вот эти параметры можно изменить с помощью процедуры msdb.dbo.sysmail_configure_sp. Процедура msdb.dbo.sysmail_help_configure_sp показывает текущие значения системных параметров. Для примера давайте изменим значение максимального размера вложения на 2 мегабайта.
--Посмотрим значения параметров до изменения. EXECUTE msdb.dbo.sysmail_help_configure_sp; EXECUTE msdb.dbo.sysmail_configure_sp -- Имя параметра. @parameter_name = 'MaxFileSize', -- Значение параметра. @parameter_value = '2097152'; --Смотрим значения параметров после изменения. EXECUTE msdb.dbo.sysmail_help_configure_sp;
- Системные представления sysmail_allitems, sysmail_sentitems и sysmail_event_log
При работе с электронной почтой в частности с компонентом Database Mail возникает необходимость узнать такие сведения как: какие сообщения были отправлены, когда они были отправлены, какой их статус, а также посмотреть реквизиты всех этих сообщений.
Для этого в SQL сервере существуют специальные представления, которые предоставляют нам всю эту информацию: - msdb.dbo.sysmail_allitems — просмотр всех сообщений;
- msdb.dbo.sysmail_sentitems – просмотр только отправленных сообщений;
- msdb.dbo.sysmail_unsentitems — просмотр неотправленных сообщений;
- msdb.dbo.sysmail_faileditems – просмотр сообщений с ошибками;
- msdb.dbo.sysmail_event_log – журнал работы компонента Database Mail.
- Например, для просмотра всех отправленных сообщений можно использовать следующий запрос:
SELECT sent_date AS [Дата отправки письма], send_request_user AS [Кем отправлено письмо], recipients AS [Кому отправлено письмо], subject AS [Тема письма], body AS [Текст письма], file_attachments AS [Отправленные файлы], query AS [SQL запрос] FROM msdb.dbo.sysmail_sentitems
Другие хранимые процедуры для работы с компонентом Database Mail
Также еще существуют и другие хранимые процедуры для работы с компонентом Database Mail, а именно (некоторые процедуры мы уже использовали в процессе настройки компонента): - msdb.dbo.sysmail_add_profile_sp — создает новый профиль компонента Database Mail;
- msdb.dbo.sysmail_update_profile_sp — изменят профиль;
- msdb.dbo.sysmail_delete_profile_sp — удаляет профиль;
- msdb.dbo.sysmail_help_profile_sp — показывает существующие профили Database Mail;
- msdb.dbo.sysmail_add_account_sp — создает новую учетную запись компонента Database Mail;
- msdb.dbo.sysmail_update_account_sp — изменят учетную запись;
- msdb.dbo.sysmail_delete_account_sp — удаляет учетную запись;
- msdb.dbo.sysmail_help_account_sp — показывает существующие учетные записи Database Mail;
- msdb.dbo.sysmail_add_profileaccount_sp — подключает учетную запись к профилю компонента Database Mail;
- msdb.dbo.sysmail_update_profileaccount_sp — обновляет порядковый номер учетной записи в профиле компонента Database Mail.
- msdb.dbo.sysmail_delete_profileaccount_sp — удаляет учетную запись из профиля;
- msdb.dbo.sysmail_help_profileaccount_sp – показывает привязки между учетными записями и профилями;
- msdb.dbo.sysmail_add_principalprofile_sp – предоставляет необходимые права доступа к профилю Database Mail
- msdb.dbo.sysmail_update_principalprofile_sp — обновляет данные о взаимосвязи между пользователем или ролью базы данных и профилем;
- msdb.dbo.sysmail_delete_principalprofile_sp — удаляет разрешение пользователя или роли базы данных на использование компонента Database Mail;
- msdb.dbo.sysmail_help_principalprofile_sp — показывает взаимосвязи между профилями компонента Database Mail и участниками базы данных;
- msdb.dbo.sysmail_help_status_sp — показывает состояние компонента Database Mail;
- msdb.dbo.sysmail_start_sp – запускает компонент;
- msdb.dbo.sysmail_stop_sp – останавливает компонент;
- msdb.dbo.sysmail_delete_log_sp — очищает журнал компонента Database Mail;
- msdb.dbo.sysmail_delete_mailitems_sp — удаляет сообщения электронной почты из внутренних таблиц Database Mail.
- Более подробную информацию об объектах SQL сервера для работы с компонентом Database Mail можете найти в официальной технической документации.
На этом у меня все, надеюсь, материал был Вам полезен, пока!
