Version Control - Dealing with Code
-
Maybe I'm not such a good writer
. I thought I had a comprehensive article, but maybe not.... oh well.DTS: Desgin the package and choose SAVE AS. There is a type box. Choose Structured Storage File which will save to a physical file. Check that in. there isn't a good way to edit this. I use VSS as a backup only.
Dependent objects - That's why I have an order for object creation. If you have stored procs that depend on others, you have to put them in the 0Upload.cmd script in the right order. Same for data updates. Sorry, no automated process here and not sure if I 'd trust one.
Stored Procedure - Check out to a phsyical file, say c:\SQL\dbo.GetBrands.prc. OPEN this file in QA and make changes, etc. compile it AND save it back to disk. Check it back in. Version controlled! Don't use EM, you are then not working with the version checked out, instead, you are working with the version from the server. Not a substantial difference, but this prevents confusion (for me).
Table:
Make changes in EM. Save scripted ALTER file. This is checked in as (for me) tablename_alter_somenote.sql. I run this to make the change to servers.
If I now add another new field, same process, but saved as tablename_alter_somenewnote.sql. All alters are separate scripts. Not reused. This is why I go back and then change the original table script tablename.sql, either regen or manually change.
I either manually make all changes for changed objects (as in stored proc) or let EM generate the script for changes (like alter table), but these are then checked in. I use QA for most everything. Rarely use EM (DTS, alters, jobs is about it).
An Alter script for a table is not equivalent to the create table. Same for a procedure, however with procedures, I rebuild the proc without really causing problems. I can't rebuild the table, so I have a separate script in VSS.
Hope this makes sense.
Steve Jones
-
I think you write just fine... I am simply a very detail oriented person and try to not leave any guess work when I write technical documents... only because I tend to ask myself "Can someone interpret this wrong or am I assuming someone would already know this". My only suggestion would be maybe to add to your original document some of the things I am asking as there may be others who would like to know more specifics to what you do.
By the way, thanks for the reminder on getting the DTS to a file... I can't believe I forgot that. I knew I did it a while back and just had a brain fart.
I see the reason why you use QA mostly... and that was one reason I was wanting to know if you include all the dependent objects when creating the scripts as well as the drop.
For example if I script a Stored Procedure that uses another SP or a View or a table then the script will generate drops and creates not only for the other SP, but also for the View and the Table that view uses and any other tables that are in there as part of joins within the view. Hence the question about including dependant objects in the script generation. My answer would be do not include them, but as you said make sure the ordering is correct in the cmd file used to create everything in the QA or production environment.
Am I correct in my assesment? Do not generate scripts to include dependents?
Again you have written a very good article... I just tend to think at the lowest common denominator because I don't know if the audience level (novice, advanced, or expert). Yes I could have made all the decisions to what I have asked you, but I like to understand the full intent of an author as opposed to making certain assumptions.
Thanks again for your time and the article.
-
Test... couldn't see my last post on the 2nd page of of posts... so placing another post to see if it will make it show up.
-
Thanks for the feedback and I may update the article with some of these items (or write a more detailed one).
You are correct in your assumptions. I manually order the items. If there is a dependent object, like a stored proc called from the one I am editing, I am either changing that object, or I don't worry about it. Since the code tells me what needs to change (as far as dependencies go), I can also move those to the release project at the same time.
The key for me has been more of an organizing problem than a technical one and there are some items, like data inserts, that can't be solved in an automated fashion. I wish QA could use version control, hopefully like I've setup my VSS, but I think using EM has been one of the biggest problems in every job I've had with tracking changes.
Recently we had a developer here that would make a change in EM, then use CFStudio to checkout the SQL script, make the change, and then check it in. The problem was he sometimes didn't make the same change or had a typo. I quickly caught this in QA and explained why this is not a good method. If he had scripted the changes from EM and saved this in VSS, that would work, but it deviates from the standard I use to develop items (as far as formatting goes).
I think we are on the same track as far as how this works. One reason I avoid scripting is that it does sometimes script other drops /creates that I do not want or need.
This has worked (as I said before) for a couple years though the biggest group I had was 3 dbas. I suspect it would still scale beyond that. And is not limited to VSS. It will work with PVCS, CVS, etc.
Steve Jones
-
Thanks for your time and input!
-
-
quote:
Yes and no. I used a third party tool for developing some T-SQL that claimed VSS integration. I could not get it to work correctly and neither could their tech support. I also investigated the integration in Visual Interdev v6.0, but I HIGHLY recommend against using this. Perhaps when the next version of SQL Server comes out and T-SQL becomes a .NET language, this will change.Although you recommend against doing this in your article, can you outline how the SQL Server/SourceSafe "integration" works? I work for someone who wants to try it, but I cannot find any complete documentation for configuring it. The closest documentation I found is "Versioning Stored Procedures with Visual Studio 6.0 and SQL Server 7.0" in Visual Studio 6.0 help, but it jumps mid-stream to an "Enable Source Control" window without saying how to find that window in the software.
Also, what are the gotchas with SQL Server/SourceSafe integration?
Thanks in advance. Any help is appreciated!
-
Boy it's been a long time since I did any Visual Studio integration with SQL Server. As far as I remember, it was only for stored procedures.
Other than that, there is not integration with VSS from SQL. Bill Wunder wrote a utility to move your schema to VSS, but I've always had better luck with tracking the changes using DBA scripting (looking for schema changes) and manually ensuring everything is in VSS.
I suspect this will change at some point, but the process outlined in these 4 articles worked at two jobs for over 150 releases of new code.
The biggest gotchas I have seen deal with developers or DBAs making changes from Enterprise Manager or even QA these days without following a checkout/checkin mentality. These are the little things that get you. Also there is no "Modified" date on the tables and views, so if you alter them, you need to keep track of this for your releases.
Steve Jones
-
Steve, an interesting article.
I generally follow a similar pattern to yours.
One thing I notice you haven't mentioned is labelling. I view this as key to successfully managing a source control environment, since it lets you verify that no-one has modified scripts between the release to testing & release to production.
Liberal use of labels when applied to the main development tree lets you version this without recourse to sharing files into other projects.
I do use the shared file/separate project as well (and label these independently); files can be branched off & merged back to the main tree as required.
-
Not sure what you mean as far as labeling. When I branch the project, there is only a single version of each file, so we can tell if anyone has changed things. If I am on version 12 of a stored proc, it gets branched, not shared when we go to QA. The branch has a single version. If we find bugs and need to modify the procedure, we would do so in the original tree, making it v13, then delete the branch and re-branch v13. When we go to production, the branched code consists of a single version of each file.
Steve Jones
-
Steve,
I use a system that is generally consistent with your approach and I've found it to be very workable. I don't like Query Analyzer as an editor so I use Eclipse as my coding environment. My company had no requirement for source control so I set up CVS which is integrated into Eclipse nicely. I use a folder structure similar to that which you describe and always work from the source repository. For compilation of code, Eclipse supports external tools so I set up tool entries using osql. I can, from the editor window, compile into my dev database (databases, if for some reason there is more than one). I like the concept of separating the code base from a specific server/database.
One little technique that I find helpful with procs and functions is to embed, in a comment section, CVS tags that are expanded on commit. I can always see, in the database, which version of a proc or function is in use. VSS also supports expansion of tags.
Good article.
-
Steve,
Have you thought of updating the article for use with .NET and Visual Studio .NET. I find that the tools in VS.NET handle about everything I need to edit and update the SQL Server database. By creating a Database project and putting all my scripts and procs there, it is easy to use SourceSafe in the VS environment. What I would like to do is to turn off permissions for all developers to directly edit anything in SQL Server, but only allow the VS.NET process to make changes. That way I can enforce the SourceSafe environment. Is there a way to do this?
In VS 2005, this is even more tightly integrated and is worthy of an article rewrite.
Dave
-
For Version Control I Use Subversion and Imceda's New Speed SQL IDE.
It it very nicely intergrated with most Version Control Software and relatialve inexpensive. Its a life saver.
VS is very expensive. Subversion is Free
Len
-
Steve,
one question I have regards the scripts to upgrade production DBs. I have worked on DBs where I tend to completely rebuild the DB for unit testing and QA, but which instead can only be upgraded for the production DB. In that case I have found that I need a "ProductionDB Scripts" folder in VSS, where I store only the relevant production scripts.
Given that no such folder appears in your article, I was wondering how you handle the above problem.
Mauro
P.S. I too would second the request for a rewrite in view of VS2005 and SQL Yukon
-
As far as environments, it's interesting that you do not mention a Staging environment. It's tough to go straight from QA/Test right into Production. In my last 3 software development companies, we've have always employed a Staging environment which is always exactly identical to what the customers have. The QA/Test environment cannot always be exactly what the customers have because you are moving new code changes into QA/Test at times (hopefully as you unit test & are ready to deliver certain pieces of functionality and not just a major releases). As smaller version of code are delivered to the QA environment, it's tough to keep track of exactly what the customer has unless you have a Staging environment.
-Vic
. I thought I had a comprehensive article, but maybe not.... oh well.Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply