Version control for stored procs?

[LNBruno]

One of the requirements for my current assignment is that all development be set up in a version control software package (ClearCase, not that it really matters). I've been told this is to include stored procedures (???).

This is a first for me. I've always relied on nightly backups, but that didn't satisfy the Project Manager.

Has anyone run into this and, if so, how did you implement since SQL Server doesn't really lend itself to version control?

TIA.

< M!ke >

 

[mrdenny]

Do all version control out side of the SQL Server, then deploy changes into the database keeping the history in the database.

There is no way within SQL Server to guarantee that it's being done this way. It's got to be a policy thing.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

You might want to check out SQL Change Manager by Idera. I have to keep 4 versions of each database and this application works very well for that. It can also compare two databases at the object level and promote or revert the code.

Good Luck.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLSister]

We use Source Save to do version control and if a particular change is not in Source Control, it gets overwritten by what is in Source Control on a regular basis. This keeps people honest about doing changes in Source COntrol and not through Enterprise manager.

Another tact to tack is to limit access to production to only dba personnel who only load to production from scripts created in the source control. If you didn't use source control for your change, it wouldn't ever end up in production that way. This still relies o nthe dba not to get in a hurry and make a non-sourced change, but it's an imperfect universe.

Questions about posting. See faq183-874

 

[SQLDenis]

>>Has anyone run into this and, if so, how did you implement since SQL Server doesn't really lend itself to version control?

I use VSS and Subversion for source control


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[LNBruno]

Thanks, all.

Failed to mention this is SQL 2K.

Mr Denny & SQL Sister each hit a nail on the head: "a policy thing" and "keeps people honest."

Who was it said "Honesty is not our policy"? As long as changes can be made directly in EM or QA....

I'm sure there will be iteratively redundant complaints over the next six months from the dev team, but I didn't think there wasn't a pretty way to accomplish this. Guess I'll just publish an update schedule (from source control to database) and issue warning e-mails the day before...

Thanks, again.


< M!ke >

 

[SQLSister]

We don't even warn people. If they lose something they were supposed to have in source control, then it is up to them to find and fix the problem with no bitching and moaning since they were in the wrong to begin with. The best thing is to be ruthless about this from the start and update from source control fairly soon after you implement to make the point stick. If you let them get away with not doing it the first time and them not having to fix their own mess, then they will never follow the policy. If they get burned and have to explain to the boss why something that used to work doesn't work because they didn't put it into source control and it got overwritten, well they will learn to do it real fast.
(for your business sake, you might refresh dev first from source control to make the point rather than go straight to prod)

Questions about posting. See faq183-874

 

[Catadmin]

We are required to do everything is Visual Source Safe (VSS) before it goes to the database. If the developers don't put it in VSS, the DBAs don't run it against the database. Everything has to be saved there because of SOX and because of our SDLC procedures.

Since VSS is a Microsoft Tool, it makes it a little easier.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"