How do you share SQL changes within your team?

Whenever you make database changes, how do you apply these changes to others databases on the team (and also your servers)?

Currently we are using a file called changes.sql where we put all our changes, separated with ISO date comm开发者_如何学Goents.

Is there a better way?

We use an expanded version of your approach.

We have an database upgrade folder for each release, which contains all the scripts which are part of the release. There is one index file in the folder, which contains pseudo links to all the scripts which should be run.

We have a cruise control job which runs each night to restore a copy of the current production database, then runs the current release's upgrade scripts against it (by executing the scripts defined in the index file). There's also a CI job which runs whenever anyone checks anything into the upgrade folder for the current release.

The scripts need to be re-runnable obviously, eg they should check for the existence of something before dropping or creating it.

Take a look at http://dbmaintain.org/overview.html - It is a quite powerful tool to manage database updates. It basically works by executing several SQL scripts in the correct order. It remembers which scripts were already executed. If an executed script is changed it either reports an error (in production mode) or clears the database and executes all scripts again (in testing mode). There is a good tutorial too.

Edit: You can also group the sql scripts (i.e. by release). The big advantage here is that you can use the same tests for your unit tests, testing environments, coninuous integration, near-live and production environments.

Not at my current job, but in the past I used a database project in Visual Studio 2010, which was then published to SVN. We had an SOP rather than software automation to push changes from development to QA, staging, and production.

The team I worked with was small - five developers with shared responsibility for DB design and .NET development.

You should also consider using version control on your database. One example is Liquibase. By using Version control you can comment all the changes to the table structure, thus you don't need a changes.sql file.

We use a migration tool (migratordotnet - other alternatives exist) that lets you write C# classes that execute database commands. The migrations run locally on each invocation of the program or of the integration tests, and on the servers on each deployment. The migration framework automatically keeps track of which migrations have been applied. Of course, the migrations are a part of the version control repository.