TFS… introduction to this series
Microsoft TFS (Team Foundation Server… recently renamed to ALM: Application Lifecycle Management) is a great project management platform.
TFS is built on meta-model abstractions (that were first seen, in Microsoft's products, in SharePoint).
TFS, like SharePoint, is composed of several software components which operate around several databases: the configuration database and one or more content databases.
Configuration and content databases' elements (tables, views, functions, stored procedures… etc.) represent the meta-models (schemas and business logic for each related feature) which govern and control much of the software components' behaviors.
TFS is often presented as a software project management solution. In my view, this seems a little reductive of its value. Because TFS features truly span a much larger project management scope.
Many useful literatures have been written about TFS: its features, usage, guidance, troubleshooting… etc. Few have enlightened its meta-model structures and, as of my knowledge, none has put some clear light about its (undocumented) databases' structures. That is what I will try to do in this series, more to value TFS abstractions than to nude its internal mechanics. And also to enforce, again, the meta-model approach as a rational basis for software solutions.
TFS logical architecture
For its contents, TFS maintains a database per project-collection.
Each collection may contain one or more projects. And each project can be managed according to a selected methodology (Agile / Scrum / CMMI… etc.). In fact, TFS applies and operates the selected methodology through the provided methodology project management template. Ultimately, this means you can build your own methodology and use it to manage your project with TFS. All what you need is to provide the desired methodology template.
Managing a project with TFS consists of recording and following-up specific Work items (defined by the methodology template)… those can be items like: Tasks, Bugs, Change requests, User story… etc.
As TFS was initially brewed to manage software projects, a common item in each project is the Source control repository which helps developers to maintain their source code versions and branches in a very efficient architecture composed of 'change sets'.
To discover and figure out its components' data and their required behaviors, TFS uses a configuration database. A first look at the configuration database will be the subject of this first article in the series.
The configuration database
Like SharePoint, TFS databases are delivered without documentation or relational diagrams. They simply don't contain any explicit Foreign-keys or relationships. Whatever the reason behind this choice, that seems a little surprising of a product whose main aim is projects' transparency and documentationJ.
That said, to discover, understand and correctly interpret the role of each TFS database item and their inter-relationships you should do a deep dive and be armed with some patience and empiric methods!
Hosts and processes
Let's have a look at three important tables:
- tbl_ServiceHost contains the list of services' host information. This includes the item's name, the database connection string (when relevant), the Virtual directory and Resource directory…
- tbl_ServiceHostProcess contains information about current processes. Machine name, Process name, Identity under which the process is running, start time…
- tbl_ServiceHostInstance contains the list of processes' instances per host (Process Id / Host Id / Start time).
In my case, the table tbl_ServiceHost contained 3 entries for:
The above relational schema allows us to query the tables and obtain results like the following about current running services:
SELECT TOP (100) PERCENT
svcHost.Name AS host,
svcHost.VirtualDirectory,
svcHost.ConnectionString,
process.MachineName AS machine,
process.ProcessName,
process.StartTime
FROM dbo.tbl_ServiceHostInstance AS instance INNER JOIN
dbo.tbl_ServiceHost AS svcHost ON instance.HostId = svcHost.HostId LEFT OUTER JOIN
dbo.tbl_ServiceHostProcess AS process ON instance.ProcessId = process.ProcessId LEFT OUTER JOIN
dbo.tbl_ServiceHost AS hostParent ON svcHost.ParentHostId = hostParent.HostId
ORDER BY process.StartTime DESC, hostParent.Name, host
|
Sample results
|
host
|
VirtualDirectory
|
ConnectionString
|
machine
|
ProcessName
|
StartTime
|
|
TEAM FOUNDATION
|
NULL
|
Data Source= hp10\SqlTfs;Initial Catalog=Tfs_Configuration;Integrated Security=True
|
HP10
|
w3wp.exe
|
2012-05-21 20:18:09.997
|
|
DefaultCollection
|
~/DefaultCollection/
|
Data Source=hp10\sqltfs;Initial Catalog=tfs_defaultcollection;Integrated Security=True
|
HP10
|
w3wp.exe
|
2012-05-21 20:18:09.997
|
|
Training collection
|
~/Training collection/
|
Data Source=hp10\SqlTfs;Initial Catalog="Tfs_Training collection";Integrated Security=True
|
HP10
|
w3wp.exe
|
2012-05-21 20:18:09.997
|
|
TEAM FOUNDATION
|
NULL
|
Data Source=hp10\SqlTfs;Initial Catalog=Tfs_Configuration;Integrated Security=True
|
HP10
|
TfsJobAgent.exe
|
2012-05-21 06:51:37.870
|
|
DefaultCollection
|
~/DefaultCollection/
|
Data Source=hp10\sqltfs;Initial Catalog=tfs_defaultcollection;Integrated Security=True
|
HP10
|
TfsJobAgent.exe
|
2012-05-21 06:51:37.870
|
|
Training collection
|
~/Training collection/
|
Data Source=hp10\SqlTfs;Initial Catalog="Tfs_Training collection";Integrated Security=True
|
HP10
|
TfsJobAgent.exe
|
2012-05-21 06:51:37.870
|
Ressources and services
Again, let's follow up the following tables:
- tbl_CatalogResourceType stores a list of resource types. Like: 'Machine', 'Infrastructure Root', 'Project Server', 'Team Foundation Project Collection Database'… etc. Here is some sample entries:
|
Identifier
|
DisplayName
|
Description
|
|
9FB288AE-9D94-40CB-B5E7-0EFC3FE3599F
|
Default location for team project portals
|
By default, each team project Web site will be a sub-site of this location.
|
|
0584A4A2-475B-460E-A7AC-10C28951518F
|
Machine
|
A machine that exists as part of the TFS deployment.
|
|
14F04669-6779-42D5-8975-184B93650C83
|
Infrastructure Root
|
The root of the catalog tree that describes the physical makeup of the TFS deployment.
|
|
1B6B5931-69F6-4C53-90A0-220B177353B7
|
Team Foundation Project Collection Database
|
The database that houses the information for a Team Foundation Project Collection.
|
|
526301DE-F821-48C8-ABBD-3430DC7946D3
|
Team Foundation Configuration Database
|
The database that houses the information for a Team Foundation Application Instance.
|
|
289DD275-CECA-4698-8042-38D2E86FC682
|
Project Server
|
Project Server Information
|
|
3DADD190-40E6-4FC1-A306-4906713C87CE
|
SharePoint Web Application
|
A SharePoint Web application that has been configured to support team project portals.
|
|
3C856555-8737-48B6-8B61-4B24DB7FEB15
|
Test Controller
|
This is a test controller type in Catalog Service.
|
|
26338D9E-D437-44AA-91F2-55880A328B54
|
Team Project Collection
|
A Team Project Collection that exists within the TFS deployment.
|
|
450901B6-B528-4863-9876-5BD3927DF467
|
Project Portal
|
A Web site or SharePoint site that provides a portal for a team project.
|
|
15DA1594-45F5-47D4-AE52-78F16E67EB1E
|
Process Guidance
|
A Web site or SharePoint library that provides process guidance for a team project.
|
|
47FA57A4-8157-4FB5-9A64-A7A4954BD284
|
Team Web Access
|
Team Web Access Location
|
|
48577A4A-801E-412C-B8AE-CF7EF3529616
|
Team Project
|
A Team Project that exists within the TFS deployment.
|
- tbl_CatalogResource stores resources (each referring to one of the types in the above tbl_CatalogResourceType table. Sample entries:
|
Identifier
|
ResourceType
|
DisplayName
|
Description
|
|
7462A882-741B-4F91-885F-1405A22256DF
|
1B6B5931-69F6-4C53-90A0-220B177353B7
|
Tfs_Training collection
|
|
|
C5CD36C5-8373-4BF7-B3FA-16199DAFF106
|
48577A4A-801E-412C-B8AE-CF7EF3529616
|
cmmi project
|
cmmi training project
|
|
16480162-C502-4197-872A-4DDDFE49BA90
|
1B6B5931-69F6-4C53-90A0-220B177353B7
|
tfs_defaultcollection
|
|
|
DCC270E7-6EC7-45BF-A827-53BBB5386606
|
14F04669-6779-42D5-8975-184B93650C83
|
Infrastructure Root
|
The root of the catalog tree that describes the physical makeup of the TFS deployment.
|
|
2F8C7D1C-EB1F-4058-B34D-60FA2F6CA7DC
|
48577A4A-801E-412C-B8AE-CF7EF3529616
|
Agile project
|
|
|
D7121DA7-3E32-49C3-BCB1-68C83C4383B7
|
47FA57A4-8157-4FB5-9A64-A7A4954BD284
|
Team Web Access
|
|
|
4B812279-A7A6-4A8A-92A4-7AA208589FEB
|
EB1E0B3B-FAA1-49D2-931A-FDC373682BA5
|
SQLTFS
|
|
|
F69EFD2B-51C6-461B-9E49-8089DF86C9A3
|
0584A4A2-475B-460E-A7AC-10C28951518F
|
hp10
|
|
|
5242DBFC-06C8-4CAD-A27B-99212BEEE999
|
526301DE-F821-48C8-ABBD-3430DC7946D3
|
Tfs_Configuration
|
|
|
713FF18A-E0E8-4192-A7B9-A0900F50A5BC
|
26338D9E-D437-44AA-91F2-55880A328B54
|
DefaultCollection
|
|
- tbl_ServiceDefinition stores a list of predefined services (classes) and the web access page for each. Sample entries (among other things, you may note curly braces which are place holders for some url parameters in the RelativePath column):
|
ServiceType
|
Identifier
|
DisplayName
|
RelativePath
|
|
AdministrationService
|
C18D6E34-68E8-40D2-A619-E7477558976E
|
Administration Service
|
/TeamFoundation/Administration/v3.0/AdministrationService.asmx
|
|
Annotate
|
74B15E02-0AC2-414F-A9B9-30268659D3B5
|
Team Web Access (Annotate)
|
/web/ann.aspx
|
|
AnnotateSourceControlItem
|
D271E722-C261-4BC2-B0F7-1C8A9E13F907
|
Team Web Access (AnnotateSourceControlItem)
|
/web/ann.aspx?pcguid={projectCollectionGuid}&path={itemPath}&cs={itemChangeset}
|
|
CatalogService
|
C2F9106F-127A-45B7-B0A3-E0AD8239A2A7
|
Catalog Service
|
/TeamFoundation/Administration/v3.0/CatalogService.asmx
|
|
ChangesetDetail
|
D40EF625-CCA7-4E73-B9EC-86CBE1534CE0
|
Team Web Access (ChangesetDetail)
|
/web/cs.aspx
|
|
CreateWorkItem
|
14CD69C6-88F9-4C8C-A259-D2441D77D1AF
|
Team Web Access (CreateWorkItem)
|
/web/wi.aspx?puri={projectUri}&wit={workItemType}
|
|
Difference
|
2B84D900-1F08-486C-9C47-0E6AF371D03C
|
Team Web Access (Difference)
|
/web/diff.aspx
|
|
Eventing
|
C424AE04-8C6F-4516-8B2D-238FFFCA3081
|
Event Service
|
/TeamFoundation/Administration/v3.0/EventService.asmx
|
|
IdentityManagementService
|
3DE26348-00BE-4B82-8E4A-E5AD004CFECD
|
Identity Management Service
|
/TeamFoundation/Administration/v3.0/IdentityManagementService.asmx
|
|
JobService
|
DA1C0184-14FE-4E13-B7FC-6EAA07D84BE8
|
TFS Background Job Service
|
/TeamFoundation/Administration/v3.0/JobService.asmx
|
- tbl_CatalogServiceReference stores per-resource services' entries. (Resource Identifier / Service Identifier…).
|
ResourceIdentifier
|
AssociationKey
|
ServiceIdentifier
|
ServiceType
|
|
D7121DA7-3E32-49C3-BCB1-68C83C4383B7
|
ChangesetDetail
|
D40EF625-CCA7-4E73-B9EC-86CBE1534CE0
|
ChangesetDetail
|
|
D7121DA7-3E32-49C3-BCB1-68C83C4383B7
|
CreateWorkItem
|
14CD69C6-88F9-4C8C-A259-D2441D77D1AF
|
CreateWorkItem
|
|
D7121DA7-3E32-49C3-BCB1-68C83C4383B7
|
Difference
|
2B84D900-1F08-486C-9C47-0E6AF371D03C
|
Difference
|
|
D7121DA7-3E32-49C3-BCB1-68C83C4383B7
|
DiffSourceControlItems
|
5E91C4DA-0013-4EBB-943D-CC77F5ADB82D
|
DiffSourceControlItems
|
|
D7121DA7-3E32-49C3-BCB1-68C83C4383B7
|
DiffSourceControlShelvedItem
|
4C81A44D-67AB-4D23-9CBE-339C9102993B
|
DiffSourceControlShelvedItem
|
|
D7121DA7-3E32-49C3-BCB1-68C83C4383B7
|
ExploreSourceControlPath
|
AC0770BC-1DD6-4B8E-A811-5A03690DF44F
|
ExploreSourceControlPath
|
|
D7121DA7-3E32-49C3-BCB1-68C83C4383B7
|
Home
|
0F9CED5D-89F9-4743-BAB8-FA511FF09A8C
|
TSWAHome
|
This allows us to query the services in a way similar to the following:
SELECT resType.DisplayName AS ResourceType,
svc.DisplayName AS Service,
svc.Description AS serviceDescription
FROM dbo.tbl_ServiceDefinition AS svc INNER JOIN
dbo.tbl_CatalogServiceReference AS svcRef ON svc.Identifier = svcRef.ServiceIdentifier
INNER JOIN
dbo.tbl_CatalogResource AS Res ON svcRef.ResourceIdentifier = Res.Identifier
INNER JOIN
dbo.tbl_CatalogResourceType AS resType ON Res.ResourceType = resType.Identifier
|
Sample results:
|
ResourceType
|
Service
|
serviceDescription
|
|
Team Web Access
|
Team Web Access (Annotate)
|
|
|
Team Web Access
|
Team Web Access (AnnotateSourceControlItem)
|
|
|
Team Web Access
|
Team Web Access (ChangesetDetail)
|
|
|
Team Web Access
|
Team Web Access (CreateWorkItem)
|
|
|
Team Web Access
|
Team Web Access (Difference)
|
|
|
Team Web Access
|
Team Web Access (DiffSourceControlItems)
|
|
|
Team Web Access
|
Team Web Access (DiffSourceControlShelvedItem)
|
|
|
Team Web Access
|
Team Web Access (DiffSourceControlShelvedItem)
|
|
|
Team Web Access
|
Team Web Access (ExploreSourceControlPath)
|
|
|
Team Project Collection
|
Location Service
|
Location Service for Visual Studio Team Foundation Server.
|
|
Team Project Collection
|
Location Service
|
Location Service for Visual Studio Team Foundation Server.
|
|
Team Foundation Server Instance
|
Location Service
|
Location Service for Visual Studio Team Foundation Server.
|
|
Team Web Access
|
Team Web Access (OpenWorkItem)
|
|
More about TFS meta-models in following posts!