В этой и двух последующих статьях я соберу различные команды из модуля dbatools в набор скриптов, которые смогут выполнить полное развёртывание SQL Server, проверить основные показатели работоспособности и конфигурации, а также привести в норму уже существующий экземпляр. Это будет рассказ об установке с нуля: это будет не только установка самого SQL Server, но и настройка хоста, конфигурация экземпляра, развёртывание задач обслуживания — в общем, всё, о чём я додумался.
Исходные условия
Для тестов я создал полигон с несколькими подсетями: три инстанса SQL Server Developer Edition, один SQL Express
и сетевой шаринг — по методике, описанной в статье Джонатана Кехайаса. Третий хост был очищен для чистой установки.
Если вы ещё не знакомы с модулем dbatools, самое время установить и попробовать его в тестовой среде.
В PowerShell выполните:
Install-Module DbatoolsImport-Module Dbatools
Назначение скрипта развёртывания
Скрипт предоставляет множество опций. Я не описываю все возможные варианты использования, но даю готовый инструмент, который вы сможете подправить под своё окружение. Вот что умеет этот набор команд:
- Настроить хост (разметка дисков, план питания и т.д.).
- Установить SQL Server и перезапустить систему при необходимости.
- Обновить SQL Server (patching) и выполнить рестарт.
- Настроить параметры экземпляра (MaxDOP, Ad Hoc, сжатие бэкапов, контроль сумм бэкапов, cost_threshold, Remote Admin и т.д.).
- Сконфигурировать файлы TempDB.
- Включить trace flag 3226 как параметр запуска.
- Настроить ретеншн логов ошибок и оповещения по критическим ошибкам.
- Развернуть административную базу данных и набор инструментов: Ola Maintenance, First Responder Toolkit, WhoisActive, а также полезное подспорье от StraightPath.
Всё это упаковано и раздаётся в zip-папке. В моей тестовой среде все шаги выполняются менее чем за 20 минут. Я запускал второй цикл с добавлением именованного экземпляра — время было примерно тем же. Если вы сейчас делаете установку вручную, скорее всего это отнимает у вас час и больше — автоматизация сэкономит время и даст отличный обучающий эффект по dbatools и PowerShell.
Результаты теста
В моём тесте первые предупреждения шли от Update-DbaInstance, вторые — от Install-DbaFirstResponderKit.
Они безвредны и исчезли после обновления версии dbatools с 2.1.31 до 2.7.2.
Раздел 1 — Определение переменных
Первая часть скрипта — определение переменных, необходимых для корректной работы. dbatools старается переиспользовать имена аргументов, поэтому выбор переменных упрощён. В скрипте большинство переключателей работают по логике: 0 — выключено, 1 — включено.
Ключевые переменные и их назначение
| Переменная | Назначение |
|---|---|
$ComputerName | Целевой хост для установки и настройки SQL Server. |
$InstanceName | Имя инстанса (оставьте пустым — будет дефолтный экземпляр). |
$CheckDriveAllocation | Включить проверку размера кластера (allocation unit) на дисках (ожидается 64 КБ). |
$AutoReformatDrives | Если включено, скрипт попытается переформатировать диски с неправильным allocation unit до 64К (внимание: уничтожает данные). |
$InstallSQL | Переключатель установки SQL Server (1 — установить). |
$DataPath, $LogPath, $TempPath, $BackupPath | Пути по умолчанию для данных, журналов, TempDB и бэкапов. |
$AdminAccounts | Учётки, которые будут добавлены в роль sysadmin на новом инстансе. |
$SQLversion | Версия SQL Server для установки (менять только если у вас соответствующие установочные файлы). |
$AutoClearDirectories | Если 1 — очищает целевые директории (удаляет файлы) перед установкой (деструктивно). |
$AutoCreateShare | Создать сетевую шару из папки со скриптом, чтобы целевой хост мог использовать инсталляционные файлы. |
$UpdateSQL | Включить этап обновления SQL Server после установки. |
$SetPowerPlan, $SetMaxDop, ... | Набор флагов для включения/отключения конфигураций (MaxDOP, оптимизация под Ad Hoc, сжатие бэкапов, cost threshold и т.д.). |
$trace3226 | Добавить trace flag 3226 в параметры запуска SQL. |
$SetErrorlog, $ErrorlogCount | Настройка ротации логов (сколько файлов хранить). |
$EnableAlerts | Включение оповещений по ошибкам 823, 824, 825 и по severity 16–25. |
$DeployOlaMaintenance, $DeployFirstResponder, $Deploy_WhoIsActive | Переключатели развёртывания сторонних инструментов: Ola, First Responder, WhoIsActive и др. |
Ниже приведён пример скрипта с реальными значениями переменных (в коде оставлены англоязычные комментарии как в оригинале):
#Region - Variables
$sw0 = [system.diagnostics.stopwatch]::startNew()
Clear-Host
$ComputerName = 'labsql3'
$instancename = '' <#Leave blank for a default instance, only put the name of the named instance here - NOT the Hostname\Instancename.
Note - This process assumes you aren't naming a named instance "DEFAULT", if you do plan on naming your instance 'Default' you will need to do
some surgery on your side. The main issue this would cause with this process is that folder naming of a default instance is 'X:\DEFAULT\SQLdata'
etc. The named instance with the name of 'DEFAULT' would attempt to use the same paths. Avoid it if you can, fix it if you must. #>
<# ================ DRIVE ALLOCATION WARNING - READ CAREFULLY =====================#>
$CheckdriveAllocation = 1 <# 1 means the process will check the allocation of the drives on the target comupter, 0 will ignore drive allocations.
This process will check the drive allocation of the drives using the drive letter at the beginning of the path values in the $datapath, $logpath, and
$Temppath variables unless it is C. #>
<# The variable "AutoReformatDrives" will do exactly its name - if the check drive allocation process finds disks using the drive letters from the three
variables described above that do not have an allocation size of 65536 it will attempt to REFORMAT them remotely. Drive formatting is a destructive process,
so please ensure the target is either a brand new host without any important data on it, or you are 100% certain there is nothing important on the drives.#>
$AutoReformatDrives = 1 # IF THIS IS "1" IT WILL ATTEMPT TO REFORMAT ANY FOUND DRIVES NOT AT 64KB ALLOCATION - THIS IS A DESTRUCTIVE PROCESS OF ANY DATA CURRENTLY ON THOSE DRIVES - BE CERTAIN!
<# ================================================================================ #>
$InstallSQL = 1
# These are the default paths in the instance, they will be created if they don't exist, and the backup path will be used for Ola backup jobs if you choose to install Ola.
$Datapath = "D:\$instancename\SQLData\"
$Logpath = "L:\$instancename\SQLLogs\"
$Temppath = "T:\$instancename\Tempdb\"
$BackupPath = '\\Labshare\SQLBackups\'
$AdminAccounts = 'LAB\DA', "LAB\Administrator", 'LAB\SQLService' #accounts that will be added to the SQL Sysadmin role
$SQLversion = 2022 # changing this will do nothing unless you also change the install media.
<# ================ Autoclear Directories WARNING - READ CAREFULLY =====================#>
$AutoClearDirectories = 1 <# This process will use the paths above to pre-clear ALL files that exist in the data, log, tempdb and instance root directories recursively.
Instance root will be the same as the data path except SQLDATA will be replaced by SQLROOT. This was useful for my testing this script where
uninstalling sql does not get rid of mdf and ldf files for all databases, and helps reduce confusion on repeated installs. BE CERTAIN BEFORE USING
THIS AS IT IS DESTRUCTIVE! #>
<# ================================================================================ #>
$AutoCreateShare = 1 <# The folder you downloaded with this script as well as the sql 2022 developer install media needs to be a network share so that the target computer
can access it. if this Variabel is set to 1, this process will automatically create a share using the folder this script is in with read access for everyone, enabling the process to be a bit
quicker. Set this variable to 0 if you want to create or use an existing share and move/ use the files already there.#>
$ManualPathtoInstall = '' <#leave blank unless you are creating the share manually. If so, this needs to be the network path to the setup.exe
(ex: '\\sharename\sqlextracted\setup.exe') for 2022 unless you also changed the sql version variable above. #>
$ManualPathtoUpdate = '' #leave blank unless you are creating the share manually. If so, this just needs to be the network path where the update files are loacated or can be downloaded to.
$UpdateSQL = 1 # Set to 1 to update the instance after install, 0 to skip updating.
# Configurations
$SetPowerPlan = 1 # 1 automatically sets the host to high performance.
$SetMaxDop = 1 # 1 automatically sets the maxdop to the recommended value based on the microsoft recommendations.
$SetOptimizeForAdHoc = 1 # 1 automatically sets the optimize for ad hoc workloads option.
$SetBackupCompression = 1 # 1 automatically sets the backup compression option.
$SetBackupChecksum = 1 # 1 automatically sets the backup checksum option.
$SetCostThreshold = 1 # 1 automatically sets the cost threshold for parallelism option to 50.
$SetRemoteAdmin = 1 # 1 automatically sets the Remote Admin Connections option.
$SetMaxMemory = 1 # 1 automatically sets the max memory option to the recommended value.
$SetTempDBConfiguration = 1 # 1 automatically configures the tempdb settings.
$trace3226 = 1 # 1 to Enable trace flag 3226 as a startup parameter
$SetErrorlog = 1 #set the errorlog to the $ErrorlogCount value
$ErrorlogCount = 52 # Set the maximum number of error log files to keep (6-99)
$EnableAlerts = 1 #This will enable alerts for errors 823,824,825 and issues with severity 16-25. Putting them in the error log for advanced issue identification and troubleshooting.
#Maintenance and Tools
$ToolsAdminDatabase = 'DB_admin'
$DeployToolsAdminDB = 1 #"1" Will create the database identified in $ToolsAdminDatabase if it doesn't exist
$DeployOlaMaintenance = 1 # 0 = False, 1 = True (set to 1 to update Ola Maintenance Solution)
$OlaDatabase = $ToolsAdminDatabase #the database where Ola Maintenance Solution store procedures will stored updated
<# Note - Ola Jobs are set to automatically install, with a weekly full, daily diff, 15 minute log backups, backups go to the $BackupPath above, cleanup
time of 336 hours (two weeks), logtotable enabled on the $OlaDatabase. If you want to change any of this you will need to go down to line 525 and modify the parts you want changed! #>
$deployFirstResponder = 1 #1 will deploy first responder toolkit to the new instance.
$FirstResponderDatabase = 'master' # the database where First Responder Kit stored procedures will be installed or updated.
$RemoveSQLVersionsTable = 1 # 0 = False, 1 = True (set to 1 to drop the dbo.SQLServerVersions that is automatically created in master as part of the update.)
$deployWhoisactive = 1 #1 will deploy whoisactive to the new instance.
$whoIsActiveDatabase = 'master' #the database where WhoisActive stored procedures will be installed or updated.
#StraightPathTools
$SPtoolsDeploymentDatabase = 'master'
$Deploy_SP_CheckBackup = 1 # https://github.com/Straight-Path-Solutions/sp_CheckBackup
$Deploy_SP_CheckSecurity = 1 # https://github.com/Straight-Path-Solutions/sp_CheckSecurity
$Deploy_SP_CheckTempDB = 1 # https://github.com/Straight-Path-Solutions/sp_CheckTempdb
#endregion
Автоматизация: подготовка, тесты и запуск
После определения переменных скрипт запустит серию проверок: начнёт писать транскрипт в лог-файл, проверит наличие прав администратора, проверит, что целевой хост не совпадает с запуском скрипта, при необходимости создаст сетевую шару и прочие подготовительные шаги.
Проверка разметки дисков (Drive Allocation)
Скрипт проверяет allocation unit (размер кластера) на дисках, указанных в переменных $DataPath, $LogPath, $TempPath. Ожидаемое значение — 64 КБ. Если найден диск с другим размером и включён $AutoReformatDrives, скрипт попытается отформатировать файловую систему с нужным allocation unit — это деструктивная операция.
#Region - Prompted Variables, Derived Variables, Tests, and Prep
$sw1 = [system.diagnostics.stopwatch]::startNew()
Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -register
# Color splats for host messages
$goodsplat = @{
foregroundcolor = 'Green'
}
$badsplat = @{
foregroundcolor = 'DarkRed'
backgroundcolor = 'White'
}
$warningsplat = @{
foregroundcolor = 'DarkYellow'
}
# Initial Tests
IF(!(([System.Security.Principal.WindowsPrincipal]::new([System.Security.Principal.WindowsIdentity]::GetCurrent())).IsInRole([System.Security.Principal.WindowsBuiltInRole]::Administrator)))
{Write-Host "ISSUE: This script must be executed in an Administrator Powershell window." @badsplat; RETURN}
IF ($ENV:COMPUTERNAME -ieq $ComputerName) {
Write-host "ISSUE: The target computer name cannot be the same as the computer running this script. Please change the `$ComputerName variable to a remote computer and try again." @badsplat
RETURN
}
# Creating the connection SPLAT
IF ($instancename.length -gt 0 ) {
$sqlsplat = @{
SqlInstance = "$ComputerName\$instancename"
}
$SqlInstance = "$ComputerName\$instancename"
$updateinst = $instancename
} ELSE {
$sqlsplat = @{
SqlInstance = $ComputerName
}
$SqlInstance = $ComputerName
$updateinst = $computername
}
#Adding credentials to the sql conneciton splat if the install sql variable is set to 1
# Prompted
IF ($InstallSQL -eq 1) {
$serviceAccount = $Host.UI.PromptForCredential("Engine & Agent Service Account", "Please enter the domain credentials for the SQL Engine and SQL Agent.", "Lab\SQLService", "")
$Cred = $Host.UI.PromptForCredential("Domain Account with permissions to run this process", "Please enter the domain credentials this process to run successfully on the remote target and with access to the network share.", "LAB\DA", "")
$SQLSplat.sqlcredential = $Cred
}
# Derived Variables
$DerInstancePath = $Datapath.replace('SQLData','SQLRoot').replace('\\','\DEFAULT\')
$DerLogPath = $Logpath.replace('\\','\DEFAULT\')
$DerTempPath = $Temppath.replace('\\','\DEFAULT\')
$DerDatapath = $Datapath.replace('\\','\DEFAULT\')
#collecting the path for the folder holding this script for referencing other resources in the folder.
$ScriptPath = Switch ($Host.name){
"Visual Studio Code Host" { split-path $psEditor.GetEditorContext().CurrentFile.Path }
"Windows PowerShell ISE Host" { Split-Path -Path $psISE.CurrentFile.FullPath }
"ConsoleHost" { $PSScriptRoot }
}
$transcriptpath = "$ScriptPath\Transcripts\DeploymentLog_$(get-date -f MM-dd-yy)_$(get-date -f "HH.mm").log"
Start-Transcript -path $transcriptpath
Write-host "PROCESS: Process Start - $(get-date -f 'MM-dd-yyyy HH:mm')" @goodsplat
IF ($AutoCreateShare -eq 1) {
# Creating a share that 'Everyone' can read so that the target computer can access the SQL Installer files during the SQL install portion, as well as the update directory during the Instance update.
$s = New-SmbShare -Name "Automated SQL Deployment Share" -Path $ScriptPath -FullAccess "Administrators" -ReadAccess "Everyone" -Temporary | Select-Object -Property path -ExpandProperty path
$share = $("\\$($env:COMPUTERNAME)" + $s.Substring(2))
}
#Prep
IF ($AutoClearDirectories -eq 1) {
Invoke-Command -ComputerName $ComputerName -ScriptBlock {
IF(test-path $using:DerInstancePath) { Get-childitem -Path $using:DerInstancePath\* | Remove-Item -Recurse -Confirm:$false}
IF(test-path $using:DerLogPath) { Get-childitem -Path $using:DerLogPath\* | Remove-Item -Recurse -Confirm:$false}
IF(test-path $using:DerTempPath) { Get-childitem -Path $using:DerTempPath\* | Remove-Item -Recurse -Confirm:$false}
IF(test-path $using:DerDatapath) { Get-childitem -Path $using:DerDatapath\* | Remove-Item -Recurse -Confirm:$false}
}
}
#follow up tests
IF ($instancename.IndexOf('\') -ne -1) {
Write-host "ISSUE: `$instancename variable has a '\' in it. Do not put 'HostName\InstanceName' as the value of that variable, only put Instancename" @badsplat
RETURN
}
IF ($null -eq $s -AND $autocreateshare -eq 1 -AND $InstallSQL -eq 1) {
Write-Host "ISSUE: The share was not automatically created and SQL is set to install - no installation media is referencable and it will fail. Please resolve this before trying again." @badsplat
RETURN
}
IF ($ManualPathtoInstall.Length -eq 0 -AND $autocreateshare -eq 0 -AND $InstallSQL -eq 1) {
Write-Host "ISSUE: The `$ManualPathtoInstall variable is empty and SQL is set to install, and auto create share is disabled. - no installation media is referencable and it will fail. Please resolve this before trying again." @badsplat
RETURN
}
$sw1.stop()
Write-host "PROCESS: Variables, Derived Variables, Prompted Variables, Tests, and Prep Steps complete. Elapsed Time: $($sw1.Elapsed.minutes)min $($sw1.Elapsed.seconds)sec" @goodsplat
#endregion
Скрипт имеет четыре возможных исхода в зависимости от значений переменных $CheckDriveAllocation и
$AutoReformatDrives, а также от того, были ли найдены проблемы с дисками.
Вариант 1:
$CheckDriveAllocation = 0
Вариант 2:
$CheckDriveAllocation = 1
$AutoReformatDrives = 0
Вариант 3:
$CheckDriveAllocation = 1
$AutoReformatDrives = 1
Вариант 4:
Применяется к переменным вариантов 2 и 3, если не обнаружено проблем с дисками.
Дальнейшие разделы
В оставшейся части оригинальной статьи автор детально проходит по каждому блоку скрипта: установка SQL Server, обновление, конфигурация tempdb, развёртывание Ola Maintenance и прочих инструментов, проверка результатов и логов.
Безопасность и рекомендации
- Внимательно относитесь к параметрам
$AutoReformatDrivesи$AutoClearDirectories— оба могут уничтожить данные. - Тестируйте скрипт в отдельной тестовой среде и не запускайте в продакшене без проверки.
- Собирайте и анализируйте логи установки (скрипт уже пишет транскрипт в файл).
- Резервируйте и проверяйте установочные и обновляющие файлы перед массовым развёртыванием.
Раздел 2 — Установка SQL Server
Здесь мы берём команду установки из предыдущей статьи и приводим её в рабочий вид. Я убрал часть специфичных для своей тестовой среды параметров (например, явную настройку Max Memory, MaxDOP и TempDB прямо в шаге установки) и заменил их на отдельные вызовы dbatools, которые будут выполнены позже. Также добавлена логика, позволяющая корректно обрабатывать установку именованного экземпляра и при ошибке — приостанавливать выполнение для расследования.
#Region - SQL Server Installation (Install-Dbainstance)
$sw3 = [system.diagnostics.stopwatch]::startNew()
IF ($InstallSQL = 1) {
Write-host "PROCESS: Starting SQL install - $($sw0.Elapsed.minutes)min $($sw0.Elapsed.seconds)sec" @goodsplat
Try {
# Modify the ISO path based on whether the user is using the auto created share or a custom location.
IF ($AutoCreateShare = 1) { $isopath = "$share\SQL Files\setup.exe"}
ELSE { $isopath = $ManualPathtoInstall }
$config = @{
UpdateEnabled = 'False' #auto update sql as part of installation
USEMICROSOFTUPDATE = 'False' #use MS updater to keep SQL Server up to date.
AGTSVCSTARTUPTYPE = "Automatic" #automatic sql agent startup
TCPENABLED = "1" # Specify 0 to disable or 1 to enable the TCP/IP protocol.
}
$splat = @{
Credential = $cred
SQLinstance = $ComputerName <# This 'SQLInstance' argument is not clearly named, it is meant to recieve the hostname that sql will
be installed on, not the name of the SQL Server Instance as it is in other Dbatools commands.
Instancename handles the name of the instance and I manage it below.#>
Version = $SQLversion
Feature = 'Engine'
AuthenticationMode = 'Mixed'
Path = $isopath
InstancePath = $DerInstancePath
Datapath = $DerDataPath
Logpath = $DerLogPath
Temppath = $DerTempPath
BackupPath = $BackupPath
AdminAccount = $AdminAccounts
SQLCollation = 'SQL_Latin1_General_CP1_CI_AS'
EngineCredential = $ServiceAccount
AgentCredential = $ServiceAccount
PerformVolumeMaintenanceTasks = $True
Restart = $True
Configuration = $config
Confirm = $false
}
# Modify the run for named instance installs.
IF ($instancename.length -eq 0)
{ $instresult = Install-DbaInstance @Splat}
ELSE
{ $instresult = Install-DbaInstance @Splat -InstanceName $instancename}
IF ($instresult.successful -eq $false) {
THROW $instresult.exitmessage
} ELSEIF ($instresult.restarted -eq $False) {
Write-host "PROCESS: Install Complete - Restarting Computer." @goodsplat
Restart-Computer -ComputerName $ComputerName -force -wait
}
} CATCH { Write-Host "ISSUE: SQL Install had an error - Stopping the process for troubleshooting." @badsplat; RETURN}
} ELSE { Write-Host "PROCESS: SQL Install was disabled." @warningsplat }
$sw3.stop()
Write-host "PROCESS: SQL Install and restart complete. Elapsed Time: $($sw3.Elapsed.minutes)min $($sw3.Elapsed.seconds)sec" @goodsplat
#endregion
Раздел 3 — Обновление SQL Server
Логика обновления инстанса в целом повторяет предыдущую статью: используются возможности Update-DbaInstance,
добавлены таймеры, улучшено логирование и учтено, что мы хотим обновлять только что установленный экземпляр, а не все
инстансы на хосте.
#Region - Update SQL Server
$sw4 = [system.diagnostics.stopwatch]::startNew()
IF ($UpdateSQL -eq 1) {
Write-host "PROCESS: Starting SQL update - $($sw0.Elapsed.minutes)min $($sw0.Elapsed.seconds)sec" @goodsplat
Try {
IF ($AutoCreateShare = 1) { $updatepath = "$share\SQL Updates\"}
ELSE { $updatepath = $ManualPathtoUpdate }
$splat = @{
ComputerName = $ComputerName
instancename = $updateinst
Restart = $true
Path = $updatepath
Confirm = $false
Credential = $cred
Download = $True
}
$result = Update-DbaInstance @Splat
If ($result.successful -eq $false) {
THROW "Update failed"
} ELSEIF ($result.restarted -eq $false) {
Write-host "PROCESS: Update Complete - Restarting Computer." @goodsplat
Restart-Computer -ComputerName $ComputerName -force -wait
}
} CATCH { Write-Host "ISSUE: SQL Update had an error - Stopping the process for troubleshooting." @badsplat; RETURN}
} ELSE { Write-Host "PROCESS: SQL Update was disabled." @warningsplat }
$sw4.stop()
Write-host "PROCESS: SQL Update complete. Elapsed Time: $($sw4.Elapsed.minutes)min $($sw4.Elapsed.seconds)sec" @goodsplat
#endregion
Раздел 4 — Конфигурация SQL Server
После перезапуска инстанса автор заметил, что в течение короткого времени SQL может пребывать в режиме пост‑обновления, поэтому добавлена пауза в 3 минуты перед попыткой подключиться и выполнить конфигурационные изменения. Далее идут отдельные блоки для каждого параметра: план питания хоста, MaxDOP, оптимизация под Ad Hoc, включение сжатия бэкапов, настройка проверки контрольных сумм, порога cost threshold, Remote Admin, настройка Max Memory, TempDB и т.д. Все операции выполняются через соответствующие команды dbatools с обработкой ошибок.
#Region - Configurations
Write-host "PROCESS: Pausing processing for 3 minutes post restart before attempting to connect for configurations and maintenance (Post SQL update upgrade mode)." @goodsplat
Start-sleep -seconds 180
$sw5 = [system.diagnostics.stopwatch]::startNew()
Write-host "PROCESS: Starting SQL Configurations - $($sw0.Elapsed.minutes)min $($sw0.Elapsed.seconds)sec" @goodsplat
IF($SetPowerPlan -eq 1) {
TRY{
Set-DbaPowerPlan -ComputerName $ComputerName -Credential $cred -Confirm:$false | Out-Null
Write-Host "PROCESS: Power Plan has been set to High Performance." @goodsplat
} CATCH { Write-Host "ISSUE: Setting Power Plan had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set Power Plan was disabled." @warningsplat }
IF($SetMaxDop -eq 1) {
TRY {
Test-DbaMaxDop @sqlsplat | Set-DbaMaxDop | Out-Null
Write-Host "PROCESS: MaxDOP has been set to the recommended value." @goodsplat
} CATCH { Write-Host "ISSUE: Setting MaxDOP had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set MaxDOP was disabled." @warningsplat }
IF($SetOptimizeForAdHoc -eq 1) {
TRY {
Get-DbaSpConfigure @sqlsplat | Where-Object { $_.displayname -eq 'Optimize For Ad Hoc Workloads'} | Set-DbaSpConfigure -Value 1 | Out-Null
Write-Host "PROCESS: Optimize for Ad Hoc Workloads has been enabled." @goodsplat
} CATCH { Write-Host "ISSUE: Setting Optimize for Ad Hoc Workloads had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set Optimize for Ad Hoc Workloads was disabled." @warningsplat }
IF($SetBackupCompression -eq 1) {
TRY {
Get-DbaSpConfigure @sqlsplat | Where-Object { $_.displayname -eq 'Backup Compression Default'} | Set-DbaSpConfigure -Value 1 | Out-Null
Write-Host "PROCESS: Backup Compression has been enabled." @goodsplat
} CATCH { Write-Host "ISSUE: Setting Backup Compression had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set Backup Compression was disabled." @warningsplat }
IF($SetBackupChecksum -eq 1) {
TRY {
Get-DbaSpConfigure @sqlsplat | Where-Object { $_.displayname -eq 'Backup Checksum Default'} | Set-DbaSpConfigure -Value 1 | Out-Null
Write-Host "PROCESS: Backup Checksum has been enabled." @goodsplat
} CATCH { Write-Host "ISSUE: Setting Backup Checksum had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set Backup Checksum was disabled." @warningsplat }
IF($SetCostThreshold -eq 1) {
TRY {
Get-DbaSpConfigure @sqlsplat | Where-Object { $_.displayname -eq 'Cost Threshold For Parallelism'} | Set-DbaSpConfigure -Value 50 | Out-Null
Write-Host "PROCESS: Cost Threshold for Parallelism has been set to 50." @goodsplat
} CATCH { Write-Host "ISSUE: Setting Cost Threshold for Parallelism had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set Cost Threshold for Parallelism was disabled." @warningsplat }
IF($SetRemoteAdmin -eq 1) {
TRY {
Get-DbaSpConfigure @sqlsplat | Where-Object { $_.displayname -eq 'Remote Admin Connections'} | Set-DbaSpConfigure -Value 1 | Out-Null
Write-Host "PROCESS: Remote Admin Connections has been enabled." @goodsplat
} CATCH { Write-Host "ISSUE: Setting Remote Admin Connections had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set Remote Admin Connections was disabled." @warningsplat }
IF($SetMaxMemory -eq 1) {
Try {
Set-DbaMaxMemory @sqlsplat | Out-Null
Write-Host "PROCESS: Max Memory has been set to the recommended value." @goodsplat
} Catch { Write-Host "ISSUE: Setting Max Memory had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set Max Memory was disabled." @warningsplat }
IF($SetTempDBConfiguration -eq 1) {
TRY {
Set-DbaTempDbConfig @sqlsplat -Datafilesize 1000 | Out-Null
Write-Host "PROCESS: TempDB configuration has been set to the recommended filecount with a default size." @goodsplat
} CATCH { Write-Host "ISSUE: Setting TempDB configuration had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set TempDB configuration was disabled." @warningsplat }
IF($trace3226 -eq 1) {
TRY {
Enable-DbaTraceFlag @sqlsplat -TraceFlag 3226 | Out-Null
Write-Host "PROCESS: Trace Flag 3226 has been added as a startup parameter." @goodsplat
} CATCH { Write-Host "ISSUE: Adding Trace Flag 3226 had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set Trace Flag 3226 was disabled." @warningsplat }
IF($SetErrorlog -eq 1) {
TRY {
Set-DbaErrorLogConfig @sqlsplat -logcount $ErrorlogCount | Out-Null
Write-Host "PROCESS: Error log file count has been set to $ErrorlogCount." @goodsplat
} CATCH { Write-Host "ISSUE: Setting Error log file count had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set Error log file count was disabled." @warningsplat }
IF($enableAlerts -eq 1) {
TRY {
Invoke-DbaQuery @sqlsplat -query "
EXEC msdb.dbo.sp_add_alert @name=N'Severity 16 Error',
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Severity 17 Error',
@message_id=0,
@severity=17,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Severity 18 Error',
@message_id=0,
@severity=18,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Severity 19 Error',
@message_id=0,
@severity=19,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Severity 20 Error',
@message_id=0,
@severity=20,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Severity 21 Error',
@message_id=0,
@severity=21,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Severity 22 Error',
@message_id=0,
@severity=22,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Severity 23 Error',
@message_id=0,
@severity=23,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Severity 24 Error',
@message_id=0,
@severity=24,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Severity 25 Error',
@message_id=0,
@severity=25,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Error 823',
@message_id=823,
@severity=0,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@category_name=N'[Uncategorized]';
EXEC msdb.dbo.sp_add_alert @name=N'Error 824',
@message_id=824,
@severity=0,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@category_name=N'[Uncategorized]';
EXEC msdb.dbo.sp_add_alert @name=N'Error 825',
@message_id=825,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
"
Write-Host "PROCESS: Alerts for Errors 823, 824, 825 and Severity 16-25 have been enabled." @goodsplat
} CATCH { Write-Host "ISSUE: Enabling Alerts had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Enable Alerts was disabled." @warningsplat }
$sw5.stop()
Write-host "PROCESS: Configurations complete. Elapsed Time: $($sw5.Elapsed.minutes)min $($sw5.Elapsed.seconds)sec" @goodsplat
#endregion
Раздел 5 — Обслуживание и инструменты
В этом блоке разворачивается база данных администратора (если задано), устанавливается пакет Ola Hallengren, First Responder Kit, WhoIsActive и скрипты sp_check* от Straight‑Path. Большая часть действий — это скачивание скриптов из репозиториев и их выполнение на целевом экземпляре.
#Region - Configurations
Write-host "PROCESS: Pausing processing for 3 minutes post restart before attempting to connect for configurations and maintenance (Post SQL update upgrade mode)." @goodsplat
Start-sleep -seconds 180
$sw5 = [system.diagnostics.stopwatch]::startNew()
Write-host "PROCESS: Starting SQL Configurations - $($sw0.Elapsed.minutes)min $($sw0.Elapsed.seconds)sec" @goodsplat
IF($SetPowerPlan -eq 1) {
TRY{
Set-DbaPowerPlan -ComputerName $ComputerName -Credential $cred -Confirm:$false | Out-Null
Write-Host "PROCESS: Power Plan has been set to High Performance." @goodsplat
} CATCH { Write-Host "ISSUE: Setting Power Plan had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set Power Plan was disabled." @warningsplat }
IF($SetMaxDop -eq 1) {
TRY {
Test-DbaMaxDop @sqlsplat | Set-DbaMaxDop | Out-Null
Write-Host "PROCESS: MaxDOP has been set to the recommended value." @goodsplat
} CATCH { Write-Host "ISSUE: Setting MaxDOP had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set MaxDOP was disabled." @warningsplat }
IF($SetOptimizeForAdHoc -eq 1) {
TRY {
Get-DbaSpConfigure @sqlsplat | Where-Object { $_.displayname -eq 'Optimize For Ad Hoc Workloads'} | Set-DbaSpConfigure -Value 1 | Out-Null
Write-Host "PROCESS: Optimize for Ad Hoc Workloads has been enabled." @goodsplat
} CATCH { Write-Host "ISSUE: Setting Optimize for Ad Hoc Workloads had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set Optimize for Ad Hoc Workloads was disabled." @warningsplat }
IF($SetBackupCompression -eq 1) {
TRY {
Get-DbaSpConfigure @sqlsplat | Where-Object { $_.displayname -eq 'Backup Compression Default'} | Set-DbaSpConfigure -Value 1 | Out-Null
Write-Host "PROCESS: Backup Compression has been enabled." @goodsplat
} CATCH { Write-Host "ISSUE: Setting Backup Compression had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set Backup Compression was disabled." @warningsplat }
IF($SetBackupChecksum -eq 1) {
TRY {
Get-DbaSpConfigure @sqlsplat | Where-Object { $_.displayname -eq 'Backup Checksum Default'} | Set-DbaSpConfigure -Value 1 | Out-Null
Write-Host "PROCESS: Backup Checksum has been enabled." @goodsplat
} CATCH { Write-Host "ISSUE: Setting Backup Checksum had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set Backup Checksum was disabled." @warningsplat }
IF($SetCostThreshold -eq 1) {
TRY {
Get-DbaSpConfigure @sqlsplat | Where-Object { $_.displayname -eq 'Cost Threshold For Parallelism'} | Set-DbaSpConfigure -Value 50 | Out-Null
Write-Host "PROCESS: Cost Threshold for Parallelism has been set to 50." @goodsplat
} CATCH { Write-Host "ISSUE: Setting Cost Threshold for Parallelism had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set Cost Threshold for Parallelism was disabled." @warningsplat }
IF($SetRemoteAdmin -eq 1) {
TRY {
Get-DbaSpConfigure @sqlsplat | Where-Object { $_.displayname -eq 'Remote Admin Connections'} | Set-DbaSpConfigure -Value 1 | Out-Null
Write-Host "PROCESS: Remote Admin Connections has been enabled." @goodsplat
} CATCH { Write-Host "ISSUE: Setting Remote Admin Connections had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set Remote Admin Connections was disabled." @warningsplat }
IF($SetMaxMemory -eq 1) {
Try {
Set-DbaMaxMemory @sqlsplat | Out-Null
Write-Host "PROCESS: Max Memory has been set to the recommended value." @goodsplat
} Catch { Write-Host "ISSUE: Setting Max Memory had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set Max Memory was disabled." @warningsplat }
IF($SetTempDBConfiguration -eq 1) {
TRY {
Set-DbaTempDbConfig @sqlsplat -Datafilesize 1000 | Out-Null
Write-Host "PROCESS: TempDB configuration has been set to the recommended filecount with a default size." @goodsplat
} CATCH { Write-Host "ISSUE: Setting TempDB configuration had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set TempDB configuration was disabled." @warningsplat }
IF($trace3226 -eq 1) {
TRY {
Enable-DbaTraceFlag @sqlsplat -TraceFlag 3226 | Out-Null
Write-Host "PROCESS: Trace Flag 3226 has been added as a startup parameter." @goodsplat
} CATCH { Write-Host "ISSUE: Adding Trace Flag 3226 had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set Trace Flag 3226 was disabled." @warningsplat }
IF($SetErrorlog -eq 1) {
TRY {
Set-DbaErrorLogConfig @sqlsplat -logcount $ErrorlogCount | Out-Null
Write-Host "PROCESS: Error log file count has been set to $ErrorlogCount." @goodsplat
} CATCH { Write-Host "ISSUE: Setting Error log file count had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Set Error log file count was disabled." @warningsplat }
IF($enableAlerts -eq 1) {
TRY {
Invoke-DbaQuery @sqlsplat -query "
EXEC msdb.dbo.sp_add_alert @name=N'Severity 16 Error',
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Severity 17 Error',
@message_id=0,
@severity=17,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Severity 18 Error',
@message_id=0,
@severity=18,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Severity 19 Error',
@message_id=0,
@severity=19,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Severity 20 Error',
@message_id=0,
@severity=20,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Severity 21 Error',
@message_id=0,
@severity=21,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Severity 22 Error',
@message_id=0,
@severity=22,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Severity 23 Error',
@message_id=0,
@severity=23,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Severity 24 Error',
@message_id=0,
@severity=24,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Severity 25 Error',
@message_id=0,
@severity=25,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
EXEC msdb.dbo.sp_add_alert @name=N'Error 823',
@message_id=823,
@severity=0,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@category_name=N'[Uncategorized]';
EXEC msdb.dbo.sp_add_alert @name=N'Error 824',
@message_id=824,
@severity=0,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@category_name=N'[Uncategorized]';
EXEC msdb.dbo.sp_add_alert @name=N'Error 825',
@message_id=825,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
"
Write-Host "PROCESS: Alerts for Errors 823, 824, 825 and Severity 16-25 have been enabled." @goodsplat
} CATCH { Write-Host "ISSUE: Enabling Alerts had an error." @badsplat }
} ELSE { Write-Host "PROCESS: Enable Alerts was disabled." @warningsplat }
$sw5.stop()
Write-host "PROCESS: Configurations complete. Elapsed Time: $($sw5.Elapsed.minutes)min $($sw5.Elapsed.seconds)sec" @goodsplat
#endregion
Завершение и логирование
По окончании всех шагов скрипт очищает созданную ранее шару (если он был создан) и останавливает логирование. Транскрипт удобен тем, что позволяет запускать автоматизацию без постоянного присутствия оператора — все подробности и ошибки будут записаны в файл для последующего анализа.
Инструкции по развёртыванию
Я подготовил ZIP‑файл со скриптом и вспомогательными каталогами: SQL Files (в неё нужно развернуть
содержимое ISO с инсталлятором SQL Server), SQL Updates (сюда будут загружаться обновления),
Supporting files (скрипты sp_check*), и Transcripts (логи выполнения).
Если вы предпочитаете собственные пути — просто замените значения переменных в начале скрипта.
Ссылка на ZIP‑файл с исходниками в GitHub SQL Server Deployment Script.
Вывод
Этот набор демонстрирует, как PowerShell + dbatools могут "выжать" огромное количество автоматизации для DBA. Сценарий позволяет экономить время и уменьшить количество повторяющихся ручных операций. Рекомендация: тестируйте в изолированной среде, поэтапно включайте опции и сохраняйте транскрипт для разбора ошибок.
Комментариев нет:
Отправить комментарий