Version Control - Dealing with Code
-
Good point! And in relation to my observation the Staging environment is where we would test the production scripts - except that most of the time we haven't got the luxury of yet another server.....(;-)
-
You stole my idea David!
I use VS .NET database projects a lot. I even keep a file for the stored procedures I generate with CodeSmith. I also keep a command/batch file in the project that will run all the scripts in order right from VS. If you use VS to auto-generate the command file make sure you add the -n switch to all the osql commands so it doesn't fill you screen with that awful osql line numbering garbage! 
I do still use Query Analyzer to modify the scripts since you can't beat the graphica query plans in QA but I like to keep all the database stuff in the same solution as the rest of the application.
You can't set permissions like you're talking about since it would disable the developers from updating the objects entirely but I have heard of ways to defeat the capabilities of Enterprise Manager by restricting access to certain stored procedures so that EM will not function. I don't think EM is a real good developer tool anyway so this might work for you. This still doesn't prevent developers from using other tools such as QA. Besides, if your developers are not professional enough to use proper tools maybe they need to be replaced.
Some flexibility needs to be available but source control is not optional.[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
Visual Studio 2003 comes stock with a type of project called, "Database project." It allows you to create, edit, delete and deploy TSQL to any server you want fairly easily. The real advantage however, is that since all of this is happening as a "Visual Studio" project, it integrates with source control just like any other c# or vb.net project. Now, certain providers like VSS do store the TSQL internally as a binary file, but as long as you just use Studio to manage those files, you are ok. Other source control providers may handle that in a more desirable way.
But I just wanted to say - if you need Source Control integration with SQL - try the Visual Studio "Database Project" and see if it covers your needs - it has served me well.
Erik Lindeblom
Information Technology Application Specialist
State of Washington
-
Tom Rizzo has a new blog related to this topic http://sqljunkies.com/WebLog/tom%20rizzo/archive/2005/03/09/8704.aspx
He claims that SQL Server 2005 Management Studio allows source control, but he does not expound much on it. I would like to see an article on this--specifically, I want to know how to enforce source control, i.e. nobody can make any changes to structure or sprocs without going through source control first. Maybe I wll have to dig in and write it myself!
Dave
-
I agree, this is an excellent article. I hesitate to use it, though. My main concern is emergency/rapid rollbacks while I am away from my desk. If I'm at home and my PC at work is off, all I would have access to are the Microsoft servers themselves. If I need to roll back to a previous version, I would be stuck since it would need to be checked out -- and my computer is off. So what we do here is similar but without the VSS. On one of the servers, we have a directory holding all of our scripts. We organize it very similarly to what's in the article. However, they're just .sql files and not checked in (obviously). When I make a change, I first make a backup of the file in a subdirectory (Backup\spProcName_MM-DD-YYYY). Then I comment the code, make the changes, comment out the old stuff, etc. Then I save and run the script. This works for us since we're supporting our in-house systems and not doing software development for public release.
I know this is imperfect but it gives us the peace of mind knowing that we can access the scripts from anywhere on the Microsoft network (as opposed to our normal Novell network) and not be reliant on an extra layer (VSS) being available.
-
VSS has a feature called Shadow Folders that might help out with what you're doing Brian. This feature allows you to set a folder on the network where files are automatically copied to when they are checked in. This way, everyone can go to the "live" location (the shadow folder) to use the scripts but if anyone needs to edit them, they check out the file to get a local copy, make the changes, and the server copy is updated automatically. This also helps prevent two people from modifying a script at the same time or at least helps with the merge if multiple check-outs is enabled.
We use shadow folders for this very purpose where I am working now and it works great.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
Fantastic! I had no idea. Sounds great. I really need to get to know VSS, since I really know very little about it. Thanks, Bryant.
-
VSS is definitely not the best source code control tool out there but it's the one I am most experienced with so I tend to favor it.
Here is a link to Microsoft's VSS documentation on MSDN: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvss/html/vssbest.asp
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
I agree with the principal in having all your source code in a source control, however I think you have made it alot more difficult than it needs to be. Have a look at the tool DB Ghost it can save you a whole load of time and effort.
-
DBGhost looks like a fine tool but I don't think it replaces source control. This is especially true if you're writing packaged software. I think DBGhost would be great for generating a final database upgrade script for a software upgrade. This doesn't provide the other benefits of source control though. If you're half-way through a feature implementation and the feature gets pulled due to slippage, how do you roll back the code changes for that feature? If you have source control in place, it can handle this type of situation. Source control is not about the end product as much as it is about smoothing out the development process.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
you should look a little closer, it works with your source control
-
I stand corrected. Thanks for the clarification. This page spells it out pretty clearly: http://www.innovartis.co.uk/DBGhostProcess.aspx
I made the download but I haven't found any pricing information. Do know if they sell it direct or do I have to go through a distributor?
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
Hi Steve,
thanks for your articles. For all three on version control.
PLease describe in detail how to:
1. set VSS to remove all local versions of files whenever it checks a new version in.
2. Use the search features of VSS
thanks
Regards
-
nice article - have you tried Imceda Speed IDE (it used to be msSQLXpress ) - its an AWESOME tool, fully integrated with vss - and its editor has inteli-sense -- its own history db, its like a better QA with many Enterprise Manager items built in....
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply