With some of the CU and SP misses that happen, what’s a good schedule for patching a SQL Server? What are some things to do before applying a CU to a SQL Server? How can we verify our SP or CU didn’t break anything? And what CU should I apply? The latest? The even number ones on the 2nd Wednesday of odd-numbered months? Read on in this post in the “What we wish you knew about SQL Server” series for some of the tips we wish you knew.
There was a time awhile back when SQL Server patching was a scary prospect. There was really no great uninstall approach; there were some rather infamous oopses. Things got better, uninstall was added, service packs and CUs were safe, and had a good run. Then. They got a little worse again more recently. I have no blame or ill-will towards Microsoft here. When you have a widely adopted product and a schedule to get CUs out – even with automation and all that telemetry data – there are test cases that slip through the cracks. No, “I’m not in the DON’T PATCH YOUR SQL SERVERS UNLESS YOU REALLY REALLY NEED TO!” Camp. No one really can be in that camp anymore. Compliance. Security. Lawyers. Insurance companies. They all conspire against installing SQL Server and mostly leaving it alone.
That’s the point of this post. A few things that I’m stealing from our practices in our SQL Server consultancy and some of the approaches we take to stay current, but not “dangerously” current with some of the pullbacks lately.
Why Should I Patch my SQL Server?
Because.
No, seriously, Because! That’s literally one of the reasons. Because your auditors will demand it, and if you have a breach or issue, even if it has nothing to do with your patch compliance – the lawyers will ask about it. Because your contracts may demand it. We need to be on top of security updates.
Also – performance! I’ve lost track of the number of times someone had an assertion error or various non-yielding scheduler stack dumps that a CU or SP fixed. Quite often, those were for folks who were a bit too far back on their patching (I’m talking RTM!!!).
Usually, and Generally, these patches are “safe.” As a regular course of action, they don’t take functionality away or add features (there are notable exceptions to the whole adding features thing), but they are enhancements and fixes. But don’t go out and grab the patch the day it’s released and apply it – see below.
How Do I Pick Which SQL Server CU or Service Pack to Apply? (When do you update?)
Well let’s get one thing out of the way first – since SQL Server 2017 – there are no more SQL Server Service packs. Only CUs. So if you are on SQL Server 2017 or later the “which service pack should I go with?” question is easier at least.
You could go with the latest… But if you did that with SQL Server 2019 CUs, I believe you would have had to go and fix what you did two times. Most recently, CU#7 was pulled and replaced nearly immediately with CU#8. With Microsoft plastering a “Please uninstall this if you’ve installed it” warning everywhere. There have been other infamous examples of “oopses” here. SQL Server 2016 SP2 CU#9 was released, then quickly replaced. SQL Server 2016 SP2 CU3. A release around the SQL Server 2016 (or maybe 2012? It’s been a while) era caused index corruption in certain situations when you rebuilt an index online!
Our strategy at Straight Path is pretty easy:
“Let someone else be in the phase 1 trials for SQL Server patches“
Microsoft’s engineers are amazing. Their test scripts and automation must be impressive. Yet, things go bump. So we like to wait for someone else to do a patch. Our rough process is something like this:
- Wait until the current patch is 2-3 months old or a new one is released to deploy the CU you are looking for. (So if a CU is a couple of months old and in the wild with no reported issues and no rapid release of a CU to fix that CU – we’ll deploy it)
- Consider it more urgent if there is an urgent security fix (a lot of times, these come out differently than CUs historically as a security GDR/etc.)
- Keep up on the patch info – each CU or SP has a KB article, which details the issues. Read them. Are you bit by (and bit hard), or are you susceptible to a production issue fixed in a CU that just got released? Do some thought process. Is the risk of staying on your current patch level worse for prod than potentially introducing odd behavior? Then go through and test and deploy the CU.
Where Can I Find Information about the SQL Server Patches?
Microsoft releases a KB article about each patch. Just searching in your favorite search engine for “SQL Server 20nn latest CU” is a good start to get to those articles. Here’s an example of the KB article for SQL Server 2019 CU8.
Another great resource I love to hit is the Sentry One list of SQL Server builds. They list each build, along with notes about why to or not to apply it. And they link back to the KB article. This is such a valuable and useful resource. Bookmark it and use it often.
What Should I Do Before Updating my SQL Servers?
PRAY! No, it’s not that, totally joking. I say the same thing before you do anything “scary” or “borderline scary” to a SQL Server. Remember my blog post from 11 years ago? DBAs should be Paranoid Control Freaks. We ought to be cautious and smart with any changes. A few suggestions:
- Review the notes – What are the risks? What’s fixed in the CU? Should you make it a higher priority because of something you could bump into or are being hurt by?
- TEST IT! Yes, it’s “only” a CU or SP – but if you have a pre-production environment to stage all production changes in, you should test it! Let it live there for a couple of weeks, run your smoke tests or test scenarios, do some failovers, etc.
- Backup your environment before patching. If you don’t have runbooks – make this an excuse to document job start/stop times. Confirm backups are good.
- Review the window you want to do this in – will you miss agent job executions from the restart of services? Do they matter? Should you re-run?
- Notify users
- Patch. If in an FCI or AG -consider patching the passive node first, failing to it, and seeing what happens. Then patch the formerly active node and failback (if that’s your policy, or stay alive on the secondary)
How can I Verify The SQL Server After I Apply a CU or SP?
- Did the installer error? What’s the error?
- What does the SQL Server error log look like?
- What about the windows even log?
- Are all of the services started? (There are quite a few folks who had the SQL Server Agent service set to manual, did a patch, and then did a patch and went to bed… OOPS)
- Did any missed jobs need to be re-run?
- Do the apps work?
- What does select @@version report for the DB engine patch? Does it match where you should be?
What If I Need to Roll Back?
That’s a lot for a blog post. The first step is the same as finding corruption. Breathe. Relax. Breathe a few more times. The last thing we need is a nervous troubleshooter pulling out the “troubleshooting shotgun” to start blowing holes in everything.
Follow the logs. The service pack/CU will have a log to look at in the setup bootstrap folder. Event viewer and SQL Server log are there. Start reviewing the logs for signs of what happened.
See if you can resolve this “in place” by fixing something broken by the SP/CU. If not, the uninstall feature does work pretty well with CUs and SPs these days.
As a last resort – you took a snapshot or at least some backups above and you know your systems configurations because you grabbed some of that data. It stinks – but you haven’t lost data.
This is why it’s great to go slow through the whole process. Don’t be one of the first to apply – there are enough folks who don’t read this blog that the 2.75 of you who do can wait a couple of months for everyone else to try first.
Bottom Line
You need to do this. Don’t do it the same day the patch comes out – especially with the misses that have happened lately. Be prepared. Be careful. And always be a data advocate for your organization. That’s some of what we wish you knew about patching! Now take that inventory, see how far off you are, and start planning your patching window! Happy (careful) patching!
