Advice for staffing and planning for database patches

Question

We have a centralized database cluster (an Oracle RAC), which serves virtually all of our applications on behalf of all of our clients.

We have always been very reluctant and very careful when making any changes, as any downtime could affect everyone.

Consequently, we have only applied emergency patches. By our definition, the need for emergency patches outweighs the risk of the potential side-effects. We only do this when needed - a very rare event.

We were purchased by a much larger company. Now, as we continue to integrate with a larger organization, the company is hinting that we must get prepared to apply database patches on a regular basis - at least quarterly, and perhaps monthly.

I'm faced with trying to find the time to regression test several dozen applications periodically, then releasing the database updates off-hours. I estimate that it would take about a week of dedicated testing by at least 3 testers to accomplish these regression tests. And of course I simply don't have that kind of time in the schedule, so I'm currently negotiating for additional headcount.

I've spoken with other groups which were previously acquired by the parent company. They don't quite have the same problem, as they support only a single application each.

I'm wondering if others have faced a similar requirement, and if so, how you have handled it?

Do you steal time from other projects to test the effects of periodic database upgrades?

Do you have dedicated testers whose sole responsibility is to test infrastructure upgrades, rather than application changes?

Or something else?

Thanks for your help!

Answer 1

You didn't mention anything about automated testing of your applications or the DB. For that additional headcount you mentioned, you could get a QA developer who's sole job is to write tests for your applications and the underlying DB. Even if the applications weren't written by you, they must have some kind of communication interface that you can point a test at. Over time, the time required to regression test the applications and infrastructure will diminish considerably.

Find ways to automate your testing. On my project, automated testing reduced testing time for a web application from days of manual testing to mere minutes. We don't do any direct testing of the DB but we do make sure that all our applications are behaving properly.

If automation isn't an option or you can't get the headcount, you could do some smoke testing in a testing environment. Identify some key functions that exercise a majority of your application's functionality then have your testers work on that. You'll know that most of your application is working. Along with this testing, if you keep a good list of which patches you've applied, that would help narrow down where a problem is occurring.

Hope that helps.

Answer 2

My company relies heavily on SQL backends. We typically make many changes to both the database schemas as well as updates to the database software as needed during a release cycle. We try to have these front-loaded towards the beginning of the release cycle so that we will have plenty of time to regress everything and ensure we have time to correct anything that might be significantly impacting our products in a negative way due to those changes.

We have a number of products that all rely on the same databases and common apis. Some of the products we barely touch or don't touch at all, but because they also have dependencies on the same backend components, we end up having to regress them nearly every release.

Historically we expected to fully regress every product every release. We approached this two ways, mostly, we had a team of off-shore vendors who helped with our regression testing, the second was automation.

Going forward, we are trying to create more targeted automation so if changes are made to the databases and the interface to the databases to support those changes, we are able to run our automation against the interface directly to ensure that it won't have a negative impact on our products rather than needing to fully regress those products that are in maintenance mode and have no code changes. Green makes a good point about automating the DB and interface. If you don't have a solid interface that is the sole communicator between your applications and database, you might consider investing in one as it sounds like that could help reduce the costs of your regression testing quite a bit.