One of the things I like best about Team Foundation Server is the ability to structure our entire "data warehouse solution" as a TFS Project and to match this structure in Source Control. For us, this includes our Visual Studio Team Edition for Database Professionals (VSTEDP) projects and any other related Visual Studio projects such as SQL Server Integration Services (SSIS) and SQL Server Reporting Services. 

We had done a lot of thinking and research about how best to organize our database solutions in Team Foundation Server and after a lot of trial and error, settled on the structure shown above. We decided to store our entire data warehouse solution in TFS using a "solution centric" model where all the inter-related Visual Studio projects are divided logically by project type since this helps us break down the "solution" into small, more manageable areas of work. We also wanted to be able to branch the entire "data warehouse solution" when we need to deploy a new major version (such as described in our recent case study) but still be able to employ continuous integration practices when adding features or bug fixes to the existing solution. And since we're a pretty small development team, we wanted to do this with a minimum of time and effort.

One of the great things we've found using this "solution centric" approach is that it helps remind us how changes in individual VS projects affect the entire solution. Take for example when we decide to pull some additional information from our ERP system into the data warehouse for an existing report we are working on. This (relatively small) change will require a change to one or more VSTEDP projects, SSIS projects and SQL Reporting Services projects with each change building upon the previous change (DB –> SSIS –> SQL Report). By structuring our TFS Project and Source Control in this manner, we are visually reminded of how all the pieces fit together in our solution and we are able to use the Scenario and Work Item Tracking features of TFS to manage the entire "development workflow" even though it spans multiple VS projects. We can test our changes through each step of the development workflow as well as at the "solution" level before we deploy this to our production servers.

The good news is that this structure works really well for us and is very easy to manage. The bad news is that we tried about ten different structures before settling on this one and all these previous "trials" are still stored (although hidden) in Team Foundation Server. Hopefully when we upgrade to the next release (TFS Orcas) we'll be able to "destroy" all this obsolete stuff and move forward with a less cluttered TFS server.

Lessons I Learned:

  • Structure your TFS Projects and Source Control at a high level to follow your corporate solution or application.
  • Use your TFS Project and Source Control structure to make it easy to manage change because change happens all the time.
  • Setup a "sandbox" instance of TFS where you can try different project and source control structures. Don't do this on your main TFS instance until the Orcas release.

Technorati Tags: Team Foundation Server, Visual Studio Team Edition for Database Professionals

Share this post: Email it! | bookmark it! | digg it! | reddit!| kick it!