Alt.Net.UK - Evolutionary Databases - Design and Deployment

One of the smaller but more diverse sessions at Alt.Net.UK was a free-form discussion on the challenges of managing changes to relational data schemas alongside corresponding releases of the associated software.

Following a brief preliminary discussion on the merits of automated deployment and 'ClickOnce'-style rollouts in general (and the problems of managing certificates for deploying signed packages to 40-odd servers to which you don't have any access - an interesting topic to follow up on next time, perhaps?), there was some lengthy and interesting discussion of various strategies for managing evolutionary databases.

There's a couple of things which were agreed in principle, specifically

Automated deployments are a good thing. Most problems in otherwise routine deployments are due to human error - someone forgetting a particular step in the roll-out process
Generating data-access layers, business logic and unit tests directly off the schema is only worthwhile in a greenfield development.
If you're developing against a shared database (i.e. there's other apps or systems using the same DB), you have to regard stored procedures as immutable - and have some sort of strategy in place to cope with breaking changes.

Related Resources

Evolutionary Database Design

http://databaserefactoring.com/

http://dbdeploy.com/

DbDeploy.Net is a Database Change Management tool. It’s for developers or DBAs who want to evolve their database design - or refactor their database - in a simple, controlled, flexible and frequent manner. DbDeploy.Net supports the creation and execution of delta scripts to migrate a given schema from one version to another, and integrates well with automated build systems (NAnt / CruiseControl.NET). Here's a basic write up about how to incorporate into a project

SQL Compare is a commercial package from Red Gate software, specific to Microsoft SQL Server, and allows in-depth comparisons of different schema versions, or comparison of a schema against a set of SQL creation scripts; a companion product, SQL Data Compare, then facilitates comparison and merging of data between different databases with the same schema.

(Red Gate were one of the sponsors of Alt.Net.UK, and one of their team sat in on this session, but they neither proposed this session nor mentioned SQL Compare during the discussion)