It’s the second Tuesday of the month again. Time for T-SQL Tuesday, edition 195. This edition is hosted by Pat Wright, who wants to know whether our code aged well. The timing of that question could not have been any better for me, because I am right now working on an assignment related to a database system that I first worked for over 10 years ago!

The database system

I started working for the company in summer 2013. This was on employment, with a one-year contract. They used a SQL Server database to support their subscription administration and invoicing process. But in a way that was … let’s call it interesting. I learned at that place that, just because you can do something in SQL Server, does not make it a good idea.

The application had clearly been developed by someone who knew just one tool: SQL Server. So he made the database do all kinds of things that really should have been in the application layer. Call external APIs to monitor the primary process of the company. Send fully formatted emails with invoice information. Send formatted HTML and interpret the callback response as a picked menu item. Yes, all that was handled in SQL Server, the frontend app did nothing more than sending query results to the web server and sending HTTP replies back to the database as a parameter into a stored procedure that handled everything.

It was also clear that the developer not only knew just this one tool; he also did not know it really well. I only worked at that company for one year (I’ll save the story of why I did not accept their offer to stay and started my own company at that time for another time). But in that one year, I refactored (read: completely rewrote from scratch) approximately half of the code in the database.

I did not fix the terrible architecture. They had nobody on staff who would be able to write the front-end code needed to remove everything from SQL Server that did not belong there. And I also could not really do much about the many flaws in the data model. I had started to work on refactoring parts of the data model, but I had to retain compatibility with code at all time, so it was a process of small steps on a long journey. And one year really was not enough time.

My legacy

Once it was clear that I would leave the company, I spent my time on two things. Leaving the company with good, solid documentation of the system (something they never had). And trying to get my successor up to speed – which was a challenge, because they only found him two weeks before my last day, and he lived in London. The documentation did help him a lot, though!

A few years later, I was contacted by the office manager. My successor had left after less than a year, and even during that year, he had been given only limited time to work on this system, because he was made responsible for other systems as well. And after he left, nobody had really taken over. In true emergencies, someone from the London office would remote in and try to fix stuff, or they would just manually edit data in the tables to at least get the right invoices to be sent. But over time, errors had built up more and more, and they needed someone to go in and fix things. Me.

So I did. It turned out that all the invoicing issues they had were caused by manual error. Even after all those years, the whole invoicing logic (one of the parts that I had rewritten from scratch) still worked flawlessly — as long as nobody strayed from the supported methods to process changes. So, I fixed the errors, reminded them of the proper way to make changes, sent my invoice, and moved on.

That repeated every few years. The last time was last year. But now the request was different. Yes, they still wanted me to fix some new invoicing errors. (And yes, they once more were caused by human error, not by flaws in the code). But they now also asked me to help them prepare for a migration to a new system. Because, after some acquisitions, the Dutch office was now a small office with a different system from the rest. And because one of the key functions, the monitoring of the primary process, no longer worked.

Had my code finally broken? Had the API specification of the external servers changed so much that the process had failed? That is what I assumed at first. Until I went in to investigate.

Some servers were incorrectly entered in the control table. Some servers had had some downtime a year or so ago. And, indeed, there was a bug in my code. I had some logic to control the frequency of retries for a non-responsive server. From first trying every time the job ran (hourly), it would back down to once every two hours, then once every six hours, and finally just once a day. A small mistake in that IF condition made it that, after a week of no response … the retry would actually never happen. Once I fixed that, and corrected the errors in the control table (I’m currently still waiting for the correct information), the entire monitoring will almost certainly just pick up again!

To the future!

So now, after I had once more fixed the invoicing errors, I am helping them to prepare to move to a new system It will be Salesforce, the system they use for all other offices too. I get how this would make the workflow a lot easier.

But I sometimes wonder whether they realize what they are biting off. Salesforce does not offer a lot of the functionality that the current system does have. At least, that is my understanding. The other offices still use Excel-based invoicing logic. And they will have a hard time implementing all the specific invoicing rules that are currently active in the database!

But it’s their choice. And, again, I get it. You really don’t want to remain dependent on a system that runs on SQL Server 2008R2, on a virtual machine that is held together with duct tape and prayers. And I get that they don’t want to invest in an upgrade for a system that supports just a single office of what is now, after many acquisitions by the mother company, a world wide company.

But when I am totally honest? Then I am secretly very proud that the work I did in 2013 and 2014 is still working flawlessly, and sad to realize that this database will most likely be shut down forever later this year.

Conclusion

Thanks, Pat, for getting me on a trip down memory lane, and for forcing me to write this very bragging blog! Trust me, I am normally much more humble. Especially because I have seen enough other old code by myself that makes me insanely humble!

But this is the project I am working on right now. So this is my answer to your question.