В состав сервера MS SQL Server входит сервис SQL Server Agent, который состоит из сообщений, операторов и работ. Наибольший интерес программистов и администраторов вызывают работы, поэтому этой теме мы уделим достаточно подробное внимание.
Работа администратора очень часто связана с выполнением однообразных задач, что превращает рабочий день в серые будни. Для меня это самое сложное, поэтому многократно выполняемые задачи я стремлюсь автоматизировать. У MS SQL Server есть достаточно мощное средство автоматизации – работ (job). Работы – это набор определенных действий (например, SQL запросов), которые могут выполняться сервером автоматически в определенное время с помощью планировщика (Schedule) или запускаться администратором вручную.
Ярким примером задач администратора, которые могут вызывать скуку, является обслуживание баз данных, о чем мы будем достаточно много говорить в главе 4. Например, можно запрограммировать сервер так, чтобы он каждый день в конце рабочего дня создавал резервную копию базы данных.
Работы состоят из шагов, которые последовательно выполняются сервером MS SQL Server. Выполнение каждого последующего шага может зависеть от результата предыдущего. Таким образом, можно строить определенную логику задач.
Вы должны учитывать, что работы выполняются не самим сервисом MS SQL Server, а сервисом SQL Server Agent, который входит в поставку MS SQL Server. Поэтому, убедитесь, что этот сервис работает, иначе работы не смогут выполняться по расписанию.
Помимо этого, если сервис обращается к удаленным серверам по сети, то SQL Server Agent должен работать под реальной учетной записью, а не под системной. Чтобы изменить имя пользователя, с правами, которыми работает сервис, запустите оснастку Сервисы (Пуск/Панель управления/Администрирование/Сервисы). Перед вами откроется окно, как на рисунке 3.1. Найдите строку с именем сервиса SQLSERVERAGENT и дважды щелкните по ней. Перейдите на закладку "Вход в систему" (Log on) и укажите реальную учетную запись пользователя, который существует в системе и обладает правами на необходимые ресурсы вашего компьютера и удаленного сервера, к которому будет происходить подключение по сети. Если сервис SQL Server Agent будет работать с правами системного аккаунта, то у него не хватит прав на подключение к удаленной системе, потому что системный аккаунт не имеет имени пользователя и пароля, необходимых для аутентификации.
Начнем с добавления записей. Для этого используется хранимая процедура sp_add_job, которая выглядит следующим образом:
sp_add_job [ @job_name = ] 'job_name'
[ , [ @enabled = ] enabled ]
[ , [ @description = ] 'description' ]
[ , [ @start_step_id = ] step_id ]
[ , [ @category_name = ] 'category' ]
[ , [ @category_id = ] category_id ]
[ , [ @owner_login_name = ] 'login' ]
[ , [ @notify_level_eventlog = ] eventlog_level ]
[ , [ @notify_level_email = ] email_level ]
[ , [ @notify_level_netsend = ] netsend_level ]
[ , [ @notify_level_page = ] page_level ]
[ , [ @notify_email_operator_name = ] 'email_name' ]
[ , [ @notify_netsend_operator_name = ] 'netsend_name' ]
[ , [ @notify_page_operator_name = ] 'page_name' ]
[ , [ @delete_level = ] delete_level ]
[ , [ @job_id = ] job_id OUTPUT ]
Рассмотрим параметры, которые передаются данной процедуре:
Прежде чем использовать процедуру, необходимо отметить, что она принадлежит базе данных msdb, поэтому необходимо подключиться именно к этой базе.
Есть еще одно ограничение – вы должны указывать имена только реально существующих в базе данных операторов, поэтому посмотрим на пример без указания операторов:
USE msdb EXEC sp_add_job @job_name = 'Тестовая работа', @enabled = 1, @description = 'Это тестовая работа', @notify_level_eventlog = 3, @notify_level_email = 2, @notify_level_netsend = 1
Оператор – это описание человека, который должен получать сообщения сервера MS SQL Server и сообщения о ходе выполнения работы. Для создания оператора используется процедура sp_add_operator, которая выглядит следующим образом:
sp_add_operator [ @name = ] 'name'
[ , [ @enabled = ] enabled ]
[ , [ @email_address = ] 'email_address' ]
[ , [ @pager_address = ] 'pager_address' ]
[ , [ @weekday_pager_start_time = ] weekday_pager_start_time ]
[ , [ @weekday_pager_end_time = ] weekday_pager_end_time ]
[ , [ @saturday_pager_start_time = ] saturday_pager_start_time ]
[ , [ @saturday_pager_end_time = ] saturday_pager_end_time ]
[ , [ @sunday_pager_start_time = ] sunday_pager_start_time ]
[ , [ @sunday_pager_end_time = ] sunday_pager_end_time ]
[ , [ @pager_days = ] pager_days ]
[ , [ @netsend_address = ] 'netsend_address' ]
[ , [ @category_name = ] 'category' ]
Рассмотрим параметры этой процедуры:
Как указать, что пользователь доступен с понедельника по пятницу? Для этого складываем соответствующие числа 2+4+8+16+32. В результате мы получим 62 и именно это значение необходимо указать в параметре @pager_days.
Посмотрим, как можно создать оператора, который будет получать сообщения на e-mail адрес:
use msdb exec sp_add_operator @name = 'Андрей', @enabled = 1, @email_address ='[SMTP:flenov@mail.ru]'
Следующий пример создает оператора, который может получать e-mail и NET SEND сообщения:
use msdb exec sp_add_operator @name = 'Михаил', @enabled = 1, @email_address ='[SMTP:admin@mail.ru]', @netsend_address ='admincomp'
Теперь посмотрим, как можно создать работу, в которой сообщения о статусе выполнения работы передаются операторам:
USE msdb EXEC sp_add_job @job_name = 'Тестовая работа 2', @enabled = 1, @description = 'Это тестовая работа с указанием оператора', @notify_level_eventlog = 3, @notify_level_email = 2, @notify_level_netsend = 1, @notify_email_operator_name = 'Андрей', @notify_netsend_operator_name = 'Михаил'
Если хотя бы один из операторов, указанных в примере не будет существовать в базе данных, выполнение процедуры завершиться неудачей.
Для изменения параметров оператора используется процедура sp_update_operator, которая выглядит так:
sp_update_operator [@name =] 'name'
[, [@new_name =] 'new_name']
[, [@enabled =] enabled]
[, [@email_address =] 'email_address']
[, [@pager_address =] 'pager_number']
[, [@weekday_pager_start_time =] weekday_pager_start_time]
[, [@weekday_pager_end_time =] weekday_pager_end_time]
[, [@saturday_pager_start_time =] saturday_pager_start_time]
[, [@saturday_pager_end_time =] saturday_pager_end_time]
[, [@sunday_pager_start_time =] sunday_pager_start_time]
[, [@sunday_pager_end_time =] sunday_pager_end_time]
[, [@pager_days =] pager_days]
[, [@netsend_address =] 'netsend_address']
[, [@category_name =] 'category']
Параметры процедуры изменения оператора такие же, как и при создании, поэтому не будем тратить время на рассмотрения оператора, а лучше посмотрим его работу на практике. Следующий пример изменяет e-mail и сетевой адрес:
exec sp_update_operator @name = 'Михаил', @email_address ='[SMTP:mikhail@mail.ru]', @netsend_address ='notebook'
Указание имени оператора является обязательным, потому что процедура должна знать, какого именно оператора нужно обновлять.
Следующий пример делает оператора не активным, после чего он не будет получать информационные сообщения:
exec sp_update_operator @name = 'Михаил', @enabled=0
Изменяется только параметр @enabled, а все остальные не изменяются и сохраняют свои значения.
Чтобы убедится в том, что изменения прошли успешно, можно воспользоваться процедурой sp_help_operator, которая выводит информацию об операторе. В качестве параметра @operator_name нужно передать имя интересующего вас оператора, например, так:
exec sp_help_operator @operator_name = 'Михаил'
Давайте снова сделаем Михаила активным, чтобы он мог получать информационные сообщения:
exec sp_update_operator @name = 'Михаил', @enabled=1
Для удаления оператора используется процедура sp_delete_operator, которая выглядит следующим образом:
sp_delete_operator [ @name = ] 'name'
[ , [ @reassign_to_operator = ] 'reassign_operator' ]
Здесь всего два параметра:
Следующий пример удаляет оператора Михаил, а все события, которые он отслеживал, будет теперь отслеживать Андрей:
EXEC sp_delete_operator
@name = 'Михаил',
@reassign_to_operator = 'Андрей'
Мы научились создавать и удалять работу, а также добавлять операторов, но все это пока лишено смысла, ведь создаваемая работа еще ничего не умеет делать. Чтобы наделить смыслом предыдущие несколько страниц данной книги, необходимо научиться создавать шаги работы. Для этого используется процедура sp_add_jobstep. В общем виде процедура выглядит следующим образом:
sp_add_jobstep [ @job_id = ] job_id | [ @job_name = ] 'job_name'
[ , [ @step_id = ] step_id ]
{ , [ @step_name = ] 'step_name' }
[ , [ @subsystem = ] 'subsystem' ]
[ , [ @command = ] 'command' ]
[ , [ @additional_parameters = ] 'parameters' ]
[ , [ @cmdexec_success_code = ] code ]
[ , [ @on_success_action = ] success_action ]
[ , [ @on_success_step_id = ] success_step_id ]
[ , [ @on_fail_action = ] fail_action ]
[ , [ @on_fail_step_id = ] fail_step_id ]
[ , [ @server = ] 'server' ]
[ , [ @database_name = ] 'database' ]
[ , [ @database_user_name = ] 'user' ]
[ , [ @retry_attempts = ] retry_attempts ]
[ , [ @retry_interval = ] retry_interval ]
[ , [ @os_run_priority = ] run_priority ]
[ , [ @output_file_name = ] 'file_name' ]
[ , [ @flags = ] flags ]
В первой строке указано, что процедуре необходимо указать или идентификатор или имя работы, которой нужно добавить новый шаг. Давайте рассмотрим параметры более подробно:
Давайте добавим в работу с именем 'Тестовая работа 2' два шага. На первом шаге будет удаляться таблица tbAndrey, а на втором, эта же таблица будет создаваться с помощью оператора SELECT INTO. Создание первого шага для решения данной задачи может выглядеть примерно следующим образом:
EXEC sp_add_jobstep @job_name = 'Тестовая работа 2', @step_name = 'Удаляем таблицу', @subsystem = 'TSQL', @command = 'DROP TABLE tbAndrey', @database_name = 'FlenovSQLBook', @on_success_action = 3, @on_fail_action = 3
Данный шаг будет выполнять команду Transact-SQL, а значит, в параметре @subsystem указываем значение 'TSQL'. В параметре @command указываем непосредственно SQL команду. Так как по умолчанию запрос будет выполняться в базе данных master, то в параметре @database_name явно указываем свою базу.
Основанная задача работы – создать таблицу tbAndrey и заполнить значениями, но для этого сначала старую таблицу нужно удалить. А что если старой таблицы нет (ее кто-то удалил или вообще ее небыло)? В этом случае все равно работа должна продолжать выполняться, поэтому в параметрах @on_success_action и @on_fail_action указываем значение 3, то есть переход на следующий шаг.
Теперь создадим второй шаг, на котором будет производиться создание таблицы:
EXEC sp_add_jobstep
@job_name = 'Тестовая работа 2',
@step_name = 'Выбираем Андреев',
@subsystem = 'TSQL',
@command = 'SELECT * INTO tbAndrey FROM tbPeoples
WHERE vcName=''Андрей''',
@database_name = 'FlenovSQLBook',
@on_success_action = 1,
@on_fail_action = 2
В данном случае переход на следующий шаг не ожидается, поэтому завершаем работу с соответствующим кодом.
Когда вы пишете сценарий для работы, вы можете использовать некоторые вспомогательные конструкции, которые во время выполнения будут заменяться на определенные параметры. Во как сказал! Рассмотрим возможные конструкции, и на что они заменяются:
Будьте внимательны, все конструкции должны заключаться в квадратные скобки, и все они чувствительны к регистру.
Давайте посмотрим, как использовать эти конструкции, а заодно увидим, как можно вставлять новые шаги. Следующим пример не добавляет новый шаг, а вставляет его на первую позицию (параметр @step_id равен 1):
EXEC sp_add_jobstep
@job_name = 'Тестовая работа 2',
@step_id=1,
@step_name = 'Вставляем строку',
@subsystem = 'TSQL',
@command = 'INSERT INTO tbPeoples (vcName, vcSurname)
VALUES(''[MACH]'', ''[STEPID]'')',
@database_name = 'FlenovSQLBook',
@on_success_action = 3,
@on_fail_action = 3
В данном примере, в качестве команды в таблицу tbPeoples вставляется строка, в которой имени назначается имя компьютера (конструкция [MACH]), а в качестве фамилии указывается текущий номер шага (конструкция [STEPID]).
Обратите внимание, что в параметре @command, в SQL запросе в параметре VALUES вставляемые в таблицу значения должны быть в одинарных кавычках, а мы указали по две одинарных с каждой стороны. Почему? Дело в том, что вся команда INSERT должна быть в одинарных кавычках:
@command='КОМАНДА'
Если внутри команды используется одинарная кавычка, то сервер воспримет ее как конец команды, и он станет преждевременным. Например:
@command = 'INSERT INTO tbPeoples (vcName, vcSurname)
VALUES('[MACH]', '[STEPID]')'
В данном случае, сервер поместит в параметр @command только строку:
@command = 'INSERT INTO tbPeoples (vcName, vcSurname)
VALUES('
Именно этот текст находиться между первыми двумя кавычками. Чтобы этого не произошло, внутри команды нужно продублировать все одинарные кавычки, что и происходит в примере выше.
Автоматически заменяемые во время выполнения конструкции действительно могут упростить разработку работ и иногда оказываются незаменимыми, особенно даты и время выполнения работы.
Давайте создадим еще один шаг, на котором будет выполняться системная команда, и при этом этот шаг мы вставим под номером 2:
EXEC sp_add_jobstep @job_name = 'Тестовая работа 2', @step_id=2, @step_name = 'Системная команда', @subsystem = 'CMDEXEC', @command = 'del c:\text.txt', @on_success_action = 3, @on_fail_action = 3
Так как будет выполняться системная команда, параметр @subsystem устанавливаем в CMDEXEC. В параметре @command для примера я указал команду удаления файла text.txt из корня диска С:. Когда вы будете тестировать пример (о том, как это сделать мы узнаем в разделе 3.5.5), не забудьте создать этот файл, чтобы убедиться в том, что файл после выполнения работы исчезает.
При выполнении системных команд вы должны учитывать следующее:
Программист, автор нескольких книг серии глазами хакера и просто блогер. Интересуюсь безопасностью, хотя хакером себя не считаю
Copyright © 2024 Михаил Флёнов. Тираж 1 штука. Отпечатано на ноутбуке. А за права отвечаю, или постараюсь.
А здесь еще и копирайт поставлю: Professional Web Development.