Staying on top of updates is important to stay secure and have SQL Server run as efficiently as possible. Sometimes you may need to apply an update that isn’t the most recent update available. Maybe the latest update hasn’t been approved by your organization or you’re wanting to hold off due to how your environment would handle the changes. Let’s run through an example of updating in this scenario.
Opening SSMS on one of my local test environments and running the script below, we see that we’re currently at SQL Server 2019 CU5:
As of this writing, the latest cumulative update for SQL Server 2019 is CU8. In our scenario, we do not want to apply CU8 quite yet and want to apply CU6 instead.
To find and download CU6, we can navigate to https://www.catalog.update.microsoft.com/Home.aspx and search for SQL Server 2019 updates.
We can click on the download button to the right of the CU6 line to bring up a download dialog and click the exe link to download the update.
Before we apply any updates, we want to read some documentation. Yes, it might be boring but it is important. We can do a Google search for the update KB4563110 to find the Microsoft article. It is worth it to read through the list of improvements and fixes but make sure you are reading the notes towards the bottom. Here are the notes for CU6:
In some cases, this area will contain notes on known issues with the release and how to handle these issues if you run into them. If we feel good about applying the update, we can double click the downloaded exe to begin.
The first window we see is the License Terms window. Once the checkbox is ticked for accepting license terms, we can click Next.
The Select Features window let’s us select instances and features to apply. Make any desired selections and click Next.
Here we have a window about consenting to install Microsoft R Open. We must click the Accept button and then the Next button to proceed.
Any files or programs running that would interfere are checked and listed. In this case, no issues were found so we can click Next to continue on.
We can review the summary of updates to be applied and click the Update button to begin.
Once the updates have been applied, we’ll see confirmation that each step succeeded or failed.
We can close that window, run our script to check version, and confirm that we are updated:
Another option for installing cumulative updates that is beneficial when deploying to multiple servers is PowerShell with dbatools. Below, I was deploying CU8 only to my local machine; however, multiple servers can be listed in the $ServerList variable to update servers more quickly. I might go into more detail with this at some point in the future but for now I want to at least include it as an option:
#Servers to Update
$ServerList = "localhost"
#Credential for Update
$Credential = Get-Credential
#Version to Update
$SQLVersion = '2019CU8'
$SQLUpdatePath = "C:\Users\Chad\Desktop\SQLCU8\"
Update-DbaInstance -ComputerName $ServerList -Path $SQLUpdatePath -Credential $Credential -Version $SQLVersion -Restart
Invoke-Sqlcmd -Query "SELECT @@VERSION;" -ServerInstance "localhost"
Keep in mind that service packs will no longer be available with SQL Server 2017 and greater. Instead, only cumulative updates and critical updates will be released. These cumulative updates will be frequent at first and then become less frequent as the lifecycle progresses.