Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Version Control Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2008 8:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 6:47 AM
Points: 319, Visits: 783
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
Post #446494
Posted Wednesday, January 23, 2008 11:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 14,205, Visits: 28,534
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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #446553
Posted Wednesday, January 23, 2008 12:56 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 6:47 AM
Points: 319, Visits: 783
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?
Post #446602
Posted Wednesday, January 23, 2008 1:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 14,205, Visits: 28,534
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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #446604
Posted Wednesday, January 23, 2008 1:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:20 AM
Points: 7,179, Visits: 15,770
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?
Post #446616
Posted Wednesday, January 23, 2008 3:10 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 6:47 AM
Points: 319, Visits: 783
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?
Post #446672
Posted Thursday, January 24, 2008 6:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 14,205, Visits: 28,534
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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #446872
Posted Thursday, January 24, 2008 7:25 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 6:47 AM
Points: 319, Visits: 783
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
Post #446907
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse