Here’s one big advantage to moving to Microsoft Azure SQL Database—you never have to patch. That’s right. The various SKUs available in Azure’s Platform-as-a-Service (PaaS) offering are automatically always kept up-to-date with the latest updates. In fact, with Microsoft’s “cloud first” product development strategy, new code is deployed to Azure first and then made available to on-premise versions of SQL Server after. And, if you want to live on the bleeding edge of technology and act as a “canary in the coal mine,” you can work with your Microsoft liaison to run your workload in its specially designated “canary” data center, where you can test out not-yet-released technologies.
That’s all well and good, but most of the SQL Server installed license base is still on-premise. If you’re in this category, then it is essential that you keep up with patching and security fixes regularly. The information in this article is the sort that experienced SQL Server DBAs keep close at hand. So, let’s take a few minutes to walk through the details of how to do that.
What Patch Do You Need?
The first thing you need to ascertain is the version of SQL Server you are currently running and the most current update to your version. To check the version number for a running SQL Server database, including the sequence number for service packs (SPs), cumulative updates (CUs), and patches, simply right-click the SQL Server instance name in SSMS, select Properties, then read the value in the Version field. If you prefer T-SQL, issue the command SELECT @@version on any version of SQL Server or SELECT SERVERPROPERTY (‘ProductVersion’) on SQL Server 2012 and greater.
If SQL Server is not running, examine the sqlservr.exe file in Windows Explorer with a right-click, then select Details and read the value in the Product Version field. Alternatively, use a text editor to read the SQL Server errorlog file from the last reboot and read the value for the ProductVersion field on the first line of the file. In my case, where I have SQL Server 2017 Developer Edition installed, the errorlog shows “Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64).” Note that the Microsoft Knowledge Base article referenced, KB4505224, points to the latest security fix I have installed.
When assessing the full value of the product version number, 14.0.2027.2 in my case, you’ll see the details in the format of major.minor.build.revision. (It is the same value and format no matter what method you use to retrieve the version information) At this point, you need to correlate your product version number with Microsoft’s marketing version number. For example, SQL Server 2017 is my installation’s marketing number.
There are quite a few websites that correlate this information for you and provide URLs to download the latest bits. For example, Microsoft MVP Aaron Bertrand keeps a detailed blog on all supported versions of SQL Server at https://sqlperformance.com/latest-builds, including lots of supporting details such as what’s new in the release and much more. If you like brevity, then consultant Brent Ozar also keeps an outstanding summary at https://sqlserverupdates.com. Another really good site for this info is https://sqlserverbuilds.blogspot.com. When I check these links, I see the latest release of SQL Server 2017 is CU20 (14.0.3294.2) indicating that I’m many CUs behind the times.
(A side note: It’s important to keep in mind that I’m only talking about the SQL Server relational engine. There are slightly different requirements and upgrade paths for Analysis Services, Integration Services, Reporting Services, in-database analytics [machine learning], or the tools SQL Server Management Studio [SSMS] and Azure Data Studio. These all have different release cadences and different files that you’ll need to download. Also remember that a bit of special handling is required for Availability Groups and Failover Clusters.)
It’s Update Time!
When you’ve figured out where you are and where you need to go, now you need to download the latest code by clicking on the link in one of the blogs I mentioned earlier. For example, when I click the SQL Server 2017 CU20 link on each of the three blogs I referenced earlier, they all take you to https://support.microsoft.com/en-us/help/4541283/cumulative-update-20-for-sql-server-2017, where you will find prerequisites, the download link, and instructions to manually install the update.
Note that SPs must be applied in order, meaning that you can’t skip directly to SP3 if you haven’t previously downloaded SP1 and then SP2. However you only need to apply the latest CU. Also note that everything described so far is for SQL Server on the Windows OS. If you need patches for SQL Server on Linux, go to https://support.microsoft.com/en-us/help/4541283/cumulative-update-20-for-sql-server-2017, And, don’t forget that applying patches can sometimes go wrong. Always have a recent backup available just in case things don’t work out or the patch you applied creates a regression in your application code.
What About Automation?
Clearly, patching a lot of SQL Servers using the steps described above can take a long time if you have a lot of SQL Server instances. To automate this process, take a look at the PowerShell toolkit at https://dbatools.io and specifically at the Update-DbaInstance cmdlet. This cmdlet installs SPs and CUs to as many remote or local servers as you specify.