Группы доступности AlwaysOn и задания SQL Server. Часть 11

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

Шаг 1. Начинаем с прилинкованных серверов

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

Шаг 2. С помощью определяемой пользователем функции UDF установите хост, где размещается основная реплика

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

Как отмечалось в части 7 («Развилка на дороге»), если вы используете подход, в соответствии с которым решение об активации или деактивации заданий принимается в зависимости от того, будут ли они выполняться на том же сервере, где находится основная реплика (реплики), без этой логики вам просто не обойтись. С другой стороны, если в шагах задания вы используете логику if-else, вам при осуществлении проверок синхронизации тоже будет полезно знать, на каком сервере размещается основная реплика в целевой группе доступности — на этот раз для того, чтобы упростить проведение проверок и осуществлять их лишь на основном сервере или с основного сервера, как будет показано ниже.

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

Соглашение об именовании, к которому относятся эти определяемые пользователем функции, скопировано с примера sys.fn_hadr_backup_is_preferred_replica. Как и в случае с данной функцией, если вы укажете имена упомянутых выше параметров @DBName или @AGName некорректно, то получите результат FALSE, а не сообщение об ошибке (это сделано намеренно).

Шаг 3. Чтобы проверить задания уровня сервера, разверните хранимую процедуру

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

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

При этом незаметно для постороннего глаза происходит кое-что еще. К примеру, имеется параметр @PrimaryAGAfinity, который используется для указания «сходства» или корреляции относительно указанной группы доступности, размещенной на ваших серверах. Идея состоит в том, что если вы укажете данное значение (например, MyProductionDbs) и SQL Server обнаружит, что группа доступности определена именно на том хосте, где выполняется этот код, и что группа доступности в данный момент не содержит основных реплик, тогда данная логика проверки не будет применяться, ибо она будет исходить из того, что мы намерены выполнять проверки на сервере, где размещается основная реплика (основные реплики).

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

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

Шаг 4. Разверните хранимую процедуру для проверки заданий группы доступности AlwaysOn

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

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

При этом используется код, приведенный в листинге 3.

Если вы решили управлять «пакетными заданиями» (как они определены в части 7) с использованием подхода «активировать или деактивировать», приведенный в листинге 3 код будет работать без дополнительной подстройки и попытается активировать или деактивировать задания на ваших серверах по мере необходимости. С этой целью он будет вызывать следующую хранимую процедуру (которая взаимодействует с таблицей состояния заданий, описанной в части 8; см. листинг 4).

Однако если вы решите действовать в соответствии с логикой IF/ELSE, реализованной в ваших пакетных заданиях, то целесообразно деактивировать (то есть закомментировать) код в «родительской» хранимой процедуре, которая пытается выполнить эти операции. Необходимо отметить, что весь код в данной статье предназначен для случаев, когда группа доступности содержит всего лишь два сервера (отсюда обращения к «партнерскому» связанному серверу; см. часть 9). Если ваша топология включает более двух серверов, базовая логика, представленная в этих статьях, будет работать, но вам придется соответствующим образом модифицировать ее (эту тему мы затронем в одной из следующих статей).

Шаг 5. Создайте задание агента SQL Server, которое будет выполнять регулярные проверки

Итак, код готов, и теперь вы можете приступать к составлению задания, которое будет выполнять регулярные проверки синхронизации. Базовая логика формирования этого задания такова. Сначала нужно составить расписание выполнения повторяющихся проверок, затем добавить отдельный шаг задания для выполнения проверок «уровня сервера», после чего добавить дополнительный шаг задания для каждой группы доступности AlwaysOn, в отношении которой вы намереваетесь осуществлять проверки. Так, если на ваших серверах размещается одна группа доступности, задание будет состоять из двух шагов, а если таких групп доступности будет три, вы получите задание из четырех шагов (один для проверок уровня сервера и по одному шагу задания, иначе говоря, по одному обращению к dba_SyncCheckupForAGLevelJobs для каждой имеющейся группы доступности).

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

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

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; -- основной

  RETURN 0; -- не основной
 
END;
GO

USE master;
GO

IF OBJECT_ID('dbo.dba_SyncCheckupForInstanceLevelJobs','P') IS NOT NULL
DROP PROC dbo.dba_SyncCheckupForInstanceLevelJobs
GO

CREATE PROC dbo.dba_SyncCheckupForInstanceLevelJobs
@MailProfileName sysname = NULL, -- напр., 'General'
@OperatorName  sysname = NULL, -- напр., 'Alerts',
@PrimaryAGAfinity sysname = NULL, -- см. комментарии выше.
@IgnoredJobs  nvarchar(MAX) = ‘’,
@ConsoleOnly  bit = 0  -- выводится только
на консоль - не направляйте предупреждения по электронной
почте (об отладке или выполнении в ручном режиме и т.д.).
AS
SET NOCOUNT ON;

IF @PrimaryAGAfinity IS NOT NULL BEGIN
  IF (SELECT dbo.fn_hadr_group_is_primary(@PrimaryAGAfinity))
  = 0 BEGIN
   PRINT 'Server is Not Primary.'
   RETURN 0;
  END
END
-- если мы не выполняем данную операцию в ручном режиме,
удостоверьтесь, что сервер является основным:
IF @ConsoleOnly = 0 BEGIN - если мы не выполняем данную
операцию в ручном режиме, удостоверьтесь, что мы
располагаем всеми параметрами:
  IF ISNULL(@MailProfileName, '') = '' BEGIN
   RAISERROR('Procedure expects parameter
   ''@MailProfileName'' which was not provided.', 16, 4);
   RETURN -2;
  END

  IF ISNULL(@OperatorName, '') = '' BEGIN
   RAISERROR('Procedure expects parameter
   ''@OperatorName'' which was not provided.', 16, 4);
   RETURN -3;
  END
END;

CREATE TABLE #IgnoredJobs (
  job_name sysname
);

-- однострочная или встроенная ‘разделенная’ функция:
DECLARE @DeserializedJobs nvarchar(MAX) = N'SELECT '
+ REPLACE(REPLACE(REPLACE(N'''{0}''','{0}',@IgnoredJobs),
',', ''','''), ',', ' UNION SELECT ');

INSERT INTO #IgnoredJobs
EXEC(@DeserializedJobs);

DECLARE @LocalServerName sysname = @@SERVERNAME;
DECLARE @RemoteServerName sysname;
SET @RemoteServerName = (SELECT TOP 1 name
  FROM PARTNER.master.sys.servers WHERE server_id = 0);

----------------------------------------------------------------------------
-- Начинаем с проверки всех заданий, не сопоставленных
с категориями, представляющими их имя ...
DECLARE @AvailabilityGroupDatabases TABLE (
  name sysname NOT NULL
);

INSERT INTO @AvailabilityGroupDatabases
SELECT name FROM sys.availability_groups

UNION
SELECT name FROM PARTNER.master.sys.availability_groups;

CREATE TABLE #LocalJobs (
  job_id uniqueidentifier,
  name sysname,
  [enabled] tinyint,
  [description] nvarchar(512),
  start_step_id int,
  owner_sid varbinary(85),
  notify_level_email int,
  notify_email_operator_id int
);

CREATE TABLE #RemoteJobs (
  job_id uniqueidentifier,
  name sysname,
  [enabled] tinyint,
  [description] nvarchar(512),
  start_step_id int,
  owner_sid varbinary(85),
  notify_level_email int,
  notify_email_operator_id int
);

INSERT INTO #LocalJobs
SELECT
  sj.job_id,
  sj.name,
  sj.[enabled],
  sj.[description],
  sj.start_step_id,
  sj.owner_sid,
  sj.notify_level_email,
  sj.notify_email_operator_id
FROM
  msdb.dbo.sysjobs sj
  INNER JOIN msdb.dbo.syscategories sc ON sj.category_id =
  sc.category_id
WHERE
  -- categoryName рассматриваемого задания != an AG Group Name
  UPPER(sc.name) NOT IN (SELECT UPPER(name)
  FROM @AvailabilityGroupDatabases);

INSERT INTO #RemoteJobs
SELECT
  sj.job_id,
  sj.name,
  sj.[enabled],
  sj.[description],
  sj.start_step_id,
  sj.owner_sid,
  sj.notify_level_email,
  sj.notify_email_operator_id
FROM
  PARTNER.msdb.dbo.sysjobs sj
  INNER JOIN PARTNER.msdb.dbo.syscategories sc ON
sj.category_id = sc.category_id
WHERE
  UPPER(sc.name) NOT IN (SELECT UPPER(name)
  FROM @AvailabilityGroupDatabases);

CREATE TABLE #Divergence (
  name sysname,
  diff_type sysname
);

INSERT INTO #Divergence
SELECT
  name,
  'Job Exists on ' + @LocalServerName + ' Only.'
FROM
  #LocalJobs
WHERE
  name NOT IN (SELECT name FROM #RemoteJobs);

INSERT INTO #Divergence
SELECT
  name,
  'Job Exists on ' + @RemoteServerName + ' Only.'
FROM
  #RemoteJobs
WHERE
  name NOT IN (SELECT name FROM #LocalJobs);

INSERT INTO #Divergence
SELECT
  lj.name,
  'Job-Level Differences (enabled, start-step, notification, etc)'
FROM
  #LocalJobs lj
  INNER JOIN #RemoteJobs rj ON rj.name = lj.name
WHERE
  lj.[enabled] != rj.[enabled]
  OR lj.start_step_id != rj.start_step_id
  OR lj.notify_email_operator_id != rj.notify_email_operator_id
  OR lj.notify_level_email != rj.notify_level_email;

CREATE TABLE #LocalJobSteps (
  step_id int,
  [checksum] int
);

CREATE TABLE #RemoteJobSteps (
  step_id int,
  [checksum] int
);

CREATE TABLE #LocalJobSchedules (
  schedule_name sysname,
  [checksum] int
);

CREATE TABLE #RemoteJobSchedules (
  schedule_name sysname,
  [checksum] int
);

-- Теперь (сопоставляя одно задание с другим) пройдем
по всем данным и проверим расписание, а также шаги заданий:
DECLARE checker  FAST_FORWARD FOR
SELECT
  lj.job_id local_job_id,
  rj.job_id remote_job_id,
  lj.name
FROM
  #LocalJobs lj
  INNER JOIN #RemoteJobs rj ON lj.name = rj.name;

DECLARE @LocalJobId uniqueidentifier,
@RemoteJobId uniqueidentifier, @JobName sysname;
DECLARE @LocalCount int, @RemoteCount int;

OPEN checker;
FETCH NEXT FROM checker INTO @LocalJobId, @RemoteJobId,
@JobName;

WHILE @@FETCH_STATUS = 0 BEGIN

  -- сначала проверяются шаги заданий:
  DELETE FROM #LocalJobSteps;
  DELETE FROM #RemoteJobSteps;

  INSERT INTO #LocalJobSteps
  SELECT
  step_id,
  BINARY_CHECKSUM(step_name + subsystem + command
  + STR(on_success_action) + STR(on_fail_action)
  + database_name) [detail]
  FROM msdb.dbo.sysjobsteps
  WHERE job_id = @LocalJobId;

  INSERT INTO #RemoteJobSteps
  SELECT
  step_id,
  BINARY_CHECKSUM(step_name + subsystem + command
  + STR(on_success_action) + STR(on_fail_action)
   + database_name) [detail]
  FROM PARTNER.msdb.dbo.sysjobsteps
  WHERE job_id = @RemoteJobId;

  SELECT @LocalCount = COUNT(*) FROM #LocalJobSteps;
  SELECT @RemoteCount = COUNT(*) FROM #RemoteJobSteps;

  IF @LocalCount != @RemoteCount
   INSERT INTO #Divergence VALUES (@JobName,
   'Different Job Step Counts between Servers');
  ELSE BEGIN
   INSERT INTO #Divergence
   SELECT
    @JobName,
    'Different Job Step Details between Servers'
   FROM
    #LocalJobSteps ljs
    INNER JOIN #RemoteJobSteps rjs ON rjs.step_id =
    ljs.step_id
   WHERE
    ljs.[checksum] != rjs.[checksum];
  END;

  -- Теперь проверяем расписания:
  DELETE FROM #LocalJobSchedules;
  DELETE FROM #RemoteJobSchedules;

  INSERT INTO #LocalJobSchedules
  SELECT
   ss.name,
   BINARY_CHECKSUM(ss.[enabled] + ss.freq_type
   + ss.freq_interval + ss.freq_subday_type +
   ss.freq_subday_interval + ss.freq_relative_interval
   + ss.freq_recurrence_factor +
   ss.active_start_date + ss.active_end_date
   + ss.active_start_date + ss.active_end_time) [details]
  FROM
   msdb.dbo.sysjobschedules sjs
   INNER JOIN msdb.dbo.sysschedules ss ON ss.schedule_id =
    sjs.schedule_id
  WHERE
   sjs.job_id = @LocalJobId;


  INSERT INTO #RemoteJobSchedules
  SELECT
   ss.name,
   BINARY_CHECKSUM(ss.[enabled] + ss.freq_type
   + ss.freq_interval + ss.freq_subday_type +
   ss.freq_subday_interval + ss.freq_relative_interval
   + ss.freq_recurrence_factor +
   ss.active_start_date + ss.active_end_date
   + ss.active_start_date + ss.active_end_time) [details]
  FROM
   PARTNER.msdb.dbo.sysjobschedules sjs
   INNER JOIN PARTNER.msdb.dbo.sysschedules ss
   ON ss.schedule_id = sjs.schedule_id
  WHERE
   sjs.job_id = @RemoteJobId;

  SELECT @LocalCount = COUNT(*) FROM #LocalJobSchedules;
  SELECT @RemoteCount = COUNT(*) FROM #RemoteJobSchedules;

  IF @LocalCount != @RemoteCount
   INSERT INTO #Divergence VALUES (@JobName,
   'Different Job Schedule Counts between Servers');
  ELSE BEGIN
   INSERT INTO #Divergence
   SELECT
    @JobName,
    'Different Schedule Details between Servers.'
   FROM
    #LocalJobSchedules ljs
    INNER JOIN #RemoteJobSchedules rjs
    ON rjs.schedule_name = ljs.schedule_name
   WHERE
    ljs.[checksum] != rjs.[checksum];
  END;
  FETCH NEXT FROM checker INTO @LocalJobId,
  @RemoteJobId, @JobName;
END;
CLOSE checker;
DEALLOCATE checker;

IF(SELECT COUNT(*) FROM #Divergence WHERE name
NOT IN(SELECT job_name FROM #IgnoredJobs)) > 0 BEGIN

  DECLARE @subject sysname = 'SQL Server Agent Job
  Synchronization Problems';
  DECLARE @crlf char(2) = CHAR(13) + CHAR(10);
  DECLARE @tab char(1) = CHAR(9);
  DECLARE @message nvarchar(MAX) = 'Problems detected
  with the following SQL Server Agent Jobs: '
  + @crlf;

  SELECT
   @message = @message + @tab + name + ': ' + @crlf
   + @tab + @tab + diff_type + @crlf
  FROM
   #Divergence
  WHERE
   name NOT IN (SELECT job_name FROM #IgnoredJobs
   WHERE job_name != '')
  ORDER BY
   NAME;

  SELECT @message += @crlf + @crlf + 'Jobs can be synchronized
  by scripting them on the Primary and running scripts
  on the Seconary.'
   + @crlf + @tab + 'To Script Multiple Jobs at once,
   SSMS > SQL Server Agent Jobs > F7 -> then shift/ctrl
   + click to select multiple jobs simultaneously.'

  IF @ConsoleOnly = 1 BEGIN
   -- распечатываем детали:
   PRINT 'SUBJECT: ' + @subject;
   PRINT 'BODY: ' + @crlf + @message;

    END
  ELSE BEGIN
   -- отправляем послание:
   EXEC msdb..sp_notify_operator
    @profile_name = @MailProfileName,
    @name = @OperatorName,
    @subject = @subject,
    @body = @message;
  END;
END;

DROP TABLE #LocalJobs;
DROP TABLE #RemoteJobs;
DROP TABLE #Divergence;
DROP TABLE #LocalJobSteps;
DROP TABLE #RemoteJobSteps;
DROP TABLE #LocalJobSchedules;
DROP TABLE #RemoteJobSchedules;
DROP TABLE #IgnoredJobs;

RETURN 0;

Листинг 3. Проверка заданий уровня группы доступности

USE master;
GO

IF OBJECT_ID('dbo.dba_SyncCheckupForAGLevelJobs','P') IS NOT NULL
DROP PROC dbo.dba_SyncCheckupForAGLevelJobs;
GO

CREATE PROC dbo.dba_SyncCheckupForAGLevelJobs
@AGName sysname, -- Необходимо
@JobsTableDBName sysname, -- Необходимо (извещает
   нас о том, в какой базе
   данных размещается список
   активированных/
   деактивированных заданий).
@MailProfileName sysname = NULL, -- факультативно -
   требуется лишь если
   @ConsoleOnly = 0
@OperatorName sysname = NULL, -- см. выше
@IgnoredJobs nvarchar(MAX) = NULL, -- игнорируемые задания
   (в категории с именем
   MirroredDB)
@ConsoleOnly bit = 0 - передает результаты
   на консоль (1), или
   направляет
   предупреждения/
   результаты по каналам
   электронной почты (0).
AS
SET NOCOUNT ON;

IF ISNULL(@AGName, '') = '' BEGIN
  RAISERROR('Procedure expects parameter ''@AGName''
  which was not provided.', 16, 4);
  RETURN -1;
END

IF ISNULL(@JobsTableDBName, ‘’) = ‘’ BEGIN
  RAISERROR(‘Procedure expects parameter ‘’@JobsTableDBName’’ which was not provided.’, 16, 4);
  RETURN -2;
END

IF @ConsoleOnly = 0 BEGIN - если мы не выполняем процедуру
‘вручную’ - удостоверьтесь, что вы располагаем всеми параметрами:

  IF ISNULL(@MailProfileName, '') = '' BEGIN
   RAISERROR('Procedure expects parameter
   ''@MailProfileName'' which was not provided.', 16, 4);
   RETURN -5;
  END

  IF ISNULL(@OperatorName, '') = '' BEGIN
   RAISERROR('Procedure expects parameter
   ''@OperatorName'' which was not provided.', 16, 4);
   RETURN -6;
  END


END;

-- Начните с выполнения задания, которое прежде всего
синхронизирует или обновляет состояние заданий
«активировано/деактивировано»:
EXEC master.dbo.dba_EnsureAGLevelJobEnabledStatuses
@AGName, @JobsTableDBName;

-- Остальные проверки и отчеты необходимо выполнять только
на основном сервере.
IF(SELECT master.dbo.fn_hadr_group_is_primary(@AGName)) =
0 BEGIN
  PRINT 'Server is Not Primary';
  RETURN 0;
END

CREATE TABLE #IgnoredJobs (
  job_name sysname
);

-- однострочная или встроенная ‘разделенная’ функция:
DECLARE @DeserializedJobs nvarchar(MAX) = N'SELECT '
+ REPLACE(REPLACE(REPLACE(N'''{0}''','{0}',@IgnoredJobs),
',', ''','''), ',', ' UNION SELECT ');

INSERT INTO #IgnoredJobs
EXEC(@DeserializedJobs);

CREATE TABLE #LocalJobs (
  job_id uniqueidentifier,
  name sysname,
  [enabled] tinyint,
  [description] nvarchar(512),
  start_step_id int,
  owner_sid varbinary(85),
  notify_level_email int,
  notify_email_operator_id int
);

CREATE TABLE #RemoteJobs (
  job_id uniqueidentifier,
  name sysname,
  [enabled] tinyint,
  [description] nvarchar(512),
  start_step_id int,
  owner_sid varbinary(85),
  notify_level_email int,
  notify_email_operator_id int
);

INSERT INTO #LocalJobs
SELECT
  sj.job_id,
  sj.name,
  sj.[enabled],
  sj.[description],
  sj.start_step_id,
  sj.owner_sid,
  sj.notify_level_email,
  sj.notify_email_operator_id
FROM
  msdb.dbo.sysjobs sj
  INNER JOIN msdb.dbo.syscategories sc ON sj.category_id =
  sc.category_id
WHERE
  UPPER(sc.name) = UPPER(@AGName);

INSERT INTO #RemoteJobs
SELECT
  sj.job_id,
  sj.name,
  sj.[enabled],
  sj.[description],
  sj.start_step_id,
  sj.owner_sid,
  sj.notify_level_email,
  sj.notify_email_operator_id
FROM
  PARTNER.msdb.dbo.sysjobs sj
  INNER JOIN PARTNER.msdb.dbo.syscategories sc ON
  sj.category_id = sc.category_id
WHERE
  UPPER(sc.name) = UPPER(@AGName);

-- Теперь начнем сопоставление различий:
CREATE TABLE #Divergence (
  name sysname,
  diff_type nvarchar(300)
);

INSERT INTO #Divergence
SELECT
  name,
  'Exists on Primary Server Only'
FROM
  #LocalJobs
WHERE
  name NOT IN (SELECT name FROM #RemoteJobs);

INSERT INTO #Divergence
SELECT
  name,
  'Exists on Secondary Server Only'
FROM
  #RemoteJobs
WHERE
  name NOT IN (SELECT name FROM #LocalJobs);

-- проверим свойства мета-данных:
INSERT INTO #Divergence
SELECT
  lj.name,
  'Job-Level Differences (owner, start-step, notification, etc)'
FROM
  #LocalJobs lj
  INNER JOIN #RemoteJobs rj ON rj.name = lj.name
WHERE
  lj.owner_sid != rj.owner_sid
  OR lj.start_step_id != rj.start_step_id
  OR lj.notify_email_operator_id != rj.notify_email_operator_id
  OR lj.notify_level_email != rj.notify_level_email;


-- Для подтверждения состояния «задание активировано» мы
имеем две проверки:
--  A: Ни одно задание, у которого Job.CategoryName =
   @AGName, не должно быть активировано
   на ВТОРИЧНОМ сервере.
--  B: мы не можем сделать такое же заключение
   применительно к ОСНОВНОМУ серверу. Поэтому если
   Job.CategoryName = @AGName и
--  enabled/disabled != Enabled битовому флагу из
   таблицы _JobEnabledStates, тогда нам нужно
--  инициировать ошибку, поскольку задание, состояние
   которого должно быть «активировано», на деле
   НЕ является таковым (или наоборот).

-- A) Проверка заданий на удаленном сервере:
INSERT INTO #Divergence
SELECT
  name,
  'Job Enabled on SECONDARY SERVER (Jobs should only be
  enabled on PRIMARY).'
FROM
  #RemoteJobs
WHERE
  enabled = 1;

-- B) Проверьте задания на локальном сервере:
DECLARE @Jobs TABLE (
  JobName sysname NOT NULL,
  [Enabled] bit NOT NULL
)

DECLARE @sql nvarchar(MAX) = N'SELECT JobName,
[Enabled] FROM ' + @JobsTableDBName + '.dbo.[' + @AGName
+ '_JobEnabledStates];'

INSERT INTO @Jobs
EXEC(@sql);

-- Сообщите о незадокументированных заданиях:
INSERT INTO #Divergence
SELECT
  lj.name,
  'Job Enabled/Disabled Status is not currently defined.
  Please check job Enabled/Disabled status then run «EXEC
  master.dbo.dba_DocumentJobEnabledStatuses ''' + @AGName
  + ''';» to document Job Enabled/Disabled state.'
FROM
  #LocalJobs lj
WHERE
  lj.name NOT IN (SELECT JobName FROM @Jobs);

-- Сообщите о заданиях, которые активированы, но не должны
быть таковыми:
INSERT INTO #Divergence
SELECT
  lj.name,
  'Job is ENABLED on Primary Replica Server but ' +
  @JobsTableDBName + '.dbo.[' + @AGName +
  '_JobEnabledStates] specifies that Job should be DISABLED.
  (Update Table or Job Status to correct this issue.)'
FROM
  #LocalJobs lj
  LEFT OUTER JOIN @Jobs j ON lj.name = j.JobName
WHERE
  ISNULL(j.[Enabled],0) = 0 AND lj.[enabled] = 1; --
  задание зарегистрировано или установлено по умолчанию
  как деактивированное, но в настоящее время активировано.

-- Сообщите о заданиях, которые деактивированы,
но не должны быть в этом состоянии:
INSERT INTO #Divergence
SELECT
  lj.name,
  'Job is DISABLED on Primary Replica Server but '
  + @JobsTableDBName + '.dbo.[' + @AGName +
  '_JobEnabledStates] specifies that Job should
  be _ENABLED_ (Running). (Update Table or Job Status
  to correct this issue.)'
FROM
  #LocalJobs lj
  LEFT OUTER JOIN @Jobs j ON lj.name = j.JobName
WHERE
  ISNULL(j.[Enabled], 0) = 1 AND lj.[enabled] = 0;
  -- задание зарегистрировано или установлено по умолчанию
  как активированное, но в настоящее время деактивировано

-- Теперь (сопоставляя задание с заданием) пройдитесь
по всем данным и проверьте графики, а также шаги заданий:
CREATE TABLE #LocalJobSteps (
  step_id int,
  [checksum] int
);

CREATE TABLE #RemoteJobSteps (
  step_id int,
  [checksum] int
);

CREATE TABLE #LocalJobSchedules (
  schedule_name sysname,
  [checksum] int
);

CREATE TABLE #RemoteJobSchedules (
  schedule_name sysname,
  [checksum] int
);

DECLARE checker  FAST_FORWARD FOR
SELECT
  lj.job_id local_job_id,
  rj.job_id remote_job_id,
  lj.name
FROM
  #LocalJobs lj
  INNER JOIN #RemoteJobs rj ON lj.name = rj.name;

DECLARE @LocalJobId uniqueidentifier, @RemoteJobId
uniqueidentifier, @JobName sysname;
DECLARE @LocalCount int, @RemoteCount int;

OPEN checker;
FETCH NEXT FROM checker INTO @LocalJobId, @RemoteJobId,
@JobName;

WHILE @@FETCH_STATUS = 0 BEGIN

  -- прежде всего, проверьте шаги заданий:
  DELETE FROM #LocalJobSteps;
  DELETE FROM #RemoteJobSteps;

  INSERT INTO #LocalJobSteps
  SELECT
  step_id,
  BINARY_CHECKSUM(step_name + subsystem + command
  + STR(on_success_action) + STR(on_fail_action)
  + database_name) [detail]
  FROM msdb.dbo.sysjobsteps
  WHERE job_id = @LocalJobId;

  INSERT INTO #RemoteJobSteps
  SELECT
  step_id,
  BINARY_CHECKSUM(step_name + subsystem + command
  + STR(on_success_action) + STR(on_fail_action)
  + database_name) [detail]
  FROM PARTNER.msdb.dbo.sysjobsteps
  WHERE job_id = @RemoteJobId;

  SELECT @LocalCount = COUNT(*) FROM #LocalJobSteps;
  SELECT @RemoteCount = COUNT(*) FROM #RemoteJobSteps;

  IF @LocalCount != @RemoteCount
   INSERT INTO #Divergence VALUES (@JobName,
   'Different Job Step Counts between Servers');
  ELSE BEGIN
   INSERT INTO #Divergence
   SELECT
    @JobName,
    'Different Job Step Details between Servers'
   FROM
    #LocalJobSteps ljs
    INNER JOIN #RemoteJobSteps rjs ON rjs.step_id =
    ljs.step_id
   WHERE
    ljs.[checksum] != rjs.[checksum];
  END;

  -- Теперь проверьте графики:
  DELETE FROM #LocalJobSchedules;
  DELETE FROM #RemoteJobSchedules;

  INSERT INTO #LocalJobSchedules
  SELECT
   ss.name,
   BINARY_CHECKSUM(ss.[enabled] + ss.freq_type
   + ss.freq_interval + ss.freq_subday_type +
   ss.freq_subday_interval + ss.freq_relative_interval
   + ss.freq_recurrence_factor +
   ss.active_start_date + ss.active_end_date
   + ss.active_start_date + ss.active_end_time) [details]
  FROM
   msdb.dbo.sysjobschedules sjs
   INNER JOIN msdb.dbo.sysschedules ss ON ss.schedule_id =
   sjs.schedule_id
  WHERE
   sjs.job_id = @LocalJobId;

  INSERT INTO #RemoteJobSchedules
  SELECT
   ss.name,
   BINARY_CHECKSUM(ss.[enabled] + ss.freq_type
   + ss.freq_interval + ss.freq_subday_type +
   ss.freq_subday_interval + ss.freq_relative_interval
   + ss.freq_recurrence_factor +
   ss.active_start_date + ss.active_end_date
   + ss.active_start_date + ss.active_end_time) [details]
  FROM
   PARTNER.msdb.dbo.sysjobschedules sjs
   INNER JOIN PARTNER.msdb.dbo.sysschedules ss
   ON ss.schedule_id = sjs.schedule_id
  WHERE
   sjs.job_id = @RemoteJobId;
  SELECT @LocalCount = COUNT(*) FROM #LocalJobSchedules;
  SELECT @RemoteCount = COUNT(*) FROM #RemoteJobSchedules;

  IF @LocalCount != @RemoteCount
   INSERT INTO #Divergence VALUES (@JobName,
   'Different Job Schedule Counts between Servers');
  ELSE BEGIN
   INSERT INTO #Divergence
   SELECT
    @JobName,
    'Different Schedule Details between servers.'
   FROM
    #LocalJobSchedules ljs
    INNER JOIN #RemoteJobSchedules rjs ON
    rjs.schedule_name = ljs.schedule_name
   WHERE
    ljs.[checksum] != rjs.[checksum];

  END;

  FETCH NEXT FROM checker INTO @LocalJobId,
  @RemoteJobId, @JobName;
END;

CLOSE checker;
DEALLOCATE checker;

IF(SELECT COUNT(*) FROM #Divergence WHERE name
NOT IN(SELECT job_name FROM #IgnoredJobs)) > 0 BEGIN

  DECLARE @subject sysname = 'SQL Server Agent
  Job Synchronization Problems';
  DECLARE @crlf char(2) = CHAR(13) + CHAR(10);
  DECLARE @tab char(1) = CHAR(9);
  DECLARE @message nvarchar(MAX) = 'Problems detected
  with the following SQL Server Agent Jobs: '
  + @crlf;

  SELECT
   @message = @message + @tab + name + ': ' + @crlf
   + @tab + @tab + diff_type + @crlf
  FROM
   #Divergence
  WHERE
   name NOT IN (SELECT job_name FROM #IgnoredJobs
   WHERE job_name != '')
  ORDER BY
   NAME;

  SELECT @message += @crlf + @crlf + 'Jobs can be synchronized
  by scripting them on the Primary and running scripts
  on the Seconary.'
   + @crlf + @tab + 'To Script Multiple Jobs at once,
   SSMS > SQL Server Agent Jobs > F7 -> then shift/ctrl
   + click to select multiple jobs simultaneously.'

  IF @ConsoleOnly = 1 BEGIN
   -- распечатайте детали:
   PRINT 'SUBJECT: ' + @subject;
   PRINT 'BODY: ' + @crlf + @message;

    END
  ELSE BEGIN
   -- отправьте сообщение:
   EXEC msdb..sp_notify_operator
    @profile_name = @MailProfileName,
    @name = @OperatorName,
    @subject = @subject,
    @body = @message;
  END;
END;

DROP TABLE #LocalJobs;
DROP TABLE #RemoteJobs;
DROP TABLE #Divergence;
DROP TABLE #LocalJobSteps;
DROP TABLE #RemoteJobSteps;
DROP TABLE #LocalJobSchedules;
DROP TABLE #RemoteJobSchedules;
DROP TABLE #IgnoredJobs;

RETURN 0;
GO

USE master;
GO
IF OBJECT_ID('dbo.dba_EnsureAGLevelJobEnabledStatuses','P')
IS NOT NULL
DROP PROC dbo.dba_EnsureAGLevelJobEnabledStatuses
GO
CREATE PROC dbo.dba_EnsureAGLevelJobEnabledStatuses
@AGName sysname, -- имя группы доступности, на которую будет осуществлено аварийное переключение.
@JobsTableDBName sysname - имя базы данных, которая содержит таблицу с деталями активированных заданий
AS
SET NOCOUNT ON;
IF(SELECT master.dbo.fn_hadr_group_is_primary(@AGName)) = 0 BEGIN
  -- Если мы находимся не на ОСНОВНОМ сервере,
  позаботьтесь о том, чтобы все задания с именем категории
  categoryName = @AGName были деактивированы
  DECLARE disabler  FAST_FORWARD FOR
  SELECT
   j.job_id,
   j.name
  FROM
   msdb.dbo.sysjobs  j
   INNER JOIN msdb.dbo.syscategories c ON c.category_id =
   j.category_id
  WHERE
   j.[enabled] = 1 AND
   c.name = @AGName;
  DECLARE @JobId uniqueidentifier;
  DECLARE @JobName sysname;
  OPEN disabler;
  FETCH NEXT FROM disabler INTO @JobId, @JobName;
  WHILE @@FETCH_STATUS = 0 BEGIN
   EXEC msdb.dbo.sp_update_job
    @job_id = @JobId,
    @enabled = 0;
   PRINT 'Disabled Job: [' + @JobName + '] ON Non-Primary Server.';
   FETCH NEXT FROM disabler INTO @JobId, @JobName;
  END;
  CLOSE disabler;
  DEALLOCATE disabler;
   END ;
ELSE BEGIN
  -- иначе, если мы НАХОДИМСЯ на основном сервере,
  мы должны АКТИВИРОВАТЬ задания, которые нужно
  активировать.
  DECLARE @Error sysname;
  DECLARE @Jobs TABLE (
   JobName sysname NOT NULL,
   [Enabled] bit NOT NULL
  );
  DECLARE @sql nvarchar(MAX) = N'SELECT JobName,
  [Enabled] FROM ' + @JobsTableDBName + '.dbo.['
  + @AGName + '_JobEnabledStates];'
  INSERT INTO @Jobs
  EXEC(@sql);
  DECLARE enabler  FAST_FORWARD FOR
  SELECT
   JobName
  FROM
   @Jobs
  WHERE
   [Enabled] = 1;
  OPEN enabler;
  FETCH NEXT FROM enabler INTO @JobName;
  WHILE @@FETCH_STATUS = 0 BEGIN
   SELECT @JobId = job_id FROM msdb.dbo.sysjobs
   WHERE name = @JobName;
   IF @JobId IS NULL BEGIN
    SET @Error = 'Unable to Locate (and set ENABLED)
    on Job: ' + @JobName + '.';
    THROW 50001, @Error, 1;
    RETURN -1; -- готово. Должны ли мы отправить
    предупреждение по электронной почте? Возможно.
   END
   EXEC msdb.dbo.sp_update_job
    @job_id = @JobId,
    @enabled = 1; -- активируйте задание здесь...
   FETCH NEXT FROM enabler INTO @JobName;
  END;
  CLOSE enabler;
  DEALLOCATE enabler;
END;
RETURN 0;
GO