OS Patching.... SQL Server DOWN!!!!!!! Yes I still like software patching


All software has bugs so patch your SQL Servers regularly and by that I mean every 1 or 3 or 6 or 12 or even 18 months.  Older versions of SQL like 2012 may not have frequent updates while current versions seem to have monthly updates.

If you take 5 minutes to hop on over to any unofficial build chart list of the known Service Packs (SP) and (CUs) look through the hotfixes in each cumulative update starting with the newest and work your way down for the version of SQL server you are on.  Simply find something or a situation you may or may not have had but don't want to and you now have a reason to patch! 

Today was a fun day in the aspect that OS patching of SQL servers caused multiple SQL FCI to go down at 8am so what happened.

1. We are running a two node SQL 2016 with Server 2016 running Storage Spaces Direct (S2D) each with it's own enclosure.
2. They all have a Cloud Witness.
3. They were all last patched in June with May updates and have been in a patching freeze since and not patched or rebooted.

Current Passive nodes were patched and rebooted during the day and we had zero issues.  Role Swap nodes (Yes, I have to say this because when you say "Fail Over" that is considered as a negative action and causes alarm and red flags) occurred as planned at 1am and things are still continuing as expected swapping the Active to Passive.  Now again as expected, we start patching the now new Passive nodes and they are needing to reboot after the first round of patching.  At this point is when we now start to have issues causing the Active node to drop the S2D disks causing SQL Server to go off-line and take down the application and causes an outage.

So why did this actually happen as the passive node shouldn't impact the active node when all votes in the cluster are still above 50%.  What actually happened is that we managed to have the node fall into the Windows Server 2016 cumulative updates that were released from May 8, 2018 (KB4103723) to October 9, 2018(KB4462917) installed and where hit with a bug/issues that was introduced and caused the S2D disks not to actually go into maintenance mode like they had previously when paused.  This caused the clustered shared disk to go off-line and the SQL Servers kind of needs this and without it goes off-line as well.





So after all the insight above we now have a process in place to ensure that the paused passive node actually is paused and puts the disks into maintenance mode before being rebooted.   Additionally, disabling live dumps will also help to mitigate the effect of live dump generation on systems that have lots of memory as most SQL servers or at least the ones in my case have lots of memory.  All this information can be gathered from a quick google search with errors from the Cluster Manager and Event logs.

Search items included:
"  Cluster physical disk resource failed periodic health check  "
"  Two Node S2D cluster passive node rebooted and goes down  "








Comments

  1. AnonymousApril 20, 2022 at 4:26 AM

    Os Patching.... Sql Server Down!!!!!!! Yes I Still Like Software Patching >>>>> Download Now

    >>>>> Download Full

    Os Patching.... Sql Server Down!!!!!!! Yes I Still Like Software Patching >>>>> Download LINK

    >>>>> Download Now

    Os Patching.... Sql Server Down!!!!!!! Yes I Still Like Software Patching >>>>> Download Full

    >>>>> Download LINK YM

    ReplyDelete

Post a Comment

Popular posts from this blog

How to mass update all or some sql server agent job step commands

How to mass update all or some sql server agent job step and commands using TSQL.   This will use the built-in Replace function to search text in SQL agent jobs  SQL Agent Job information is stored in MSDB and can reviewed using some of the following queries: SELECT * FROM msdb.dbo.SysJobs SELECT * FROM msdb.dbo.SysJobSteps -- OR SELECT a.name as job_name, b.step_name, b.subsystem, b.command, b.database_name, a.enabled       ,a.description, a.date_created, b.job_id, b.step_id   FROM [dbo].[sysjobsteps] as b   INNER JOIN sysjobs as a ON a.job_id = b.job_id Since I will be focusing on the Commands column I will look at using this query to extract data into a temp table so that I can test any updates with the replace step before doing an update to MSDB. This would be your opportunity to archive or save any commands as needed.  Some people may prefer to generate all the syntax necessary to use EXEC sp_update_jobstep so you a...
Read more

SQL Monitoring for Blocking and Locking Capture and Log to table with sp_WhoIsActive or sp_BlitzWho

Image
You can use  sp_WhoIsActive or sp_BlitzWho and log the capture data to a table.   In my example I will use sp_whoisactive as I am more familiar with its use and data collection, however I will look at creating a future post using BlitzWho.  I will provide a few examples that were used to determine that the paid SQL monitoring products can be fancy tools but can be useless if you don't understand the basic concepts or even an understanding of how we capture similar data or in some cases more finely tuned to a specific scenario or use case.  Sometimes these paid solutions are incorrect or things can simply go wrong (Services Fail to start or the data is aggregated and misses something like the query commands and SQL Fragments) and it's always good to plan ahead and be prepared.   Sp_WhoIsActive is a procedure written by Adam Machanic. It can be found here:  sp_whoisactive downloads Sp_BlitzWho is a procedure written by the Brent Ozar team....
Read more

SQL Server General Index Naming Convention

SQL Server General Index Naming Convention  easy to follow:  PK_ for primary keys PK_  Primary Keys, which are logical constructs and not necessarily physical constructs, get tagged with a PK_ prefix. In most systems that I design the PK_ isn’t a clustered index.  IX_ for non clustered non unique indexes IX_  standard single or even multi-column indexes used for seeks or intersection just get prefixed with an IX_ (for  I nde X ). In cases where I need a composite index (i.e., multiple columns) but still explicitly ‘tolerate’ key/bookmark lookups, I’ll still call my seek/intersection/composite indexes IXes. UK_ for unique keys UK_  for unique key index UX_ for unique indexes UX_  for unique index. CLIX_ for Clustered Index (non PK) CLIX_  (non-PK) Clustered Index name is prefixed with CLIX (for  CL ustered  I nde X ) Additionally, I like to code all Create Indexes in SQL 2012 and above with an if not ...
Read more