Team Foundation Server schema

UPDATE: some more on the TFS schema here and here. On the recent developments: here.

I happen to run an on-premises instance of Microsoft Team Foundation Server for a medium sized software shop. TFS has pretty good reporting capabilities, but out of the box, almost no cross-collection reporting. Fortunately, those who are blessed with admin rights in TFS get to connect to the production database server.

The schema of TFS databases (there are multiple) is occasionally convoluted, but generally approachable. Each collection gets a database, and the server-level information is stored under Tfs_Configuration. If you've dabbled with TFS REST API, you'd know that both collections and projects are identified with GUIDs in addition to their display names.

The list of team collections is in table Tfs_Configuration.dbo.tbl_ServiceHost. The field HostId corresponds to the collection's  GUID.

The list of projects is in table dbo.tbl_projects in each collection's database. The GUID is under project_id. Table dbo.tbl_Project doesn't have the GUID, just the dataspace ID.

The build/release definitions and queues are stored on per-project basis, but there's no project ID there. Instead, there's an integer field DataspaceId which can be linked back to the project via the dbo.tbl_Dataspace table. In that table, the field DataspaceIdentifier (distinct from DataspaceId!) contains the project GUID. Dataspaces have a string type, stored in DataspaceCategory; e. g. the agent queue dataspace corresponds to type "DistributedTask", and release definitions belong to dataspace of type "ReleaseManagement".

The table Release.tbl_DefinitionEnvironmentStep doesn't contain steps. It contains approvals. Same goes for Release.tbl_ReleaseEnvironmentStep. The latter stores the approvals received during execution, the former stores the configured approvals.

References to users and groups are stored in collection tables as GUIDs. The GUIDs are collection specific; in order to resolve them, use the table dbo.tbl_IdentityMap. The field localId corresponds to a collection-specific GUID, masterId is the global GUID.

In order to resolve the masterId further to the actual group or AD user, use either table Tfs_Configuration.dbo.tbl_Group or Tfs_Configuration.dbo.tbl_Identity. Even collection- and project-level groups can be found in the former. The latter one stores references to the AD accounts.

The table tbl_Group contains both server-, collection-, and project-level groups. In order to retrieve the collection and/or project, use the InternalScopeId field. It's a reference to Tfs_Configuration.dbo.tbl_GroupScope. The server level scope has a hard-coded ID 1.