Change Your SQL Server Oil

One of the things I learned early in life was that some regular car maintenance goes a long way. I didn’t need to be fanatical about checks or ensure that I did the every recommended maintenance item at every interval, but one thing I’ve always done is change my oil regularly. I could let brake fluid, or coolant, or even tires go a touch longer than I’d like if funds were tight, but changing the oil was critical. I’ve lived by that with most of my vehicles over the years, and they’ve tended to last a long time.
I read a post from Glenn Berry that reminded me of this recently. Glenn talks about the need to perform maintenance on your SQL Server instances in the form of regular patches. I think that’s a good analogy, but certainly not complete. On top of these oil changes, you should think about more regular index maintenance, checking security and backups, monitoring space and more. Those are topics for another day, but no less important than patching.
So how often do you change your oil, or patch your database? For cars, we used to change every 3,000 miles or 3 months (I typically used the former). Over the years some cars have gone to 5,000 miles, though with more modern vehicles and better oil, many cars have gone to 7,500-10,000 mile intervals. My BMW says 15,000 miles, though I usually get nervous around 10k. No matter what your interval, it’s good to have a value and stick to it. That prevents confusion, forgetfulness, or other human errors. Choose a distance or time and maintain your vehicle regularly.
For your SQL Server, I’d typically do the same thing. While Glenn likes to ensure he’s got the latest patches for his customers, I’m not completely sold on applying patches as soon as they’re released. There is a risk of things breaking, or even just service disruption, so I prefer to limit the issues. I prefer to let CU patches come out for a few months and see if any major issues are reported. I also likely would apply patches only 3-4 times a year, rather than the 6 times that the patches are released. In the absence of issues, I prefer stability. I do recognize that I’m taking a chance that I could run into an issue that requires patching to get support, or a security patch is released and needs to be installed quickly. In those situations, I’d need to allocate some testing time and deal with the potential issues at that time.
Along those same lines, I don’t like updating all my instances at once. Certainly not all of a same version. If I have 20 SQL Server 2016 instances, I don’t necessarily want to patch them all today. I might patch 2-3 to ensure that things work smoothly. If that’s the case, then I’d schedule the rest next week. My experience with patching is that we usually need at least two groups, and sometimes three. I have an early and normal patch group, and sometimes a delayed one where the patch timing is problematic. However, I can’t get too far out of date as it’s easy to forget about patching older servers, and that can cause plenty of other issues in the future. In general, I prefer just two patch groups.
As Glenn mentions, there are other benefits to regularly patching. You touch these servers and have the chance to ensure that your DR/HA plans are up to date. You think about potential issues, and in general, don’t become complacent with regards to the health of your systems. I certainly think you should apply SPs, and at least a few CUs a year to keep semi-current. And, of course, since you want to treat your instances like cattle, not pets, you also need to ensure you patch test and development systems to match their production counterparts.
Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial. Bookmark the permalink.