Простой сценарий для настройки компонента SQL Server Database Mail

Компонент Database Mail в Microsoft SQL Server — мощный, но нечасто используемый инструмент, хотя он полезен любому администратору базы данных. Этот компонент существует уже несколько лет, однако и по сей день нередко остается незамеченным. Я обнаружил, что использую Database Mail (далее в статье буду называть его просто DBM) для решения следующих задач:

извещение администраторов баз данных о завершении, сбое или успехе задания;
передача результатов запросов по электронной почте заинтересованным лицам;
передача предупреждений при возникновении определенных условий на экземплярах SQL Server.

Если какие-то из перечисленных функций вас интересуют, то рекомендую прочитать эту статью.

Где искать Database Mail в среде SQL Server Management Studio

DBM находится в узле SQL Server Agent в окне обозревателя объектов Object Explorer (см. экран 1).

Экран 1. Database Mail

Двойным щелчком по этому пункту запускается пошаговый процесс создания профиля DBM и связанной учетной записи. Прежде чем начать, важно уточнить терминологию, относящуюся к учетным записям и профилям.

Учетные записи Database Mail тесно связаны с учетной записью протокола SMTP (Simple Mail Transfer Protocol). Учетная запись DBM, в сущности, представляет собой сохраненную информацию о почтовом сервере, системе защиты процесса подключения к почтовому серверу и о том, как отправитель электронного сообщения будет идентифицирован получателями. При настройке учетной записи DBM вам необходимо предоставить следующие данные:

имя учетной записи;
описание учетной записи;
исходящая информация почтового сервера SMTP;
адрес электронной почты;
отображаемое имя;
адрес электронной почты для ответа;
имя сервера SMTP (приемлемы имена DNS или IP);
номер порта;
информация SSL;
метод проверки подлинности SMTP (Windows, обычный или анонимный).

Профили Database Mail — не более чем идентификаторы, используемые SQL Server для связи запроса («что?») с логистической информацией для доставки («как?»). Все, что требуется, — имя для идентификации профиля, необязательное описание для профиля и имя учетной записи, связанное с профилем. С данным профилем можно связать несколько учетных записей, и если первой учетной записи не удастся выполнить передачу, то будет задействована вторая учетная запись.

Теперь, когда вы получили информацию, необходимую для создания учетных записей и профилей DBM, перейдем к сценарию, который позволит выполнить все действия в одном запросе.

Шаблоны

В данном запросе используются параметры шаблона. Если вы хотите узнать, что это такое, прежде чем перейти к программному коду, обратитесь к статье «Обозреватель шаблонов в среде SSMS» (опубликована в Windows IT Pro/RE № 9 за 2016 год), а также к статье «Обозреватель шаблонов SQL Server Management Studio» (опубликованной в этом же номере журнала). Не обязательно помнить всю информацию, содержащуюся в этих материалах. Достаточно скопировать приведенный в листинге запрос в окно нового запроса в среде SQL Server Management Studio, активном на экземпляре, который предстоит настроить для DBM, и воспользоваться сочетанием клавиш Control + Shift + M или перейти к строке меню и выбрать Query («Запрос»), а затем Specify Values for Template Parameters («Задание значений для параметров шаблона») из раскрывающегося меню.

Запрос

Переходим к тому, ради чего написана эта статья: к сценарию, выполняющему все необходимые действия (см. листинг).

Сценарий разбит на следующие разделы:

Создание учетной записи Database Mail.
Создание профиля Database Mail.
Включение Database Mail для экземпляра SQL Server.
Отправка проверочного сообщения Database Mail.
Назначение профиля Database Mail агенту SQL Server.

Из графического интерфейса пользователя в среде SQL Server Management Studio мы не можем включить Database Mail на экземпляре или назначить профиль агенту SQL Server. Благодаря сценарию экономятся усилия на выполнение соответствующих действий в настройках сервера Facets и агента SQL Server.

При попытке замены параметров шаблона вы увидите окно, подобное представленному на экране 2.

Экран 2. Настройка параметров шаблона

Некоторым параметрам присвоены значения по умолчанию, но не обязательно принимать эти значения. Их основная цель — показать, как выглядят значения по умолчанию в параметрах шаблона в сценарии. В качестве примера на экране 3 показано, как выглядела бы форма, если заполнить ее подходящими значениями для моего тестового экземпляра.

Экран 3. Пример конкретных настроек

После нажатия кнопки OK эти параметры заменят конструкцию в сценарии, а затем вы получите работоспособную установку Database Mail на своем экземпляре. И времени на установку Database Mail вы потратите меньше, чем на чтение этой статьи.

Листинг. Сценарий настройки Database Mail

--======================================================
-- НАСТРОЙКА DATABASE MAIL
--======================================================
--======================================================
-- Создание учетной записи Database Mail
--======================================================
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = '',
    @description = '',
    @email_address = '',
    @replyto_address = '',
    @display_name = '',
    @mailserver_name = '',
        @port = ;

--======================================================
-- Создание профиля Database Mail
--======================================================
DECLARE @profile_id INT, @profile_description sysname;
SELECT @profile_id = COALESCE(MAX(profile_id),1) FROM
msdb.dbo.sysmail_profile
SELECT @profile_description = 'Database Mail Profile for ' + @@servername

EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = '',
    @description = @profile_description;

-- Добавить учетную запись к профилю
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = '',
    @account_name = '',
    @sequence_number = @profile_id;
-- Предоставить доступ к профилю роли DBMailUsers
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = '',
    @principal_id = 0,
    @is_default = 1 ;

--======================================================
-- Включение Database Mail
--======================================================
USE master;
GO
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

--EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'DatabaseMailProfile', N'REG_SZ', N''
--EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1
--GO

EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder = 0
GO

--======================================================
-- Просмотр результатов
--======================================================
SELECT * FROM msdb.dbo.sysmail_profile;
SELECT * FROM msdb.dbo.sysmail_account;
GO

--======================================================
-- Проверка Database Mail
--======================================================
DECLARE @sub VARCHAR(100)
DECLARE @body_text NVARCHAR(MAX)
SELECT @sub = 'Test from New SQL install on ' + @@servername
SELECT @body_text = N'This is a test of Database Mail.' + CHAR(13) + CHAR(13) + 'SQL Server Version Info: ' + CAST(@@version AS VARCHAR(500))

EXEC msdb.dbo.[sp_send_dbmail]
    @profile_name = ''
  , @recipients = ''
  , @subject = @sub
  , @body = @body_text
--======================================================
-- Настройка свойств SQL Agent
--======================================================
EXEC msdb.dbo.sp_set_sqlagent_properties
        @databasemail_profile = ''
        , @use_databasemail=1
GO