Source control for databases

  • Every now and again the subject of source-controlling database objects gets raised.

    I work in a 400 strong IT department and so far the only situation we have had where code clashes occurred were in an apallingly badly designed database. Had the database been designed properly there would have been no clash.

    I am curious to know what your experience with database source control has been.

  • I use source control for all DDL commands to be issued. Haven't found that it actually helps with anything, but it might some day. I think it makes the other devs and such feel more comfortable, gives them a sort of warm fuzzy feeling that they are "doing it right".

    I'm a firm believer in what could be called "service packs" for the database. All DDL scripts and any related DML scripts (to populate lookup tables and such) get put into a single script that contains error handling, commenting, lists the author(s) and the purpose of the changes, and can be run repeatedly without harming the database or crashing the script. If stored and run sequentially, starting with the original "create database" script, they should return a database to any point-in-time you want, in terms of code and structure (not data, of course).

    Storing those in Source Safe makes the devs comfortable. And, since they need to be kept somewhere, it might as well be in a source control system.

    That's what I use source control for. Could just as easily store the scripts as varchar(max) objects in a database. Doesn't matter much to me.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • The only good method I've seen so far is to source control the DDL scripts (maybe DML deployment as well).

    As soon as you allow GUI changes, things easily get out of hand.

  • I love it. Integrating our builds with the development builds makes everything so much easier. The power that comes from knowing exactly what has changed, when, by whom, makes all our development and deployment processes work better. The tool that really makes it all shine is Visual Studio Team System Database Edition, aka Data Dude. Great stuff.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply