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.
The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.
NEW SQL Clone - version 1 now available!
Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test and diagnose issues faster. Try it free.
Identifying a need for better collaboration between teams is only the first step to introducing DevOps workflows into your organization. You also need to define a common DevOps goal, a strategy to achieve it, and then to break down this strategy into series of manageable tasks for each team. Bob Walker describes how Farm Services of America started to "do DevOps". More »
Master the fundamentals of Transact-SQL—and develop your own code for querying and modifying data in Microsoft SQL Server 2016. Led by a SQL Server expert, you’ll learn the concepts behind T-SQL querying and programming, and then apply your knowledge with exercises in each chapter. Get your copy from Amazon today.
Yesterday's Question of the Day
(by Steve Jones):
I am worried that other DBAs in my organization are abusing trace flags. I want to determine if there are any session or global trace flags set. How can I do this?
Answer: DBCC TRACESTATUS with no parameters shows all trace flags
DBCC TRACESTATUS with no parameters will show you all trace flags set on your system, either global or session level.
How to modify Rank() function
I have troubles with this query:
SUM(repayment) as suma,
DATEADD(S, -1, DATEADD(mm,DATEDIFF (m,0,date+1,0)) AS obd
RANK() OVER(PARTITION BY id,suma, ORDER BY DATEADD(S,...
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.