Database change management and build process using TFS

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

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.

Answer 2

We use TFS with the Database edition.

The database creation script has post scripts to load Dev data into the DB.

We do regular deploys to DEV environment. All developers have SQL installed locally and they do their own Get Latest and Deploy.

In the Unit Test environment, the Logins, Databases (OLTP and OLAP), Replication, ETL packages, SQL Jobs, etc. are all deployed to their individual locations and everything is seeded.

Developers do not make any changes outside and not check them in because then the deployment to Unit Test does not work.

Answer 3

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.

As a temporary measure, here's a clever trigger mechanism to ensure all DB changes are sync'd back to your source control server: http://www.solidrockstable.com/blogs/PragmaticTSQL/Lists/Posts/Post.aspx?ID=33

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 4

Red Gate is currently attempting to solve the database development management 'problem' by integrating source control support to SQL Server Management Studio. The product will be named SQL Source Control. The way this is done is by maintaining a scripted version of the database in your source control system. This is designed to be transparent to the database developers. When developers make a change to their development database (which is linked to the source control database representation) SQL Source Control will detect the change and build up a change list of object modifications that can then be selectively commited to source control when they are deemed 'ready'. Any new changes made to source control (for example by another developer working on the same project) that need to be retrieved can be achieved in a similar manner. SQL Source Control does this by synchronizing from the source control version to the development database. You may have guessed that under the hood SQL Compare's engine is being used! From the developer's point of view all he sees is that there are objects he can commit and objects he can retrieve.

The bad news is that we're only halfway through the project. The good news is that we have an early access program so if you're interested in participating in usability trials or being notified of early builds to try out, you can find out more and sign up from the following link:

http://www.red-gate.com/products/SQL_Source_Control/index.htm

Best wishes,

David Atkinson, Product Manager, Red Gate Software

Answer 5

There is much more opinion on this in this Stack Overflow question: What are the real benefits of Visual Studio Team System Database Edition (GDR)? (I don't know why, but my searches brought me to this question instead, and i have had a lot of trouble finding opinions on this. Hopefully this link will help others performing the same search.)