Voice of the DBA

Tag: patching

  • The New CU Schedule

    Is my math right? The new CU servicing schedule for SQL Server 2017 is:

    • 1 CU per month for the first year
    • 1 CU per quarter through mainstream support (5 years)

    That means by my count, we get this:

    WITH myTally(n)
AS
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
  FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
   CROSS JOIN (VALUES (1), (2), (3)) b(n)
)
   SELECT dt = CAST('20171002' AS DATE)
   , patch = 'RTM'
   UNION
SELECT dt = DATEADD( MONTH, n, CAST('2017-10-02' AS date))
    , patch = 'CU' + CAST(n AS VARCHAR(2))
FROM myTally
WHERE n < 12
UNION
SELECT dt = DATEADD( QUARTER, n-13, CAST('2018-10-02' AS date))
    , patch = 'CU' + CAST((n -1) AS VARCHAR(2))
FROM myTally
WHERE n >12
AND n < 30
ORDER BY dt

    Or, for those of you want want it spelled out:

    2017-10-02 RTM
    2017-11-02 CU1
    2017-12-02 CU2
    2018-01-02 CU3
    2018-02-02 CU4
    2018-03-02 CU5
    2018-04-02 CU6
    2018-05-02 CU7
    2018-06-02 CU8
    2018-07-02 CU9
    2018-08-02 CU10
    2018-09-02 CU11
    2018-10-02 CU12
    2019-01-02 CU13
    2019-04-02 CU14
    2019-07-02 CU15
    2019-10-02 CU16
    2020-01-02 CU17
    2020-04-02 CU18
    2020-07-02 CU19
    2020-10-02 CU20
    2021-01-02 CU21
    2021-04-02 CU22
    2021-07-02 CU23
    2021-10-02 CU24
    2022-01-02 CU25
    2022-04-02 CU26
    2022-07-02 CU27
    2022-10-02 CU28

    There will be slippage, and potentially other issues, so I don’t know we’ll see 28 CUs, but having a schedule is something I’m glad about.

  • Security and Patching Struggles

    A couple weeks ago we had a huge security issue with the WannaCry ransomware racing across the world. I was out of the office, and mostly offline, so I was a bit out of touch. However, many organizations were affected, and I’m sure many infrastructure people were scrambling to patch and protect vulnerable systems, possibly even restore affected systems. I certainly hope that most (or all) organizations didn’t pay any ransoms. For the future, my view is it’s better to lose a little data and restore systems than pay ransoms.
    I can remember when we got patches at random times, as vendors wanted to provide more functionality or finally had enough bugs fixed to release a patch. Sysadmins struggled to deploy these patches, being out of practice from the infrequency and struggling with non-standardized ways of applying updates. There was also the concern about the quality of patches, many of which introduced more bugs and issues. In the Windows world, I found many companies wanting to wait until an SP2 was released before applying, or even upgrading, many systems.
    That changed, with many companies moving to regular patches, and standard ways of applying, or even slipstreaming, patches easily onto machines. I welcomed the Microsoft Patch Tuesdays, as this provided a regular release, an expectation, and both admins and users became comfortable with the idea of regular patches. I haven’t loved the auto patches in Windows 10, but I find myself agreeing with Troy Hunt that we should just be patching. In an organization you may want someone to be responsible, but for home users, just patch.
    And, by the way, vendors, you need to do more work, and be more responsive to any issues that come up from patches. Your quality issues lead to greater security issues.
    If you want to ready about the WannaCry issue, there’s a good general post, as well as some guidance from the SQL Server perspective. If you haven’t patched, that’s something you should do ASAP, and while you’re at it, be sure you have the latest security patches for SQL Server applied. I’ve got a series of Build Lists at SQLServerCentral, one for each version. I’m still nervous about applying Cumulative Updates too quickly, but I certainly would download and have them ready, perhaps applying each a month late once once the early adopters have had a chance to report any major problems.
    Patching is a reality for the modern software world. We get regular patches for applications, but our core infrastructure (including servers, desktops, and various devices) also need patches to the OS and platforms. There’s a balance between ensuring stability with known softwre versions and keeping up with patches to prevent problems. We need to find a balance, which is probably different for each organization, and re-evaluate periodically if we are updating an an appropriate level. I lean towards fewer patches when I can, but I always want to keep up with security patches. We never know when someone will take advantage of those.
    Steve Jones

  • Another Re-Release

    If you haven’t noticed, SQL Server Release Services dropped an updated CU6 for SQL Server 2014 SP1 this week. This replaces a previous CU6 that had a NOLOCK bug in it, and the old KB article (and patch) have been deprecated. You can install the new CU#6 on top of the old one, and you should. The old patch could cause you some issues, so if you’ve applied CU#6 previously (build 12.0.4449), go download the updated patch, test it, and apply it to your instances.

    However, there’s a couple issues with the process here. First, re-releasing a patch under the same name (with a different build) is confusing. I am sure there are going to be plenty of people, especially accidental DBAs, that think they’ve applied CU 6, and they don’t realize there has been a change. There will be others that apply the patch from an old download that’s shared on their file system. I’d much rather have fixed patches released as a new CU. What does it matter if CU #7 is released now instead of in a few months? There’s no limit I’m aware of for the number of CUs allowed for a particular version, so let’s just increment numbers.

    The second issue, for me, is that this eats up time. Releasing quickly is one of the problems with an agile approach, where you update software quite often. There isn’t necessarily enough time to completely test the the fixes, and as comprehensive as the Microsoft testing suite is, there will be things that are missed. I certainly think Microsoft deserves kudos for finding the issue and releasing a fix so quickly. However, will this patch be distributed as quickly as the original CU #6?

    If you are used to applying these CUs, are you going to notice there is a new version of this CU to apply? The blog entry title doesn’t note this is re-released. If you look for the latest patches and see CU #6, will you realize this fix has been updated? If you know there’s a new patch, will you have time to re-test the update and schedule another release? I know from experience in a large organization, re-applying Service Pack 3a for SQL Server 2000 was a chore, with limited time to re-deploy a patch among all the other work we had scheduled.

    One thing I’ve noticed is that more and more companies are depending on their databases more often, demanding higher uptime and fewer maintenance windows. The more patches we have, the more troublesome it can be to get permission to apply these patches, especially across a large server farm. Microsoft is building a better engineering process, that allowed for more comprehensive (internal) testing and quicker releases, but this process doesn’t necessarily prevent all mistakes. Those mistakes are not only bad press, but they reduce confidence in the entire process.

    I do think these CUs will start to take the place of Service Packs at some point, though I think the pace will become problematic for many organizations, especially those running third party software. I’m guessing that at some point, a good portion of the SQL Server community will start treating these patches like upgrades, and not applying every one. Many people will end up applying only every fourth or fifth patch, much like people seem to be upgrading many instances every 6 or 8 years.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.1MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Install Cumulative Updates

    For years I haven’t recommended installing Cumulative Updates (CU) as they are released. The main reason is that Microsoft has had language associated with their CUs that say customers should not install a particular CU unless they are experiencing the specific issues the CU corrects. Each CU has (in the past) also noted that you should stick with the latest Service Pack otherwise. That disclaimer has been enough for me to be concerned about CUs in a general sense, despite the insistence that the CUs were tested as well as SPs. My thought was that if the testing was the same, that disclaimer wouldn’t exist.
    Well, things have changed. The latest CUs have this language in the section where a KB says that Microsoft recommends CUs as they are release:
    • SQL Server CUs are certified to the same levels as Service Packs, and should be installed at the same level of confidence.
    • Historical data shows that a significant number of support cases involve an issue that has already been addressed in a released CU.
    • CUs may contain added value over and above hotfixes. This includes supportability, manageability, and reliability updates.
    That’s good news, though as Kendra Little notes, you still need to test. Bugs will still exist in patches, and really all software, so it’s important that you test in your own environment. That means you need a test plan that can easily run, preferably an automated test plan. If nothing else, this is a good reason to use tSQLt and have tests written for your system. At least you can verify important queries and systems are working. Kendra has a good list, so read her post.
    While I think the quality of CUs is up and they are probably as safe as most of the Windows patches we get every month (and are often installed no matter our individual feelings), I’d still be wary. If you can’t test, if you’re busy, if this is a month you can’t afford for things to fail, then don’t install the CU. This is like throwing your own developers’ code into production without any testing. Make sure you know what is being changed, and you look for obvious problems. No one will be too upset of an obscure issue, but if your scheduled jobs start failing, you’ll dramatically reduce the confidence people have in you.
    I am still wary of all patches. They’re disruptions, to both your routine, and potentially to availability as well. Make sure you test, but if you have the time, I’d say keeping up with patches is worth doing. Microsoft is constantly fixing issues, and you want to take advantage of their hard work, if you can verify the patches don’t degrade your system.
    Steve Jones