December 8, 2008 at 11:23 am
(Sorry if this is a bit of a cross post - my earlier post got hardly a response so I think it was in the wrong place because this seems like an important and common topic).
We're new to version control, using TFS and VSS. I know we can keep our various sql object SCRIPTS in source control. But what about the physical tables, sprocs, etc.? Do DBAs usually maintain separate releases of a db itself to match the various branches (in our case releases) of the application source code?
TIA
December 8, 2008 at 12:16 pm
Well, We version every object that goes into production DB (That is the script to screate the object). But we do not keep version fo the physical table itself.
-Roy
December 8, 2008 at 12:45 pm
So what do you do when versions of, for ex a SPROC, conflict? Let's say V1 of your app is in production. V2 is in user testing. Now a bug is found in V1. So your developers create V1.1 of let's say SP_ABC. Where would that be tested? If you run the V1.1 change script for SP_ABC in the db being used for V2 user testing you screw up user testing for V2.
December 8, 2008 at 12:50 pm
you handle this in two ways.
1. You add the fix to v2 and either accelerate testing, or they wait for it to finish.
2. You make a change to v1, test that, and then deploy it. Whoever makes the change is also responsible for then updating v2 with the change.
There isn't a foolproof way to do this.
December 8, 2008 at 12:53 pm
When ever you change any Sproc or any object, it is done in a Branch. The Sproc will be promoted to the mainline only when they are released. Till then the first version will stay. As long as you promote the file to the main line at the right moment you have control of the Versions and you will not mess up anyones version.
If the same SProc is to be modified by two different person or team, each person/team will have their own Branch and working directory.
I hope that answers your question.
PS : I forgot to mention that just before you promote, you are supposed to rebase and Merge your project.
-Roy
December 8, 2008 at 1:40 pm
Roy Ernest (12/8/2008)
When ever you change any Sproc or any object, it is done in a Branch.
"In a branch" means a branch inside the source control system that's holding the script. What about the physical db that's holding the SPROC? Do you create branches for the physical table/sprocs too? Seems to me that's the only way to go unless you want to just wing it like the post above suggests.
I'm new to this, and I'm really surprised how MUCH info there is on version control for source code files INSIDE the source control tool, but nothing on the actual physical implementations. Same goes for web files, like aspx/vb files.
December 8, 2008 at 1:49 pm
In Your DB you can have only one version of an Object.
You have been talking about the versioning the Physical tables. Do you mean with the Data inside you are keeping versions? How big is your database?
-Roy
December 8, 2008 at 2:27 pm
I mean versions of the Db. On my test server (and dev server too) where we do user testing we've always had a single db named ABC. Inside that we have all the tables, sprocs, etc for all our apps (well many). There's no version control. We just yell over the cube wall about changes being made to a sproc and hope it doesn't break something. That's before doing source control.
Now we're going to do version control on our "big" app. So I'm thinking to do it right, I need to create a separate db just for this app, call it BIG. I'll have one db for each version of the web application it supports. So I might have a db called BIG for the current version that's also deployed in Prod. Then when version 2 of the web app gets under way, I'll create another new db called BIG_V2. (So I've "branched" the physical db to mirror the branch in the source control for the web apps code/html/aspx etc.). If a bug is found in V1 (BIG) developers can change a SPROC, test it in BIG, then I can "merge" it into BIG_V2.
Make sense? And it's THIS kinda thing I don't see discussed much, only the sort of theoretical stuff about branching/merging etc. of scripts and source files in TFS or VSS.
December 9, 2008 at 5:58 am
Hi ,
let us say that you have a Sproc 'x'. This was later on identified as a bug. The developers are actually working on another version of the software. If you have proper set up, You will always have a seperate environment just for this new version. You will also have a Replica of production for Support and Bug fixes. When you do identify a bug in your orginal Stored Proc, you will have to get fix and test it out on the production replica. Once you know it is fixed, you will send out a Bug fix (Request for change or Hot fix).
You then promote your Bug fix to your Source Controls Mainline and version it as you prefer.
When the new version is to be released, You will be Merging the new versions code base with the MainLine code base. At that time, the change you made will also get implimented in the new version.
This is how we do it. Every company has their own way of handling Source Code. Which ever is convenient and practical for your way of working is how you should approach these kind of issues. The plan we have might not work for your company.
Roy
-Roy
December 9, 2008 at 7:56 am
Roy, when you say promote and merge into the mainline, you really mean two developers communicating, correct? Someone has to physically look at the patch that occurred, and then type or copy that code by hand into the new version of the proc they're working on.
December 9, 2008 at 8:08 am
Steve, The Source Control that we have can actually Merge by itself. When you rebase the Braches, it will make sure to make the additional Changes needed (Add code or make sure that the new version has the latest file or changes made).
If more than one programmer has changed the code to the same Proc, then the Source control software asks you to manully Merge the changes and will guide you through the Merge process.
-Roy
December 9, 2008 at 9:55 am
Does your source control merge the changes into the physical SPROC or just into the scripts that can generate the physical SPROC? My initial issue was really one about the difference between versioning all the code, whether for the web app, the sql scripts, etc. versus versioning the physical things that all the code corresponds to. Seems like the only way to go is to have separate setups for each and every release, in each and every environment. But that seems like overkill, even on a big app.
December 9, 2008 at 10:07 am
Steve Jones - Editor (12/9/2008)
Roy, when you say promote and merge into the mainline, you really mean two developers communicating, correct? Someone has to physically look at the patch that occurred, and then type or copy that code by hand into the new version of the proc they're working on.
It's actually part of the version control and/or deployment process.
for example:
- We have 4 code branches in TFS (DEV/QA/MO/MAIN), which match up to 4 physical environments.
- You check code out in Dev, work on it until it's ready for QA. At that point, you check it back in to source control and "promote" the Dev code for the objects you changed (tables procs and all) from the Dev branch to the QA branch.
- Part of the promotion might involve a concept called "mergiing" (to insure that there are no changes you might be overwriting).
- Source control programs that understand branching will have a built-in conflict resolution process. For example - TFS has the ability to tell whether the QA version of a stored proc might have been changed AFTER it got to QA, and would then pop up a window with both versions of the code, forcing you to resolve the conflict. The differences are highlighted for you.
- If there's no conflict, no window - the new code gets promoted in.
So - yes, someone usually has to review, but it's not exactly manual.
There are lots of merge tools out there, and a fair amount of very good ones are freeware or shareware (so not budget busters).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 9, 2008 at 11:01 am
Matt Miller (12/9/2008)
We have 4 code branches in TFS (DEV/QA/MO/MAIN), which match up to 4 physical environments.
What do you do with different releases then? For ex, TFS/DEV/Release1, TFS/DEV/Release2 etc. Do you have corresponding physical environments (or sql dbs within a server) for each release, for ex. SQLSvr1/DB_Release1/TableA, SQLSvr1/DB_Release2/TableA and so on?
December 9, 2008 at 11:01 am
Matt, You explained it better than me. 🙂 I always knew that I did not have the ability to explain what is in my mind properly. Now you have prooved it true..:-)
-Roy
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply