Многие знают, что начиная с версии 2005 в SQL Server существует встроенная возможность посылать электронные письма, которую администраторы баз данных часто используют для отправки срочных оповещений, например, при сбое задач, выполняемых по расписанию. Однако лишь немногим известно, что посылать письма в SQL Server можно прямо из SQL-запросов, функций и хранимых процедур. И если вы один раз уже настроили почту в SQL Server, то на отправку письма у вас уйдет всего минута, а целую рассылку можно организовать за 15-20 минут. Называется эта система Database Mail (DBMail), и сегодня я хотел бы поделиться опытом ее использования.
Настройка
Перед работой с DBMail нужно сначала включить систему и прописать в ней почтовый аккаунт (SMTP), с которого будут рассылаться письма. Работать с IMAP система, к сожалению, не умеет, но это в подавляющем большинстве случаев и не нужно.
Я, в свою очередь, покажу сейчас альтернативный вариант: как произвести настройку программно, через SQL-скрипт. Этот вариант не так нагляден, но он быстрее и будет удобнее тем, кому нужно производить настройку DBMail на многих серверах или переносить настройки с сервера на сервер, в этом случае будет достаточно просто запустить этот скрипт на новом сервере. Важно только не забыть, что для использования нижеследующих скриптов нужно войти на сервер как член группы sysadmin, иначе система безопасности укажет на недостаток прав.
Перейдем сразу к делу. Вот скрипт, подготавливающий сервер для использования DBMail:-- Сначала включим Service broker - он необходим для создания очередей -- писем, используемых DBMail IF (SELECT is_broker_enabled FROM sys.databases WHERE [name] = 'msdb') = 0 ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK AFTER 10 SECONDS GO -- Включим непосредственно систему DBMail sp_configure 'Database Mail XPs', 1 GO RECONFIGURE GO
Далее нужно проверить, запущена ли служба DBMail:EXECUTE msdb.dbo.sysmail_help_status_sp
И если она не запущена (ее статус не "STARTED"), то запустить ее запросомEXECUTE msdb.dbo.sysmail_start_sp
Теперь нужно создать SMTP-аккаунт для отсылки писем, создать профиль администратора почтовых рассылок и подключить SMTP-аккаунт к этому профилю. Предположим, что администратору сайта MySite.ru нужно организовать почтовую рассылку для зарегистрированных пользователей своего сайта, и для этого он использует свой ящик admin@mysite.ru на сервере smtp.mysite.ru.-- Создадим SMTP-аккаунт для отсылки писем EXECUTE msdb.dbo.sysmail_add_account_sp -- Название аккаунта @account_name = 'admin@mysite.ru', -- Краткое описание аккаунта @description = N'Почтовый аккаунт admin@mysite.ru', -- Почтовый адрес @email_address = 'admin@mysite.ru', -- Имя, отображаемое в письме в поле "От:" @display_name = N'Администратор MySite.ru', -- Адрес, на который получателю письма нужно отправлять ответ -- Если ответа не требуется, обычно пишут "no-reply" @replyto_address = 'no-reply@please.no-reply', -- Домен или IP-адрес SMTP-сервера @mailserver_name = 'smtp.mysite.ru', -- Порт SMTP-сервера, обычно 25 @port = 25, -- Имя пользователя. Некоторые почтовые системы требуют указания всего -- адреса почтового ящика вместо одного имени пользователя @username = 'admin', -- Пароль к почтовому ящику @password = 'MyPassword', -- Защита SSL при подключении, большинство SMTP-серверов сейчас требуют SSL @enable_ssl = 1; -- Создадим профиль администратора почтовых рассылок EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'MySite Admin Mailer'; -- Подключим SMTP-аккаунт к созданному профилю EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'MySite Admin Mailer', @account_name = 'admin@mysite.ru', -- Указатель номера SMTP-аккаунта в профиле @sequence_number = 1; -- Установим права доступа к профилю для роли DatabaseMailUserRole базы MSDB EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'MySite Admin Mailer', @principal_id = 0, @is_default = 1;
Для надежности рекомендуется создать пару SMTP-аккаунтов для двух разных почтовых служб и подключить их к профилю. Это позволит отправить важное письмо даже при отсутствии связи с одним из SMTP-серверов. В этом случае для приоритетного SMTP-аккаунта параметр @sequence_number процедуры sysmail_add_profileaccount_sp должен равняться 1 (см. выше), для запасного аккаунта параметр должен равняться 2.
Очень массовые почтовые рассылки рекомендуется производить с ящика частного домена. При попытке рассылки с ящиков публичных доменов (ящиков на yandex.ru, mail.ru, gmail.com и т. д.) SMTP-серверы могут счесть вас спамером и заблокировать рассылку или даже весь ящик.
Брандмауэр
Непосредственной отправкой писем в SQL Server будет заниматься отдельная программа. В разных версиях SQL Server она называется "DatabaseMail90.exe" или "DatabaseMail.exe" и по умолчанию располагается в папке "C:\Program Files\Microsoft SQL Server\...\MSSQL\Binn\". Важно не забыть разрешить для нее исходящий трафик в брандмауэре (файрволе).
Тестовое письмо
Пора попробовать, все ли в порядке. Осуществить отправку тестового письма может любой пользователь из группы sysadmin, владелец базы (db_owner) MSDB или пользователь с ролью DatabaseMailUserRole. Для добавления пользователю роли DatabaseMailUserRole используется стандартная процедура sp_addrolemember:
sp_addrolemember @rolename = 'DatabaseMailUserRole', @membername = '<имя_пользователя>'; Теперь отправим тестовое письмо:
EXEC msdb.dbo.sp_send_dbmail -- Созданный нами профиль администратора почтовых рассылок @profile_name = 'MySite Admin Mailer', -- Адрес получателя @recipients = 'friend@mysite.ru', -- Текст письма @body = N'Испытание системы SQL Server Database Mail', -- Тема @subject = N'Тестовое сообщение', -- Для примера добавим к письму результаты произвольного SQL-запроса @query = 'SELECT TOP 10 name FROM sys.objects'; Дополнительные параметры процедуры sp_send_dbmail можно посмотреть в ее описании в MSDN.
Если что-то не в порядке, сначала нужно посмотреть на статус письма:SELECT * FROM msdb.dbo.sysmail_allitems
А затем заглянуть в лог:SELECT * FROM msdb.dbo.sysmail_event_log
Наиболее типичные проблемы рассмотрены в статье MSDN "Troubleshooting Database Mail", а также в статье "SQL Server tasks for DBAs: Troubleshooting Database Mail".
Успешно отправленные письма можно посмотреть таким SQL-запросом:SELECT sent_account_id, sent_date FROM msdb.dbo.sysmail_sentitems
Почтовая рассылка
Теперь предположим, что администратору сайта MySite.ru нужно выбрать из таблицы пользователей своего сайта тех, кто не заходил на сайт более года, и отправить им приглашения. Мы специально возьмем настоящую жизненную ситуацию, чтобы продемонстрировать больше возможностей DBMail и SQL, включая курсоры и циклы. Для усложнения задачи добавим еще несколько условий:
- Письма не должны отсылаться тем, кто указал при регистрации, что не желает получать письма. Для еще большей сложности будем определять отказавшихся при помощи отдельного SQL-запроса в цикле.
- Письма должны быть оформлены в формате HTML.
- Письма должны рассылаться с интервалом в 3 секунды, чтобы не перегружать наш SMTP-сервер.
- Рассылка должна начаться не сразу после запуска SQL-скрипта, а, скажем, в 3 часа ночи.
-- Используем условную базу данных mysite USE mysite GO -- Объявим переменные: идентификатор пользователя, имя, -- дата последнего входа на сайт, почтовый адрес DECLARE @user_id int, @user_name nvarchar(255), @last_login_date smalldatetime, @email_address varchar(255); -- Переменная @body будет содержать текст письма в формате HTML DECLARE @body nvarchar(MAX); -- Переменная @no_mail будет обозначать пользователей, отказавшихся от рассылок DECLARE @no_mail int; -- Объявим курсор базы данных, курсор обязательно должен быть локальным -- Для большей скорости также укажем параметр курсора FAST_FORWARD DECLARE users CURSOR LOCAL FAST_FORWARD FOR SELECT id, name, last_login_date, email_address FROM users WHERE user_role IN (3,4,5) AND account_state = 2 AND email_address IS NOT NULL AND DATEDIFF(day, last_login_date, GETDATE()) > 365 ORDER BY id -- Пусть данная рассылка начнется не сейчас, а в 3 часа ночи WAITFOR TIME '03:00:00' -- Открываем курсор users OPEN users -- Возьмем первый ряд выборки из курсора FETCH NEXT FROM users INTO @user_id, @user_name, @last_login_date, @email_address -- Запустим цикл до конца выборки WHILE @@FETCH_STATUS = 0 BEGIN -- Определим, не отказался ли пользователь от рассылок SET @no_mail = (SELECT id FROM users WHERE id = @user_id AND allow_mail = 0) -- Если отказался, то перейдем к следующему пользователю IF @no_mail IS NOT NULL BEGIN PRINT N'Пользователь ' + @user_name + N' отказался от рассылок.' FETCH NEXT FROM users INTO @user_id, @user_name, @last_login_date, @email_address CONTINUE END PRINT N'Отправка письма для ' + @email_address + N' ...' -- Сформируем текст письма SET @body = N' <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <title>Приглашение</title> </head> <body> <p><img style="float:right;" src="http://mysite.ru/images/logo.png"/></p> <p>Здравствуйте, ' + @user_name + N'!</p> <p>Приглашаем Вас снова посетить сайт MySite.ru. У нас появилось много новых интересных функций, обновилось содержание, и мы приложили много усилий для повышения удобства Вашей работы с сайтом.</p> <p>Желаем успехов!</p> <p>С уважением, администрация сайта MySite.ru</p> </body> </html>'; -- Отправим письмо EXEC msdb.dbo.sp_send_dbmail @recipients = @email_address, @subject = N'Приглашаем Вас снова посетить сайт MySite.ru', @body = @body, -- Формат письма может быть либо 'HTML', либо 'TEXT' @body_format = 'HTML', -- При необходимости к письму можно прикрепить файл --@file_attachments ='C:\attachment.jpg', -- Можно также указать адрес для направления копии --@copy_recipients ='me@gmail.com', -- "Blind copy" или "carbon copy" - так называют копии письма, -- получатели которых не видят адресов получателей других копий --@blind_copy_recipients ='me2@gmail.com', -- Укажем созданный ранее профиль администратора почтовых рассылок @profile_name = 'MySite Admin Mailer'; -- Установим интервал в 3 секунды между письмами, чтобы не перегружать SMTP-сервер WAITFOR DELAY '00:00:03'; -- В конце цикла берем следующую запись из курсора FETCH NEXT FROM users INTO @user_id, @user_name, @last_login_date, @email_address END -- Закрываем курсор базы данных CLOSE users GO Всё готово. Можно идти отдыхать.