Database Mail: Почтовые рассылки прямо из Microsoft SQL Server

Многие знают, что начиная с версии 2005 в SQL Server существует встроенная возможность посылать электронные письма, которую администраторы баз данных часто используют для отправки срочных оповещений, например, при сбое задач, выполняемых по расписанию. Однако лишь немногим известно, что посылать письма в SQL Server можно прямо из SQL-запросов, функций и хранимых процедур. И если вы один раз уже настроили почту в SQL Server, то на отправку письма у вас уйдет всего минута, а целую рассылку можно организовать за 15-20 минут. Называется эта система Database Mail (DBMail), и сегодня я хотел бы поделиться опытом ее использования.

Настройка

Перед работой с DBMail нужно сначала включить систему и прописать в ней почтовый аккаунт (SMTP), с которого будут рассылаться письма. Работать с IMAP система, к сожалению, не умеет, но это в подавляющем большинстве случаев и не нужно.

Настройка DBMail через интерфейс администратора SQL Server очень хорошо и подробно описана в статье «Настройка Database Mail в MS SQL Server 2005 и старше», так что не буду повторяться. Думаю, этот вариант будет более удобен для тех, кто настраивает систему впервые.

Я, в свою очередь, покажу сейчас альтернативный вариант: как произвести настройку программно, через 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 FilesMicrosoft SQL Server...MSSQLBinn». Важно не забыть разрешить для нее исходящий трафик в брандмауэре (файрволе).

Тестовое письмо

Пора попробовать, все ли в порядке. Осуществить отправку тестового письма может любой пользователь из группы 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

Всё готово. Можно идти отдыхать.

Автор: CodeDriller

Источник