In this article, I'll show you how to create a tool in PowerShell that will allow you to update multiple SQL servers in one swath with a single command.
Profile gravatar of Adam Bertram

Adam Bertram

Adam is a Microsoft Cloud and Datacenter Management Most Valuable Profressional (MVP) who specializes in Windows PowerShell. You can reach Adam at adamtheautomator.com or on Twitter at @adbertram.
Profile gravatar of Adam Bertram

Latest posts by Adam Bertram (see all)

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:

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

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

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.

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:

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.

Take part in our competition and win $100!

Share
1+

Related Posts

18 Comments
  1. avatar
    Kevin parks 8 months ago

    This looks great, just what I was looking for!

    0
  2. avatar
    Jach 2 months ago

    Seems that there are a couple of things required in the script to make it work:
    1. Get-Callerpreference is found here: https://gallery.technet.microsoft.com/scriptcenter/Inherit-Preference-82343b9d
    2. Write-Log is found here: https://gallery.technet.microsoft.com/scriptcenter/Write-Log-PowerShell-999c32d0
    3. -Source is not a valid Write-Log parameter, so I couldn't go any further, I tried deleting all occurrences of it to continue.
    4. Computer name "...does not match the "^([\w-]+\.){2}\w+$" pattern", I commented ValidatePattern out, as the name was valid, to continue.
    5. ConvertTo-VersionObject I can't find anywhere, Microsoft has documentation for: ConvertTo-Csv, ConvertTo-Html, ConvertTo-Json and ConvertTo-Xml, no idea what to do from here.

    0
    • Profile gravatar of Adam Bertram Author
      Adam Bertram 2 months ago

      Apologies. There were still references to private functions in my internal modules. I've removed or replaced all of them. Take a look again.

      0
  3. avatar
    Jeff 1 month ago

    This error still happened for me:

    4. Computer name "...does not match the "^([\w-]+\.){2}\w+$" pattern", I commented ValidatePattern out, as the name was valid, to continue.

    0
  4. avatar
    Jeff 3 weeks ago

    Adam,

    Just a few notes on what I have found so far:

    The Validate set is only allowing a few version:
    ie.  ValidateSet('2008R2', '2012', '2014')

    Can this handle older versions?
    $verKey only accounts for sql 2012 and 2014.
    Caption doesn't account for newer versions:
    Caption = {
    switch -regex ($version)
    {
    "^11"  { "SQL Server 2012"; break }
    "^10\.5" { "SQL Server 2008 R2"; break }
    "^10"  { "SQL Server 2008"; break }
    "^9"  { "SQL Server 2005"; break }
    "^8"  { "SQL Server 2000"; break }
    default { "Unknown"; }
    }
    Will this work in version < 2008r2?
    I can't seem to get a valid file returned for this:
    $servicePacks = Get-ChildItem -Path $SqlServerInstallerBasePath | Where-Object { $_.Name -match '^\d{4}' } | Get-ChildItem -Filter 'Updates' | Get-ChildItem -Filter 'SQLServer*-SP?-*.exe'

    it feels like this isn't quite right: { $_.Name -match '^\d{4}' } | Get-ChildItem -Filter 'Updates' | Get-ChildItem -Filter 'SQLServer*-SP?-*.exe'
    Is there any way to make the sql version code (ie, sql 2016,  sql 2017) more dynamic so the file doesn't have to be updated with each new major release?  I'm guessing not.

    Thanks,
    Jeff

     

    1+
    • avatar
      Jeff 3 weeks ago

      nevermind the "$servicePacks = Get-ChildItem".  I got past that part,

      0
  5. avatar
    Kez 3 weeks ago

    I cannot get past this

    Cannot process argument transformation on parameter 'Version'. Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.Version"

    0
  6. avatar
    Kez 3 weeks ago

    Its because it is trying to pass multiple rows... I changed it to the below and I get further. I don't think this is the most suitable solution.

    $currentVersion = ConvertTo-VersionObject -Version $sqlInstance.Version[0].ToString()

    1+
    • avatar
      Jeff 3 weeks ago

      You shouldn't need to make a change for
      $currentVersion = ConvertTo-VersionObject -Version $sqlInstance.Version

      Check and make sure your csv has the correct versions.
      If this is for 2016 you will need to look thru the script (look for 2012 or 2014) and add similar entries for 2016.  Here are a few tweaks needed:

      elseif ($version.Split('.')[0] -eq '13')
      {
      $verKey = $reg.OpenSubKey('SOFTWARE\\Microsoft\\Microsoft SQL Server\\130\\SQLServer2016\\CurrentVersion')
      $version = $verKey.GetValue('Version')
      }

      Caption = {
      switch -regex ($version)
      {
      "^13" { "SQL Server 2016"; break }
      "^12" { "SQL Server 2014"; break }
      "^11" { "SQL Server 2012"; break }
      "^10\.5" { "SQL Server 2008 R2"; break }
      "^10" { "SQL Server 2008"; break }
      "^9" { "SQL Server 2005"; break }
      "^8" { "SQL Server 2000"; break }
      default { "Unknown"; }
      }

      [Parameter()]
      [ValidateNotNullOrEmpty()]
      [ValidateSet('2008R2', '2012', '2014', '2016')]
      [string]$SqlServerVersion,

      1+
  7. Profile gravatar of Adam Bertram Author
    Adam Bertram 3 weeks ago

    I'm going to rework it to be able to add newer versions more easily.

    1+
  8. avatar
    Kez 2 weeks ago

    Thanks Jeff, I will give that a try.  But to clarify I tested this against a standard 2012 and 2014.  Just trying to get the basics working before I add the extra version info.

    0
  9. avatar
    Kez 2 weeks ago

    Hi
    I cannot figure out what is going on.... if I don't explicitly return 1 record it fails
    $currentVersion = ConvertTo-VersionObject -Version $sqlInstance.Version[0]

    I tried this script on 3 different editions and 2 different versions and I can confirm I am getting multiple records on the first 2.... below is what it returns (the first 2 attempts return 2 records... the last just the one record... which will then work. They all have similar components installed as well.

    @{ComputerName=xxxxxx; InstanceType=Database Engine; InstanceName=MSSQLSERVER; InstanceID=MSSQL11.MSSQLSERVER; InstanceDir=E:\MSSQL; Edition=Enterprise Edition; Version=11.2.5058.0; Caption=SQL S
    erver 2012; IsCluster=False; IsClusterNode=False; ClusterName=; ClusterNodes=System.Object[]; FullName=xxxxxx}
    @{ComputerName=xxxxxx; InstanceType=Analysis Services; InstanceName=MSSQLSERVER; InstanceID=MSAS11.MSSQLSERVER; InstanceDir=E:\MSSQL; Edition=Enterprise Edition; Version=11.2.5058.0; Caption=SQL
    Server 2012; IsCluster=False; IsClusterNode=False; ClusterName=; ClusterNodes=System.Object[]; FullName=xxxxx}

    @{ComputerName=yyyyyy; InstanceType=Database Engine; InstanceName=yinstance; InstanceID=MSSQL12.yinstance; InstanceDir=d:\Program Files\Microsoft SQL Server; Edition=Developer Edition; Ve
    rsion=12.2.5522.0; Caption=Unknown; IsCluster=False; IsClusterNode=False; ClusterName=; ClusterNodes=System.Object[]; FullName=yyyyyy\yinstance}
    @{ComputerName=yyyyyy; InstanceType=Reporting Services; InstanceName=yinstance; InstanceID=MSRS12.yinstance; InstanceDir=D:\Program Files\Microsoft SQL Server; Edition=Developer Edition;
    Version=12.2.5522.0; Caption=Unknown; IsCluster=False; IsClusterNode=False; ClusterName=; ClusterNodes=System.Object[]; FullName=yyyyyy\yinstance}

    @{ComputerName=zzzzz; InstanceType=Database Engine; InstanceName=zinstance; InstanceID=MSSQL12.zinstance; InstanceDir=d:\Program Files\Microsoft SQL Server; Edition=Standard Edition; Ver
    sion=12.2.5522.0; Caption=Unknown; IsCluster=False; IsClusterNode=False; ClusterName=; ClusterNodes=System.Object[]; FullName=zzzzz\zinstance}

    Thanks
    Kez

    0
  10. Profile gravatar of Adam Bertram Author
    Adam Bertram 2 weeks ago

    Can you file an issue on the GitHub repository?

    0
  11. avatar
    Neeraj 2 weeks ago

    Hi Adam,

    Thanks for wonderful post. We would want to have more control on working with patched and update the SQL with desired patch. We are saving it in one location and then executing the same from remote machine but this is giving error somehow. Error thrown while executing an exe file for sQL server patch on remote computer using invoke-command. The command runs without any error but do not start the patching process.

    $Servicepack = "Location of service pack"

    $PatchCMD = "$Servicepack /action=patch /InstanceName=$Instance /IAcceptSQLServerLicenseTerms"
    $Server = $GetPatchFile.servernames
    invoke-command -ComputerName $Server scriptblock{$PatchCMD}

    Please help here.

    Thanks,
    Neeraj

    0
  12. avatar
    Staffan Olofsson 1 week ago

    Hi, great script. My first problem is to download all SP's and CU's and rename them correctly... Do you have any script for that ?

    /Staffan

    0
    • Profile gravatar of Adam Bertram Author
      Adam Bertram 7 days ago

      Unfortunately, no. I looked for a good way to automate that but I chose not to because I would have had to figure out all of the URLs and they could potentially change. There's no real one place where all of them are stored. I had to download them manually at first.

      - Adam Bertram
      Adam, the Automator Blog

      0

Leave a reply

Your email address will not be published. Required fields are marked *

*

CONTACT US

Please ask IT administration questions in the forum. Any other messages are welcome.

Sending
© 4sysops 2006 - 2017

Log in with your credentials

or    

Forgot your details?

Create Account