Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm working for a company with very large databases which all refresh overnight, ready for users to interrogate in the morning. These over night loads essentially truncate all the tables, and reload again using pretty complicated business logic.

This is all fine and good, but occasionally, rather than going through the proper routes, a quick fix is needed in the business logic, and changes will be made on the "Live" server to the business logic without too much testing.

This has, as I'm sure you can imagine, resulted in subtle but significant mistakes being made. I basically want to now independently write logic (in T-SQL probably) which will try to spot any abnormal changes, so at least I'm ahead of the game, and can report them sooner.

My question is, fairly obviously, does anyone have any advice on this? Is there any theory or general good practice which I could incorporate to hopefully limit the impact of future "quick and dirty" changes to business logic?

Just so you know, we do use source control, and use Live, UAT, and Dev server environments, its just that sometimes, changes need to made immediately, rather than going through the stricter channels.

share|improve this question

closed as too broad by gnat, MichaelT, ozz, World Engineer Oct 16 '13 at 23:59

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

Is the database implementing any sort of referential integrity (ie. foreign keys defined)? Does it model the domain correctly (ie. fields are the right types and sizes?)? – Oded Oct 20 '11 at 19:18
Some do (i.e. mine), others don't. I'm not going to lie, its not the best design at all, but its huge, and would take at least a year or 2 to redevelop. The databases I control and develop feed off some of the larger and more "vulnerable to change" databases, so mine are more at risk. – CatchingMonkey Oct 20 '11 at 19:32
Abnormal changes to data or to the code? – JeffO Oct 20 '11 at 20:56
An ill thought out join etc...something that fixes one problem, but may cause a another, subtler one else where, for example. – CatchingMonkey Oct 20 '11 at 20:59
The right solution is to stop patching the live server and do it the correct way. – Robert Harvey Oct 14 '13 at 22:17

The use of transactions should pay off here. If any issues arise, don't commit the changes being made, simply roll it back to either the beginning or at a another point in time (break it up into multiple transactions with tables for each step). If you use additional tables for each step in your process, you can go through the data changes made at each point and pinpoint where the error occurred.

Based on the feedback you had given me for my original answer. We know a few things about you environment:

(1) It's a corporate environment where there is little communication between teams. This is evidenced by the fact it appears to be difficult for you to obtain log files from the DBA team.

(2) It's a behemoth of a system.

Before you can fix a problem, you need to understand it. In your case, getting the log files and keeping a diary/journal of the errors and types of errors you are getting will give you a better idea of what's happening. Are you getting data entry errors? Is there data missing? Is referential integrity being violated anywhere? If you can theme the errors, you will have a better understanding of what is going wrong. If getting the logs is as troublesome as you say, speak to your manager about the problem and ask him if he can obtain the logs for you. In a corporate environment, people tend to be very territorial about their work and don't answer to everybody as they are probably under a lot of stress with minimum bandwidth to deal with other issues. If the request comes from someone higher up, you may get a more positive response.

Once you have got the log files and managed to theme the problems, you should have a second discussion with your manager. Give him feedback on what you have found and suggest two or three solutions to resolving this. Outline the short term and long term benefits of each, providing a rough estimate of how long you believe it would take to implement. The key here is communication. Your replies to my initial answer indicate there is a complete lack of it, so you need to make a strong effort to promote it. Having your manager supporting you will give you an additional medium to communicate with the DBA team as he can speak to their manager if they become unresponsive.

If all this fails and you are unhappy with the situation, my only other advice is that you look for another place to work. I've found this situation happens in many companies and to different degrees, and it can boil down to a case of how much you can tolerate. After it happened to me a few times, I came to the conclusion that it simply was not my company and the issues that came up were down to management. If it made life difficult for me, I'd simply move on to other things. Life is too short.

Obviously, you can make use of the standard log files that come with your DBMS on-top of this, but if your company operates under a "hurry up mantra" with regards to getting stuff out the door and it's causing a lot of delays, you should make an effort to discuss these problems with management. Show them what their ignorance of testing is costing them.

share|improve this answer
Thanks! Unfortunately I don't have access to the logs or transaction files as I am "merely a dev". Also, its not like the dba's even know some of these changes are going through, to give us access. – CatchingMonkey Oct 20 '11 at 21:11
Ok, this sound very corporate to me, with a strong divide between software developers and DBAs. So, the first step is to discuss this with your manager. If you see it as a pain point in the process, you need to make then aware of it and discuss several strategies to improve it. Don't just give them one, given then a 3 or 4. – Desolate Planet Oct 20 '11 at 21:23
Lack of communication between teams is definitely a bad thing. I just finished reading Tom Demarco's Slack, and it reminded me of some of the horrors I witnessed working for an investment bank, similar to what you've put in your question. Developers find pain points in the application, but management just cut out all the slack to make it's employees efficient, consequently, there is no time to go back and remediate problems in the software. A real sad sight, but very common. – Desolate Planet Oct 20 '11 at 21:27
Yes, its sadly very corporate. There has been Change Freezes imposed, which I understand to some extent, but it limits your ability to fix issues, and update logic based on say current political climate or legislation. You are definitely right about bringing up these issues, but its more the culture to get these changes through on the sly than to sort it properly. My thinking is to develop something independently that will highlight changes, and hopefully therefore a better way of going about things that can then be implemented a bit more solidly in the future. – CatchingMonkey Oct 20 '11 at 21:35
@CatchingMonkey - The first thing to do is make sure you fully understand all the business logic that is being applied to the data. One thing you could try that I've not suggested in my answer, is to use a SQLite DB and hook your application to that. That might be easier said than done, but at least with that, you can do a dry run of all the data transformations before deciding on whether to go ahead with the production DB. – Desolate Planet Oct 20 '11 at 21:41

I'm presuming that the overnight run is calling stored procedures. If you can't write a text log & get access to it fight for it. DBAs can sometimes be a bit possessive of logs because they don't like developers reading them and asking difficult questions ;).

Assuming you lose the fight, create a simple logging table and a logging library (if your RDBMS doesn't have one). Then during the run write entries to the table. You want these entries outside of the main transaction so you get "encountered error type XYZ in record ABC rolling back" type entries in the log instead of having the error log rolled back as well.

Then, first thing you do in the morning is scan the days log entries & look for issues.

Over time you may encounter data issues that don't get logged. If you get an unlogged issue, write code to identify and log the problem before you fix the code (a bit like TDD). That way, like unit tests the assumptions about the data will be documented and tested and exceptions logged so you can fix them.

share|improve this answer

If you have many such changes, then your organization has to face the fact that the installed software does not match the business requirements. Accordingly, the organization should launch a project to determine the gap and fix the existing software and eventually publish a new release or version.

If your organization desire quality, there is no substitute for following proper development, testing and promotion procedures which take time and effort.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.