Agile development and database changes [closed]

Question

For those agile practitioners out there...

How do you manage changes to a database schema during a project? My assumption is that in an agile project the schema of any database involved will change and be refactored just as happens with the codebase.

Is this assumption correct? If so, do you have any particular tools or processes that you use to help keep things running smoothly?

Answer 1

AgileData.org is an excellent resource -- much more than I cram into a single response -- on Agile Database development. In particular, you might be interested in Agile Data Best Practices. If you use SQL Server, you might also be interested in SQL Compare from Red Gate software. Our DBAs have used it to help me migrate changes from QA to Production for existing applications.

Answer 2

For each update, I'd be:

• deploying roll forward and rollback scripts,
• deploying a "build the DB from scratch" script,
• deploying a data migration script, and
• enforcing a mechanism whereby the code is locked to the database version, i.e. testing for a value that returns the current version of the DB, if there's a mismatch, the system bails and bleats loudly about the mismatch.

HTH

cheers,

Rob

Answer 3

In our Agile setup, there's a folder for DB changes, done as .SQL files. So far we've had a DB change in every version, and the file is named after the app version. The install script automagically applies all the change files when updating sites.

We also have a full schema dump of a reference DB, that's used for new installs, created by our DB Admin tool.

I know there are tools that help automate this process, such as Red Gate, but manually creating the SQL change file is very easy.

Answer 4

Ideally you make non breaking changes, then when release is done you can fully deprecate old parts of the schema. This is not easy and requires discipline. It is not even always possible.