Version Control

  • Hi All,

    I was hoping you could share your experiences on the following issue and what strategies you have adopted in the workplace.

    I work as part of a development team and we find ourselves working on multiple servers and instances. Quite often we will be developing different upgrades simultaneously, i.e. one version will be in live, another in test and possibly a further two releases in development.

    We use TFS and regularly deploy new builds on to the array of various servers available to us. The main problem we have is identifying which versions are on what servers.

    Do any of you guys find yourself in a similar scenario and have found an way, (ideally an automated one!) to get round this?

    Thanks Guys

  • Yes, absolutely. Visual Studio Team Edition for Database Professionals, AKA DBPro, AKA DataDude. This tool has turned around our build & deployments. As a matter of fact, I think the article I wrote on multi-environment deployments is coming out tomorrow. It's expensive, but it's been great.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Hi thanks for your reply. This is actually what we currently use - Team Foundation Server we call it!

    I would like to set the db extended property value to something that relates to the release we are deploying automatically?

  • Not a problem. Provide a variable, say the TFS version number or something, in the build command line and set the extended property for the database in a Post-Deployment script.

    Actually, when you're doing incremental deployments from DBPro, it does this for you, although since that's an internally generated number, I doubt it's available to you for documentation:

    IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)

    BEGIN

    DECLARE @stampValue nvarchar(128),

    @stmt nvarchar(4000),

    @parm nvarchar(4000)

    SELECT @stmt = N'USE [$(DatabaseName)] SELECT @valueOUT = CAST(value as nvarchar(128)) FROM ::fn_listextendedproperty(''microsoft_database_tools_deploystamp'', null, null, null, null, null, null)',

    @parm = N'@valueOUT nvarchar(128) OUTPUT'

    EXEC sp_executesql @stmt, @parm, @valueOUT = @stampValue OUTPUT

    IF (@stampValue = CAST(N'c4b9a607-e198-4907-b0e0-edef35034203' AS nvarchar(128)))

    BEGIN

    RAISERROR(N'Deployment has been skipped because the script has already been deployed to the target server.', 16, 100) WITH NOWAIT

    RETURN

    END

    END

    GO

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Also - if you've installed the "original" Team suite, you probably don't have access to the nice new database projects which unlock so much more than the "default" database template.

    In that case (if you don't happen to have a SQL2005 and/or SQL2000 database project type, which allows for all downloading of the entire database schema), you should download and update your VSTS install with this add-on:

    Visual Studio 2005 Team Edition for Database Professionals Add-on for Visual Studio 2005 Team Suite Edition

    ----------------------------------------------------------------------------------
    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?

  • Guys, thanks for your suggestions.

    In reference to Grant's post this solution would still require user intervention to set the variable at each command line, wouldn't it? Or am I missing something.

    I want to try to steer clear of any user intervention wherever possible. Ideally I would want DFS to dynamically recognise which version is being deployed and set the extended properties as such.

    Please ask me for more information if you think it would help in finding a solution?

  • What about adding a statement from TFS using the tf.exe to get the the version of the project, add that to a variable and pass that variable into the command line that does the build & deploy? I'm not sure of the specific syntax to do that, but I think the command prompt properties will work:

    tf properties project /version

    I'd have to do some experimentation to find the precise mechanism to capture it into a variable, but once you have it, you'd just run the build & deployment like this:

    msbuild my.dbproj /t:sqlbuild /p:Configuration="myconfig";TFSVersion=OutputVariable

    msbuild my.dbproj /t:sqldeploy /p:Configuration="myconfig";TFSVersion=OutputVariable

    Then you can set the database property in one of the post-deployment scripts like this:

    exec sys.sp_addextendedproperty @name = N'TFSVersion', @value = $(TFSVersion)

    That will capture and put it into the completion of each build. You may have to adjust for incremental builds (look to see if the property is there first, update it if it is, add it if it isn't). But that should get you what you need.

    All this assumes you're using the full on Team Edition for Database Professionals and not simply adding a database project (two totally different things) into TFS.

    The more I think about this, I may add this to our process...

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant, thanks for your reply I also found your article you posted interesting today.

    I need to read your last post again to make total sense of it as I am fairly new to TFS.

    Thanks again

Viewing 8 posts - 1 through 7 (of 7 total)

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