Someone asked me on Twitter recently if I ran full regression tests before applying Cumulative Updates (CUs). I decided it wasn’t worth discussing in 140 character chunks, so I decided to jot a few notes down. I’ll also expand this to encompass Service Packs since these are almost CU rollups delivered yearly.

The short answer: it depends.

I hate giving that answer, but it’s honestly the correct one. There isn’t a single way to answer this question without examining the situation and environment in which I’m working.

Do I Have Regression Tests?

You’d be surprised how many apps I’ve worked on, whether third party or developed internally, where we didn’t have a set of comprehensive regression tests. If I was lucky, we had a good set of tests for each release, but more often than not I’ve found developers and testers focusing on specific features and ignoring the overall application.

If I don’t have full tests, then no, I don’t run them. I can’t.

However what I can do is schedule someone to look at the application on a test system after the CU/SP has been applied. It isn’t comprehensive, and it doesn’t necessarily prove the patch hasn’t broken anything, but it does get the “business” to sign off on the patch.

Timing and Resources

If I have full regression tests, and I have had them for some applications, the timing of the patch comes into play. CUs are released every other month, which is a fairly rapid pace. It’s one reason I don’t recommend applying them IF you don’t have a specific issue addressed by the CU.

As a DBA, I’m paid to ensure that the databases are running at an acceptable level and I can recover them (quickly) if some disaster befalls the system. However I’m also paid to be strategic and improve my employer’s ability to conduct their business. CUs interrupt my schedule, that of testers, and distract from getting other work done. Therefore, I avoid CUs if I don’t need to apply them for a specific reason.

If I do need to address an issue, I would perform regression tests on the specific system(s) that has the issue and if it passes the tests, only upgrade  those system(s). I have found that testing every system isn’t worth the time it takes to keep all systems at the same level. I always have exceptions anyway due to vendor support issues.

For Service Packs I would always schedule some testing and apply them within a couple months of release. If possible I’d get them the month they are released, but they aren’t usually a high priority, more like medium high.

Severity

There are exceptions to every process and I would agree I make exceptions. Most CUs are patching bugs, not security issues. Therefore the severity for their application, even if they address an issue I’m having, is likely middle of the road.

If a high severity patch is released, I would schedule testing of some sort. Full regression testing is preferred, but in the case of some apps it’s as little as

• apply the patch to a test system
• reboot it
• see if the application comes up and someone can log in.

That’s not great, but it’s all I’ve had at times. Note that I’d still get sign off (see below).

Automation

The key to this process is always automation. Getting regression tests set up in a harness of some sort is time consuming, and likely would take a year in many environments to cover all the instances. Even small environments are full of a variety of instances, and I’d ensure that I can successfully patch the development environments as well as production.

Working with developers, testers, and the business to write checks takes time. Building some sort of harness (SQLCMD, Powershell, etc.) is software development, and it’s something you iterate through to ensure you can check the results, not just execute the tests. I wished I’d had something like SQL Test when I was doing this to at least cover the T-SQL side of things.

However you do it, whether with a formal tool or with cobbled together checks, stick them in source control and ensure they can be executed as a group.

Sign Off

That’s how I’ve approached patches, and it’s worked well for me. I’m conservative, and don’t like to “fix” things that aren’t broken. I avoid patches I don’t need. Others feel differently and some of it depends if you are performing lots of development on applications. In that case, you might apply patches so developers don’t run into (and code around) bugs.

The last thing I’ll mention is that even with regression tests, I’d always ask someone from the client side to work on the test system. I can’t always enforce that or guarantee it has happened, but I do ensure they sign off on the patch, having tested it or not, before I apply it.

And, of course, I always make sure I have a backup of the system, off the local disks, before I apply the CU.

Filed under: Blog Tagged: administration, sql server, syndicated