internal/functions/Install-SqlServerUpdate.ps1
|
function Install-SqlServerUpdate { <# Originally based on https://github.com/adbertram/PSSqlUpdater Internal function. Invokes installation of a single SQL Server KB based on provided parameters. #> [CmdletBinding(SupportsShouldProcess, ConfirmImpact = 'High', DefaultParameterSetName = 'Latest')] param ( [Parameter(Mandatory)] [ValidateNotNullOrEmpty()] [DbaInstanceParameter]$ComputerName, [pscredential]$Credential, [Parameter(Mandatory, ParameterSetName = 'Latest')] [ValidateSet('ServicePack', 'CumulativeUpdate')] [string]$Type, [string[]]$MajorVersion, [Parameter(ParameterSetName = 'Number')] [int]$ServicePack, [Parameter(ParameterSetName = 'Number')] [int]$CumulativeUpdate, [Parameter(Mandatory, ParameterSetName = 'KB')] [ValidateNotNullOrEmpty()] [string]$KB, [bool]$Restart, [string[]]$Path, [bool]$EnableException = $EnableException ) process { # check if any type of the update was specified if ($PSCmdlet.ParameterSetName -eq 'Number' -and -not ((Test-Bound ServicePack) -or (Test-Bound CumulativeUpdate))) { Stop-Function -Message "No update was specified, provide at least one value for either SP/CU" return } $computer = $ComputerName.ComputerName $activity = "Updating SQL instance builds on $computer" ## Find the current version on the computer Write-ProgressHelper -ExcludePercent -Activity $activity -StepNumber 0 -Message "Gathering all SQL Server instance versions" $currentVersions = Get-SQLServerVersion -ComputerName $computer if (!$currentVersions) { Stop-Function -Message "No SQL Server installations found on $computer" return } # Group by version and select the earliest version installed $currentVersionGroups = $currentVersions | Group-Object -Property NameLevel | ForEach-Object { $_.Group | Sort-Object -Property Build | Select-Object -First 1 } $verCount = ($currentVersionGroups | Measure-Object).Count $verDesc = ($currentVersionGroups | Foreach-Object { "$($_.NameLevel) ($($_.Build))" }) -join ', ' Write-Message -Level Debug -Message "Found $verCount existing SQL Server version(s): $verDesc" #Check if more than one version is found if (($currentVersionGroups | Measure-Object ).Count -gt 1 -and ($CumulativeUpdate -or $ServicePack) -and !$MajorVersion) { Stop-Function -Message "Updating multiple different versions of SQL Server to a specific SP/CU is not supported. Please specify a version of SQL Server on $computer that you want to update." return } if ($MajorVersion) { $currentVersionGroups = $currentVersionGroups | Where-Object { $_.NameLevel -in $MajorVersion } } $verCount = ($currentVersionGroups | Measure-Object).Count $verDesc = ($currentVersionGroups | Foreach-Object { "$($_.NameLevel) ($($_.Build))" }) -join ', ' Write-Message -Level Verbose -Message "Found $verCount applicable SQL Server version(s): $verDesc" ## Find the architecture of the computer if ($arch = (Get-DbaCmObject -ComputerName $computer -ClassName 'Win32_ComputerSystem').SystemType) { if ($arch -eq 'x64-based PC') { $arch = 'x64' } else { $arch = 'x86' } } else { Write-Message -Level Warning -Message "Failed to determine the arch of $computer, using x64 by default" $arch = 'x64' } $targetLevel = '' # Launch a setup sequence for each version found foreach ($currentVersion in $currentVersionGroups) { $stepCounter = 0 $currentMajorVersion = "SQL" + $currentVersion.NameLevel Write-ProgressHelper -ExcludePercent -Activity $activity -Message "Parsing versions" # create a parameter set for Find-SqlServerUpdate $kbLookupParams = @{ Architecture = $arch MajorVersion = $currentVersion.NameLevel Path = $Path } # Find target KB number based on provided SP/CU levels or KB numbers if ($CumulativeUpdate -gt 0) { #Cumulative update is present - installing CU if (Test-Bound -Parameter ServicePack) { #Service pack is present - using it as a reference $targetKB = Get-DbaBuildReference -MajorVersion $currentVersion.NameLevel -ServicePack $ServicePack -CumulativeUpdate $CumulativeUpdate } else { #Service pack not present - using current SP level $targetSP = $currentVersion.SPLevel | Where-Object { $_ -ne 'LATEST' } | Select-Object -First 1 $targetKB = Get-DbaBuildReference -MajorVersion $currentVersion.NameLevel -ServicePack $targetSP -CumulativeUpdate $CumulativeUpdate } } elseif ($ServicePack -gt 0) { #Service pack number was passed without CU - installing service pack $targetKB = Get-DbaBuildReference -MajorVersion $currentVersion.NameLevel -ServicePack $ServicePack } elseif ($KB) { $targetKB = Get-DbaBuildReference -KB $KB if ($targetKB -and $currentVersion.NameLevel -ne $targetKB.NameLevel) { Write-Message -Level Debug -Message "$($targetKB.NameLevel) is not a target Major version $($currentVersion.NameLevel), skipping" continue } } else { #No parameters = latest patch. Find latest SQL Server build and corresponding SP and CU KBs $latestCU = Test-DbaBuild -Build $currentVersion.Build -MaxBehind '0CU' if (!$latestCU.Compliant) { #more recent build is found, get KB number depending on what is the current upgrade $Type $targetKB = Get-DbaBuildReference -Build $latestCU.BuildTarget $targetSP = $targetKB.SPLevel | Where-Object { $_ -ne 'LATEST' } | Select-Object -First 1 if ($Type -eq 'CumulativeUpdate') { if ($currentVersion.SPLevel -notcontains 'LATEST') { $currentSP = $currentVersion.SPLevel | Where-Object { $_ -ne 'LATEST' } | Select-Object -First 1 Stop-Function -Message "Current SP version $currentMajorVersion$currentSP is not the latest available. Make sure to upgade to latest SP level before applying latest CU." -Continue } $targetLevel = "$($targetKB.SPLevel | Where-Object { $_ -ne 'LATEST' })$($targetKB.CULevel)" Write-Message -Level Debug -Message "Found a latest Cumulative Update $targetLevel (KB$($targetKB.KBLevel))" } elseif ($Type -eq 'ServicePack') { $targetKB = Get-DbaBuildReference -MajorVersion $targetKB.NameLevel -ServicePack $targetSP $targetLevel = $targetKB.SPLevel | Where-Object { $_ -ne 'LATEST' } Write-Message -Level Debug -Message "Found a latest Service Pack $targetLevel (KB$($targetKB.KBLevel))" } } else { Write-Message -Message "$($currentVersion.Build) on computer [$($computer)] is already the latest available." -Level Verbose continue } } if ($targetKB.KBLevel) { if ($targetKB.MatchType -ne 'Exact') { Stop-Function -Message "Couldn't find an exact build match with specified parameters while updating $currentMajorVersion" -Continue } $targetLevel = "$($targetKB.SPLevel | Where-Object { $_ -ne 'LATEST' })$($targetKB.CULevel)" $targetKBLevel = $targetKB.KBLevel | Select-Object -First 1 Write-Message -Level Verbose -Message "Upgrading SQL$($targetKB.NameLevel) to $targetLevel (KB$($targetKBLevel))" $kbLookupParams.KB = $targetKBLevel } else { Stop-Function -Message "Could not find a KB$KB reference for $currentMajorVersion SP $ServicePack CU $CumulativeUpdate" -Continue } # Compare versions - whether to proceed with the installation if ($currentVersion.BuildLevel -ge $targetKB.BuildLevel) { Write-Message -Message "Current $currentMajorVersion version $($currentVersion.BuildLevel) on computer [$($computer)] matches or already higher than target version $($targetKB.BuildLevel)" -Level Verbose continue } ## Find the installer to use Write-ProgressHelper -ExcludePercent -Activity $activity -Message "Searching for update binaries" $installer = Find-SqlServerUpdate @kbLookupParams if (!$installer) { Stop-Function -Message "Could not find installer for the $currentMajorVersion update KB$($kbLookupParams.KB)" -Continue } ## Apply patch Write-ProgressHelper -ExcludePercent -Activity $activity -Message "Installing $targetLevel KB$($targetKB.KBLevel) ($($installer.Name)) for $currentMajorVersion ($($currentVersion.BuildLevel))" if ($PSCmdlet.ShouldProcess($computer, "Install $targetLevel KB$($targetKB.KBLevel) ($($installer.Name)) for $currentMajorVersion ($($currentVersion.BuildLevel))")) { $invProgParams = @{ ComputerName = $computer Credential = $Credential ErrorAction = 'Stop' } # Find a temporary folder to extract to - the drive that has most free space $chosenDrive = (Get-DbaDiskSpace -ComputerName $computer -Credential $Credential | Sort-Object -Property Free -Descending | Select-Object -First 1).Name if (!$chosenDrive) { # Fall back to the system drive $chosenDrive = Invoke-Command2 -ComputerName $computer -Credential $Credential -ScriptBlock { $env:SystemDrive } -Raw -ErrorAction Stop } $spExtractPath = $chosenDrive.TrimEnd('\') + "\dbatools_KB$($targetKB.KBLevel)_Extract" if ($spExtractPath) { try { # Extract file Write-ProgressHelper -ExcludePercent -Activity $activity -Message "Extracting $installer to $spExtractPath" Write-Message -Level Verbose -Message "Extracting $installer to $spExtractPath" $null = Invoke-Program @invProgParams -Path $installer.FullName -ArgumentList "/x`:`"$spExtractPath`" /quiet" # Install the patch Write-ProgressHelper -ExcludePercent -Activity $activity -Message "Now installing update from $spExtractPath" Write-Message -Level Verbose -Message "Starting installation from $spExtractPath" $log = Invoke-Program @invProgParams -Path "$spExtractPath\setup.exe" -ArgumentList '/quiet /allinstances /IAcceptSQLServerLicenseTerms' -WorkingDirectory $spExtractPath $success = $true } catch { Stop-Function -Message "Upgrade failed" -ErrorRecord $_ return } finally { ## Cleanup temp try { Write-ProgressHelper -ExcludePercent -Activity $activity -Message "Removing temporary files" $null = Invoke-Command2 -ComputerName $computer -Credential $Credential -ScriptBlock { if ($args[0] -like '*\dbatools_KB*_Extract' -and (Test-Path $args[0])) { Remove-Item -Recurse -Force -LiteralPath $args[0] -ErrorAction Stop } } -Raw -ArgumentList $spExtractPath -ErrorAction Stop } catch { Write-Message -Level Warning -Message "Failed to cleanup temp folder on computer $computer`: $($_.Exception.Message) " } } } if ($Restart) { Write-ProgressHelper -ExcludePercent -Activity $activity -Message "Restarting computer $computer and waiting for it to come back online" Write-Message -Level Verbose "Restarting computer $computer and waiting for it to come back online" try { $restartParams = @{ ComputerName = $computer } if ($Credential) { $restartParams += @{ Credential = $Credential } } $null = Restart-Computer @restartParams -Wait -For WinRm -Force -ErrorAction Stop $restarted = $true } catch { Stop-Function -Message "Failed to restart computer" -ErrorRecord $_ return } } else { $message = "Restart is required for computer $computer to finish the installation of $currentMajorVersion$targetLevel" } } else { $message = 'The installation was not performed - running in WhatIf mode' $success = $true } # return resulting object. This function throws, so all results here are expected to be shown only in a positive light [psobject]@{ ComputerName = $ComputerName MajorVersion = $kbLookupParams.MajorVersion TargetLevel = $targetLevel KB = $kbLookupParams.KB Successful = [bool]$success Restarted = [bool]$restarted Installer = $installer.FullName ExtractPath = $spExtractPath Message = $message Log = $log } if (-not $restarted) { Write-Message -Level Verbose "No more installations for other versions on $computer - restart is pending" return } } } } |