Группы доступности AlwaysOn и задания SQL Server, часть 6

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

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

Экран 1. Код определения реплики

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

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

Экран 2. Измененный код

Логика подсказывает, что код, представленный на экране 2, должен работать. Если основная реплика размещается на сервере, где выполняется код, мы перейдем к логике, которая приведет нас в базу данных MyAGDatabase и запустит наш код. А если наш сервер не является основным, то код завершает работу нормально, не выполняя никаких действий. Логически все должно работать нормально — но здесь мы попадаем в ловушку № 2.

Работать с оператором USE труднее, чем может показаться

Смена контекста базы данных (то есть выполнение команды USE dbNameHere) — операция несложная. Но, к сожалению, целесообразность присутствия оператора USE всегда подтверждается в процессе синтаксического анализа запросов или пакетов. Иначе говоря, присутствие одного оператора USE в 1, 10, 200, 2000 и т.д. строках кода будет всегда рассматриваться как ДОПУСТИМАЯ операция еще ДО ТОГО, как начнется фактическое выполнение хотя бы одной строки кода. А это, с учетом того обстоятельства, что база данных MyAGDatabase недоступна, означает, что одним только включением в логику выявления оператора USE проблему не решишь.

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

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

а) произвести синтаксический анализ пакета;

б) определить, что мы имеем дело с инструкцией USE FakeDB;

в) установить, что базы данных FakeDB не существует и что попытка использовать ее по этой причине недопустима;

г) выдать сообщение об ошибке.

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

• Использовать конструкции IF/ELSE. Как и в приведенных примерах, при обнаружении оператора USE эта конструкция всегда выдает сообщение об ошибке.
• Задействовать операторы GOTO. Попытка переломить ситуацию с их помощью не проходит — операторы GOTO тоже наталкиваются на оператора USE.
• «Срезать угол» с помощью команды RETURN. Попытка построить простую логическую конструкцию IF NOT PRIMARY, которая обеспечивала бы выполнение команды RETURN до команды USE, тоже ничего не дает. И в этом случае код подвергается синтаксическому анализу до своего фактического выполнения. Кроме того, применение команды RETURN в окне команд или кода для шага задания SQL Server Agent не допускается.

Варианты и обходные маневры

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

• Задействуйте полностью динамический язык SQL. При одном упоминании об этой возможности я испытываю неловкость. Но как бы то ни было, идея состоит в том, что ВЕСЬ код заключается в оболочку и выполняется с помощью команд EXECUTE или sp_executesql — после решения вопроса о том, следует ли вообще выполнять этот код. В сущности это вполне возможно. Но поскольку данный вариант способен вызвать серьезные угрозы в плане безопасности, а также потому, что по мере усложнения заданий и операций управление процессами становится невероятно трудным, я не могу рассматривать это решение в качестве практически приемлемого.
• Использование хранимых процедур для перенаправления. Это тоже не совсем надежный способ. На мой взгляд, он мало чем отличается от использования полностью динамического SQL – разве что этот вариант чуть лучше. Идея состоит в следующем. Вы можете создавать хранимые процедуры, скажем, в основной базе данных (тогда вам придется обеспечивать синхронизацию соответствующего кода на всех серверах вашей топологии AG). В этом случае шаги или логика вашего задания агента SQL Server смогут выполнять следующие операции: определять, является ли данный хост основным, после чего либо нормально завершать работу, либо продолжать обработку кода или операций, пытаясь выполнить что-то вроде EXEC master.dbo.DoJobLogic @Params, @Etc, где тело этой хранимой процедуры dbo.DoJobLogic будет просто выполнять перенаправление с помощью кода вроде следующего: «DELETE FROM MyAGDatabase.dbo.SomeTable WHERE blahBlahBlah = @Params» и т.д. Опять-таки, я не стал бы рекомендовать этот обходной маневр, но он возможен. Высока вероятность того, что вы сразу же столкнетесь с проблемами, даже при попытке выполнить операции INSERT, UPDATE, DELETE в таблицах, расположенных в другой базе данных (хотя здесь вы можете использовать SYNONMYMS — по-моему, это один из немногих случаев, где это можно считать уместным). И вы, скорее всего, придете к выводу, что обслуживание (особенно с учетом всех перенаправлений) доставит вам немало хлопот. Поэтому я не стал бы рекомендовать и этот способ. Более того, я настоятельно рекомендую воздержаться от его использования (как и от применения динамического SQL).
• Проверки с помощью добавления дополнительных шагов заданий. Этот вариант приемлем, но имеет свои недостатки (как будет показано ниже). Так или иначе, идея состоит в следующем. Если вам нужно выполнить задание агента SQL Server, состоящее всего из одного шага, вы добавляете новый шаг задания перед существующим шагом, чтобы этот новый шаг определял, выступает ли целевая база данных (для второго шага задания) в роли основной реплики на текущем сервере, а затем передавал управление второму шагу (если мы находимся на основном сервере) или завершал выполнение, если наш сервер не основной. Словом, процесс этот довольно запутанный, и я бы не хотел с ним связываться. Кроме того, для него характерны особенности, не соизмеримые с проблемами, типичными для подобных задач. Мы поговорим об этом подробнее чуть позже.
• Отказаться от этого подхода и придумать что-нибудь другое. Вместо того чтобы, образно говоря, забивать в круглое отверстие квадратную затычку (то есть пытаться решить проблему с помощью оператора USE), мы можем взять на вооружение какой-нибудь другой подход. Иными словами, вместо того чтобы пытаться динамически определить, следует ли выполнять код после запуска системы, мы можем пойти по пути, описанному мной в одной из предыдущих статей — просто синхронизировать задания по всем серверам и затем активировать или деактивировать их в зависимости от того, размещаются ли они на серверах, содержащих основную реплику. Да, здесь придется потрудиться. Зато этот метод не имеет ничего общего с теми сомнительными вариантами, которые предполагают «динамическое выявление целесообразности запуска кода в процессе исполнения».

Третья ловушка на пути динамической идентификации

Выше я показал, почему попытка динамически вставить команду USE myTargetDatabase в системе SQL Server удается лишь в случае использования таких небезопасных средств как перенаправления из хранимых процедур или применение динамического SQL. Кроме того, мы говорили о том, что администратор может дополнить свое задание новым шагом и в качестве задачи этого добавленного шага задания предусмотреть процедуру проверки. Теперь мы рассмотрим все обстоятельства, возникающие при использовании такого подхода, и убедимся в том, что данное решение никак нельзя рассматривать в качестве оптимального и пригодного для реализации в сочетании с группами доступности AlwaysOn.

Динамическая проверка в процессе выполнения заданий агента SQL Server

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

Представим себе, что наше задание первоначально состоит всего из одного шага (см. экран 3).

Экран 3. Единственный шаг задания

Чтобы вставить процедуру проверки перед этим шагом задания, откройте окно SQL Server Agent Job и перейдите на узел Steps. Проследите за тем, чтобы данный шаг задания выполнялся в основной базе данных, и далее реализуйте в нем логику, подобную той, что представлена на экране 4. Ваша логика должна обеспечивать генерацию сообщения об ошибке, если мы находимся не на сервере, где размещается основная реплика.

Экран 4. Шаг проверки

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

Экран 5. Дополнительная настройка шага

Это определение совершенно справедливо, ведь вам придется особо проследить за тем, чтобы выполнение задания фактически начиналось с шага № 1 (хотя вы нажимали кнопку Insert для включения этого шага в общую цепочку), см. экран 6. Теперь ваши шаги задания должны выглядеть примерно так, как на экране 7.

Экран 6. Хитрое добавление

Экран 7. Дополнительный шаг

Внеся указанные изменения, вы можете перейти к настоящему шагу задания (то есть к шагу, существовавшему изначально) и настроить его таким образом, чтобы проверки IF/ELSE, без которых нельзя было бы обойтись, если бы вы руководствовались изложенными выше положениями, в данном случае были бы не нужны. Иными словами, поскольку шаг №1 в данном задании в ходе проверки определяет, нужно ли продолжать работу, следовательно, раз уж мы добрались до шага № 2, необходимость в дальнейших проверках отпадает, и мы можем обращаться к базе MyAGDatabase и выполнять наш код или логику, как и предполагалось, не прибегая к каким-либо проверкам, то есть действовать так, как показано на экране 8.

Экран 8. Код для целевой базы данных

Наконец, вам нужно будет передать это обновленное задание на оба сервера (основной и вспомогательный серверы в вашей группе доступности).

Недостатки использования этого метода

Итак, мы перепрыгнули через несколько препятствий и реализовали предшествующую выполнению кода проверку: если выясняется, что мы находимся на сервере, где размещается основная база данных нашей группы доступности, обработка кода продолжается. В ином случае при выполнении шага № 1 возникает ошибка, и задание завершается нормально. Впрочем, препятствий, на мой взгляд, пожалуй, многовато. Конструкция, возникающая при выполнении такого задания, с трудом поддается логическому анализу (к примеру, стороннему специалисту, привлеченному для проведения диагностики, если при выполнении задания возник какой-то сбой, возможно, не удастся сразу сообразить, что именно здесь было сделано). Но есть и более серьезная проблема: что же, собственно, мы получаем в итоге при использовании такого подхода?

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

Экран 9. История выполнения заданий

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

Экран 10. История заданий на неосновном сервере

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

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

Итак, рассматриваемый подход работает, но:

а) в нем довольно непросто разобраться;

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

в) этот метод создает угрозу того, что либо вы, либо члены вашей команды могут подумать, будто где-то произошел сбой (чего не было на самом деле).

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

— FakeDB НЕ существует:
IF EXISTS (SELECT NULL FROM sys.DATABASES
WHERE name = 'FakeDB') BEGIN
USE FakeDB; — это ВСЕГДА будет давать ошибку
PRINT 'Using FakeDB';
END
ELSE
PRINT 'Not Using FakeDB — it doesn''t exist.';