9

For the last two months or so I have been looking for solutions or practices to handle release management within databases. I'm looking for what people view as the best process for handling this.

We have 3 environments for our databases:

  • Development
  • User Acceptance Testing (UAT)
  • Production

The problem is sometimes we are making changes to several things within our development database and come time to deploy, some of the features may not be ready to be released to UAT.

Recently we have started using Red Gate SQL Source control for storing all of our entities (with regular commits).

I was thinking of going based off of changesets (i.e. say everything from changeset X and back is now being pushed to UAT) however, this means that people are only checking their code into source control just before we do a deploy which can get confusing (especially since people are forgetful). Another issue with going with the changeset approach is if there is a bug in a stored procedure that needs to be fixed, the changeset number would end up being out of scope of our max changeset for the revision therefore making it so that if we need to recreate the database off of a maximum changeset, we would be pushing the bug out again.

Any suggestions on a process?

Thanks

Steven A. Lowe
  • 33,828
judda
  • 193

3 Answers3

5

Migrations

An up and a down, that are in your repo and tagged along with your app.

You can even DIY with sql flatfiles that modify your schema and update the schema version. All you really have to do is:

  • keep your migrations next to the source code, they should be versioned and tagged together
  • always use managed changes (migrations) in all environments
  • never allow ad-hoc modifications in any environments

Well you can do development changes in dev, but you should always blow away you db and rebuild it with migrations once you've captured the change.

iteratingself
  • 8,703
2

Source control!

You don't deploy your development binaries directly to production without going via svn/git/p4, So why would your databases alone do that? Get private instances for developers to test their local changes, but when it has to go to the development db, it has to go via the checked in ddl files. You can even write up tools to apply these ddl changes automatically (I don't know of any existing way to do this correctly).

Once you have the restrictions around db schema changes in place (No more sqlplus -> issue ddl!) and have strict account control (no ddl access to everyone), this should become more manageable.

Sub S
  • 2,448
0

Following up with the suggestion of using migrations...perhaps use an O/RM that supports Migrations like Ruby on Rails and Entity Framework 4.3 The problem with both approaches however is that a migration is all or nothing. You can't (usually) select which Migrations are applied like you can in terms of change sets.

Another viable option (if you're on the microsoft stack, you never mentioned the platform) is to manage your SQL with the Visual Studio Database tools. It has built in refactoring (rename/remove columns, etc.) and does verification of the model. If for example, a stored proc references a column that's no longer there, it will inform you.

Michael Brown
  • 21,822