В предыдущей части статьи (см. Windows IT Pro/RE № 12 за 2014 год), посвященной общим подходам и рациональной основе выполнения пакетных заданий, было показано, что существует два способа выполнения пакетных заданий (или заданий агентов SQL Server, относящихся к базам данных, которые являются частью той или иной группы доступности AlwaysOn). Мы можем иметь дело с одним из двух вариантов. Либо рассматриваемые задания агента SQL Server могут определить, является ли сервер, где они выполняются, владельцем основной реплики базы данных, которую вы указываете, либо вы можете подключать или отключать все задания целиком в зависимости от того, выполняются ли они на серверах, где размещается основная реплика соответствующего задания. В любом случае вам потребуется код, позволяющий определить, является ли на данный момент хост SQL Server, на котором выполняется ваш код, владельцем основной реплики для базы данных этой группы доступности Availability Group (AG).

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

Для иллюстрации этой мысли я привожу в статье два сценария, по одному на каждый подход, см. листинг 1 и листинг 2.

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

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

— псевдокод — скорее всего, он не будет работать в соответствии с вашими ожиданиями:

IF master.dbo.fn_hadr_group_is_primary('MyAGName') = 1 BEGIN

— делайте все, что вы собирались делать в основной реплике:

PRINT 'Выполнение задач в основной реплике';
END
ELSE BEGIN

— мы не в основной реплике — выходим изящно:

PRINT 'Это не основная реплика — успешное завершение';
END

Как и встроенная процедура SQL Server adr_backup_is_preferred_replica(), обе помещенные выше пользовательские функции, написанные мною, весьма чувствительны к тому, как пользователь называет или определяет имена баз данных либо групп доступности. Так, если вы при вводе имени укажете Toolz вместо Tools, то получите ответ Negative (0). Система не будет указывать на ошибку даже в том случае, если Toolz не является допустимым именем группы доступности или базы данных. Это сделано специально.

Дальнейшие действия

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

Первая ловушка на пути динамического выявления способа выполнения задания

Если вы читаете эту серию статей, что называется, для общего развития и вас интересует лишь проблема выбора решения, пользы для вас будет не так много. Работая над статьями, я старался пояснить, ПОЧЕМУ попытки динамически определить, следует ли выполнять то или иное задание (в зависимости от того, размещается ли основная реплика на том сервере, где это задание выполняется), не всегда бывают успешны.

Распознавание не составляет труда, но это только верхушка айсберга

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

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

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

Если бы нам предстояло выполнить такой сценарий, было бы логично присвоить заданию агента SQL Server имя, например, Delete Older User Activity Logs, с соответствующим графиком и предусмотреть в задании единственный шаг с деталями, который имел бы примерно такой вид, как на экране 1.

 

Пример задания
Экран 1. Пример задания

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

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

 

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

Вы просто добавили логическую конструкцию if/else, которая позволит вам динамически определить (во время выполнения), содержит ли сервер, на котором выполняется это задание, основную реплику. Теперь давайте исходить из того, что в вашей группе доступности имеется только два сервера, или хоста. Распределите это единственное задание по двум серверам, исходя из того, что логика, которую вы только что реализовали, решит ваши проблемы и гарантирует, что задание Delete Older Logs теперь будет выполняться лишь на том сервере/базе данных, на котором это возможно и где расположена основная реплика (где разрешены операции чтения/записи).

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

 

Сообщение об ошибке
Экран 3. Сообщение об ошибке

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

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

Листинг 1. Проверка по имени группы доступности

USE master;
GO
IF OBJECT_ID('dbo.fn_hadr_group_is_primary','FN') IS NOT NULL
DROP FUNCTION dbo.fn_hadr_group_is_primary
GO
CREATE FUNCTION dbo.fn_hadr_group_is_primary (@AGName sysname)
RETURNS bit
AS
BEGIN
DECLARE @PrimaryReplica sysname;
SELECT @PrimaryReplica = hags.primary_replica
FROM
sys.dm_hadr_availability_group_states hags
INNER JOIN sys.availability_groups ag ON ag.group_id = hags.group_id
WHERE
ag.name = @AGName;
IF UPPER(@PrimaryReplica) = UPPER(@@SERVERNAME)
RETURN 1; — primary
RETURN 0; — not primary
END;
GO

Листинг 2. Проверка по имени базы данных

USE master;
GO
IF OBJECT_ID('dbo.fn_hadr_database_is_primary','FN') IS NOT NULL
DROP FUNCTION dbo.fn_hadr_database_is_primary
GO
CREATE FUNCTION dbo.fn_hadr_database_is_primary (@DBName sysname)
RETURNS bit
AS
BEGIN
DECLARE @description sysname;
SELECT
@description = hars.role_desc
FROM
sys.DATABASES d
INNER JOIN sys.dm_hadr_availability_replica_states hars ON d.replica_id = hars.replica_id
WHERE
database_id = DB_ID(@DBName);
IF @description = 'PRIMARY'
RETURN 1;
RETURN 0;
END;
GO