Getting sourcecontrol on stored procedures

Question

First off the technical stuff:

We're using VS 2008 pro and running a MS SQL 2008 server. For sourcecontrol we use Subversion.

We'd really like to keep our stored procedures in subversion, so we can tell what was changed, when and so forth.

However in order for this to work, it has to be seamless, otherwise the developers are just going to forget getting the procedures included in their commits.

So are there any good solutions for this ?

I'm certain we can't be the only company with this issue :-)

Answer 1

In our company, we use Team foundation server for Visual Studio team system to keep track of code sources and stored procedures versioning; stored procedures are handled just like any other source of code.

Database is not automagically syncronized to TFS; so, for every new SP you need to:

1. Create it
2. Add it to TFS
3. Check in
4. Add to Database

if you need to modify :

1. Check out on TFS
2. Modify source
3. Check in on TFS
4. Modify on Database

When we need to commit to production, we formally ask dba to synchronize the new/modified Stored Procedure to our production DB downloading and applying the latest version from TFS.

Possible problem scenario:

1. Lazy developers (like me) modify SP directly on Sql server and don't align code on TFS
2. Crazy developers (like me) forget to Checkin the latest fixed version and Dba align the bugged one in production

Answer 2

In Visual Studio 2008, you can create a database project which will allow you to store all of your Stored Procedures and any other SQL statements within a nice project, which can then be housed in Subversion.

Answer 3

If you are following an agile method, I suggest that you add "Dont forget to commit SPs" to your Done, Done definition so it becomes a natural part of your process.

Answer 4

I'm very fond of Visual Studio Team Edition for Database Professionals which is now free to use if you have Visual Studio Team Edition for Developers. It will allow you to work with your database objects in an offline model which can be checked in to source control. The offline model is then used to automatically generate deploy scripts to the database.

The only downside is the price.

Answer 5

We have a program that makes any needed database versioning changes when program is installed/upgraded.

So in order for any SQL code being deployed, it must be included in that program (as resources in our case). This enables automatic versioning & adding of code to VS supported source control system.

See more in-depth description of our system behind the link.

Answer 6

I'm currently using a free tool called SQL Source Control from Red-Gate to do this and it works great, it plugs into SSMS. Here is a link with more info http://www.red-gate.com/products/SQL_Source_Control/index.htm I have to mention this is currently in beta but works great with SVN and TFS.