Source Control in SQL Server

  • Comments posted here are about the content posted at temp

  • I have found the following significant irritations when using Managmenent studio source control:

    1. It creates 'connection objects' in the project, these can be very confusing and dangerous as they can easily point to your live environment. This means when you open a script in source control you have to carefully check which environment, management studio has decided to open a connection to.

    2. It doesn't sort scripts in alphabetical order?? The fact this feature is missing is extremely aggrevating and is surely very easy to develop.

    3. Getting source safe to create the desired folder structure, is impossible (and if not impossible very difficult). Not sure if this is management studio's fault as VSS is

  • I've always shied away from using any of the source control integration offered by editors/IDEs, instead using the source control client itself for check-outs and check-ins. I can't quite put my finger on why I do it this way, because I definitely like the idea of integration. I think it's because I know that regardless of the application/editor I eventually use to edit my source controlled files, using the same UI for the check-out/in operation is somehow comforting to me - I know that it's something that I've done successfully before, it doesn't involve any more configuring of source control settings, and I have confidence that it will work.

    I'd be curious to know if it's just me, or whether most people actually prefer using the built-in source control integration that various IDEs like SSMS and Visual Studio provide.

    David Atkinson, Red Gate Software

  • pretty bad as well.

    4. Everytime i open a solution, I get a message about solution vs project binding. While this is less important, it reminds me everytime i use 'source control' that this is a shoddy implementation.

  • Hi david,

    I used to use VSS's client directly as you described. I have found that management studio saves a marginal amount of time as i don't have to go in and out of QA & VSS all the time as was the case with sql 2000. SQL 2005 doesn't replace the VSS client completely as I still have to use this to sort out any messes made by management studio & do things like labelling.

  • Hi Noel,

    For me it's a trade off between a "marginal" improvement and the familiarity of the source control client. If I'm going to have to have my source control client open anyhow (which I do), it's quite a trival operation to alt-tab to it and perform whichever operation I need. As you point out, there are some functions that are only available in the client, so I guess you probably have yours open too. If labelling (and the other functions that you can't do in SSMS) were integrated, would that make your life a lot easier? I definitely agree with the principal that the fewer tools that are required to do a job, the better.


  • I agree with Noel wholeheartedly, the lack of alphabetical sorting is extremely annoying and wastes precious time.

    However I much prefer to have source control integrated into the 1 piece of s/w where I do all of my DB development (SSMS), so that's a big plus for me.

    My other bug-bear at the moment is that when initially adding a file to a project it is created as binary so I can't compare different versions, I have to go into source safe -> right click the file -> properties and change binary to text. This would be a useful feature for SSMS too.

    Overall though, it's a good start, but could try harder!

  • Are there any other source control features that would make sense to control from within SSMS?

    Noel mentioned labelling. Would this be something that you would want to do from inside SSMS? Or are some features best left to the source control client?


  • Amongst the other problems that have been mentioned, for me, the biggest problem with VSS integration was something the author alluded to in the conclusion:

    "...whenever a script has been updated, the only way to reflect the changes in the source control is to script the file and update the changes". 

    And vice-versa - when you change your source control file, check it in you then have to remember to apply those changes to the database.

    Which is why I developed an integrated source control app that automatically applies the changes to the SQL Server when a script is checked in (link in sig.  small plug ).  Oh, and the source control database sits on a SQL Server.

    But then, as David indicated, some people shy away from the integrated editors - which I can also relate to.

    So I then thought I'd throw in intelliense - only for Red-Gate to come and steal my thunder.

  • Visual Studio Team Edition for Database Professionals includes improved source control functionality. In addition, you can associate assigned work tasks with source changes.

    I attended a product launch last week during which it was indicated that the Team Edition suite of products is where Microsoft is investing their source control development budget; Visual Source Safe is not being developed anymore.

  • I haven't seen the VS for DB Developers yet, however, I've been using the "Database Projects" of Visual Studio for years to integrate scripts into Source Safe! They've been awesome! I can create any folder structure I want and also it sorts things alphabetically!

    Usually I create one solution then have multiple Database Projects (one for each DB). I can then manage the connections right in the Projects so that the scripts can be executed right from VS to multiple servers. Usually I just delete the pre-canned folder structure and make one like so:





    (Categorized folders of Procs for easier maintenance)







    [misc files like the database script]

    The "_Install" folder contains my batch files that can be used for scripting the entire database or just a subset of changes for a particular release. Since I create "generic" scripts which can be used for the creation or alteration of DB objects, I don't really have to worry about different batch files. Usually when different versioned deployments need sent out, I zip up the batch files and only the affected scripts (maintaining their folder structure) into a backup folder (i.e. Scripts\ This way I can always see what was released per deployment.

    It's been a great help because I can also have Developers edit their scripts themselves, then review the scripts that have changed before deploying them. This way I don't need developers touching the DB all the time of sifting through different DBs or Servers just to find the latest version of a Proc. With the connections being the in the Project, the Developers have rights to script the objects into the Development servers, but only DBAs have rights to script them to the Staging / Prod Servers. So it's nice having everything in one place!

  • Does anyone know of a way to have SQL source control at an object level instead of at a project level? What I really want to see is table X and what the history is on that object. What I understand of VSS is that you have to add a script of the table to a project and that will by under source control. However, if someone else makes a change to the exact same table later, there is no connection to the source control that was made prior in your project.

    Am I misunderstanding this? Is there a better way?


  • Just like using source control elsewhere, if you modify the code outside of source control - there's not going to be any tie-in/history in the Source Control system. That's true here too.

    In this case - I've found that you can get around the human factor by simply putting up barriers to entry. Meaning- if a developer has access to everything, then it's altogether too easy for them to go in and "just update something" without following protocol; if, on the other hand, the only thing they see is their own local DEV instance, and all other environments are "limited permissions" to them, then the only way things gets moved into UAT, and then into Prod, is through checking in code.

    Still - it's ultimately no different from using Source control for .NET code, or any other language. You have to respect the SCC solution's place, or it's worthless.

    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?

  • I agree with Matt. You need rules for Devs and DBAs, backed to some extent by permissions, to ensure that some VCS is used.

  • OK, I think I am definitely confused based on your reply of how SQL source control is working. Let me give a scenario and hopefully then I can understand how this really works:

    Tom - SQL developer

    Sally - SQL developer

    Tom creates a table "CUSTOMER" and adds this script to a project called "CUSTOMERS" and checks this in.

    Sally updates the table "CUSTOMER" and adds this script to her project that has other scripts on it as well for her specific project.

    Looking at Source Safe, first of all there is really no way to tell where table "CUSTOMER" has been modified and secondly which projects have touched "CUSTOMER".

    Are you saying that each table, SP, View, etc. has their own project and when someone works on this object that they check out that project? If so, when a developer is working on a development project that touches multiple tables and SPs, he/she would be checking in potentially many projects into VSS?

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply