Introduction
As I look at SQL Server instances “in the wild”, one persistent and common finding over the years is organizations running extremely old builds of SQL Server. This means running a very early (and old) build of whatever major version of SQL Server that they are using. This is a bad practice for a number of reasons. So, the question here is, why should you patch SQL Server?
Here is the executive summary:
- You will have fewer issues with SQL Server
- Your applications will be able to take advantage of new features in your current version
- You will get better support from Microsoft
- Your organization will look more competent
- You will look more competent as a DBA
What Is SQL Server Patching?
SQL Server patching is the act of running an installation program from Microsoft to install an updated build of SQL Server on an existing SQL Server instance. This could be in the form of a Service Pack (SP), a Cumulative Update (CU), or a General Distribution Release (GDR).
SQL Server 2017 and newer do not have Service Packs. Instead, they only have CUs and GDRs. There are more details about this change here:
The way that SQL Server patches are developed and released is called the servicing model, which Microsoft has changed multiple times over the years.
SQL Server Servicing
A major SQL Server version is in Mainstream Support for five years after its initial release. During Mainstream Support, the product will get servicing releases in the form of CUs and GDRs. CUs are released every month for the first 12 months, and every two months for the remaining four years of Mainstream Support. CUs contain security and functional bug fixes and feature enhancements. In contrast, GDRs usually only contain security updates, and they are only released as needed.
After five years, the product falls out of Mainstream Support and into Extended Support for the next five years. During Extended Support, there will only be GDR releases for security issues, with no functional bug fixes or feature enhancements (no CUs).
After ten years, the product falls out of Extended Support. This generally means there are no more servicing releases at all. Microsoft sometimes breaks this rule by releasing security updates for SQL Server versions that are out of Extended Support.
Here is how you find the latest GDR or CU update for your version of SQL Server:
You can simply run SELECT @@VERSION; on an instance to get your major version and build information. The SERVERPROPERTY function also has methods to return the major version and build numbers that are easier to work with programmatically.
What Servicing Train Are You On?
As we have seen, Microsoft uses GDRs and CUs to update SQL Server. GDRs usually only have security updates, while CUs have security updates, functional bug fixes, and feature enhancements. Some of these feature enhancements in CUs are very useful!
There are two “Servicing Trains” that you can choose from. The “GDR Train” is when you only install GDR releases. Some organizations stay on the GDR train on purpose, since this reduces their patching frequency, and they also think it reduces their risk.
The “CU Train” is when you install any CU release. Once you are on the CU Train, you are stuck there. This just means that you will have to keep installing CUs going forward rather than just GDRs. Remember, CUs are actually cumulative, and they also include all previously released GDR security updates.
I strongly prefer being on the CU train, so that I get those functional bug fixes and feature enhancements. My personal experience (and historical Microsoft CSS data) shows that you also have fewer SQL Server issues when you are on the CU train and you make an effort to stay current.
Why Do You Have an Old Build?
When a new major version of SQL Server first becomes generally available (GA), that particular “build” of SQL Server is called the Release to Manufacturing (RTM) build. This is because of the historical practice of creating a “golden master” magnetic floppy disk or optical disk that was sent to a factory for mass duplication and retail packaging. That is not commonly done anymore, but the RTM term survives.
For example, the RTM build of SQL Server 2022 is 16.0.1000.6, which was released on November 16th, 2022. This RTM build is what most people will have when they initially install SQL Server 2022. Unless you (or your system) have patched your SQL Server 2022 instance, you may still be on that RTM build many years later.
In order to avoid this, you need to decide how you want to patch your SQL Server instances, and then you actually have to do it. This may be easier said than done. This is not because it is actually hard to do, but because of the various roadblocks that make it more involved than simply running an installation program. BTW, Brent Ozar has a good post on How to Patch SQL Server.
Here are the official Microsoft KB articles with the public build lists for the most recent major versions of SQL Server:
- SQL Server 2022 Build Versions
- SQL Server 2019 Build Versions
- SQL Server 2017 Build Versions
- SQL Server 2016 Build Versions
As you do your research across your data estate, you will probably find some “weird” build numbers that are not on the CU build list for your major version of SQL Server. This usually happens when Microsoft Update pulls down and installs a GDR security update on top of whatever CU build you previously had installed. These GDR updates usually show up in Microsoft “Patch Tuesday” releases.
This is bad for two reasons. First, I don’t think you should be letting Microsoft Update install SQL Server patches on its schedule. You should be doing it on your schedule. Second, if this is happening, it is a sign that your organization has room for improvement on your patching implementation.
Why Should You Patch SQL Server?
On March 24, 2016, Microsoft published an important change to their official guidance about Cumulative Updates.
The gist of this change was that Microsoft got rid of some old language about installing CUs and replaced it with this:
“We now recommend ongoing, proactive installation of CU’s as they become available“
Announcing updates to the SQL Server Incremental Servicing Model (ISM) | Microsoft Docs
The previous cautionary CU language was often enough to discourage organizations from patching their SQL Server instances. As a result, many SQL Server instances were seldom if ever patched. Microsoft CSS data shows that a significant percentage of customer issues have been previously addressed in a released CU, that has not been installed by the customer.
Microsoft Support
If you ever open a support case with Microsoft CSS about a SQL Server problem, one of the first questions they will ask you is what version and build you are running. If you are on an extremely old build, Microsoft will be less able (and willing) to help you until you install a newer build.
Being on a very old, possibly unsupported build makes you look bad right from the start. Perhaps you are having problems with Query Store, and you are running the RTM build of SQL Server 2016 (Build 13.0.1601.5) from June 1, 2016. There have been many Query Store fixes and improvements since the RTM build. There is a pretty good chance that the problem that you are seeing was fixed in a later build that you have not installed.
On the other hand, if you are on a fairly recent build that is still supported, you are in a stronger position to start with. This not only applies to Microsoft, but in general.
But maybe you don’t care what Microsoft thinks about patching, and you never use Microsoft CSS. If that is the case, then there are even better reasons to patch SQL Server and to be on the CU train.
Bug Fixes and Functional Improvements
SQL Server CUs typically have anywhere from about 20 to 50 fixes and improvements in each CU. If you are on a very old build, you may be missing literally hundreds of fixes. Some of these may be very important.
Microsoft has frequently backported functional bug fixes and new features from newer versions of SQL Server to older versions of SQL Server. Things like indirect checkpoints, Query Store improvements, and persisting statistics sampling rates come immediately to mind.
It is a very good idea to get in the habit of reading the release notes and fix list for every SQL Server CU when it is released. I usually write a blog post when new CUs are released, such as these ones:
Why People Don’t Patch SQL Server
One reason is simple ignorance. Many accidental DBAs and organizations that don’t have any experienced SQL Server DBAs don’t even know that SQL Server has a servicing mechanism and that it needs to be patched. They don’t know what they don’t know.
Another common reason is that they don’t want to (or can’t) take any outages in order to patch their production SQL Server instances. Patching a SQL Server instance requires a restart of the SQL Server Service, and it may require a restart of the operating system.
With a standalone instance, this could involve a relatively lengthy outage, especially on a bare-metal instance. You can minimize the outage if you have an appropriate high availability (HA) architecture in place. You can use a rolling upgrade methodology with your HA infrastructure to do this.
Yet another reason is risk aversion. Anytime you make any changes to a computer system, there is a chance that you will break something. Some people (and organizations) simply don’t want to take that risk, so they rarely if ever patch their SQL Server instances. I think this is a mistake, but I can understand the reasoning.
A final reason is regulatory or legal. Perhaps you are a DBA at a hospital, and you have medical applications that are only certified with specific builds of SQL Server. You are simply not allowed to patch some of your SQL Server instances. This is also a common problem with some ISVs, which will threaten to withhold support if you install a new, uncertified build of SQL Server. Better ISVs don’t withhold application support just because a customer installed a CU.
What other reasons have you run into?
How to Patch SQL Server 2022
This video shows how to patch SQL Server 2022 with a Cumulative Update.
How to Patch SQL Server 2019
This video shows how to patch SQL Server 2019 with a Cumulative Update.
Final Words
I am not suggesting that you immediately patch all of your instances on the day that a new CU is released. You need to have a detailed plan and schedule for how you go about patching and testing all of your SQL Server environments in order to reduce your risk.
Once you have a procedure and detailed plan in place, you need to start implementing it, instance by instance. The first few iterations may be a little rough. Your plan and detailed steps should improve over time, until it becomes pretty routine.
I think this is a far better strategy than never patching SQL Server. In addition, I also think it is better to be on the CU train rather than the GDR train.
What do you think? I would love to hear your thoughts on this in the comments.
If you have any questions about this post, please ask me here in the comments or on Twitter. I am pretty active on Twitter as GlennAlanBerry. Thanks for reading!
Glenn. The other issue I’ve experienced with patching is getting operating system changes into the process. I appreciate that it complicates things but do you have any thoughts on that and how you might make it a more integrated process?
Glenn, I am taking a 6 month DBA training class. We are covering Patching in our class tonight. Our Prof said to read your article before class. Your article was very informative and easy to understand. I think it will make I will be able to grasp tonight’s class on Patching much easier. Richard