Database structure and source control - best practice
- by Paddy
Background
I came from several years working in a company where all the database objects were stored in source control, one file per object. We had a list of all the objects that was maintained when new items were added (to allow us to have scripts run in order and handle dependencies) and a VB script that ran to create one big script for running against the database.
All the tables were 'create if not exists' and all the SP's etc. were drop and recreate.
Up to the present and I am now working in a place where the database is the master and there is no source control for DB objects, but we do use redgate's tools for updating our production database (SQL compare), which is very handy, and requires little work.
Question
How do you handle your DB objects? I like to have them under source control (and, as we're using GIT, I'd like to be able to handle merge conflicts in the scripts, rather than the DB), but I'm going to be pressed to get past the ease of using SQL compare to update the database.
I don't really want to have us updating scripts in GIT and then using SQL compare to update the production database from our DEV DB, as I'd rather have 'one version of the truth', but I don't really want to get into re-writing a custom bit of software to bundle the whole lot of scripts together.
I think that visual studio database edition may do something similar to this, but I'm not sure if we will have the budget for it.
I'm sure that this has been asked to death, but I can't find anything that seems to quite have the answer I'm looking for.
Similar to this, but not quite the same:
http://stackoverflow.com/questions/340614/what-are-the-best-practices-for-database-scripts-under-code-control
