svn - Exposing SQL Server database objects as files in a file system

Question

There's more than one file system

Most version control tools operate on the local disk file system.

Database objects for most relational database systems do exist in a file system, inasmuch as there is a textual name identifying the object and the creation script can be retrieved or at least generated using this name.

But it isn't the local disk file system, and as a result they are invisible to tools like CVS or SVN, which operate strictly on the local disk file system.

In order for SVN to be applied to database objects, they must be replicated into the local disk file system, and changes to the local disk file system must be replicated to the database.

Different mode of use

Unlike source code, of which each developer maintains a private working copy, developers tend to work on a shared database on a server somewhere on the network. While Visual Studio provides direct support for mount-on-demand project-local copies of the database, developers have shunned this facility because there is no convenient and reliable way to merge changes.

However, once changes to the database structure are managed by a copy-merge version control system like CVS or SVN, propagation and merging will be mostly automatic (bar conflicts) and there is no longer any reason to share a database.

Ruling out SCC as an option

Microsoft SQL Management Studio supports version control for anything that implements the SCC spec. Microsoft only lists VSS (blech) but Google reveals a plethora of options. However, SCC is all about locking - double blech.

Replicating between file systems

The whole question now devolves to one of replicating between file systems. CodePlex contains an implementation for VS2005/SQL2005 but it doesn't work with VS2008/SQL2008.

At this point I think the underpinning question of "how should I go about this" has been satisfactorily addressed, although I'm not sure how to award points.

Thank you to all concerned for your input.

Some concrete questions do arise, mostly to do with how to script out various types of schema object.

• How to extract create and alter scripts in dependency order for
  • table
  • view
  • stored procedure
  • function
  • trigger
  • index
  • foreign key
• How to extract table population scripts in dependency order
• How to efficiently detect changes to the schema (in the absence of triggers on sys.objects it will be necessary to poll; this had better be fast and cheap)

Detecting changes

It has come to my attention that it is possible to bind actions to changes in schema using policies. There remain the questions of dependency ordering and of how to script a table creation statement

Answer 1

We use Red Gate to compare current schema against the scripted files stored in SVN for baselines, versioning etc

However, our master reference is actually a restored copy of production. This is our baseline and should correspond to SVN. It's part of deployment process to commit the master scripts to SVN, which Red Gate does usefully: it only changing the files for changed objects.

We further separate our working scripts and release scripts (changes only), so we always have a master DB and a baseline in SVN. We only use scripts for development though.

Database source control is good stuff, but it's challenging to implement because of the nature of what a SQL Server object is: a row or 3 in some tables...