A PowerShell script to remotely install SQL Server service packs

Any Microsoft SQL database administrator (DBA) will need to patch a SQL installation at one time or another. Using PowerShell, this can be made far easier and more convenient, especially with large numbers of servers. In this article, we are going to build a simple script for patching SQL Servers with Service Packs.

Script overview

There will be a few steps that we need to take in this script to effectively patch a system.

1. Verify SQL Server is accessible and not updated
2. Test for pending reboots
3. Install the service pack and restart the server
4. Verify proper service pack installation

Environment prerequisites

Retrieving information from SQL Server via PowerShell is done with the SqlServer module. This can be imported as below from the PowerShell gallery. This script is also built with the assumption that this will be run against a remote SQL Server to allow for proper waiting on a reboot.

Import-Module -Name SqlServer

Verify SQL Server is accessible and not updated

By retrieving the existing Microsoft SQL Server version number, we will verify that the script can both access the SQL Server to update and that the service pack has not already been installed.

$ComputerName = 'SQLServer'
$ServicePack  = 'SP2'

Try {
    $SQLInstance  = Get-SQLInstance -ComputerName $ComputerName -ErrorAction 'Stop'
} Catch {
    Throw "Unable to retrieve SQL Instance"
}

$CurrentServicePackVersion = $SQLInstance.ServicePack
Write-Host "Current Service Pack Version: $CurrentServicePackVersion"

If ($CurrentServicePackVersion -EQ $ServicePack) {
    Throw "Service Pack already installed"
}

Test for pending reboots

Often when installing software, a pending reboot will cause an installation to fail. In this case, it is prudent to test for this and make sure that it works properly. There are usually three locations to check that a reboot is not needed:

• Component-based servicing
• Windows update
• Pending file rename actions

If any one of these three areas needs a reboot, then the server must be restarted first to clear out those pending operations to ensure a successful install.

$PendingRebootNecessary = $False

Try {
    $Params = @{
        'ComputerName' = $ComputerName
        'ScriptBlock'  = {
            $Params = @{
                'Path'        = 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing'
                'Name'        = 'RebootPending'
                'ErrorAction' = 'SilentlyContinue'
            }

            Get-ItemProperty @Params
        }
    }

    $PendingReboot = Invoke-Command @Params

    If ($PendingReboot) {
        $PendingRebootNecessary = $True
    }

    $Params = @{
        'ComputerName' = $ComputerName
        'ScriptBlock'  = {
            $Params = @{
                'Path'        = 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update'
                'Name'        = 'RebootRequired'
                'ErrorAction' = 'SilentlyContinue'
            }

            Get-ItemProperty @Params
        }
    }

    $PendingReboot = Invoke-Command @Params

    If ($PendingReboot) {
        $PendingRebootNecessary = $True
    }

    $Params = @{
        'ComputerName' = $ComputerName
        'ScriptBlock'  = {
            $Params = @{
                'Path'        = 'HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager'
                'Name'        = 'PendingFileRenameOperations'
                'ErrorAction' = 'SilentlyContinue'
            }

            Get-ItemProperty @Params
        }
    }

    $PendingReboot = Invoke-Command @Params

    If ($PendingReboot -And $PendingReboot.PendingFileRenameOperations) {
        $PendingRebootNecessary = $True
    }
} Catch {
    Write-Error -Message $_.Exception.Message
}

If ($PendingRebootNecessary) {
    Throw "Pending restart is needed, please reboot the server first"
}

Install the service pack & restart the server

Now that we have cleared several checks, we need to install the service pack on the server. Using the given installation path and extraction directory, start the install process using the .NET method of creating a process from within PowerShell. By utilizing this method, it grants more control over how to run the given process.

This script assumes that both the installation file and extraction path exist and accessible by the account running the script

$ExtractionPath     = 'C:\Windows\Temp\SQLSP'
$InstallerPath      = 'C:\Temp\spinstaller.exe'
$Arguments          = "/extract:`"$ExtractionPath`" /quiet"
$SuccessReturnCodes = @(0, 3010)

$Installer = (Get-Item $InstallerPath).FullName

If ($Installer) {
  $Params = @{
    'ComputerName' = $ComputerName  
  }

  $Params.ScriptBlock = {
    Try {
      $processStartInfo           = New-Object System.Diagnostics.ProcessStartInfo
      $processStartInfo.FileName  = $Using:InstallerPath
      $processStartInfo.Arguments = $Using:Arguments

      # Necessary for Windows Core Installs
      $processStartInfo.UseShellExecute = $false
      
      $process           = New-Object System.Diagnostics.Process
      $process.StartInfo = $processStartInfo
      
      $null = $process.Start()
      $process.WaitForExit()
            
      If ($process.ExitCode -NotIn $Using:SuccessReturnCodes) {
        Throw "Error running program: $($process.ExitCode)"
      }
    } Catch {
      Write-Error $_.Exception.ToString()
    }
  }

  $InstallResult = Invoke-Command @Params

  If ($InstallResult) {
    Restart-Computer -ComputerName $ComputerName -Wait -Force
  }
} Else {
  Throw "Installer not found"
}

Verify proper service pack installation

Much like in the beginning, run the same code to verify that the SQL Server is accessible and to return the SQL Service pack. If the service pack matches the variable as defined at the start of our script, declare a successful install.

Try {
    $SQLInstance  = Get-SQLInstance -ComputerName $ComputerName -ErrorAction 'Stop'
} Catch {
    Throw "Unable to retrieve SQL Instance"
}

$CurrentServicePackVersion = $SQLInstance.ServicePack
Write-Host "Current Service Pack Version: $CurrentServicePackVersion"

If ($CurrentServicePackVersion -EQ $ServicePack) {
    Write-Host "Sucess, the Service Pack is installed"
}

Managing a single SQL Server can be difficult, let alone multiples. Utilizing PowerShell to automate and make these tasks easier will allow for improved reliability, error logging and speed. Avoiding the need to use the GUI will greatly simplify how quickly an administrator can update a large number of servers.

This script is simple to start with, but with additional function abstraction, error logic and parameters for control, this script can very quickly become a valuable tool in your arsenal. Adapting this for use in cumulative updates or any software that needs to be installed, will make short work of keeping your servers up to date!