15

I need help with organizing my arguments for why it's MADNESS to develop against production data.

Backstory: I started working here six months ago and I noticed we're developing directly against production data. The codebase is local, but the database we're connected to is live data! So i have to be super careful constantly when doing update/store operations, so that I don't break anything.

We have an annual software meeting soon and I want to raise my concerns, and come loaded with arguments but I have a really hard time coming up with good solid points to something that's should be obvious as this. It's as if they would ask me to come up with arguments to why I need to breathe.

My Question: What arguments are there for NOT developing on production database directly?

10
  • 7
    If you have some personal data in the database, you can just say its just illegal.
    – JMelnik
    Jul 2, 2014 at 7:47
  • 1
    possible duplicate of How much database access should developers have? See also Safely fixing production database data
    – gnat
    Jul 2, 2014 at 8:21
  • 2
    Why do you have to develop on the production database? Can't you restore a backup somewhere else and connect to that?
    – JeffO
    Jul 2, 2014 at 9:39
  • Have you heard the "Dear Rich Bastard" story? This happened soon after I entered the workforce. It has stuck with me ever since. There's a great reason not to touch production data until everything's signed off. Jul 2, 2014 at 9:53
  • 4
    What are the proponents' points in favor of developing on the live database? They must have some.
    – usr
    Jul 2, 2014 at 10:54

7 Answers 7

17

Because as a developer you just can't work efficiently.

Does this SQL query to update all statuses for a given condition work?
You have now just updated status fields in your table.

Does this SQL query to update from version X to Y work?
You have now just modified your database. What if your status fields with allowed values 0...3 were updated to 0...5 with new values inserted? What if a field has to be removed from a table?

"Let's see if the code sees the password change"
You have now changed a password in your DB.

"From now on were going to salt all database passwords"
You have now changed ALL passwords in your DB. Oops.

"Let's delete the records for this client." Oops, the SQL where clause was incorrect.

In all these examples it is possible to come up with counter-arguments ("Use a fictitious client to test with", "Wrap your work in transactions", "We can do that between midnight and 1 o'clock when nobody is using the database") but they all result in extra work and they are all precautions against accidents (which will fail from time to time)

The way you phrase it in your question is correct: it is MADNESS to develop against a production database. Sooner or later your company is going to have a disaster on its hands. I bet there have already been incidents in the past that you do not know about.

1
  • 7
    +1 for the "SQL where clause was incorrect". I remember working with a bunch of data, and I had a delete statement with a where clause on the next line. I kept that statement in the query window because I used it a bunch. Well, one time, I missed selecting the where clause, and blew away 2 months worth of data. FORTUNATELY, this was on a development server, and the data was not real. But if this was production, we would have crashed and burned until a backup could have been loaded.
    – CurtisHx
    Jul 2, 2014 at 11:57
14

There are several reasons.

The first reason is the risk, if you're working on a system with live customer's email addresses there's a risk (no matter how small) that you could end up emailing them (for example) - the same logic could be applied to texts/bank details etc. If you don't have this data there is no way you can accidentally charge a member for access to your development site.

Second (perhaps minor) is the ability to recognise which system you're on. If you have several versions of the software open (live/dev) and they run off the same data then how will you know which is which? You may accidentally end up performing actions on the real system you meant to do in dev.

The third reason is the most important. Your company has a legal duty to protect the personal information of your clients. Your live database may contain names, addresses, contact information and bank details of your members. This should be treated with the utmost security. Leaving it lying around on a developers machine (dare I say laptop!?) is not safe. What happens if your office is burgled? Are your backups all stored securely? What happens when you dispose of the machines?

Live systems are expensive not only because of their redundancy but because of their restricted access. It's much harder to break into a hosting facility than it is an office! Imagine the crushing blow to your business's reputation of an unauthorised person gained access to this information.

Your customers' personal information is some of your most sensitive information - it shouldn't be lying around on desktop machines!

9

TL;DR: make sure you consider the advantages if you want to convince others to change their stance.

Working on production data in development has numerous downsides:

  • Privacy: Leakage of unnecessary data to the developers. However, given the devops movement this may be inevitable because they're also the sysadmins; and often people just don't care as long the number of devs is limited.
  • Security: connecting to the production database implies that any hacked developer machine fairly trivially can do really nasty things. Hacks require attack targets, and a fully-loaded workstation with one of those unreliable meatbags in charge adds a lot of surface area. It's bad enough if devs can access source control; but it takes more than a script kiddy to exploit that, and it's not always easy to hide infiltration via the code. By contrast, if you can just access the servers, even an automated hack tool has a good chance of taking control of your server.
  • Reliability: Working on the live data is bad for two reasons here: Firstly, you can't experiment with data changes (outside of tricky limited scenarios) which means your code will be less well tested when the dev finishes it. Secondly, bugs can have catastrophic consequences because they can make large-scale changes to the most important bits of data you have.

These three downsides have knock-on effects. Because it's risky, work takes more time and attention, which increases the cost of any development. Your network may need fancier firewalling and routing because you suddenly have these "priviledged" machines you need to secure. External assistence is a little trickier because you can't just give every temp the keys to the kingdom.

However...

The various downsides of working on live databases are fairly obvious. People at your workplace surely realize that working with production data takes care and care costs money - even if they don't focus on it. You may clarify and highlight the dangers, and that might help you - but you're probably not telling them anything particularly new, which makes it hard to make a case. You might want to flesh out how the downsides are realistic risks to the specific business you're doing.

However, if you really want to win this argument, you should focus at least as much on the ADVANTAGES of working with production data. You're going to need to understand why they do this, even unstated gut feelings. In short: my real answer is that I think you're asking the wrong question :-).

Once you understand why they do this, you can make a case why your alternative is sufficient.

I don't know your workplace, but I can take a wild stab at the dark:

  • Spagetti data-code: The code depends on the data. E.g. something like an accounting period may be a row in an sql table, and without a valid accounting period, (and a valid user, and a valid sales item, and a valid sales rep...) you can't add a bill, which is what you're currently developing. If you've always had production data to play with, it may not be trivial to even run any code without a fairly realistic database because there can be complex interrelated constraints your mock data needs to satisfy.
  • Mock data isn't representative: When the problem domain is large and complicated, you mock data is probably not representative of real-world data in some way. This means that code can be less well tested in scenarios that matter. For example, you may have exceptional accounting periods that overlap due to legacy or changes in the legal framework, or just due to old now unresolvable bugs. All those all corner cases are unlikely to be in your mock data once things get complex.
  • Mock data is expensive: Creating mock data can be (or seem like) an insurmountable task. If your data model is sufficiently complex, you may need to spend a lot of time creating mock data, and once you're finished, you still need to maintain and update this mock data as code evolves. You've just signed up for even more maintenance duty.
  • Conservative mindset: Changing development practices is risky. If it works now, why change and risk making things worse? There's probably dozen of subtle differences you haven't thought of, and any one of those could cost us business. Stick with a proven recipe.
  • Learning Curve: Even if the new way is better, it's not worth the investment (and there's always something new in IT, why not pick some other more valuable improvement to make first?)

I bet there are more reasons - but again, I don't know your team. If you want to change their minds, you'll need to consider whatever advantages they think the status quo has, and convincingly demonstrate the new way isn't (much) worse.

4
  • 1
    You should certainly answer BOTH questions. Another one would be - how easy is it to make a good sanitized export? It might be simply that no one's ever done it in the company, hence they don't even know how long it will take...
    – Ordous
    Jul 2, 2014 at 10:33
  • @Ordous: You're right; I've added some possible answers to both questions. On the topic of santization: Given the netflix data sanitization drama, and more recently the new york taxi data, it should be clear that creating a truly sanitized export is very, very hard. However, the needs for a "developer" export are of course lower (I mean, I'm kind of assuming your developers aren't hostile, persistent threats). Jul 2, 2014 at 11:01
  • 3
    You don't need "mock data"; just a safe, non-live copy of the real database.
    – Kaz
    Jul 2, 2014 at 13:15
  • @Kaz: I decided not to emphasize that intermediate option because it's tricky to do right (from experience). Firstly, you still have all the privacy issues, if anything they're greater now that you're copying around all that sensitive data. Secondly, databases can get large - making copies can be slow, and running tests and experiments on them will be slow. Jul 3, 2014 at 12:27
4

So i have to be super careful constantly when doing update/store operations, so that I don't break anything.

What if you are not super careful and make a mistake?

Even when removing the potential of destroying/losing your production data (for example by working on a copy created from your daily backup), there is also the problem of securing sensitive data. Should developers really have access to that? This may even be illegal if this is for example medical data.

2

Even if you are super careful, everyone has sometimes a weak moment. Back in the days we also developed against a production database and at the end of a long day, I needed to change something super simple in a user-table update query, but forgot the where id = '$id' part. So all users had my data as their data. Luckily we had a recent backup, but that are mistakes you don't want to make, even when most of the time nothing goes wrong. It only needs just one mistake to take the whole DB down.

Everyone makes mistakes, even if you're super careful. The best way to develop (and test) is a copy of the DB structure filled with fake data.

0
  1. People make mistakes. It only takes one little mistake, and you've trashed the company database. That's especially true if your application modifies the database, rather than just querying it.
  2. You need to test corner cases. If it's a customer database, can you handle customers with no middle names, customers with accented characters or punctuation marks in their names, customers with no phone number, customers with overseas addresses, and so on? The only way to test this is to add such customers to the database. But what then happens when the marketing department decide to send a mailshot to all the company's existing customers?
0

Is the live system supposed to be highly available? In your situation availability can easily be compromised by a bug during development or a human error.

Were there problems in the past? Precedence is often an easy way to convince people.

Is the company legally obliged to protect this data? Developers can export all data and sell it or send it to the competition.

I think developer productivity arguments are not likely to get you far here. Obviously, nobody thinks this is a problem for developers. I'd rather argue with business goals like HA and legal obligations. These are terms that management can understand.