SCM for database entities, bridging to production

[Markbnj]

We recently moved from a relatively loose regime where all our script lived in the db, and was extracted, edited, and placed back in the db, to one where all of our entities are under SCM in Subversion.

We've elected to keep a baseline script for all static structures (tables, indexes, constraints), and individual scripts for each stored procedure, function, synonym, and view. A per-build incremental script is used to record changes.

There are approximately 300 of these scripts. What I'm looking for now is a reliable way to bridge between a folder full of small scripts and the test and production databases (dev tracks daily changes). The company probably won't spring for something like Redgate. I've been thinking about ginning up a utility using command line sql to iterate the scripts and execute them.

Any better ideas out there? Is the approach itself going to be workable over the long term and possibly for larger databases? What all have you guys tried and found successful when it comes to SCM for database entities?

 

[imported_Dhaval00]

I have never used Subversion, but we use "Labels" in VSS to mark each release/version. That way you can get the entire project using the respective label. This still doesn't allow you to "diff" the modifications across the entire hierarchy, but definitely keeps things organized (as opposed to maintaining an update path every time a new release goes out).

I use Windiff to compare entire folder hierarchies. This "diffing" technique has been more effective over the past few projects since I started using ScriptDB: http://www.codeplex.com/ScriptDB. We can spend the money, but I'll try to avoid purchasing something that can be achieved for free. Also, we used to check SSMS solutions in VSS, but now we have switched to checking in the hierarchy generated by ScriptDB.

Your solution doesn't seem feasible in the long run (again, this could be wrong depending on what all Subversion allows you to do). Quite comparably, I might be overlooking flaws in my approach!

 

[Markbnj]

I think in our case the gap is less about how to manage the scripts in svn, and more about how to reliably migrate the current version to a database, including getting the dependency order correct. I'll check out ScriptDB. Thanks for the tip.