Ben Davis
July 28, 2016 9:49 am
I heard Jeffrey Snover from Microsoft talk about the pets and cattle comparison at Microsoft Ignite 2015 in a session about the Windows Server 2016 Nano Server installation option. That’s the first I heard of this metaphor.
Loading...
Reply
Automating Patching? – Curated SQL
July 29, 2016 5:20 am
[…] Kendra Little takes on the question of whether patching should be automated on SQL Server instances: […]
Loading...
Reply
Kevin Parks
August 1, 2016 12:08 pm
great script for windows updates https://community.spiceworks.com/scripts/show/82-windows-update-agent-force-script-email-results-version-2-7 have a scheduled job on the server call the script from a file server. Set it to like every Saturday at 8PM or whatever day/time you want to run. When you don’t want it to run rename the file, the job will fail. when you want it to run, rename it to the correct name. The script give you loads of information about your updates and provide e-mail with logs.
Loading...
Reply
Mark Dreyband
August 3, 2016 5:30 am
Hi, Kendra!
It will be very interesting to see your post about SQL Server (not Windows OS) patching, like installing Service packs and hotfixes. I think there are a lot of underwater rocks you may point.
P.S. And thank you for your community input! 🙂
Loading...
Reply
Kendra Little
August 3, 2016 7:19 am
Hey Mark,
Loading...
Reply
John G Hohengarten
August 4, 2016 7:30 am
Hi Kendra,
You mention a couple times about “database offline?” type of verbiage.
Is it a best practice when patching Windows servers which have SQL Server to put databases offline prior to shutting down the SQL Service and launching the updates? It seems implied by the way you talk about it. Or am I misunderstanding that it was a problem that the databases were offline, as opposed to wanting them to be offline?
Disclaimer: I’m not a DBA, I’m a BI Developer with no responsibility for updating the servers in my org.
Loading...
Reply
Kendra Little
August 4, 2016 10:18 am
Great question! I don’t set databases offline for patching. In the cases in question, the databases were in the process of being archived. They were legacy databases, and they were believed to not be in use anymore, but the system was complex enough that we wouldn’t have been surprised if a monthly or quarterly process queried them which nobody had remembered (or perhaps nobody realized was still running).
For this reason, the removal process was to take a final backup, set the database offline (so it could quickly be brought back if needed), and then eventually verify that we still have the backup, the backup still restores, and drop the database.
The obvious problem that would happen is that sometimes the drop process would be delayed or forgotten. Usually people were pretty good about documenting in the runbook which databases were purposefully offline, but you know how that goes 🙂
Loading...
Reply
Cody
August 12, 2016 3:14 am
In my experience there’s a significant point of contention between how management thinks every server is or should be cattle, and a DBA experience on the ground knowing that every server is more like someone’s fussy pet made unique with the scars of 5-10 years of patches, OS upgrades, SQL upgrades, tweaks, applications, and who knows what.
I apply about 100 SQL SPs/CUs per month. They can fail in twenty ways that I’ve documented so far and that’s just the predictable stuff. So when it comes to scripting automation for it’s a massive project and nobody seems to “understand” why.
Of course if you have a smaller environment it’s probably not a big deal, maybe you won’t hit all those edge cases and you’re lucky. I suspect most people haven’t even tried to understand the Microsoft documentation on properly patching a cluster or AG or replication member – because what they suggest is crazy, out of date (written mostly for Windows Server 2008 R2), and borderline dangerous. Are you really going to automate removing node votes in the cluster during each patch session and then reapply it all automatically afterwards? That’s a resume generating event waiting to happen.
And you have to make crazy decisions. Let’s say you’re super smart and automate querying the version from the SQL Server AND automate working out which patch files are what build and can be used. Now.. what patch are you going to apply to a SQL 2008 R2 instance? You better know there are two completely different version number streams; GDR and QFE. That’s assuming you do not jump up to the latest TLS 1.2 which overrides both but has been fraught with problems from every angle. (Is anyone even using SQL Server TLS? Judging from my chats with security professionals and searching for information on how to do it, my answer would be probably not.)
WSUS isn’t really an answer. It requires very specific settings to be used to pick up the right patches and which often aren’t used in some larger environments. SCCM uses WSUS under the covers and is better but… now you have all of those same problems plus one more trying to integrate “your” lowly DBA stuff into the system administrator’s baby and he/she is very protective of their baby!
Oh well, those are my thoughts 🙁
Loading...
Reply
Kendra Little
August 12, 2016 10:39 am
Oooo, great comment. So with the issues with WSUS/SCCM, what do you use for your patching when it comes to Cumulative Updates and Service Packs? Just curious what ends up being most efficient for you at that volume.
For other readers who are confused by the GDR and QFE comments (and it’s confusing), the SQL Sentry team has a great resource for figuring out what build you want to apply at: http://blogs.sqlsentry.com/category/sql-server-builds/
When there are hotfixes released out of band (whether Quick Fix or General Distribution), they note them and then also do some of the legwork for you to figure out which QFE’s/GDRs are in which Cumulative Updates, etc. What an acronym party 🙂
Loading...
Reply
Aaron Gonzalez
June 4, 2018 1:25 pm
Okay, this is an old thread but I found this somewhat concerning…
“So, if you’ve got any servers where jobs or applications are running as sa, well…. hope they finish up fast.”
… because Ola Hallengren’s maintenance solution creates its jobs with sa as owner.
Loading...
Reply
Michael Lewis
December 17, 2019 3:39 pm
Just sticking this here… DBA-Tools have a PowerShell module that can automate this… https://docs.dbatools.io/#Update-DbaInstance
Comments migrated from wordpressBen Davis July 28, 2016 9:49 am I heard Jeffrey Snover from Microsoft talk about the pets and cattle comparison at Microsoft Ignite 2015 in a session about the Windows Server 2016 Nano Server installation option. That’s the first I heard of this metaphor.
Loading...
Reply Automating Patching? – Curated SQL July 29, 2016 5:20 am […] Kendra Little takes on the question of whether patching should be automated on SQL Server instances: […]
Loading...
Reply Kevin Parks August 1, 2016 12:08 pm great script for windows updates https://community.spiceworks.com/scripts/show/82-windows-update-agent-force-script-email-results-version-2-7 have a scheduled job on the server call the script from a file server. Set it to like every Saturday at 8PM or whatever day/time you want to run. When you don’t want it to run rename the file, the job will fail. when you want it to run, rename it to the correct name. The script give you loads of information about your updates and provide e-mail with logs.
Loading...
Reply Mark Dreyband August 3, 2016 5:30 am Hi, Kendra! It will be very interesting to see your post about SQL Server (not Windows OS) patching, like installing Service packs and hotfixes. I think there are a lot of underwater rocks you may point.
P.S. And thank you for your community input! 🙂
Loading...
Reply Kendra Little August 3, 2016 7:19 am Hey Mark,
Thanks! I wrote a bunch about testing for SQL Cumulative Updates and Service Packs here – http://www.littlekendra.com/2016/04/28/required-testing-for-installing-sql-server-cumulative-updates-and-service-packs/
Loading... Reply John G Hohengarten August 4, 2016 7:30 am Hi Kendra,
You mention a couple times about “database offline?” type of verbiage.
Is it a best practice when patching Windows servers which have SQL Server to put databases offline prior to shutting down the SQL Service and launching the updates? It seems implied by the way you talk about it. Or am I misunderstanding that it was a problem that the databases were offline, as opposed to wanting them to be offline?
Disclaimer: I’m not a DBA, I’m a BI Developer with no responsibility for updating the servers in my org.
Loading...
Reply Kendra Little August 4, 2016 10:18 am Great question! I don’t set databases offline for patching. In the cases in question, the databases were in the process of being archived. They were legacy databases, and they were believed to not be in use anymore, but the system was complex enough that we wouldn’t have been surprised if a monthly or quarterly process queried them which nobody had remembered (or perhaps nobody realized was still running).
For this reason, the removal process was to take a final backup, set the database offline (so it could quickly be brought back if needed), and then eventually verify that we still have the backup, the backup still restores, and drop the database.
The obvious problem that would happen is that sometimes the drop process would be delayed or forgotten. Usually people were pretty good about documenting in the runbook which databases were purposefully offline, but you know how that goes 🙂
Loading...
Reply Cody August 12, 2016 3:14 am In my experience there’s a significant point of contention between how management thinks every server is or should be cattle, and a DBA experience on the ground knowing that every server is more like someone’s fussy pet made unique with the scars of 5-10 years of patches, OS upgrades, SQL upgrades, tweaks, applications, and who knows what.
I apply about 100 SQL SPs/CUs per month. They can fail in twenty ways that I’ve documented so far and that’s just the predictable stuff. So when it comes to scripting automation for it’s a massive project and nobody seems to “understand” why.
Of course if you have a smaller environment it’s probably not a big deal, maybe you won’t hit all those edge cases and you’re lucky. I suspect most people haven’t even tried to understand the Microsoft documentation on properly patching a cluster or AG or replication member – because what they suggest is crazy, out of date (written mostly for Windows Server 2008 R2), and borderline dangerous. Are you really going to automate removing node votes in the cluster during each patch session and then reapply it all automatically afterwards? That’s a resume generating event waiting to happen.
And you have to make crazy decisions. Let’s say you’re super smart and automate querying the version from the SQL Server AND automate working out which patch files are what build and can be used. Now.. what patch are you going to apply to a SQL 2008 R2 instance? You better know there are two completely different version number streams; GDR and QFE. That’s assuming you do not jump up to the latest TLS 1.2 which overrides both but has been fraught with problems from every angle. (Is anyone even using SQL Server TLS? Judging from my chats with security professionals and searching for information on how to do it, my answer would be probably not.)
WSUS isn’t really an answer. It requires very specific settings to be used to pick up the right patches and which often aren’t used in some larger environments. SCCM uses WSUS under the covers and is better but… now you have all of those same problems plus one more trying to integrate “your” lowly DBA stuff into the system administrator’s baby and he/she is very protective of their baby!
Oh well, those are my thoughts 🙁
Loading... Reply Kendra Little August 12, 2016 10:39 am Oooo, great comment. So with the issues with WSUS/SCCM, what do you use for your patching when it comes to Cumulative Updates and Service Packs? Just curious what ends up being most efficient for you at that volume.
For other readers who are confused by the GDR and QFE comments (and it’s confusing), the SQL Sentry team has a great resource for figuring out what build you want to apply at: http://blogs.sqlsentry.com/category/sql-server-builds/
When there are hotfixes released out of band (whether Quick Fix or General Distribution), they note them and then also do some of the legwork for you to figure out which QFE’s/GDRs are in which Cumulative Updates, etc. What an acronym party 🙂
Loading... Reply Aaron Gonzalez June 4, 2018 1:25 pm Okay, this is an old thread but I found this somewhat concerning…
“So, if you’ve got any servers where jobs or applications are running as sa, well…. hope they finish up fast.”
… because Ola Hallengren’s maintenance solution creates its jobs with sa as owner.
Loading... Reply Michael Lewis December 17, 2019 3:39 pm Just sticking this here… DBA-Tools have a PowerShell module that can automate this… https://docs.dbatools.io/#Update-DbaInstance