0,0
рейтинг
15 мая 2013 в 21:09

Разработка → 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 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

Всё готово. Можно идти отдыхать.
Ярослав Васильев @CodeDriller
карма
6,0
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Реклама

Самое читаемое Разработка

Комментарии (31)

  • +1
    За использование WAITFOR TIME в «реальной жизненной ситуации» надо бить. Что, агента в MS SQL нет?
    • 0
      Вы правы, надежнее использовать не WAITFOR TIME, а создать для этого отдельный Job в SQL Agent, хоть это и более трудоемко. Надежнее потому (я поясняю для читателей), что запрос в режиме ожидания WAITFOR TIME будет сброшен при аварийной перезагрузке сервера, а Job в SQL Agent будет выполнен.
      • +3
        То есть сам факт многочасового ожидания в запущенном скрипте вас никак не смущает? Учитывая, что у скриптов бывает таймаут.

        (впрочем, вас и блокировка таблицы с пользователями на неопределенный срок тоже не смущает, чего уж там)
        • 0
          Бог с вами, никакой блокировки таблицы пользователей здесь и в помине быть не может. И никакого таймаута для обычных (не remote) скриптов в SQL Server тоже нет, если вы только сами его не установите.
          • 0
            Вы хотите сказать, что курсор не накладывает никаких блокировок на таблицу, по которой он ходит?
            • 0
              Может, он сделал
              ALTER DATABASE [mysite] SET ALLOW_SNAPSHOT_ISOLATION ON;
              и забыл о блокировках при чтении как о страшном сне
            • 0
              В данной ситуации на время ожидания WAITFOR TIME таблица не блокируется, ни на чтение, ни на запись.
              • 0
                На чтение — верю. А за счет чего она не блокируется на запись? Не может быть двойных чтений и пропущенных записей?

                (может вы еще скажете, что и schema lock на нее не ставится?)
                • 0
                  Если вы не верите про запись — можете проверить сами. Точно я не смогу ответить на ваш вопрос, за счет чего. Это вопрос реализации. Я как-то взял посмотреть «Microsoft SQL Server 2008 Internals», и даже там реализация динамических курсоров освещена не была. Видимо, это составляет технологическую тайну Microsoft. Но, вообще говоря, если верить предположениям, что в динамических курсорах начальный SQL-запрос с минимальными изменениями выполняется на каждый FETCH, то остальное домыслить несложно. Соответственно такой подход в обычной ситуации позволяет поддерживать в курсоре актуальный порядок записей. /Перенес на ветку ниже/
                  • 0
                    Актуальный порядок записей — это как?

                    Возьмем банальный пример: вот у вас идут записи 1, 2, 5, 6 (по id; эта ситуация выглядит надуманной только до тех пор, пока вы сортируете записи по id и id числовой, стоит вам сделать сортировку по имени, или взять id в виде guid — она сразу станет реальной), вы только что обработали запись 2 и попали в ожидание. В его время пользователь добавил запись 3 (у нас же нет блокировки на запись, правда?). Какая запись выберется после ожидания, 3 или 5?

                    Окей, предположим, вам повезло, выбралась 3, вы ее обработали, затем выбрали и обработали 5, снова стоите в ожидании. В это время пользователь добавляет запись 4. Какая запись выбретеся после ожидания, 4 или 6?
                    • 0
                      Первый вопрос: выберется запись 3, потому что после 2 SQL Server снова сделает полный SELECT и указатель курсора (в документации это называется positioning marker) укажет на запись, следующую за 2. Второй вопрос: выберется запись 6, потому что она следует за 5.

                      То есть порядок будет действительно актуальным: все завершенные транзакции будут доступны в курсоре в режиме реального времени, а если установлен уровень изоляции «read uncommitted», то даже и незавершенные.
                      • 0
                        Поздравляю вас, вы только что продолбали одно письмо, которое надо было отправить.
                        • 0
                          Прошу прощения, не понял…?
                          • 0
                            Добавленная запись с id 4.
                            • 0
                              Ну нет, рассылка была предназначена для тех, кто целый год не был на сайте, а не тех, кто зарегистрировался 0,87 секунды назад. :-)
                              • 0
                                Осталось убедить в этом заказчика. Не говоря уже о том, что это для id работает только сценарий с добавлением, а для всех остальных полей — и изменение тоже.

                                Собственно, это наглядная проблема блокировок и их отсутствия.
                                • 0
                                  Изменения полей отражаются в динамическом курсоре тоже в режиме реального времени.

                                  А если вам по какой-то причине будет нужна блокировка, вы можете вызвать ее искусственно.
                                  • 0
                                    Вот только вы про пересортировку записей забыли. «В реальном времени».
                                    • 0
                                      И пересортировка в режиме реального времени будет, на каждый FETCH. Какой вы ORDER для курсора укажете, такая и будет сортировка.
                                      • 0
                                        После чего вы (если не будет блокировок) получите двойные отправки и пропущенные письма.
                                        • 0
                                          Я повторюсь. Вы сами выбираете поля для сортировки. Если вы собираетесь во время выполнения скрипта переприсваивать id пользователей, то вы можете либо сортировать по другому полю, либо вызвать блокировку искусственно. Всё в вашей власти. Хотя переприсваивать идентифицирующие поля я бы вам не рекомендовал — это очень плохая практика.
                                          • 0
                                            Я повторюсь: вы отчетливо не понимаете последствий того, что делаете, решая задачу максимально в лоб. Это опасно.
                            • 0
                              //
                  • 0
                    В данном случае динамические курсоры не к месту. Да и вообще большинство задач можно выполнить без их использования. Возможно поэтому функционал курсоров довольно скудно освещается со стороны Microsoft.

                    Советую вам поближе познакомиться с возможностями службы SQL Agent, в совокупности с SSIS.
                    • 0
                      + полезно хранить информацию о том, что и кому вы отправляли.
                      • 0
                        Эта информация сохраняется автоматически. Я писал выше в статье, отправленные письма можно посмотреть таким SQL-запросом:
                        SELECT * FROM msdb.dbo.sysmail_sentitems
                        
                    • 0
                      В данном случае без курсора вы не обойдетесь. (Впрочем, если вы готовы что-то предложить, я с удовольствием ознакомлюсь.) И для выборки годичных записей не имеет значения статический курсор или динамический. Динамический чуть лучше, потому что он «видит» удаленных во время исполнения скрипта пользователей, а статический «не видит». Хотя это преимущество минимально.

                      Информации насчет курсоров достаточно (лучший, на мой взгляд, источник — книга «SQL Server Query Performance Tuning Distilled»), просто Microsoft не раскрывает детали внутренней реализации курсоров, видимо, считая эту информацию секретной. Может быть, я соберусь еще написать отдельную статью о курсорах и блокировках.

                      Насчет ознакомления с SQL Agent — благодарю, мэтр, это бесценный совет. :-)
                      • 0
                        SQL Agent, в совокупности с SSIS. позволяют автоматизировать абсолютно любые процессы.

                        В данном случае я бы делал рассылку на регулярной основе. Каждый день формируется очередь на отправку уведомлений и сохраняется в реляционную таблицу. В удобное время запускаем job который проходит по необработанным записям в очереди с TOP 1 и отправляет письмо.

                        Такой механизм является более универсальным и позволяет наладить отправку различных уведомлений. Шаблоны писем храним в отдельной таблице.
                        • 0
                          А, понятно. С SSIS я действительно не знаком, поэтому не представляю, как это там делается. При возможности ознакомлюсь. Спасибо. Вариант принимается.
  • 0
    Если вы не верите про запись — можете проверить сами. Точно я не смогу ответить на ваш вопрос, за счет чего. Это вопрос реализации. Я как-то взял посмотреть «Microsoft SQL Server 2008 Internals», и даже там реализация динамических курсоров освещена не была. Видимо, это составляет технологическую тайну Microsoft. Но, вообще говоря, если верить предположениям, что в динамических курсорах начальный SQL-запрос с минимальными изменениями выполняется на каждый FETCH, то остальное домыслить несложно. Соответственно такой подход в обычной ситуации позволяет поддерживать в курсоре актуальный порядок записей.
    • 0
      Извините, пожалуйста, не в ту ветвь запостил ответ.

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.