I've mentioned previously that I'm in no danger of being mistaken for a DBA, but I've recently made a few changes in how we work with our database that have upped the maturity level a bit.

Previously we had a single development database shared by all. This is an inflexible working arrangement because messing up, even in the very short-term, can impact other people. Also, throwing the whole thing out and starting over isn't an option. So change number one was private databases for database development.  Not doing this is akin to checking your source code into the main repository in order to see if it works.

To facilitate this, we're using dbdeploy, which isn't perfect, but good enough. You start with a baseline schema (just a SQL script that creates all the needed tables and other objects), then add deltas (also SQL scripts) when changes are needed. The simple job of dbdeploy is, given a bunch of deltas, to make sure they're applied once in the appropriate order. It keeps track of the applied deltas with a table in the database being maintained. You can also try to have a roll-back script for any change, but that tends to get dicey.

In addition to the baseline script and the deltas, I also have a "development" script that does things to make life easier for developers that aren't necessarily appropriate for a non-private database, like pre-populating the User table with some users for testing.

Everything is controlled by an Ant script, using both the built-in "sql" task as well as the task provided as part of dbdeploy. Here's what the "create-database" target looks like:

<target name="create-database"        
  description="Create a database from scratch, including the baseline, changelog and deltas">
    <antcall target="echo-settings"/>
    <sql 
        driver="${db.driver}" 
        url="${db.create.url}"
        userid="${db.user}" 
        password="${db.password}" 
        classpathref="dbdriver.classpath"
        encoding="UTF-8" 
        delimiter="GO"
        >
        <!-- Create the database from scratch with the baseline schema. -->
        <fileset file="${db.baseline.dir}/baseline.sql"/>

        <!-- Additional setup for development purposes. -->
        <fileset file="${db.baseline.dir}/baseline-development.sql"/>
    </sql>
    <antcall target="create-changelog" />
    <antcall target="update-database" />
</target>

The "create-changelog" target adds the dbdeploy tracking table to the database using a script included with dbdeploy:

<target name="create-changelog"
    description="Create the changelog table in an existing database">
    <!-- Note that we have to specify the database on the url for this script or the database 
    will be whatever is default for ${db.user}, which might be "master", for example. --> 
    <antcall target="echo-settings"/>
    <sql 
        driver="${db.driver}" 
        url="${db.url}"
        userid="${db.user}" 
        password="${db.password}" 
        classpathref="dbdriver.classpath" 
        delimiter="GO"
        >
        <fileset file="dbdeploy/scripts/createSchemaVersionTable.mssql.sql"/>
    </sql>
</target>

The "update-database" target called from "create-database" uses the custom dbdeploy task to apply any deltas:

<target name="update-database"
    description="Apply all available, but not yet applied deltas to a database">
    <antcall target="echo-settings"/>
    <dbdeploy
        driver="${db.driver}"
        url="${db.url}"
        userid="${db.user}"
        password="${db.password}"
        dir="${db.deltas.dir}"
        delimiter="GO"
        />    
</target>

The result is the ability to create a complete database from scratch in just a couple of seconds. It is very liberating because I can mess up in various and sundry ways while trying to get a stored procedure just right, and the consequences are minimal.

Once I'm satisfied that a delta script is good, I check it in and TeamCity (our build server) automatically applies the deltas to the development database using the "update-database" Ant target.

An important part of this is publishing all of the deltas in existence at that moment in the artifacts for the build. That means that our operations guys can take any build, run dbdeploy against the attached deltas, and know that the database will be in the correct state for the application generated by that build. This is true even if many intermediate builds (potentially containing database changes) are never installed in staging or production.

Our workflow for database schema changes is then:

1. Create a .sql file in the "deltas" directory that makes the needed change. You can use SQL Management Studio to figure out the script, but don't actually make the changes there. Create a script instead.
2. The .sql file should have a name following the pattern:\ <sequence number><description>.sql\ For example:\ 001_create_firmware_table.sql
3. Optionally back up your database.
4. Run "ant update-database" which will apply your script to your local database.
5. Test your code that depends on the new database structure.
6. If your script wasn't right, restore from backup or drop your whole local database and rebuild it.
7. Once you're sure everything works, check in your .sql script and code changes at the same time.
8. The continuous build server will update the dev database and test your code.
9. If you discover something that needs changing after this, you need to create a new delta .sql script.

Of course, manual schema changes in any environment are against the rules.