Отправка электронной почты средствами MS SQL Server DBMail

Материал из Lexema.ru
Перейти к: навигация, поиск

Компонент Database Mail — это подсистема для отправки сообщений электронной почты от компонента SQL Server Database Engine. Используя компонент Database Mail, приложения базы данных (запросы, хранимые процедуры, триггеры) могут отправлять почтовые сообщения пользователям. Сообщения могут содержать результаты выборки из БД, а также могут включать файлы из любого доступного сетевого ресурса. Отправка электронных сообщений используется протокол SMTP, наличие дополнительного ПО (например, почтового клиента Outlook) не требуется.

По умолчанию компонент Database Mail не активен. Для использования компонента Database Mail его необходимо явно включить с помощью мастера настройки компонента Database Mail, хранимой процедуры sp_configure или аспекта «Настройка контактной зоны» средства управления на основе политики.

Для использования компонента Database Mail необходимо выполнить ряд действий:

  • включение компонента DBMail
  • настройка:
    • создание учётных записей
    • создание профиля
  • выполнение SQL-скрипта для отправки сообщения электронной почты

Примеры сценариев использования DBMail

  • каждое утро менеджер хочет получать на почту итоги вчерашнего дня: сколько и какой продукции было продано, какой клиент сделал наибольшую заявку, какие клиенты имеют задолженность и прочее. В конечном итоге, результаты любого запроса могут быть отправлены с помощью Database Mail;
  • администратор баз данных хочет получать на почту информацию о том какие задачи (job'ы) или планы обслуживания завершились с ошибкой (или наоборот, выполнились успешно);
  • администратор баз данных хочет получать на почту информацию об ошибках определенного уровня серьезности, или с определенным номером, произошедших на сервере;
  • и т.п.

Параметры безопасности

Для настройки DBMail необходимо быть членом предопределенной роли сервера sysadmin. Чтобы отправлять почту с помощью компонента Database Mail, необходимо быть членом роли DatabaseMailUserRole в базе данных msdb.

Настройка компонента Database Mail

Регистрация учётной записи электронной почты на сервере

До начала настройки DBMail необходимо зарегистрировать новую учётную запись на почтовом сервере или использовать существующую. Понадобятся следующие параметры:

  • символьный или IP-адрес почтового сервера, работающего по протоколу SMTP, а также порт (по умолчанию - 25)
  • поддержка шифрования с помощью SSL/TLS
  • адрес e-mail
  • пароль

Использование мастера настройки

Запустите программу MS SQL Server Management Studio, установите соединение с экземпляром SQL Server. Раскройте узел Управление, щелкните правой кнопкой мыши на компонент Database Mail и выберите команду "Настроить компонент Database Mail". Если он используется впервые, выберите параметр "Установить компонент Database Mail". В противном случае выберите пункт "Настроить компонент Database Mail":

DBMailSettingsMenuItem.png

Окно приветствия можно пропустить, нажав Далее.

В следующем окне необходимо выбрать одну из нескольких задач:

DBmailSettingsTasks.png

Для дальнейшей настройки DBMail необходимо различать 2 элемента:

  • Учётная запись компонента Database Mail - содержит сведения, необходимые для отправки электронной почты на SMTP-сервер (адрес, логин, пароль и т.п.).
  • Профиль компонента Database Mail - является коллекцией учетных записей компонентов Database Mail. Профили повышают надежность в случаях, когда почтовый сервер становится недоступен, предоставляя альтернативные учетные записи компонента Database Mail. Необходима, по крайней мере, одна учетная запись компонента Database Mail.

До начала создания и настройки профиля необходимо создать одну или несколько учетных записей компонента Database Mail.

Создание учётной записи

В диалоговом окне "Выбор задачи по настройке" выберите пункт "Управление учётными записями и профилями компонента Database Mail", в следующем окне - "Создать новую учётную запись". Заполните форму параметров новой учётной записи:

DBMailCreateAccount.png

Подтвердите создание учётной записи нажатием кнопок "Готово" и "Закрыть" в соответствующих диалоговых окнах.

Создание профиля

В диалоговом окне "Выбор задачи по настройке" выберите пункт "Управление учётными записями и профилями компонента Database Mail", в следующем окне - "Создать новый профиль". Заполните форму параметров нового профиля:

Введите имя и описание профиля.

DBmailCreateProfile.png

Для привязки одной или нескольких учётных записей к профилю нажмите кнопку "Добавить":

DBMailAttachAccount.png

Выберите имя созданной ранее учётной записи, нажмите кнопку "ОК". При необходимости привяжите к профилю другие существующие учётные записи или создайте новые. С помощью кнопок "Вверх" и "Вниз" расставьте учётные записи в порядке убывания приоритетов. Наличие нескольких учётных записей повышает надёжность отправки сообщений - при неработоспособности одной УЗ система DBMail автоматически использует другие УЗ из списка. Подтвердите создание профиля нажатием кнопок "Готово" и "Закрыть" в соответствующих диалоговых окнах.

Отправка тестового сообщения

Запустите программу MS SQL Server Management Studio, установите соединение с экземпляром SQL Server. Раскройте узел Управление, щелкните правой кнопкой мыши на компонент Database Mail и выберите команду "Отправить тестовое сообщение". В поле "Кому" введите электронный адрес получателя тестового сообщения, при необходимости смените тему и текст сообщения:

DBMailSendTestMsg.png

Нажмите кнопку "Отправить тестовое сообщение". В диалоговом окне с информацией о сообщении нажмите кнопку "ОК" для его закрытия.

В случае успешной отправки в почтовом ящике получателя отобразиться тестовое сообщение:

DBMailTestMessage.png

Диагностика неисправностей

В случае возникновения проблем с доставкой электронных сообщений в первую очередь необходимо просмотреть журнал компонента DBMail. Для этого щелкните правой кнопкой мыши на компонент Database Mail и выберите команду "Просмотр журнала компонента Database Mail".

DBMailLog.png

В сообщении содержится информация о причине ошибки. Например, в данном случае ошибка связана с отсутствием настройки шифрования подключения с использованием SSL.

Выполнение SQL-запроса для отправки сообщений из скриптов Lexema.ru

Для отправки сообщений электронной почты из SQL-скриптов используется системная хранимая процедура sp_send_dbmail, расположенная в БД msdb:

msdb.dbo.sp_send_dbmail @profile_name = ПРОФИЛЬ, @recipients = ПОЛУЧАТЕЛИ, @subject = ТЕМА_СООБЩЕНИЯ, @body = ТЕКСТ_СООБЩЕНИЯ, @body_format = ФОРМАТ_СООБЩЕНИЯ, @importance = ВАЖНОСТЬ

Параметры:

  • ПРОФИЛЬ - название профиля компонента Database Mail, с помощью которого следует отправить сообщение (работа с профилями описана в данной статье выше)
  • ПОЛУЧАТЕЛИ - список с разделителями (точки с запятыми), содержащий адреса электронной почты, по которым будут рассылаться сообщения. Список получателей относится к типу varchar(max)
  • ТЕМА_СООБЩЕНИЯ - тема сообщения электронной почты, имеет тип nvarchar(255). Если тема не указана, то по умолчанию устанавливается «Сообщение SQL Server»
  • ТЕКСТ_СООБЩЕНИЯ - текст сообщения электронной почты. Аргумент message имеет тип nvarchar(max) и значение по умолчанию NULL
  • ФОРМАТ_СООБЩЕНИЯ - формат текста сообщения. Аргумент имеет тип varchar(20) и значение по умолчанию NULL. Если установлено значение этого аргумента, то устанавливаются заголовки исходящих сообщений, что указывает на формат текста сообщения. Аргумент может содержать одно из следующих значений: TEXT, HTML
  • ВАЖНОСТЬ - важность сообщения. Аргумент имеет тип varchar(6). Аргумент может содержать одно из следующих значений: Low, Normal, High. По умолчанию имеет значение Normal

Полный список параметров и их описание приведены в официальной документации.

Для удобства использования DBMail для отправки электронной почты при разработке прикладных конфигураций в сценариях Lexema целесообразно создать отдельную функцию или метод объекта. Например:

// объект для работы с электронной почтой
L8.App.MailManager = (function () {
    // имя профиля
    var mailProfile = 'Lexema mail demo';

    // метод отправки сообщения
    function sendMessage(recipients, subject, text, fCallback) {
        // адрес для дублирования рассылки 
        var logEmail = 'ecosoft.maillog@gmail.com';
        
        if (recipients) {
            //recipients = recipients + '; ' + logEmail;
            [[
                -- получение профиля из таблицы с настройками модуля СЭД
                declare @EmailProfile varchar(1000)
                select @EmailProfile = EMailProfile from DocflowSettings where LTRIM(RTRIM(isnull(EMailProfile,'')))<>''
                
                if LTRIM(RTRIM(isnull(@EMailProfile,'')))<>''
                begin            
                    -- журналирование фактов отправки сообщений
                    insert dbo.Logs(Level, Message, CreateDate, MachineName, UserName, Details)                                    
                    select 'MailInfoEst', 'EstDBMail. Попытка отправить письмо', GETDATE(), HOST_NAME(), dbo.GetLogin(), :recipients+'/'+:subject+'/'+:text   

                    -- вызов процедуры отправки сообщения
                    DECLARE @res int
                    EXEC @res = msdb.dbo.sp_send_dbmail @profile_name = @EMailProfile, @recipients = :recipients, @subject = :subject, @body = :text, @body_format = 'HTML'
                end
            ]].done(function (data) {
                console.log('Сообщение на адрес ', recipients, ' было успешно отправлено ', new Date().toISOString());

                -- вызов функции обратного вызова, если соответствующий параметр был указан
                if (fCallback && (typeof fCallback === 'function')) {
                    fCallback();
                 };                
            });
        } else {
            console.warn('sendMessage: не указан получатель!', recipients, subject, text, new Date().toISOString());
        }
    }
    
    return {
        sendMessage: sendMessage,
        mailProfile: mailProfile
    }
}());

Примечание: в системе уже создан данный скрипт:

DBMailLexemaScript.png

Для его вызова необходимо подключить этот скрипт к экранной форме и вызвать метод:

L8.App.MailManager.sendMessage('your_mail@gmail.com', 'Тестовое сообщение', 'Тело тестового сообщения', function () {
    console.log('Тестовое сообщение отправлено');
});

Возможные проблемы при отправке

Как показала практика использования компонента DBMail, при попытке отправки большого количества (нескольких сотен или тысяч) сообщений почтовый сервер не успевает обработать большую часть запросов или блокирует их. Для решения данной проблемы были добавлены небольшие паузы (1 сек.) между отправками отдельных сообщений и более длительные паузы между отправками пачек (например, после отправки 30 сообщений - пауза на 30 секунд). Проблема осложнялась асинхронным характером метода отправки сообщений. Применение описанной схемы позволило добиться нормальной отправки всех сообщений. Пример функции отправки множества сообщений по описанному алгоритму показан ниже:

function sendInvitations() {
        var subject = L8.App.Estimate.Settings.InvitationSubject,
            message,
            email,
            link,
            timeout = 100,
            timeoutOffset = 1000, // интервал между отправками писем
            timeoutBatchOffset = 60000, // интервал между пачками
            MSG_BATCH_NUMBER = 30; // кол-во писем в пачке                   
        
            if (confirm('Вы действительно хотите разослать приглашения на оценку выбранным сотрудникам?')) {
                L8.System.pageBlocker.on();
                $.each(employeesList, function (index, employee) {                   
                    if (employee) email = employee.Email;
                    
                    if (employee && email) {
                        var fBuildAndSendMessage = function (resultsVCode, email) {
                            setTimeout(function () {
                                link = buildEstimationLink(resultsVCode);
                                message = L8.App.Estimate.Settings.InvitationMsg;
                                message = replaceField(message, '{ФИО}', employee.FullName);                            
                                message = replaceField(message, '{ССЫЛКА}', link);
                                message = replaceField(message, '{ЛОГИН}', employee.UserName);
                                
                                console.log('sendInv', new Date().toISOString(), email, subject, message);                                                               
                                                            
                                L8.App.MailManager.sendMessage(email, subject, message, function () {
                                    if (index == employeesQuantity - 1) {
                                        L8.System.pageBlocker.off();
                                        L8.View.lkpProcedureonchange();
                                        alert('Приглашения на оценку были отправлены успешно');                                       
                                    }
                                });
                            }, timeout);
                            console.log('timeout', new Date().toISOString(), timeout);
                            // интервал между письмами или пачками
                            if (index % MSG_BATCH_NUMBER == 0) {
                                timeout += timeoutBatchOffset;
                            } else {
                                timeout += timeoutOffset;                       
                            }
                        };                                                
                        // ... сохранение результатов отправки ...                        
                    }
                });             
            }
            
    }

Примечание: в данной задаче требовалось каждому пользователю отправить уникальное сообщение. Если всем получателям необходимо направить одинаковые сообщения, достаточно сформировать список их адресов с разделителем-запятой и передать его в качестве параметра recipients процедуры sp_send_dbmail.

Дополнительные источники