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

В статье «Сценарий для настройки Database Mail» (опубликована в Windows IT Pro/RE № 2 за 2014 год) я показал, как составить сценарий для настройки компонента Database Mail для SQL Server 2005 и более новых версий. Для этой задачи я сначала воспользовался данным сценарием, а затем ввел дополнительные шаги, чтобы добавить уведомления ко всем заданиям на сервере.

После того, как Database Mail будет настроен, можно подключиться к объекту JobServer сервера, в котором размещаются задания агента SQL Server.

# SQL 2005 and up
$js = $svr.JobServer

Для всех заданий агента необходимо назначить тип уведомления для использования компонента Database Mail. Поэтому настраиваем свойство AgentMailType сервера заданий на использование Database Mail.

$js.AgentMailType = [Microsoft.SqlServer.Management.Smo.Agent.AgentMailType]::DatabaseMail
$js.DatabaseMailProfile = 'DBAAlertsMail'
$js.Alter()

Кроме того, необходим оператор, который будет получать уведомления. Создаем нового оператора и устанавливаем свойство адреса электронной почты.

$oper = New-Object Microsoft.SqlServer.Management.Smo.Agent.Operator ($js, 'DBAAlerts')
$oper.EmailAddress = 'DBAAlerts@example.com'
$oper.Create()

Теперь достаточно пройти по заданиям и настроить передачу уведомлений о сбоях новым операторам. Сервер заданий располагает коллекцией заданий, а свойство Count сообщает, как много заданий имеется в наличии. Вы перебираете задания одно за другим, указываете в свойстве OperatorToEmail имя оператора, свойству EmailLevel назначаете действие OnFailure и изменяете задание.

for ($i=0; $i -lt $js.Jobs.Count; $i++) {
$job = $js.Jobs[$i]
$job.OperatorToEmail = $oper.Name
$job.EmailLevel = [Microsoft.SqlServer.Management.SMO.Agent.CompletionAction]::OnFailure
$job.Alter()
}

В SQL Server 2005 и более новых версиях это все, что нужно сделать. Однако существует множество компаний, до сих пор работающих с SQL Server 2000, и клиент, задавший вопрос, не был исключением. В этой версии Microsoft предоставила компонент SQL Mail, но для него необходимо установить на сервере клиент MAPI, что связано с некоторым риском для безопасности. Герт Дрейперс, в прошлом эксперт Microsoft, подготовил собственный почтовый компонент SMTP, именуемый xp_smtp_sendmail (по его словам, этот компонент проектировался для включения в состав продукта, но тогдашнее руководство решило иначе). Впоследствии Дрейперс выпустил его самостоятельно, распространяя через свой старый веб-сайт.

Я пытался (безуспешно, впрочем) найти надежный источник этого незаменимого инструмента, но в моем архиве нашлась копия программы. Процедура установки состоит в копировании библиотеки DLL в каталог BINN программы SQL Server и регистрации расширенной хранимой процедуры.

Когда это будет сделано, необходимо изменить задания для уведомления о сбоях. Я не мог задействовать встроенную функцию уведомления, поэтому метод, использованный с Database Mail, непригоден. Все задания этой компании состоят из единственного шага, поэтому я решил добавить новый шаг к каждому заданию, присваивая существующему шагу значение succeed, если он завершается успешно, и переходя к новому шагу, FailNotify, если на первом шаге происходит сбой. Затем я добавил шаг FailNotify, чтобы отправить почтовое сообщение с использованием расширенной хранимой процедуры Герта.

Сначала подключитесь к серверу и назначьте переменной $js значение объекта JobServer сервера.

# For SQL 2000
$svr = New-Object Microsoft.SqlServer.Management.Smo.Server MyServer
$js = $svr.JobServer

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

for ($i=0; $i -lt $js.Jobs.Count; $i++) {
$job = $js.Jobs[$i]
$jnm = $job.Name

Затем создаются инструкции T-SQL, чтобы настроить переменные темы и сообщения, после чего вызывается xp_smtp_sendmail с нужными параметрами. Используйте here-string, чтобы облегчить читаемость.

$cmd = @«
DECLARE @subj varchar(100)
DECLARE @mesg varchar(2000)
select @subj = @@servername + ' — $jnm Failure'
select @mesg = 'The $jnm job on ' + @@servername + ' failed at ' + convert(varchar(25), getdate(), 100)
exec master.dbo.xp_smtp_sendmail
@FROM = N'DBAAlerts@example.com',
@FROM_NAME = N'DBAAlerts',
@TO = N'DBAAlerts@example.com',
@priority = N'NORMAL',
@subject = @subj,
@message = @mesg,
@type = N'text/plain',
@server = N'smtpsrv.example.com'
»@

Необходим счетчик шагов задания. Используйте значение счетчика, уменьшенное на единицу, в качестве указателя на последний шаг в задании. А пока передайте объект JobStep в переменную $jst.

$jsc = $job.JobSteps.Count
$jsu = $jsc — 1
$jst = $job.JobSteps[$jsu]

Создайте новый шаг с именем FailNotify, используя подсистему TransactSql, и назначьте подготовленную ранее команду T-SQL в качестве команды для этого шага.

$jsn = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobStep') ($job, 'FailNotify')
$jsn.SubSystem = 'TransactSql'
$jsn.Command = $cmd

Поскольку это шаг сбоя, как OnSuccessAction, так и OnFailAction устанавливаются в значение QuitWithFailure, и создается шаг в задании.

$jsn.OnSuccessAction = 'QuitWithFailure'
$jsn.OnFailAction = 'QuitWithFailure'
$jsn.Create()

Наконец, OnFailAction исходного завершающего шага устанавливается в значение 'GoToStep', а OnFailStep получает значение на единицу больше, чем последний шаг. Измените шаг и измените задание.

$jst.OnFailAction = 'GoToStep'
$jst.OnFailStep = $jsc + 1
$jst.Alter()
$job.Alter()
}

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

Поделитесь материалом с коллегами и друзьями

Купить номер с этой статьей в PDF