Параллельное резервное копирование баз данных SQL Server с помощью PowerShell

Данный материал является переводом оригинальной статьи "MSSQLTips : Backup SQL Server Databases in Parallel with PowerShell".

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

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

Скрипт PowerShell для запуска одновременных бэкапов:

$ErrorActionPreference = "Stop" #stop when an error is encountered
#declare variables
$server = "YOURSERVER"
$database = "master"
$query = @"
sp_databases
"@
$querytimeout = 0 #0 means forever, change it as needed
$objectExclude = 'tempdb, northwind, pubs' -split ", " #databases to not backup
$objectNameField = "DATABASE_NAME" #this one is returned by sp_databases
$objectSortExpression = @{Expression={$_[1]}} #DATABASE_SIZE returned by sp_databases, add Ascending=$false to start with the largest
$SleepTimer = 1000 #after X milliseconds, check if the jobs have finished. 1000 is every second.
$MaxResultTime = 7200 #after X seconds, all jobs are killed. 7200 is two hours.
$tasks = @(# taskOrder, taskName, maxThreads, scriptToRun) args0=databaseName. Only one command.
,@(1, 'backup', 1, 'sqlcmd -Q "BACKUP DATABASE [$args0] TO DISK=N''C:\${args0}.bak'' WITH INIT" -r0 -b')
,@(2, 'store', 1, 'Copy-Item -Path "C:\${args0}.bak" -Destination "\\archive\c$\${args0}.bak"')
,@(3, 'delete', 1, 'Remove-Item "C:\${args0}.bak"')
)
$startAtTask = 1 #i.e. if the network was unavailable, you may need to re-run starting from that task
#import modules
Import-Module SqlPs -DisableNameChecking #uncomment for running it directly in a ps command prompt
$error.clear() #clear error generated by last command
#get list of databases and sort
$objects = @((Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query -querytimeout $queryTimeout) | where {$objectExclude -notcontains $_.$objectNameField} | sort $objectSortExpression )
#environment setup
$RunspacePools = @()
$Jobs = @()
$ISS = [system.management.automation.runspaces.initialsessionstate]::CreateDefault()
$taskInfo = @{} # key=taskOrder; value=nextTaskOrder
$maxTaskOrder = -1
$objectInfo = @{} # key=taskId; values=each database
$objectInfoArr = @(0) * $objects.length
$output = ""
$errors = ""
$errorCount = @{}
for ($i=0; $i -lt $tasks.length; $i++) {
    $RunspacePools += [runspacefactory]::CreateRunspacePool(1, $tasks[$i][2] <# maxThreads #>, $ISS, $Host)
    $RunspacePools[$i].Open()
    if ($taskInfo.Count -eq 0 -Or -Not $taskInfo.ContainsKey($tasks[$i][0])) { #taskOrder
        $taskInfo.Add($tasks[$i][0], -1)
        if ($tasks[$i][0] -gt $maxTaskOrder) { $maxTaskOrder = $tasks[$i][0] }
    }
    $objectInfo.Add($i, $objectInfoArr.clone())
    $tasks[$i][3] = '$ErrorActionPreference = "Stop"; $args0 = $args[0]; try { $output = ' + $tasks[$i][3] + ' 2>&1 } catch { $err = $_.Exception; $errors = $err.Message; while($err.InnerException) { $err = $err.InnerException; $errors += "|" + $err.Message } } $LastExitCode; $errors | where { $_ } | Out-String; $output | where { $_ } | Out-String'
    $errorCount.Add($i, 0)
}
foreach ($key in @($taskInfo.Keys)) { $taskInfo[$key] = ($taskInfo.Keys | where {$_ -gt $key} | sort | select -First 1) }
#function to create thread and start processing
function CreateThread() {
    param ([string]$objectName, [int]$objectIndex, [int]$taskIndex, [int]$taskOrder, [ref]$Jobs)
    $PowershellThread = [powershell]::Create().AddScript($tasks[$taskIndex][3]) #scriptToRun
    $PowershellThread.AddArgument($objectName) | out-null
    $PowershellThread.RunspacePool = $RunspacePools[$taskIndex]
    $Handle = $PowershellThread.BeginInvoke()
    $Job = "" | select Handle, Thread, ObjectName, ObjectIndex, TaskIndex, TaskOrder, object
    $Job.Handle = $Handle; $Job.Thread = $PowershellThread
    $Job.ObjectName = $objectName; $Job.ObjectIndex = $objectIndex; $Job.TaskIndex = $taskIndex; $Job.TaskOrder = $taskOrder
    $Jobs.value += $Job
}
$ResultTimer = Get-Date
#start processing first task for each database
for ($i=0; $i -lt $objects.length; $i++) {
    $object = $objects[$i].$objectNameField
    $tasks | where {$_[0] -eq $startAtTask} | foreach { CreateThread $object $i ([array]::IndexOf($tasks, $_)) $_[0] ([ref]$Jobs) }
}
while (@($Jobs | where {$_.Handle -ne $Null}).count -gt 0) {
    #update completed jobs and dispose them
    foreach ($Job in @($Jobs | where {$_.Handle -ne $Null -and $_.Handle.IsCompleted -eq $True})) {
        #update status
        $objectInfo[$Job.TaskIndex][$Job.ObjectIndex] = 1
        #get results. 0=LastExitCode (must be 0 or null), 1=$errors (must be empty), 2=$output (may be empty)
        $results = $Job.Thread.EndInvoke($Job.Handle)
        $errors += $results[1] + "`r`n"
        $output += $results[2] + "`r`n"
        if (($results[0] -and $results[0] -ne 0) -or $results[1] -ne "") { $errorCount[$Job.TaskIndex] += 1; $objectInfoArr[$Job.ObjectIndex] = 1; }
        #launch next task
        if ($Job.TaskOrder -lt $maxTaskOrder -and #there are pending tasks
            (@($Jobs | where {$_.TaskOrder -eq $Job.TaskOrder -and #same taskOrder
                                 $_.ObjectName -eq $Job.ObjectName -and #same database
                                 $_.Handle.IsCompleted -eq $False}).count -eq 0) -and #no active threads
            $objectInfoArr[$Job.ObjectIndex] -eq 0) { #no errors so far
            $tasks | where {$_[0] -eq $taskInfo[$Job.TaskOrder]} | foreach { CreateThread $Job.ObjectName $Job.ObjectIndex ([array]::IndexOf($tasks, $_)) $_[0] ([ref]$Jobs) }
        }
        #end thread
        $Job.Thread.Dispose()
        $Job.Thread = $Null
        $Job.Handle = $Null
    }
    #show progress
    for ($i=0; $i -lt $tasks.length; $i++) {
        $inProgress = @($Jobs | where {$_.TaskIndex -eq $i -and $_.Handle.IsCompleted -eq $False}).count
        $failed = $errorCount[$i]
        Write-Progress `
            -Id $i `
            -Activity $tasks[$i][1] `
            -PercentComplete (($objectInfo[$i] | measure-object -Sum).Sum * 100 / $objects.length) `
            -Status "$inProgress in progress, $failed failed"
    }
    #exit on timeout
    $currentTime = Get-Date
    if (($currentTime - $ResultTimer).totalseconds -gt $MaxResultTime) {
        Write-Error "Child script appears to be frozen, try increasing MaxResultTime"
        break
    }
    #sleep
    Start-Sleep -Milliseconds $SleepTimer
}
#dispose thread pools
for ($i=0; $i -lt $tasks.length; $i++) {
    $RunspacePools[$i].Close() | Out-Null
    $RunspacePools[$i].Dispose() | Out-Null
}
if (($errors -replace "`r`n", "") -ne "") { throw $errors + " " + $output } else { $output }

Переменные скрипта

Большинство переменных прокомментированы в скрипте, но дадим некоторые дополнительные пояснения:

• $server это имя экземпляра SQL Server. Для именованного экземпляра должно быть SERVER\INSTANCE.
• $database имеет значение "master", потому что в контексте этой БД выполняются запросы.
• $query вызывает "sp_databases", что является системной хранимой процедурой, которая возвращает все доступные БД в экземпляре.
• $querytimeout имеет значение "0", что значит "вечно". Поменяйте на нужное значение.
• $objectExclude определяет перечень имён БД, которые следует пропустить.
• $objectNameField означает имя столбца, содержащего имя БД, возвращенное из "sp_databases".
• $objectSortExpression использует второй столбец в результирующем наборе (0,1,2,и .т.д..) для сортировки результатов.
• $SleepTimer определяет количество миллисекунд для проверки завершения задания. Значение "1000" означает раз в секунду.
• $MaxResultTime определяет количество секунд, после которых все задания прекращаются. Значение "7200" означает два часа.
• $tasks определяет список действий для выполнения над каждой БД, возвращенной из "sp_databases". Имя БД помещено внутрь переменной "Args0", использованной в параметре задачи "scriptToRun".

Задания для выполнения следующие:

• backup – будет создавать полный бэкап локального сервера
• store – перемещает полный бэкап в расположение архива
• delete – удаляет локальную копию бэкапа

Это шаги для выполнения в среде автора скрипта, но можно конфигурировать их по своей надобности.

Внутри каждой задачи мы имеем следующее:

• Первое поле "taskOrder". Учтите, что они не обязательно будут последовательны (можно закомментировать строки при тестировании) и можно указывать один и тот же "taskOrder" более, чем в одной задаче (это приведет к параллельному выполнению обеих задач, например, как копирование в локальный и удаленный репозиторий за один раз).

• Второе поле "taskName" отображается в прогресс-баре, когда выполняете скрипт в строке PowerShell.

• Третье поле "maxThreads" позволяет бэкапить множество БД параллельно, или параллельно архивировать много файлов, либо удалять много файлов параллельно. Его надо тщательно тестировать, так как будет потребляться больше ресурсов (CPU, память, сеть) при одновременном выполнении множества задач.

• Последнее поле "scriptToRun". Следует принять во внимание, что введенная вами команда будет запущена как отдельная программа (так что вы не сможете использовать переменные, определенные где бы то ни было, исключая те, что отправлены со строки 52).

Взглянем ближе на первую задачу, взятую для бэкапа.

Использованные значения тут:

• taskOrder = 1
• taskName = backup
• maxThreads = 1 (если хочется запустить несколько бэкапов сразу, надо заменить на большее значение).
• scriptToRun =  'sqlcmd -Q "BACKUP DATABASE [$args0] TO DISK=N''C:Backup${args0}.bak'' WITH INIT" -r0 –b'.

Для "scriptToRun" используется "sqlcmd" для выполнения команды "BACKUP DATABASE".

Здесь использованы динамические значения.

• [$args0] - это имя БД, которое передается
• ${args0} - это тоже имя БД, которое передается, чтобы использовать в имени файла бэкапа.  Можно было бы еще что-то добавить в имя, для включения даты, но хотелось упростить пример.

Параметры "sqlcmd" здесь:

• -Q - запрос для выполнения
• -r0 - возврат ошибок
• -b - прерывание пакета при ошибке

Выполнение скрипта PowerShell параллельного бэкапа SQL Server

Для тестирования использовалось следующее окружение:

• 32 БД со средним размером около 2.5GB
• Общий объём резервных копий 78GB

Скрипт открывали в PowerShell ISE. После настройки переменных в соответствии с задачей, при запуске, представлен вывод из PowerShell.

Учтите, что пока выполняется задача 'backup', задача 'store' выполняется для БД, которая уже забэкапилась.

Вот сравнение последовательного задания через SQL Server Agent для бэкапа одной БД за раз, затем копирования файла и удаление локальной копии. Можно видеть, что это заняло 1 час 31 минуту.

Вот другое задание SQL Server Agent, но в этот раз используя скрипт PowerShell. Использовалось maxThreads = 4. Это заняло 1 час и 9 минут на выполнение.

Следующие шаги

• Скачайте скрипт
• Поменяйте задачи по вашим потребностям и экспериментируйте с другими задачами, которые  захотите выполнять параллельно.
• Этот скрипт также позволяет бэкапить удаленные БД, которые могут не иметь SQL Server Agent.
• Этот скрипт можно использовать для выполнения действий над другими элементами; он не ограничен лишь БД.
• Переменную $query можно сделать более сложной, чтобы проверять, если БД уже были заархивированы; в таком случае, если задание перевыполняется, оно не будет тратить ресурсы, а вместо этого будет работать над тем, что было упущено.
• Можно включить иные шаги, вроде DBCC или сжатия БД, или восстановления резервной копии для ее проверки.