В предыдущих материалах серии я подчеркивал, что хотя решение вопроса о том, следует ли выполнять или воздерживаться от выполнения задания агента SQL Server в отношении базы данных, входящей в группу доступности AlwaysOn, не составляет особого труда, обеспечить выполнение или невыполнение этого задания — проблема чуть более сложная. Это объясняется некоторыми ограничениями, связанными с особенностями функционирования системы SQL Server, а также агента SQL Server. Указанные соображения сохраняют силу.

Неожиданный конфуз

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

 

Неработающий код
Экран 1. Неработающий код

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

 

Новая возможность
Экран 2. Новая возможность

Цена «прокола»

Это очень серьезная проблема, и вот почему.

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

Как ни печально, игнорирование сказанного выше означает следующее.

  • Отказываясь от рассмотрения этого подхода в качестве варианта или обходного маневра, я затрудняю решение всей проблемы, поскольку метод, предусматривающий введение проверки if/else в хранимую процедуру (в целевой базе данных) намного проще, чем то, что делал я.
  • Не учитывая указанный подход, я очень ошибался. Я весьма признателен читателю Дейлу Хирту, который обратил мое внимание на упомянутый вариант в своем комментарии к предыдущей статье. Что и говорить, обидно прослыть идиотом, написавшим целую серию статей. Но еще обиднее оказаться идиотом, написавшим серию статей, которые никто не читает. Как бы то ни было, новый поворот событий несколько усложняет дело. В долгосрочной перспективе перед нами два пути: динамическое выявление во время выполнения задания или вариант с динамической активацией и деактивацией заданий. Ниже публикуются аргументы за и против каждого из названных подходов.

Динамическое выявление во время выполнения (относительно простой подход)

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

Обзор/требования

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

  • Убедиться, что задания выполняются в главной (или другой, не входящей в группу доступности) базе данных.
  • Перед выполнением провести предварительную проверку с помощью логики if/else.
  • Инкапсулировать логику пакетного задания в хранимую процедуру, помещенную в целевую базу данных.
  • Убедиться в том, что задание завершается процедурой синхронизации на всех серверах, участвующих в топологии соответствующей группы доступности AlwaysOn.

За

  • Простота. Применить конструкцию if/else в качестве «оболочки» для решения вопроса о том, следует ли выполнять задание, довольно просто. Более того, когда вы передаете исполнение в целевую базу данных с помощью хранимой процедуры, вам не приходится сталкиваться с негативными явлениями, описанными в предыдущей статье.
  • Хранимые процедуры в качестве контейнеров логических конструкций. Я — сторонник помещения логики пакетных заданий в хранимые процедуры и выполнения инструкции Exec в SQL Server Agent Job Steps. В этом случае вам не нужно пытаться помещать всю логику и код в поле Command шага задания. Иначе говоря, преимущество использования сочетания конструкции If/Else с хранимой процедурой состоит в том, что задача решается достаточно просто.

Против

  • Синхронизация. В сущности, необходимость синхронизации как таковая не является аргументом против: ведь детали задания должны быть синхронизированы на всех серверах вне зависимости от того, какого подхода вы придерживаетесь. Но я решил упомянуть здесь это обстоятельство для полноты картины.
  • Пакеты SSIS. Это главный аргумент против рассматриваемого подхода (использования динамического выявления внутри самих шагов задания), он состоит в том, что реализовать логику If/Else внутри пакета выполнения SSIS (SSIS Package Execution) далеко не так просто. Возьмем задание агента SQL Server, которое должно выполнить шаг задания типа SQL Server Integration Services Package. Строго говоря, возможности внедрить в «инструкции по выполнению» такого задания проверку вида if/else не существует (см. экран 3).
  • Планы по обслуживанию. Планы по обслуживанию сами по себе представляют отдельную тему, и я рассмотрю ее в одной из следующих статей. Помимо того, что эти планы в некотором отношении не выдерживают никакой критики, у них имеется (в контексте нашей темы) один серьезный недостаток: в конечном счете они являются не более чем обыкновенными пакетами SSIS. Однако существует довольно простой способ внедрять в них логику IF/ELSE (когда речь идет об обычных пакетах SSIS, задача решается не так просто).

?

Настройки пакета выполнения SSIS
Экран 3. Настройки пакета выполнения SSIS

Динамическая активация или деактивация заданий (более сложный подход)

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

Обзор/Требования

Чтобы использовать данный подход, потребуется предварительно решить следующие вопросы.

  • Реализовать логику, обеспечивающую периодическую проверку и переключение в состояние активации/деактивации на серверах. Эта логика строится на идее применения определяемых пользователем функций для решения вопроса о том, является ли соответствующий хост владельцем интересующей нас основной реплики, но затем возникают новые задачи, вытекающие из необходимости активации/деактивации заданий.
  • С целью более эффективного определения, какие задания следует, а какие не следует активировать, предусмотреть способ ассоциирования заданий с целевой базой данных (или целевой группой доступности). Более подробно о том, как отличить задания уровня сервера от пакетных заданий, было рассказано в статье, опубликованной в предыдущем номере журнала. Этот вопрос важен, поскольку мы не можем исходить из того, что если основной экземпляр не размещается на том или ином сервере, все задания на данном сервере необходимо деактивировать.
  • Далее, логика, ответственная за активацию или деактивацию заданий, должна предусматривать возможность аварийного переключения входящей в состав группы доступности базы данных на новый сервер (где, предположим, четыре пакетных задания должны выполняться в упомянутой базе данных). При этом не все задания должны быть автоматически активированы в обязательном порядке просто потому, что упомянутая база данных внезапно превратилась в активную основную. Иными словами, если предполагается, что задание будет выполняться в определенной базе данных, отсюда еще не следует, что оно всегда должно быть активировано.

Как и при использовании всех других подходов, описанных выше, синхронизация деталей заданий на всех серверах, входящих в состав группы доступности AlwaysOn, является строго обязательной.

За

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

  • SSIS и планы по обслуживанию. В противоположность другим подходам, описываемым в данной статье, метод деактивации и активации позволяет с большей легкостью работать с заданиями SSIS, особенно когда этих заданий много. Аналогичные преимущества дает он и при работе с планами по обслуживанию (хотя я не вижу причин иметь их на каждом сервере больше нескольких штук, ибо этим планам можно доверять только операции по резервному копированию, если под рукой у вас нет более надежного решения от стороннего поставщика).
  • Он проще в управлении при выполнении сложных заданий. В ходе работы со сложными заданиями с большим количеством шагов (я, кстати, обычно не рекомендую связываться с такими заданиями) требуется вставлять логику if/else во все коды и все шаги задания. В ряде случаев, если эти задания часто модифицируются и обновляются, невключение того или иного блока кода в блок if/else может обернуться проблемой. Поэтому проще, пожалуй, пойти по другому пути: активировать (или не активировать) все задание целиком в зависимости от того, размещается ли оно на хосте, содержащем основную реплику.

Против

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

Построение заданий, которые активируют/деактивируют задания и создание механизмов, отслеживающих активированное/деактивированное состояние различных заданий — это, конечно, не бином Ньютона, но это сложнее, чем просто разместить логику if/else вокруг кода, который вы хотите выполнить внутри простых заданий.

Что дальше

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

Однако наряду с этим некоторое время я хочу посвятить описанию более простого подхода. Как я полагаю, большинство читателей этой серии, по всей вероятности, в конце концов придут к использованию более простого подхода во многих сетях (на мой взгляд, он имеет более широкую область применения, а его реализация и управление требуют меньших усилий). Но с другой стороны, читатели смогут брать на вооружение более сложный подход в ситуациях, когда простой метод не годится — например, при работе с задачами SSIS или со сложными заданиями.

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

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

В остальных случаях вся идея активации и деактивации заданий сводится к следующему. Если нам нужно реализовать пакетное задание, которое выполняется в целевой базе данных MyDatabase и эта база данных размещается в простой, состоящей из двух узлов группе доступности таким образом, что база данных MyDatabase размещается на двух различных серверах/экземплярах, нам необходимо, чтобы логика данного пакетного задания выполнялась в основной реплике. Таким образом, задание агента SQL Server на основном сервере должно быть активировано, тогда как дополняющий его дубликат или клон на вспомогательном сервере должен быть деактивирован (для того чтобы этот дубликат не выполнялся, не пытался внести изменения в собственную восстанавливаемую копию базы данных и не выдавал сообщения об ошибках).

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

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

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

Далее, то обстоятельство, что мы смогли отличить три задания уровня AG от остальных 15 размещенных на сервере заданий, не дает нам оснований без дальнейших разбирательств указывать, какие из этих заданий должны быть активированы на сервере после аварийного переключения. Предположим, что одно из трех заданий уровня AG было намеренно деактивировано администраторами, скажем, на несколько дней. Допустим также, что на сервере Server1 в данный момент размещается основная реплика нашей целевой базы данных. Теперь пусть через день после того, как мы деактивировали одно из этих трех заданий, происходит аварийное переключение. Рассуждая логически, мы можем предположить, что поскольку мы переходим на другой сервер, все три задания уровня AG на сервере Server1 должны быть деактивированы. Но мы не можем огульно заключить, что все три задания уровня AG должны быть активированы на сервере Server2. Если мы допустили такое предположение, у нас возникнут проблемы. Правда, мы можем опросить Server1, дабы выяснить, каков был статус тех трех заданий, но ведь весь смысл применения групп доступности и аварийных переключений как раз в том и состоит, что в ситуации, когда произойдет аварийное переключение, Server1 скорее всего будет недоступен для опроса. Поэтому нам нужны другие средства отслеживания состояния заданий уровня группы доступности (то есть определения, были ли они явным образом отключены администраторами так, что их можно оставить в состоянии деактивации после аварийного переключения).

Решение проблем

Для решения трех перечисленных выше проблем мы используем два метода.

— Идентификация баз данных уровня AG. Прежде всего, для определения того, какие задания агента SQL Server на данном сервере являются пакетными заданиями (или предназначены для выполнения в одной из баз данных, входящих в состав той или иной группы доступности), мы используем схему или метод, применяемый при зеркальном отображении баз данных и описанный специалистами из группы консультантов пользователей SQL Server (blogs.msdn.com/b/sqlcat/archive/2010/04/01/using-sql-agent-job-categories-to-automate-sql-agent-job-enabling-with-database-mirroring.aspx). Этот метод сводится к формированию категорий заданий агента SQL Server с соблюдением следующих требований: имя категории должно соответствовать имени группы доступности, а кроме того задания, предусматривающие работу с соответствующей группой доступности (или с базами данных внутри этой группы), должны быть связаны или ассоциированы с данной категорией.

Для иллюстрации рассмотрим такой пример. Допустим, у нас имеется группа доступности SSV (содержащая единственную базу данных с именем SSV4), а также пакетное задание, которое мы хотим ассоциировать с базой данных SSV4. Для начала щелкнем правой кнопкой мыши на узле SQL Server Agent > Jobs, в открывшемся меню выберем пункт Manage Job Categories и создадим новую категорию заданий с именем SSV (указывающим на имя группы доступности, а не базы данных). Далее войдем в рассматриваемое пакетное задание и укажем, что оно должно относиться к категории SSV, как показано на экране 4.

 

Настройка пакетного задания
Экран 4. Настройка пакетного задания

Теперь нам будет очень просто определить, что данное задание агента SQL Server ассоциировано с группой доступности.

— Идентификация активированных/деактивированных заданий уровня AG. Упомянутая выше статья SQLCAT описывает логику (или код), показывающую, как активировать/деактивировать задания, исходя из категорий заданий агентов SQL Server, к которым они отнесены. Но там не разъясняется, как поступать с заданиями агента SQL Server, которые могли быть временно, но явным образом деактивированы администраторами — неважно, по каким причинам. Чтобы решить данную проблему, мы используем таблицу состояний — простую таблицу, в которой будут указаны имена заданий и их состояния (активировано или деактивировано). Это несколько усложнит нашу задачу. В результате возникнет ряд дополнительных проблем, которые тоже придется решать. Впрочем, обеспечить хранение этой таблицы довольно просто: мы можем поместить ее в одну из баз данных группы доступности (в нашем случае — в базу данных SSV), из чего следует, что любой сервер, где в данный момент размещается группа доступности SSV, будет иметь доступ к информации о состоянии.

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