Need good scheme/workflow for managing database objects using Subversion

Question

How do you track/manage your stored procedures, views, and functions in SQL Server?

I'd like to use Subversion, but it looks like I would have to just save & commit the CREATE/ALTER statements. That might work okay for me, but I suspect I'd end up doing a lot of nagging.

Is anyone using versioning with their databases? Is there a better way?

In the past, people have just commented out parts of the code and left it in. Or, they add little "added on 2/31/2010" comments all over. It drives me nuts, because I know there is a better way.

We do log changes in the object's header, but that's pretty limited. It would make my life easier to be able to diff versions.

Additional Info
We are using SQL Server 2005. I have Subversion (via VisualSVN Server) and TortoiseSVN installed, but I'm open to other suggestions.

By database objects, I specifically mean stored procedures, views, and functions.

There are only a few tables I would need to track. The database is the backend for a commercial application, and we mostly pull information out for reporting

I found a related question about stored procedure versioning

Answer 1

We revision our database, schema creation, dw, etl, stored procedures just like any other piece of code, because it's code!

I have also seen people type dates in headers, etc. This is normally due to them completely missing the point of revision control.

Answer 2

I'm not sure what you all mean with "database objects". Are these only the tables, views, procedures etc or also data? I mean daily created data?

Assumed you mean the database schema definition. By my experience there is only one way to handle database schema definitions (if you don't have NHibernate or some similar tool). You write sql scripts that create your database from scratch and check them in. You use the same scripts for installation of your software. You see the differences by just comparing the scripts files.

Answer 3

We script everything and put it into Subversion. Nothing can be loaded to Prod without a script (developers do not have rights to prod) and the people with rights on prod only accept scripts they loaded from Subversion.

Answer 4

Have a look at liquibase, here

It manages your sql changes/scripts for you, and can apply them in conjunction with svn via hooks or scripts. Makes doing all sorts of setup easy, and helps eliminate the case of the missing trigger/sproc/etc...

Answer 5

Whenever I've gone through this excercise, it's come down to 3 main things that need to be source-controlled:

1. Stored Procedures / Views / Triggers (more or less anything that can be fairly expressed as "code". These are fairly simple, include a conditional drop and create at the top of the file.
2. Table Schema - DROP / CREATE statements as above. You can try to get fancy with ALTER statements, but it tends to get really messy.

The biggest challenge we faced was this forces you into a system where your DB goes back to an initial state often - if there's a fair amount of work involved in bringing DBs to something usable / testable, it can be a pain. In that case we kept a library of scripts that brought a DB to various usable states, and source controlled those as well.

1. Data within tables. We looked at a couple of approaches here - either a series of INSERT statements stored in a file like "TableName_Data.sql" or a CSV file with custom build tooling that parsed and inserted when the DB was rebuilt.

Ultimately we went with the INSERT statements for simplicity's sake.