How Often Do You Patch?

  • Comments posted to this topic are about the item How Often Do You Patch?

  • SJ» Amazing that it's been around for 15 years and has moved into Extended support. We'll still get security updates, but nothing will be fixed.

    You might want to correct the 15 years. Still, SQL Server 2005 is 16 years' old and it doesn't seem that long ago when I was trying to get my head around all of the new features being introduced.

    We patch everything on a monthly basis. After Patch Tuesday the test environment is patched and then once that is deemed to be OK, the production environment is patched. Of course, more urgent patches get special treatment. This two tiered approach is good for the odd time when patches break things. Some years' back, one of the CUs disabled database mail, which was a real nuisance when a lot of jobs were automated and you are relying on eMail notification to inform you of a failed job. Still, we had other tools in place to help mitigate this nuisance.

    I'm also happy about SP3 for SQL Server 2016, but mostly for sentimental reasons. On a practical level for every installation of a new instance of SQL Server 2016, you'll still have to download the latest SP and the latest CU for your new instance to be up-to-date.

  • Applying service packs used to be a process that was weeks in planning and rehearsing.

    The number and sophistication of hostile actors these days results in pressure to patch frequently and as close to the point of issue as possible.

    The level of automated testing that is possible these days greatly reduces the risk associated with patching from an application perspective.  This is how the app and the DB behaves.

    Then there is how SQL Server behaves. SQL Server 2000 SP4 (build2039) was found not to use memory beyond the 32bit limit.  Build 2040 was issued PDQ by Microsoft.  Could we have written some form of test for that back in those days?  In hindsight we probably could but it wouldn't have been easy.

    Now we have extended events, a rich set of DMVs, increasingly sophisticated monitoring software.  For me there are three big limitations

    1. Our imagination in thinking of what to test
    2. Allocation of time and resource to ensure that such testing is implemented.
    3. Testing on production workloads without actually being in production.

    The latter has been a problem for the decades I've been in IT.  The thinking these days is that if you have a robust set of tests that shows your application works in lower environments.  No unusual DB Engine behaviours, IOPs, Memory are apparent then you do test in production.

    I would make damn sure that the current DR process is demonstrable on the patched version before moving to prod.

  • I'm on a committee that evaluates several of our servers with old SQL Server instances on them (SQL 2008 and older). They're being upgraded on a regular basis. However, they're being upgraded to SQL 2016. I'm shocked to learn that SQL 2016 has gone out of mainstream support. I'm wondering how it is we're busy upgrading to SQL 2016, when it now seems like we should have gone to something newer than that. How was that missed?

    Rod

  • We upgrade our version of SQL Server every 7 years or so. We went from SQL Server 2008 to 2016. Almost all of our servers are on SQL Server 2016 and we have Extended Support. We will probably start migrating all of our servers to vNext, whenever it has proved to be as stable as the previous iterations.

    @doctor Who 2: why not migrate to SQL Server 2019. The licences are more expensive than 2008 but that is the way of things. Of course, if your SQL Server servers don't have direct access to the Internet and you have experienced sysadmins and up-to-date firewall software (and the like), then why upgrade at all?

  • Good question, Sean. I don't know the answer to that. I'll have to ask about this when we have our next meeting.

    Rod

  • And some of us are accidental DBAs who are terrified of something going wrong!  No MS support, and although we have support for our servers and networks, it's hands-off when it comes to SQL Server.  I know theoretically how to restore a VM image, but to do it in production...<shiver>

  • SQL Monitor is helpful for noting when a new CU is released. We will patch our dev/qa servers and keep a close eye on them for a couple weeks and if all the functionality and performance is good we'll submit a change request and get a patch window to patch production. On the Windows side of things IT has a similar cycle. They patch dev/qa VMs the weekend after Patch Tuesday and then production the following weekend.

    The Print Nightmare vulnerability forced a company wide immediate patch to all Windows systems to plug that hole but that was a one-off.

  • miapjp wrote:

    And some of us are accidental DBAs who are terrified of something going wrong!  No MS support, and although we have support for our servers and networks, it's hands-off when it comes to SQL Server.  I know theoretically how to restore a VM image, but to do it in production...<shiver>

    As a former accidental DBA myself, I'm right there with you. I can create databases, tables, views, stored procs and triggers with the best of them. But tuning queries or indices, forget it. I got pretty good at making up maintenance plans, but that's because SSMS held my hand. Outside of those constraints, it was strictly, "There be Dragons!"

    • This reply was modified 2 months ago by  Doctor Who 2. Reason: edited out the double quote

    Rod

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply