SQL Scripts (SProcs & Tables) and TFS/Source Safe

[techgnome]

Does any one here use source control (of any kind) for their SQL Scripts? At my last shop, if you needed to change a SProc, you checked it out... made your modifications, ran it against the dev DB, and when done, checked it in. Here, when I asked about version controlling the sprocs, I was told "Yep... when it goes into production, the whole DB gets versioned." .... yipes.... mean while, the system I'm working on has never gong to production, so there is no version history for it (we do use TFS for the code bits.... just not the SQL end of things (????)) so if I need to revert back a sproc in dev or in the test environs, I'm outta luck. The issue came up again today as we discussed getting closer to doing fully automated build and clickonce deployments - we can get it to build the projects and deploy them to CO just fine for the different environs we have (there's about 12 or so).... BUT, there isn't a way to automate the SQL changes. I think it would be possible IF we had the SQL Scripts in TFS.... then we could create a custom component that would grab the scripts, and run them against the proper database(s)....

So my question is, how do other shops handle this? Do you version control your SQL scripts? If so, how do you manage it? If not, why not? Pros? Cons? Stories from the trenches? Lessons learned? Things you wished you had done but didn't? Things you DID you wished you HADN'T?

-tg

[GaryMazzone]

We currently use AccuRev for source control at my current job. All scripts are kept here for the database (create db,create table, alter tables,sp, indexes). Last job (and the one before that) we used Source Safe to store. I like the versioning and comparison to older versions in both products. (AccuRev is a little less user friendly).

[mendhak]

We're scarily similar to your last shop - the SPs/Views are in TFS, modify them, check in, then the DBAs get it from TFS when it's time to go live.

Not foolproof though, there's always the temptation to change some script directly in TFS without running it against the database, if there's a last minute change required. But you're right - I can revert back to previous versions if a noob on my team decides to mess things up for everyone else.

But at least you've got some reliability. You can see who did what change for what reason, make more changes, and so on, and finding problems is easier simply based on doing a DIFF.

I'd like better integration between SSMS and VSS, because you are always going to make a mistake in TFS at some point, it is bound to happen; when an SP is modified in SSMS, it should do the VSS prompt for checkout - hey, it might already exist, but I haven't seen it being used anywhere.

[szlamany]

Our whole shop has always used VSS to share all our development folders.

Everyone has a "GET RECENT COPY" of all files - folders with SPROCS - folders with VB6 apps - folders with .Net apps - Winform/Webform - even our own silly website...

We VSS everything.

If I've got 3 people working actively on a project then we all "check in" all our stuff each afternoon - and then all "GET RECENT COPY" when we start our day.

It facilitates code review by the team to be able to go into VSS and compare code changes from day to day.

And it really facilitates ripping code from other sprocs or apps.

I've never used VSS from the IDE of anything - VB6, .Net - we always use the real VSS client (old v6 one actually - lowest common denominator runs on all our boxes in the office).

[Gambit001]

in the last place i worked we done somehting similar where the scripts where in sourcesafe you checked it out, made your changes & run it, checked it back in when fully compiles

main issue i always found though was a way to stop developers making changes on the fly directly on the database sp's - but then you have to go down the user & permissions etc

the visual studio team for database developers has DB projects templates which you can link into sourcesafe for both oracle & sql. i had started looking at these and they looked very good but then i left the company!