internal/functions/Get-SqlServerUpdate.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
function Get-SqlServerUpdate {
    <#
    Originally based on https://github.com/adbertram/PSSqlUpdater
    Internal function. Provides information on the update path for a specific set of SQL Server instances based on current and target SQL Server levels.
    Component parameter is using the output object of Get-SqlInstanceComponent.
    #>

    [CmdletBinding(DefaultParameterSetName = 'Latest')]
    param
    (
        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        [DbaInstanceParameter]$ComputerName,
        [pscredential]$Credential,
        [Parameter(Mandatory, ParameterSetName = 'Latest')]
        [ValidateSet('ServicePack', 'CumulativeUpdate')]
        [string]$Type,
        [object[]]$Component,
        [Parameter(ParameterSetName = 'Number')]
        [int]$ServicePack,
        [Parameter(ParameterSetName = 'Number')]
        [int]$CumulativeUpdate,
        [Parameter(Mandatory, ParameterSetName = 'KB')]
        [ValidateNotNullOrEmpty()]
        [string]$KB,
        [bool]$Restart,
        [string]$InstanceName,
        [string[]]$Path,
        [bool]$EnableException = $EnableException,
        [bool]$Continue
    )
    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
        $verCount = ($Component | Measure-Object).Count
        $verDesc = ($Component | Foreach-Object { "$($_.Version.NameLevel) ($($_.Version.Build))" }) -join ', '
        Write-Message -Level Debug -Message "Selected $verCount existing SQL Server version(s): $verDesc"

        # Group by version
        $currentVersionGroups = $Component | Group-Object -Property { $_.Version.NameLevel }
        #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
        }
        ## 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 ($currentGroup in $currentVersionGroups) {
            $currentMajorVersion = "SQL" + $currentGroup.Name
            $currentMajorNumber = $currentGroup.Name

            # Use the earliest version in case specifics are needed
            $currentVersion = $currentGroup.Group | Sort-Object -Property { $_.Version.BuildLevel } | Select-Object -ExpandProperty Version -First 1

            #create output object
            $output = [pscustomobject]@{
                ComputerName  = $ComputerName
                MajorVersion  = $currentMajorNumber
                Build         = $currentVersion.Build
                TargetVersion = $null
                TargetLevel   = $null
                KB            = $null
                Successful    = $false
                Restarted     = $false
                InstanceName  = $InstanceName
                Installer     = $null
                ExtractPath   = $null
                Notes         = $null
                ExitCode      = $null
                Log           = $null
            }

            # create a parameter set for Find-SqlServerUpdate
            $kbLookupParams = @{
                Architecture = $arch
                MajorVersion = $currentGroup.Name
                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 $currentMajorNumber -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 $currentMajorNumber -ServicePack $targetSP -CumulativeUpdate $CumulativeUpdate
                }
            } elseif ($ServicePack -gt 0) {
                #Service pack number was passed without CU - installing service pack
                $targetKB = Get-DbaBuildReference -MajorVersion $currentMajorNumber -ServicePack $ServicePack
            } elseif ($KB) {
                $targetKB = Get-DbaBuildReference -KB $KB
                if ($targetKB -and $currentMajorNumber -ne $targetKB.NameLevel) {
                    Write-Message -Level Debug -Message "$($targetKB.NameLevel) is not a target Major version $($currentMajorNumber), skipping"
                    continue
                }
            } else {
                #No parameters = latest patch. Find latest SQL Server build and corresponding SP and CU KBs
                $latestCU = Test-DbaBuild -Build $currentVersion.BuildLevel -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' }
                        if ($currentVersion.SPLevel -contains 'LATEST') {
                            Write-Message -Message "No need to update $currentMajorVersion to $targetLevel - it's already on the latest SP version" -Level Verbose
                            continue
                        }
                        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
                }
                $output.TargetVersion = $targetKB
                $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 {
                $output.Notes = "Could not find a KB$KB reference for $currentMajorVersion SP $ServicePack CU $CumulativeUpdate"
                $output
                Stop-Function -Message $output.Notes -Continue
            }

            # Compare versions - whether to proceed with the installation
            $needsUpgrade = $false
            foreach ($currentComponent in $currentGroup.Group) {
                $groupVersion = $currentComponent.Version
                #target version is less than requested
                if ($groupVersion.BuildLevel -lt $targetKB.BuildLevel) {
                    $needsUpgrade = $true
                }
                #target version is the same but installation has failed last time
                elseif ($groupVersion.BuildLevel -eq $targetKB.BuildLevel -and $Continue -and $currentComponent.Resume) {
                    $needsUpgrade = $true
                }
            }
            if (!$needsUpgrade) {
                Write-Message -Message "Current $currentMajorVersion version $($currentVersion.BuildLevel) on computer [$($computer)] matches or already higher than target version $($targetKB.BuildLevel)" -Level Verbose
                continue
            }

            $output.TargetLevel = $targetLevel
            $output.KB = $kbLookupParams.KB
            ## Find the installer to use

            $installer = Find-SqlServerUpdate @kbLookupParams
            if (!$installer) {
                $output.Notes = "Could not find installer for the $currentMajorVersion update KB$($kbLookupParams.KB)"
                $output
                Stop-Function -Message $output.Notes -Continue
            }
            $output.Installer = $installer.FullName
            $output.Successful = $true
            #Return the object for further processing
            $output
        }
    }
}