В этой статье мы более подробно рассмотрим простые механизмы, которые могут применяться для отслеживания состояния заданий агента SQL Server, используемых как пакетные задания. О пакетных заданиях речь у нас шла в части 3 «Определение пакетных заданий» (опубликованной в Windows IT Pro/RE № 11).

Зачем нужны таблицы состояния?

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

Для примера представим такую ситуацию. У нас есть процесс, который запускается каждую ночь, и этот процесс в соответствии с политикой аудита и сохранения удаляет данные аудита, полученные более 13 месяцев назад. Теперь предположим, что не так давно в нашей организации прошла дискуссия о том, что такие данные надо иметь под рукой подольше, возможно, в течение 25 месяцев, — но окончательного решения принято не было. В таком случае администратору не стоит вносить в процесс соответствующее изменение (ведь решение пока не принято). Продолжать удаление данных тоже не следует. Проще всего принять решение о деактивации задания и вновь вернуться к проблеме через несколько недель.

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

Реализация

В конце концов я решил в практической работе максимально упрощать таблицы состояний. На мой взгляд, нет никакой нужды нашпиговывать их сведениями, которые мы, специалисты по работе с базами данных, считаем важными (горы метаданных, содержащих информацию по широчайшему кругу вопросов, включая время последних модификаций внесенных в базы изменений, принадлежность ресурсов тем или иным субъектам и т.д.). Я придерживаюсь другого подхода: сохранению подлежит только имя соответствующего задания, а также простой логический флаг, указывающий на то, активировано это задание или нет.

Имея в виду достижение указанной цели, для создания таблицы состояний я использую следующий код:

USE []
GO
CREATE TABLE [dbo].[_JobEnabledStates](
[JobName] [sysname] NOT NULL,
[Enabled] [bit] NOT NULL
) ON [PRIMARY];
GO

Кстати, обратите внимание: определяя две переменные, я использую синтаксис шаблонов T-SQL Template для обеспечения места под заголовки.

Описание параметров:

  • TargetDbName. Имя базы данных, в которой будет функционировать формируемая таблица. Следует отметить, разумеется, что эта база данных должна быть частью группы доступности — той, где будут выполняться ваши пакетные задания.
  • AGName. Имя соответствующей группы доступности. Приведу пример. Если бы я имел дело с двумя базами данных, SSV2 и SSV4 и если бы они были логически связаны и размещены в одной группе доступности с именем SSV, я указал бы SSV4 в качестве значения для переменной TargetDbName и SSV — для переменной AGName.

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

Но после того как таблица сформирована, необходимо заполнить ее данными. Это можно сделать вручную, выполнив ряд процедур, включающих команду INSERT. Дабы избежать столь скучного занятия, я написал простую хранимую процедуру, которая размещается в главной базе данных на всех серверах, участвующих в работе групп доступности (см. листинг).

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

EXEC master.dbo.dba_DocumentJobEnabledStatuses 'SSV', 'SSV4';

Тогда она будет запущена, отбросит все записи, внесенные в таблицу на данный момент, а затем просто «задокументирует» статус (активировано или деактивировано) всех заданий агента SQL Server, которые были внесены в настраиваемую категорию заданий агента SQL Server 'SSV' (упомянутую в предыдущей статье).

Итак, в данном случае идея состоит в том, что как только вы создадите эту таблицу, вам останется лишь запустить приведенную выше хранимую процедуру, и она задокументирует за вас состояния заданий; таким образом, документирование становится простейшим делом, вне зависимости от того, вносятся какие-либо изменения или нет. Вернемся к приведенному выше примеру. Когда администратор временно деактивировал задание по удалению данных аудита, сотрудникам достаточно было деактивировать задание, перейти в основную реплику и запустить представленную выше хранимую процедуру. В результате было бы задокументировано обновленное состояние всех заданий на сервере, ориентированных на выполнение в данной группе доступности.

Не забывайте о синхронизации

Разумеется, описанная выше хранимая процедура заметно облегчает документирование состояний «задание активировано». Но отсюда не следует, что администраторы будут всегда помнить о необходимости запуска данного обновления всякий раз, когда они осуществляют те или иные изменения. И это подводит нас к одной из сложнейших проблем, с которыми приходится сталкиваться при выполнении любых заданий агента SQL Server с использованием групп доступности. Я имею в виду обеспечение постоянной согласованности деталей заданий (и сведений о состоянии) на всех серверах (и по всем данным таблиц состояния).

Организация проверок синхронизации

Если сравнивать группы доступности AlwaysOn с экземплярами отказоустойчивых кластеров AlwaysOn Failover Cluster Instances (FCI) или с кластеризованными системами SQL Server, можно заметить следующее: одна из трудностей при работе с группами доступности состоит в том, что единственная база данных SQL Server (или группа баз данных) размещается на нескольких хостах — иначе говоря, все зависимости, обнаруженные в такой базе данных, должны быть продублированы на всех серверах, где располагается эта база данных. Идея автономных баз данных (Contained Databases), вероятно, пока не достигшая той степени зрелости, которая устроила бы большинство потребителей, помогает решать некоторые из обозначенных проблем. Но, увы, эта идея оказывается бесполезной, когда речь заходит о том, что делать, когда по той или иной причине нам приходится обращаться к базам данных из заданий агента SQL Server. Поэтому если в дело вступают группы доступности AlwaysOn, необходимо проследить за тем, чтобы все сформулированные вами задания (которые будут выполняться в базах данных вашей группы доступности AlwaysOn), были синхронизированы на всех серверах. При этом детали, касающиеся времени выполнения, владения заданиями, оповещений и, разумеется, фактических действий, которые будут предприниматься, должны быть со всей тщательностью синхронизированы на всех серверах топологии.

В этой ситуации в свою очередь возникает два вопроса.

  • Какие механизмы вы используете для синхронизации деталей таких заданий?
  • Как вы можете определить, что задания не синхронизированы?

Методы управления синхронизацией заданий

Существует несколько способов синхронизации заданий агентов SQL Server на различных серверах. Пожалуй, прежде всего в голову приходит вариант синхронизации заданий с помощью службы SQL Server Integration Services (SSIS) и инструмента Transfer Jobs Tasks.

Правда, надо сказать, что это средство было разработано для передачи заданий, а не для их синхронизации. Считается, что оно еще и создает некоторые проблемы, даже когда дело сводится к тривиальной передаче (или перемещению) заданий с одного экземпляра на другой — учитывая, что имеются ограничения на возможные способы обработки состояний «активировано/деактивировано» перемещенных заданий. Варианты управления этими состояниями оставляют желать лучшего, ведь выбор невелик: все перемещенные задания должны быть активированы/деактивированы на целевом/конечном сервере.

Соответственно, предпочтительный метод синхронизации заданий агентов SQL Server между серверами состоит, как ни странно, в следующем: задавать их в виде сценария на одном сервере, а выполнять — на других серверах. Рассуждая теоретически, можно заключить, что после того как мы синхронизировали все задания, случаи, когда большое число заданий рассинхронизируется с другими заданиями, будут относительно редки. При условии, что время от времени мы будем модифицировать некоторые задания, а это потребует определенных усилий. Но не таких уж больших:

  1. Вносим все необходимые изменения в задание на одном сервере.
  2. После внесения изменений записываем в виде сценария новое определение задания.
  3. Устанавливаем соединения со всеми остальными серверами и удаляем старое определение задания.
  4. Запускаем версию нашего задания в виде сценария, чтобы «заменить» им старое определение.

К сожалению, мы не сможем использовать сценарное задание через функцию DROP and CREATE, потому что эта логика удаляет задания по их идентификаторам (Job ID), а они обычно различаются от сервера к серверу. Подобным же образом, если требуется сохранить историю заданий, то метод удаления и записи поверх старых заданий не подойдет, и нам придется вносить все изменения вручную во все задания.

В остальном я считаю, что метод записи довольно сложен. Правда, вы сможете значительно облегчить свою задачу с помощью двух приемов.

  • Одновременно записывайте несколько заданий агентов SQL Server. Если вам потребуется записывать несколько заданий в одно и то же время, вы сможете выполнить эту работу в среде SQL Server Management Studio (SSMS), воспользовавшись окном Object Explorer Details. Просто выберите на сервере, с которого будете вести запись, узел SQL Server Agent, Jobs а затем откройте окно Object Explorer Details нажатием кнопки F7. Из этого окна вы сможете, используя комбинацию клавиш SHIFT+SELECT и/или CTRL+SELECT, выбрать несколько заданий агента SQL Server, а затем щелкнуть правой кнопкой мыши и записать их по мере необходимости (см. экран 1).
  • Измените подключения, чтобы не прибегать к методу копирования и вставки. Разумеется, если вы создаете сценарий, вам так или иначе придется копировать, вставлять, выполнять его на всех серверах, где этот сценарий необходимо запускать. Пожалуй, это не самая трудная работа, но вам придется установить новые соединения с серверами и ускорить выполнение нового запроса для вставки и выполнения кода. Более простое решение — щелкнуть правой кнопкой мыши на сценарии, который вы только что создали, и выбрать пункты Connection, Change Connection, чтобы изменить соединение с новоиспеченным сценарием. Это даст возможность связаться с одним из серверов, где нужно выполнить данный код (см. экран 2). Затем выполните этот код на целевом сервере, поменяйте и повторяйте по мере необходимости, пока вся задача не будет завершена.

 

Выбор заданий агента SQL Server
Экран 1. Выбор заданий агента SQL Server

 

Выполнение задания на разных серверах
Экран 2. Выполнение задания на разных серверах

Как обнаружить несовпадение заданий

Теперь, поскольку вы разобрались со средствами синхронизации заданий, которые, по вашему заключению, утратили согласованность друг с другом, остается ответить на один вопрос — как выявлять такие проблемы? Увы, для этого вам придется позволить серверам каким-то образом общаться друг с другом. Или использовать разработанный сторонним производителем процесс либо приложение, которое сможет подключиться ко всем серверам, на которых размещается ваша группа доступности, чтобы опросить их относительно деталей заданий. И на основании результатов вы сможете определить, есть ли какие-то проблемы.

Я считаю, что в данном случае следует воспользоваться решением с линковкой серверов, ведь вы и так уже принимаете меры к тому, чтобы серверы, на которых размещается ваша группа (группы) доступности, общались друг с другом. В следующей статье мы рассмотрим ряд связанных с безопасностью проблем, которые нам необходимо решить, чтобы обеспечить взаимодействие кода, выполняемого на основном сервере, с другими серверами в вашей топологии, и получение подробностей заданий. А затем мы обсудим использованную мною логику проверки синхронизации.

Листинг. Заполнение таблицы состояний

USE master;
GO
IF OBJECT_ID('dbo.dba_DocumentJobEnabledStatuses','P') IS NOT NULL
DROP PROC dbo.dba_DocumentJobEnabledStatuses
GO
CREATE PROC dbo.dba_DocumentJobEnabledStatuses
@AGName sysname, — имя AG, обеспечивающей предотвращение отказа.
@JobsTableDBName sysname — имя БД, содержащей таблицу со сведениями об активации заданий
AS
SET NOCOUNT ON;
— если мы не на основном сервере, сообщить об ошибке
-- — поскольку эта хранимая процедура должна выполняться вручную:
IF(SELECT master.dbo.fn_hadr_group_is_primary(@AGName)) = 0 BEGIN
RAISERROR('Сервер не содержит основные реплики для указанных %', 16, 1, @AGName);
RETURN -1;
END
— начать с отбрасывания данных в целевой таблице:
DECLARE @SQL nvarchar(MAX) = N'TRUNCATE TABLE ' + @JobsTableDBName
+ '.dbo.[' + @AGName + '_JobEnabledStates];'
EXEC(@SQL);
— создаем инструкцию по введению (INSERT) записей в таблицу:
+ @AGName + '_JobEnabledStates] (JobName, [Enabled])
SELECT
j.name [JobName],
j.[enabled] [Enabled]
FROM
msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.syscategories c ON c.category_id = j.category_id
WHERE
c.name = @AGName;'
— выполняем инструкцию:
EXEC sp_executesql @SQL, N'@AGName sysname', @AGName;
RETURN 0;
GO