Компоненты высокого уровня доступности для Microsoft SQL Server остаются стабильными со времени появления групп доступности AlwaysOn в редакции Enterprise Edition в SQL Server 2012. С тех пор вносились некоторые изменения (например, ограниченный вариант групп доступности AlwaysOn вошел в редакцию Standard Edition SQL Server 2016), но средства высокой доступности, обеспечивающие доставку журналов, кластеризацию и зеркальное отображение, похоже, достигли зрелости. Чтобы наиболее эффективно задействовать возможности высокого уровня доступности, администраторы баз данных должны понимать, какие основные проблемы возникают при работе с агентом SQL Server (https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent) и группами доступности AlwaysOn (https://technet.microsoft.com/en-us/library/hh510230 (v=sql.110).aspx).

Архитектура групп доступности

Прежде чем продолжить, я хочу напомнить вам основные принципы построения групп доступности AlwaysOn. Группы доступности (AG) состоят из двух или нескольких отдельных серверов, на которых размещается экземпляр SQL Server. Эти серверы именуются репликами. На каждой реплике размещается экземпляр одной или нескольких баз данных, которые состоят в группе доступности. Для любой AG только на одной реплике будут размещены базы данных в пригодном для чтения и записи состоянии; эта реплика называется основной. Другие экземпляры реплики баз данных, участвующих в группе доступности, пригодны или непригодны для чтения, в зависимости от способа структурирования AG при ее построении.

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

Несогласованная настройка задания SQL Server

Агент SQL Server не является частью группы доступности. Этот компонент и базы данных, содержащие метаданные, используемые в его процессах, а именно msdb и master, существуют независимо от AG на каждой реплике. Я располагаю поддерживаемыми средами с экземпляром SQL Server, имеющим задания, которые запланированы в агенте SQL Server и должны выполняться только на доступном для записи экземпляре баз данных или на базе данных, размещенной вне группы доступности на первичной реплике.

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

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

Гибкий рабочий процесс для заданий агента SQL Server и групп доступности

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

Существует только два требования для создания гибкого рабочего процесса:

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

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

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

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

Далее, поскольку функция создана во всех репликах, участвующих в группе доступности, ее можно использовать в задании агента SQL Server. Эта схема иллюстрируется экранами 1, 2 и 3.

 

Шаги задания
Экран 1. Шаги задания

 

 

Программный код шага Litmus Test
Экран 2. Программный код шага Litmus Test

 

 

Рабочий процесс
Экран 3. Рабочий процесс

 

Как показано на экране 1, я вставил новый шаг перед первоначальным шагом 1 и назвал его Litmus Test. Кроме того, я заменил начальный шаг задания на новый.

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

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

Можно вставить указанный выше шаг перед существующим заданием и заменить начальный шаг на шаг проверки (litmus test) в задании агента SQL Server, применяемом к базе данных, размещенной в любой группе доступности. При этом объем дополнительной работы невелик, и вам не придется начинать с нуля. После того как мы перешли на эту модель, моей группе удалось сэкономить много часов работы и избежать отказов заданий.

Листинг 1. Проверка возможности обновить базу данных 
USE [master]
GO

CREATE FUNCTION [dbo].[fn_is_writeable_replica] (@dbname sysname)
RETURNS BIT
WITH EXECUTE AS CALLER
AS

BEGIN

      DECLARE @is_writeable BIT;

      IF EXISTS(SELECT 1 FROM master.sys.databases WHERE [name] = @dbname)
            BEGIN
                  IF (DATABASEPROPERTYEX(@dbname, 'Updateability') <> 'READ_WRITE')
                        SELECT @is_writeable =  0

            ELSE
                  SELECT @is_writeable =  1
            END
      ELSE
            BEGIN
                  SELECT @is_writeable =  0
            END

      RETURN(@is_writeable);

END

GO
Листинг 2. Выбор базы данных для операций
DECLARE @is_prime bit;

SELECT @is_prime = dbo.fn_is_writeable_replica('sql_cruise_db');

IF @is_prime = 0
    BEGIN
          PRINT 'EXITING GRACEFULLY';
          THROW 51000, 'This is not a writeable replica', 1;
    END