Having a great development environment

I'm a big fan of checklists, and one of the ones I like is the one by Joel Spolsky (Joel on Software) called The Joel Test: 12 Steps to Better Code.

He goes through a whole set of things that you need to be doing right. The one I want to focus on today is the "Can you make a build in one step", and how it translates to the database world.

What Joel Spolsky focuses on mainly are software products. A build is not a big deal in his world. However, in the world of complex database systems, with all kinds of inputs and outputs from external vendors, things are different. In this world, doing a build, to me, means not just having the database objects set up correctly, but also having data. I'm talking about a large set of data, probably extracted from the production environment - enough to develop and test everything that you might need to.

This is a lot harder than it may seem, and most companies don't do too well at it. The problem is that almost everybody starts out getting database backups from production, and using those in their development environment. This works fine, until the database starts getting too large. Then, obviously, it's not so easy to get backups from production because the files are just so darn huge.

What a lot of companies do is set up the development environment, with all the database objects, and leave the transferring of data up to the individual developer. That's a big mistake. It can take a very long time to specify exactly what data you need, transfer from a production environment, and then import into a development environment. Multiple this by every developer, for every project they're working on, and you're talking about a lot of wasted time.

The better approach is to write up some code to get a reasonable subset of data. There's lots of ways to do this, and I may write more about it at some point, but it's absolutely critical to get data into your dev/test database systems. The biggest difficulty is getting management to approve spending time on a project like this.