Как сделать синхронизацию Agent-джобов между узлами Always On (AG )

автор: автор автор
27 августа, 2026

Оглавление

Что делает скрипт

Скрипт автоматически поддерживает одинаковый набор заданий SQL Server Agent на всех узлах группы доступности Always On (AG с листенером shr-db04). Он определяет, какая нода сейчас Primary, а какие — Secondary, и:

Бэкапит все текущие джобы на каждой ноде AG (и Primary, и Secondary) перед внесением любых изменений.
Сравнивает список джобов на Primary и на каждой Secondary-ноде.
Копирует на Secondary недостающие джобы, которые есть на Primary (в отключённом виде — -DisableOnDestination, чтобы они не начали выполняться сразу на резервном узле).
Удаляет с Secondary джобы, которых больше нет на Primary (устаревшие/удалённые задания).
Логирует каждый шаг — какие переменные использовались, какие джобы найдены, что было скопировано/удалено.
Автоматически очищает старые логи и бэкапы (старше заданного срока хранения).

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

Как это работает

1. Определение ролей узлов

Скрипт не хранит имена серверов в переменных вручную. Вместо этого при каждом запуске он опрашивает AG через листенер shr-db04 и получает актуальный список реплик с их ролями (Primary / Secondary). Это значит, что при плановом или аварийном failover скрипт автоматически подстроится под новую топологию — правка кода не требуется.

Также если в AG добавится новая Secondary-нода, скрипт обработает её автоматически — она попадёт в общий цикл обработки без изменений в коде.

2. Резервное копирование джобов

Перед любыми изменениями скрипт экспортирует все джобы каждой ноды (и Primary, и Secondary) в отдельные .sql-файлы со скриптами создания (шаги, расписания, уведомления). Бэкапы раскладываются по структуре:

Backup\
  <ИмяСервера>\
    <ГГГГ-ММ-ДД>\
      <Сервер>-<таймстамп>-<ИмяДжоба>-script.sql
      _JobsList_<Сервер>_<таймстамп>.txt   ← опись всех джобов на момент бэкапа

Это позволяет восстановить любой джоб на любую дату без обращения к Primary.

3. Сравнение и синхронизация

Для каждой Secondary-ноды скрипт сравнивает список её джобов со списком на Primary (за вычетом системных/служебных заданий, которые не нужно синхронизировать) и определяет:

какие джобы нужно добавить (есть на Primary, нет на Secondary);
какие джобы нужно удалить (есть на Secondary, но их больше нет на Primary).

4. Тестовый режим

В начале скрипта есть переменная:

powershell

$TestMode = $true   # тест: ничего не копируется и не удаляется
$TestMode = $false  # боевой запуск: изменения применяются

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

5. Логирование

Каждый запуск создаёт отдельный лог-файл с таймстампом в названии. В лог попадают:

все значения ключевых переменных (AG, пути, режим, срок хранения);
какие ноды найдены и их текущие роли;
список джобов на Primary и на каждой Secondary;
какие джобы добавлены/удалены (или были бы добавлены/удалены в тестовом режиме);
итоговое время выполнения.

6. Автоочистка

Файлы логов и бэкапов, созданные скриптом, автоматически удаляются, если их возраст превышает заданный срок хранения (по умолчанию — 20 дней). Пустые папки дат/серверов, оставшиеся после очистки, тоже удаляются.

Расположение файлов

Что	Путь
Логи запусков	`D:\Log\`
Бэкапы джобов	`D:\DBA_job\Backup\<Сервер>\<Дата>\`

Исключаемые из синхронизации джобы

Следующие задания считаются локальными для каждого сервера и не участвуют в сравнении/копировании/удалении:

syspolicy_purge_history
AlwaysOn_Latency_Data_Collection

Требования

Модуль dbatools для PowerShell.
Учётная запись, под которой выполняется скрипт, должна иметь права sysadmin (или права на msdb) на всех узлах AG.
Права на запись в каталог логов/бэкапов.

Как запустить

Тестовая проверка (безопасно, ничего не изменяет):

Открыть скрипт.
Убедиться, что $TestMode = $true.
Выполнить скрипт целиком в PowerShell.
Проверить лог-файл — какие джобы были бы скопированы/удалены.

Боевой запуск:

Изменить $TestMode = $false.
Выполнить скрипт целиком.
Проверить лог-файл на предмет ошибок и итоговый статус выполнения.

Сам скрипт

# ============================================
# 0. НАСТРОЙКА ПОДКЛЮЧЕНИЯ (при необходимости)
# ============================================
Set-DbatoolsInsecureConnection -SessionOnly

$StartTime = (Get-Date)

# ============================================
# 1. ПЕРЕМЕННЫЕ (всё в начале скрипта)
# ============================================
$TestMode      = $true                          # $true  = тестовый режим (ничего не копируется/не удаляется)
                                                  # $false = боевой запуск

$AGLSN         = 'db01'                     # Listener AG

$ExcludeJobs   = 'syspolicy_purge_history', 'AlwaysOn_Latency_Data_Collection'

$BasePath      = 'D:\log\DBA_job'
$LogPath       = Join-Path $BasePath 'Log'
$BackupPath    = Join-Path $BasePath 'Backup'

$Timestamp     = Get-Date -Format 'yyyyMMddHHmmss'
$DateFolder    = Get-Date -Format 'yyyy-MM-dd'
$SyncLogFile   = Join-Path $LogPath "DBA_synchronize_AG_log_$Timestamp.txt"

$RetentionDays = 20                             # хранить логи/бэкапы N дней

# ============================================
# ФУНКЦИЯ ЛОГИРОВАНИЯ (пишет и в консоль, и в файл, ВСЕГДА, даже в тесте)
# ============================================
function Write-Log {
    param(
        [string]$Message,
        [string]$Level = 'INFO'   # INFO, TEST, WARN, ERROR
    )
    $line = "[{0}] [{1}] {2}" -f (Get-Date -Format 'yyyy-MM-dd HH:mm:ss'), $Level, $Message
    Write-Output $line
    Add-Content -Path $SyncLogFile -Value $line
}

# ============================================
# ФУНКЦИЯ: получить/создать папку бэкапа для сервера на текущую дату
#   Backup\<ServerName>\<yyyy-MM-dd>\
# ============================================
function Get-ServerBackupFolder {
    param([string]$ServerName)

    $folder = Join-Path $BackupPath (Join-Path $ServerName $DateFolder)
    if (-not (Test-Path $folder)) {
        New-Item -Path $folder -ItemType Directory -Force | Out-Null
    }
    return $folder
}

# ============================================
# 2. ПОДГОТОВКА ПАПОК
# ============================================
foreach ($path in @($LogPath, $BackupPath)) {
    if (-not (Test-Path $path)) {
        New-Item -Path $path -ItemType Directory -Force | Out-Null
    }
}

Write-Log "========================================================"
Write-Log "Запуск скрипта синхронизации джобов AG"
Write-Log "Режим: $(if ($TestMode) {'ТЕСТ (изменения НЕ применяются)'} else {'БОЕВОЙ'})"
Write-Log "AGLSN            = $AGLSN"
Write-Log "ExcludeJobs      = $($ExcludeJobs -join ', ')"
Write-Log "BasePath         = $BasePath"
Write-Log "LogPath          = $LogPath"
Write-Log "BackupPath       = $BackupPath"
Write-Log "DateFolder       = $DateFolder"
Write-Log "Timestamp        = $Timestamp"
Write-Log "SyncLogFile      = $SyncLogFile"
Write-Log "RetentionDays    = $RetentionDays"
Write-Log "========================================================"

# ============================================
# 3. ОЧИСТКА СТАРЫХ ЛОГОВ И БЭКАПОВ (> $RetentionDays дней)
# ============================================
$CutoffDate = (Get-Date).AddDays(-$RetentionDays)
Write-Log "Очистка файлов старше $RetentionDays дней (дата отсечения: $CutoffDate)"

foreach ($path in @($LogPath, $BackupPath)) {
    $oldFiles = Get-ChildItem -Path $path -File -Recurse -ErrorAction SilentlyContinue |
        Where-Object { $_.LastWriteTime -lt $CutoffDate }

    Write-Log "Папка '$path': найдено файлов на удаление: $($oldFiles.Count)"

    $oldFiles | ForEach-Object {
        if ($TestMode) {
            Write-Log "Был бы удалён файл: $($_.FullName) (LastWriteTime: $($_.LastWriteTime))" -Level TEST
        } else {
            Remove-Item -Path $_.FullName -Force -ErrorAction SilentlyContinue
            Write-Log "Удалён файл: $($_.FullName)"
        }
    }

    # Удаляем пустые папки серверов/дат, оставшиеся после чистки файлов
    Get-ChildItem -Path $path -Directory -Recurse -ErrorAction SilentlyContinue |
        Sort-Object FullName -Descending |
        Where-Object { (Get-ChildItem -Path $_.FullName -Force -ErrorAction SilentlyContinue).Count -eq 0 } |
        ForEach-Object {
            if ($TestMode) {
                Write-Log "Была бы удалена пустая папка: $($_.FullName)" -Level TEST
            } else {
                Remove-Item -Path $_.FullName -Force -ErrorAction SilentlyContinue
                Write-Log "Удалена пустая папка: $($_.FullName)"
            }
        }
}

# ============================================
# 4. АВТООПРЕДЕЛЕНИЕ PRIMARY / SECONDARY В AG
# ============================================
Write-Log "Опрос AG '$AGLSN' на предмет реплик..."

$allReplicas       = Get-DbaAgReplica -SqlInstance $AGLSN
$primaryReplica    = $allReplicas | Where-Object Role -eq Primary
$secondaryReplicas = $allReplicas | Where-Object Role -eq Secondary

Write-Log "Все найденные реплики AG: $(($allReplicas | ForEach-Object { "$($_.Name)[$($_.Role)]" }) -join ', ')"
Write-Log "Primary реплика:   $($primaryReplica.Name)"
Write-Log "Secondary реплики: $($secondaryReplicas.Name -join ', ') (всего: $($secondaryReplicas.Count))"

if (-not $primaryReplica) {
    Write-Log "Primary реплика не найдена! Проверьте доступность AGLSN '$AGLSN'." -Level ERROR
}
if ($secondaryReplicas.Count -eq 0) {
    Write-Log "Secondary реплики не найдены! Синхронизация выполняться не будет." -Level WARN
}

$JobsOnPrimary = Get-DbaAgentJob -SqlInstance $primaryReplica.Name -ExcludeJob $ExcludeJobs
Write-Log "Джобов на Primary ($($primaryReplica.Name)), после исключений: $($JobsOnPrimary.Count)"
Write-Log "Список джобов Primary: $($JobsOnPrimary.Name -join ', ')"

# ============================================
# 5. БЭКАП ДЖОБОВ НА PRIMARY (независимо от роли, бэкапим всегда обе ноды)
# ============================================
Write-Log "--------------------------------------------------------"
Write-Log "Бэкап джобов на Primary-ноде: $($primaryReplica.Name)"

$PrimaryBackupFolder = Get-ServerBackupFolder -ServerName $primaryReplica.Name
Write-Log "Папка бэкапа Primary: $PrimaryBackupFolder"

$JobsToBackupPrimary = Get-DbaAgentJob -SqlInstance $primaryReplica.Name
Write-Log "Джобов на $($primaryReplica.Name) для бэкапа: $($JobsToBackupPrimary.Count)"

foreach ($job in $JobsToBackupPrimary) {
    $SafeJobName = ($job.Name -replace '[\\/:*?"<>|]', '_')
    $FileName    = "$($primaryReplica.Name)-$Timestamp-$SafeJobName-script.sql"
    $FilePath    = Join-Path $PrimaryBackupFolder $FileName

    $job | Export-DbaScript -FilePath $FilePath | Out-Null
    Write-Log "  Бэкап джоба '$($job.Name)' -> $FilePath"
}

$JobsListFilePrimary = Join-Path $PrimaryBackupFolder "_JobsList_$($primaryReplica.Name)_$Timestamp.txt"
$JobsToBackupPrimary | Select-Object Name, Enabled, DateCreated, DateLastModified |
    Sort-Object Name |
    Format-Table -AutoSize |
    Out-String -Width 1024 |
    Out-File $JobsListFilePrimary

Write-Log "Backup завершён для Primary $($primaryReplica.Name). Джобов: $($JobsToBackupPrimary.Count). Опись: $JobsListFilePrimary"

# ============================================
# 6. БЭКАП ДЖОБОВ + СИНХРОНИЗАЦИЯ НА КАЖДОЙ SECONDARY
# ============================================
$secondaryReplicas | ForEach-Object {

    $secondaryName = $_.Name
    Write-Log "--------------------------------------------------------"
    Write-Log "Обработка secondary-ноды: $secondaryName"

    # --- 6.1. Бэкап всех текущих джобов на этой secondary-ноде (выполняется всегда, даже в тесте) ---
    $SecondaryBackupFolder = Get-ServerBackupFolder -ServerName $secondaryName
    Write-Log "Папка бэкапа $secondaryName`: $SecondaryBackupFolder"

    $JobsToBackup = Get-DbaAgentJob -SqlInstance $secondaryName
    Write-Log "Джобов на $secondaryName для бэкапа: $($JobsToBackup.Count)"

    foreach ($job in $JobsToBackup) {
        $SafeJobName = ($job.Name -replace '[\\/:*?"<>|]', '_')
        $FileName    = "$secondaryName-$Timestamp-$SafeJobName-script.sql"
        $FilePath    = Join-Path $SecondaryBackupFolder $FileName

        $job | Export-DbaScript -FilePath $FilePath | Out-Null
        Write-Log "  Бэкап джоба '$($job.Name)' -> $FilePath"
    }

    $JobsListFile = Join-Path $SecondaryBackupFolder "_JobsList_$secondaryName`_$Timestamp.txt"
    $JobsToBackup | Select-Object Name, Enabled, DateCreated, DateLastModified |
        Sort-Object Name |
        Format-Table -AutoSize |
        Out-String -Width 1024 |
        Out-File $JobsListFile

    Write-Log "Backup завершён для $secondaryName. Джобов: $($JobsToBackup.Count). Опись: $JobsListFile"

    # --- 6.2. Сравнение джобов Primary <-> текущая Secondary ---
    $JobsOnSecondary = Get-DbaAgentJob -SqlInstance $secondaryName -ExcludeJob $ExcludeJobs
    Write-Log "Джобов на $secondaryName (после исключений, для сравнения): $($JobsOnSecondary.Count)"

    $diffToAdd    = $JobsOnPrimary   | Where-Object Name -notin ($JobsOnSecondary.Name)
    $diffToRemove = $JobsOnSecondary | Where-Object Name -notin ($JobsOnPrimary.Name)

    Write-Log "К добавлению на $secondaryName`: $($diffToAdd.Count) -> $($diffToAdd.Name -join ', ')"
    Write-Log "К удалению на $secondaryName`: $($diffToRemove.Count) -> $($diffToRemove.Name -join ', ')"

    if ($diffToAdd) {
        if ($TestMode) {
            $diffToAdd.Name | ForEach-Object {
                Write-Log "Был бы скопирован джоб '$_' с $($primaryReplica.Name) на $secondaryName" -Level TEST
            }
        } else {
            $result = Copy-DbaAgentJob -Source $primaryReplica.Name -Destination $secondaryName -Job $diffToAdd.Name -DisableOnDestination -Force
            $result | Select-Object DateTime, Type, DestinationServer, Name, Status, Notes |
                Format-Table -Property * -AutoSize | Out-String -Width 1024 |
                ForEach-Object { Write-Log $_ }
        }
    }

    if ($diffToRemove) {
        $diffToRemove | ForEach-Object {
            if ($TestMode) {
                Write-Log "Был бы удалён джоб '$($_.Name)' на $($_.ComputerName)" -Level TEST
            } else {
                $result = Remove-DbaAgentJob -SqlInstance $_.ComputerName -Job $_.Name -Confirm:$false
                $result | Format-Table -Property * -AutoSize | Out-String -Width 1024 |
                    ForEach-Object { Write-Log $_ }
            }
        }
    }
}

# ============================================
# 7. ИТОГ
# ============================================
$EndTime   = Get-Date
$TotalTime = $EndTime - $StartTime
Write-Log "========================================================"
Write-Log "Скрипт завершён. Время выполнения: $($TotalTime.ToString())"
Write-Log "========================================================"

Добавить комментарий Отменить ответ

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