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

Improve this question
4
  • It sounds like your database scripts aren't in the same source control as your "actual" code. Why is this? Can you treat it as "source code" and put it with the individual branches?
    – Mike M.
    May 27, 2011 at 14:03
  • We currently only store the development version of the scripts in source control and UAT / Production both fall out of sync with active development. Each of the scripts in source control are updated every time a developer does a commit. The issue with individual branches is we have 1 centralized database that everyone uses (for bigger changes we branch out the separate databases).
    – Anonymous
    May 27, 2011 at 14:09
  • 1
    You could create a branch for the release, and only commit changes to this that pertain to the release. All other changes should be made to the trunk. You would need two development databases to achieve this. Would this be a problem?
    – Anonymous
    May 27, 2011 at 23:16
  • That sounds like one is likely to fall out of date fairly quickly. No? For one of my projects we are in the middle of a massive overhaul of the database, so we did branch that one off so that active development could still occur in the unmodified version of the database. However, each day I see our branched version getting more and more out of date which I'm not sure if that is ok or not ... I've never really had to deal with situations such as this before.
    – judda
    May 28, 2011 at 3:38

3 Answers 3

Reset to default
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.

Improve this answer
3
  • What would happen if a bug was found in the scripts? Do you go back to the sql script and update it?
    – judda
    May 29, 2011 at 1:20
  • 1
    No, you create a new migration (which increases the schema version). That is how you know the fix has been deployed, by looking at the schema version in the database.
    – snakehiss
    May 29, 2011 at 4:08
  • Thank you for your help. From first glance this appears to be a very solid approach and similar to our branching strategy.
    – judda
    May 31, 2011 at 13:31
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.

Improve this answer
4
  • 1
    Sadly our databases are too large to be passed along between developers to get the private sessions running. As well as that doesn't really seem to lean towards more team based development because as of right now, I do back end development while talking with the people for the UI work linking the two. I would need to start by checking in all of the changes and then getting them to get latest on the database which seems to be a fairly big hassle.
    – judda
    May 28, 2011 at 3:42
  • Why does your development db have to be the same size as the production db? They can have the same schema, and you can even have a special load testing fleet with all data, but the local dev databases could be small. This also prevents the data leak concerns etc. - in theory, prod data should not hit development at all.
    – Subu Sankara Subramanian
    May 28, 2011 at 3:46
  • All of our data is loaded through our data load process which processes the files provided to us from the client and then transform them into the data that we need. So it is impossible for us to separate the dev and prod data given all of the data loads need to be verified in development anyways. I guess it doesn't need to be the same size however, it does need comparable amounts of data in order for the reports that we create to generate meaningful information.
    – judda
    May 28, 2011 at 3:54
  • The whole DB doesn't need to be replicated. In Oracle every user has their own schema and we have one application schema. Create public synonyms for all objects in application schema. Then every user can change the objects (tables, SPs) in their own schema and if they connect as themselves their object names would be used. For objects that are not in there schema the synonyms woukd be used. This is how we work.
    – softveda
    May 28, 2011 at 5:04
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.