March 4, 2020 at 11:16 am
Hi,
I'm considering the numerous updates available for SQL Server and how best to approach their installation. I have 'inherited' 100+ SQL instances covering Dev, QA, UAT and Production. These are used for anything from internal testing to hosting business critical 3rd party DBs. I've also been told that they have not been receiving regular SQL updates (OS updates are being applied via SCCM) and so may be out of date. I can run an inventory as to what each instance is patched to but was wondering on the options of 'auto updating' them (allowing SCCM to download, deploy and install as the updates become available).
As far as I can tell the following update types exist (I've also included my thoughts on auto update):
Security update (e.g. CVE-2020-0618) Auto applied but from the bottom up (Dev > QA > UAT > Prod)
Hot Fixes Not auto applied as too specific (unless solve explicit problem)
Cumulative update (e.g. CU12) Not auto applied as requires testing and 3rd party support. Manual installation.
Service Pack (e.g. SP2) Not auto applied as requires significant testing and 3rd party support. Manual installation.
Have I missed any?
Do my thoughts on their auto update status seem appropriate?
I can see the benefit of applying CU and SP updates shortly after they become available, but only when 3rd parties support them. Because of their wider impact I feel that they need careful analysis and testing prior to being applied and therefore do not work well with an automated approach.
Thanks,
Mike
March 4, 2020 at 2:47 pm
Are there multiple versions of SQL Server in your environment? Or are they on a particular version? The reason I ask is because some of the recent CU's for SQL 2019 have been problematic (like breaking SQL Agent), so I would be careful about automatically applying the most recent patches for SQL Server.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 4, 2020 at 2:59 pm
I do indeed have multiple versions as some of these are dictated by my 3rd party vendors. There is a risk with any version though as not all CUs seem to work out of the box (forums are littered with failure cases), which is part of my concern about auto updating them. Just trying to gauge opinion as to whether I've missed anything or my views are out of date.
March 4, 2020 at 3:38 pm
Yeah, as much of a pain as it will be, I would manually apply SP's and CU's to SQL Server, even if it is a large environment.
Which makes me wonder (I know nothing about SCCM, other than it's a software distribution service) if one could stage the specific patches and deploy them to specific servers using SCCM? For example, the more stable versions of SQL Server (2012, 2014, maybe even 2016) could have the most recent patch deployed using SCCM to those specific instances. Not even sure if this is possible or it will, in the long run, save any time, but it is a thought.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 4, 2020 at 3:42 pm
I do not know who maintains this, but this is the most up to date list of patches for every version:
https://sqlserverbuilds.blogspot.com/
Automatic patching with SCCM is problematic. If you try to patch SQL along with the rest of the patches, it fails more often than not.
We have created a separate set of collections for SQL servers in SCCM, broken down by environment and version. In each, I pick the patches to deploy, and schedule them as appropriate. So far it works. We leave them running in DEV and QA for at least a month before moving them to production.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 4, 2020 at 4:16 pm
In my research I've seen a solution to what you describe posted by Adam Bertram. Essentially he verifies a CU or SP and drops it into a predefined folder structure and allows a scheduled task to push only those preapproved updates out. It is however PowerShell rather than SCCM. For my line of questioning at the moment this isn't relevant but it might help you.
March 4, 2020 at 4:18 pm
Michael,
I think that you're agreeing with me that the automatic download, deployment and installation is not a good idea for SQL updates. I like the sound of using SCCM though to distribute and deploy authorised updates.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply