Committing Stored Procedures to SVN Repository

Question

My current development environment for C# projects is Visual Studio, with a SQL Server database and using VisualSVN to connect to my SVN repository. To manage revisions of my Stored Proceduress, Views, etc I save the ALTER script to a folder watched by my SVN client so these get included in the repository.

I have checked out some (now older) posts like this one (How to keep Stored Procedures and other scripts in SVN/Other repository? and Is there a SVN plugin for SQL Server Management Studio 2005 or 2008?) and have seen a recommendation for these tools: http://www.red-gate.com/products/sql-development/sql-source-control/ and http://www.zeusedit.com/agent/ssms/ms_ssms.html .

As I infrequently work with projects doing much DB-side programming, this has never been a major bother (a dozen scripts in a folder with some naming scheme is not much to manage manually), but I have just inherited a project with a few hundred views and 1000+ Stored Procedures which have never been included in version control.

My question is:

What process do others follow for managing the versioning of their SQL Server code - is there a an accepted, clever or otherwise obvious approach I am missing here? I am leaning currently towards the purchase of one of the aforementioned tools - but am looking for advice from the community before I do this.

I realize this may result in a tool recommendation rather than a code solution but posted to SO as I think this is the appropriate crowd to ask this of.

Answer 1

I would recommend you go with something like the redgate tool, and treat any SQL database in the same way you'd treat your C# source code; manually keeping track of the ALTER statements will trip you up sonner or later as the number of modifications grow..can't speak for the zeus edit tool but having used the redgate one, it "just works" - and another benefit of using a tool like this is that it can manage your migration scripts so you can make a bunch of changes on your development version, then generate a single update script to update your testing database, etc,including data changes which is imho the biggest PITA to manually manage.

The other thing to consider, even if the number of changes are infrequent and you get away with manually tracking the ALTER statements, what if someone else ends up working on the same project; now you have another potential for mismanaged change scripts....

Anyway, do let us know how you get on and best of luck with it!

Answer 2

I’ve been maintaining a database with around 800+ db objects in it. We've always just scripted the database objects to a svn-watched folder as you describe. We have had some issues with this method, mostly with people forgetting to script new or modified objects. At the end of the day it hasn't been a huge problem for our project, but yours may be different.

We’ve looked into a couple tools, but they always assume you are starting from scratch, and we have almost 10 years of history we’d like to preserve. In the end we just end up settling back into our text-based manual solution. It's cheap and easy.

Another option you might want to look into is setting up a Visual Studio Database Project. It will script all your objects and provide some deployment options as well. My opinion was that it tired to be a little too tightly integrated for our tastes - we have a few named references to linked databases that it just wouldn't give up on.