Update multiple SQL Server systems with PowerShell

• Author
• Recent Posts

Adam Bertram

Adam Bertram is a 20-year IT veteran, Microsoft MVP, blogger, and trainer.

Latest posts by Adam Bertram (see all)

• Create a certificate-signed RDP shortcut via Group Policy - Fri, Aug 9 2019
• Monitor web server uptime with a PowerShell script - Tue, Aug 6 2019
• How to build a PowerShell inventory script for Windows Servers - Fri, Aug 2 2019

Patches (service packs and cumulative updates) are periodically released from Microsoft. Judging by the importance of the data contained in databases, it's critical that these servers are patched ASAP. How do you install service packs and cumulative updates on SQL servers today? Are you still going to download.microsoft.com, downloading the bits, extracting them, transferring the bits to each SQL server, opening up remote desktop and executing the update manually? If so, there's a better way:

Update-SqlServer –ComputerName 'SERVER1','SERVER2','SERVER3' –ServicePack Latest –CumulativeUpdate Latest

This requires a bit of planning and work. Lucky for you, I've already done that! To get started, we'll need to create more than just a single Update-SqlServer function. Since we'll be installing service packs and cumulative updates, we'll need at least three different functions. In fact, we'll end up with a lot of various functions. Remember that it's always better to build smaller functions so they can be used as building blocks to create the tool.

The SQL Server update tool (Update-SqlServer) we'll be creating will consist of a total of seven custom functions along with some helper functions. We're going big time here!

• Install-SqlServerCumulativeUpdate
  • Get-SQLServerVersion
  • Find-SqlServerCumulativeUpdateInstaller
  • Get-LatestSqlServerCumulativeUpdateVersion
• Install-SqlServerServicePack
  • Get-SQLServerVersion
  • Find-SqlServerServicePackInstaller
  • Get-LatestSqlServerServicePackVersion

Since there are so many functions, I'm going to emphasize the flow of this tool rather than diving into the code. If you want to grab the code for this tool, feel free to download it from my Github repository.

For this tool to work, you must prepare the service packs and cumulative updates ahead of time and ensure they all follow a standard naming convention.  Here's how mine are arranged and the schema the tool expects:

The service packs and cumulative updates

You can see that I have folders structured like this: Microsoft/SQL/<2012/2014/2016>/Updates. I also have service pack 1 for download as well as CU5 for SP1 and CU12 for RTM. As long as you follow the same naming convention, this tool will allow you to select whatever service pack and cumulative update you'd like to install.

Once all of the installers have been downloaded, I then use a CSV file called sqlserverversions.csv that exists in the same folder as my SqlUpdater.ps1 script. This is where I keep track of all of the available service packs and cumulative updates for each version. I use this in the tool as a way to define the "latest" SPs and CUs for every version.

sqlserverversions.csv file

This CSV will need to be updated periodically when new SPs and CUs are released for the latest SQL Server version.

That's it for the ancillary files needed. Let's get into some code!

The primary function is Update-SqlServer. This function is simply a wrapper function for both Install-SqlServerServicePack and Install-SqlServerCumulativeUpdate. As you can see below, Update-SqlServer has a few parameters; ComputerName which indicates the server to install the update on, the service pack and cumulative update defaulting to the latest (remember that CSV file?) and an optional Credential if you need to use alternate credentials.

function Update-SqlServer
{
    [CmdletBinding()]
param
(
        [Parameter(Mandatory)]
        [ValidateNotNull0rEmpty()]
        [string]$ComputerName,

        [Parameter()]
        [ValidateNotNull0rEmpty()]
        [ValidateSet(1, 2, 3, 4, 5, ‘Latest’)]
[string]$ServicePack = ‘Latest’,

        [Parameter()]
        [ValidateNotNull0rEmpty()]
        [ValidateSet(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, ‘Latest’)]
        [string]$CumulativeUpdate = ‘Latest’,

        [Parameter()]
        [ValidateNotNull0rEmpty()]
        [pscredential]$Credential
)
…
}

As you can see above, Update-SqlServer has a few parameters: ComputerName, which indicates the server on which to install the update; the service pack and cumulative update, which defaults to the latest (remember that CSV file?); and an optional Credential if you need to use alternate credentials.

Let's say that you need to install the latest SP and CU on a SQL Server. If so, you'd run Update-SqlServer like this:

Update-SqlServer –ComputerName 'SERVER1' –ServicePack Latest –CumulativeUpdate Latest

This will kick off the following flow:

1. Find the version of SQL Server that's installed on SERVER1.
2. Find the latest SP for that version in the CSV file.
3. Find the appropriate installer in the shared UNC path.
4. Install the service pack.
5. (Repeat for the CU)

The only thing that must be done for this to work seamlessly is to ensure that the standard naming convention is followed for the installers and that each version's CSV exists. Otherwise, it should work great! However, no code is perfect, so feel free to try it out and let me know how it works.