Database change management and build process using TFS [closed]

Question

Anybody using Team Foundation Server for managing their databases? We are currently using subversion. Team is complaining that it is hard to create a build process in TFS and is shying away from it.

Any good pointers, articles, experiences?

Answer 1

DB change management doesn't have much to do with your choice of version control systems, so long as you have one in the first place. Of course, if you are using change management tools from MS you can be quite sure that they've been tested against TFS and the rest of the MS developer stack quite well. That's true whether you use DBPro or the far older/crappier forms of integration seen in the classic VS "database project" or in SQL Management Studio's stripped down project/solution bindings. But there's no reason you can't use DBPro with Subversion, or Red Gate with TFS for that matter.

Same goes for build generation. CC.NET vs Team Build, NAnt vs MSBuild, etc...the official MS tools tend to be roughly on par with the competition. You haven't described your DB deployment process in much detail but I can't fathom it would be significantly harder to script in MSBuild than what you're using now, if at all. Nor is it hard to choose different toolsets at different points in the stack: you can have CC.NET drive MSBuild-based builds which use Red Gate's command line deploy, or any other combination. I happen to think the tight integration offered by sticking to the MS world far outweighs the quirks in any one tool, but the choice is there.

Let me get to the point: it sounds like your main problem is not technical, but getting DBAs to actually adopt version control in the first place. If your "dev" and "prod" environments are their own living entities, rather than generic machines defined exclusively by the result of some repeatable build process, then you're not really using version control in my book. Imagine if a client developer occasionally hand-tweaked the DLLs on various machines around the company, then complained that they were too hard to synchronize; you'd think he was crazy.

Beyond that, the most important investment is getting to a place where nothing is ever done to a DB directly (any more than you'd poke around in %programfiles%). If it's not in the source repository, it doesn't exist.

I don't think how you get there is as important. You could write all of your CREATEs and ALTERs in Notepad, check them in from the command line, and have your "build process" be a 2-line shell script that concatenates them into a well-known file where the deploy script knows to look. Or you could use a fancy tool like DBPro to amplify your productivity with intellisense / unit testing / offline modeling / etc. There are great reasons to head in the latter direction (especially if you believe declarative programming is something to strive for in general), but I really believe the first step is the biggest.

Answer 2

We're using Visual Studio 2008 Team Suite along with TFS. I was able to import our databases into TFS with relative ease. However, I've found that most of the team (including the DBAs) forgets to update TFS when they modify an object in SQL.

Any kind of build process is going to rely on DB Pro to generate difference scripts between your dev environment and the target environment. I've found that this is problematic because our dev environment is not a clean match to our production environment. Permissions are certainly different and we have a number of other cases where changes were applied in dev/QA but never moved out to prod (but were also never reversed). Trying to isolate your changes from alot of other changes in DB Pro is challenging because it the UI makes you exclude objects from the final script (so if you modify 2 objects and 1000 others are different, you have to uncheck the other 1000 objects). In addition, configuration of the schema comparison is often done in tools->options whereas other tools like Red Gate allow you to configure the comparison on the same screen that you start it.

I think the tool has potential but we certainly need to adapt our existing procedures and systems to work with TFS. In addition, having versioning on your database objects is invaluable, even if it's not 100% up to date.