Updating in production, like a boss

Who, Me? Testing in production has always been a thing, sometimes by accident and sometimes because the powers that be cannot be bothered with multiple environments. And sometimes things go wrong. Welcome to Who, Me?

Our tale, from a reader Regomised as Nicolás, takes us back through the decades and to the glory days of Microsoft SQL Server 7.

SQL Server 7, which turned up in 1998, was a major rewrite of the old Sybase engine that had underpinned previous versions of Microsoft's database. Not so fragrant and fresh, however, were the other tools Nicolás used in his Madrid day job: Visual Basic 6 and that terror of programmers everywhere at the time, Crystal Reports.

At this point we'd normally go off on a historical tangent regarding the reporting tool, which came bundled with the likes of Visual Studio and Borland Delphi, but the trauma of having to change data sources programmatically still haunts this hack decades after the event. However, it was not Crystal Reports, nor was it Visual Basic 6 that lay at the heart of this week's metaphorical locking of the brakes in front of a looming truck. It was SQL Server.

Nicolás and his manager had been tasked with fixing problems with some brand spanking new in-house accounting software. “To say that the previous coding was utter crap is an understatement,” he muttered. Inexplicably, the duo were denied a development and test environment, or even space for backups.

• Do you come from a land Down Under? Where diesel's low and techies blunder
• Peter Moore: IT consultant and Iraq hostage – Part One
• That thing you were utterly sure would never happen? Yeah, well, guess what …
• Thanks, boss. The accidental creation of a lights-out data centre – what a fun surprise
• Congestion or a Christmas cock-up? A Register reader throws himself under the bus

“Keep up the good work,” exhorted the management as the pair glumly looked at the task.

They got cracking. “I was the database expert,” Nicolás modestly told us, as well as being the resident VB6 guru. The latter meant that he was elbow-deep dealing with what he described as “seriously crap VB6 code (forms would duplicate and crash)” when his colleague decided to have a crack at some data modifications.

Nicolás, although more experienced, was the junior partner in terms of job titles and so his colleague did not trouble him with the ins and outs of the task. It was just an update. On the production database server. Without backups.

Looking back, Nicolás told us: “To this day I don’t really know what went through their head, but those modifications were intended to fix some inconsistencies in the database (one [insert expletive here] had added a small amount of fixes to hide the previous software rounding errors!)

“Since we were not having rounding errors, those small adjustments had to go.”

Alas, the adjustments were not the only thing to go.

“Oh Dios … la he jodido.”

Nicolás looked up from his work. What had been, er, screwed up?

There are two types of database programmer in the world. Those who have missed a critical filter in the WHERE clause of an UPDATE, and those that will do so at some later point in their career.

Nicolás’s colleague had skipped from the latter camp to the former, and done so in production and without backups. Every record in the table had been inadvertently updated.

While the need for anonymity forbids us from revealing the purpose of the database, the cock-up would make for national news if it could not be fixed. Nicolás played the only card available to him: “The database is going down for important performance updates, please do not use the software.”

There is a happy ending to the story: he was able to reconstruct the borked data from the content of other tables during what we imagine was a very sweaty-palmed SQL session and bring the production system back online, the users none the wiser.

“It required so many filters it took hours,” he said, “not to mention carefully checking data consistency to make sure it made sense.”

Job done, he took himself home to soothe his rattled nerves. Perhaps with an adult beverage or two. It could, after all, have been much, much worse – a DELETE might have been issued rather than an update.

Then it would have had to be a "capacity optimisation" instead of the "performance updates."

We all know never to test or develop in production. But sometimes management is reluctant to pay for all those extra environments. Tell us about your moment of expense justification with an email to Who, Me? ®