Part 1

Author:      Arun Ramalingam

Reviewer:   Lakhminder Singh

Starting now, we are planning on a series of posts on SQL queries that will help TFS administrators. To start with, here is a set of SQL queries and the equivalent Web service call (where ever possible) that will help while working with TFS 2005/TFS2008 instances. 

Important:  The intention of these queries is to gain information from TFS databases. Please note that manipulating the data inside the TFS databases can cause irrecoverable data loss and configuration failures.

1)      SQL server Analysis services instance

This Query will help in identifying the SQL server Analysis services instance name and the cube that is configured with your TFS 2005/ 2008  instance. In a server down scenario this query might come handy to know how your TFS was configured initially, especially when you don’t have any information of the initial Topology.

SQL query:

Use TfsIntegration

Select servername 'SQL Server name'  , dbname 'Database Name' from dbo.tbl_database

Where name = 'BISANALYSIS DB’

 Web service call:

The same information can be retrieved from a web service. This can be used if you have the application tier still running.

url: http://<servername>:8080/Services/v1.0/Registeration.asmx

Select GetRegisterationEntries and in the new webpage that appears enter vstfs as the value and click Invoke.

In the resulting page scroll down to the node names <database> and the section named BISANALYSIS DB has the information of the cube and the SQL server.

clip_image002

2)      SharePoint wiring

   To retrieve the SharePoint wiring information from the TFS databases.

 SQL query:

Use TfsIntegration

Select   url  ' Central Administration site:', (select url  from  dbo.tbl_service_Interface  where  name = 'baseserverUrl' ) ' Portal Site'

from  dbo.tbl_service_interface

Where name = 'WssAdminService'

  Web service call: 

Browse to : http://<servername>:8080/Services/v1.0/Registeration.asmx

Select GetRegisterationEntries

Enter WSS as the value in the new webpage that appears

Click invoke

clip_image004

 

3)      Reporting services URL

        To identify the Reporting services URL that is wired to TFS instance

        SQL Query:

Use TfsIntegration

Select  url  'The reports URL' , (select  url from dbo.tbl_service_interface where  name = 'ReportService') 'ReportServer Url'

From dbo.tbl_service_interface

Where name = 'BasereportsUrl'   

Web Service call

Browse to :8080/Services/v1.0/Registeration.asmx">:8080/Services/v1.0/Registeration.asmx">http://<servername>:8080/Services/v1.0/Registeration.asmx

Click on "GetRegistrationEntries"

Enter the value Reports and click Invoke

The details are ordered as ReportService, BaseReportUrl, DataSourceServer which represents "ReportServer Url", "Reports Url"and "Reports instance" respectively.

 clip_image006

4)      Service Accounts

            To find the "TFSservice" account and the "TFSreports" account (if they are different)

SQL Query:

Use TfsIntegration

Select  value 'The service account' , (select  value  From dbo.tbl_registration_extended_attributes Where name = 'RsDataSourceAccount') 'The Report reader account'

From dbo.tbl_registration_extended_attributes Where name = 'TfsServiceAccount'

Web Service call:

Browse to :8080/Services/v1.0/Registeration.asmx">:8080/Services/v1.0/Registeration.asmx">http://<servername>:8080/Services/v1.0/Registeration.asmx

Click on "GetRegistrationEntries"

Enter the value vstfs and click Invoke

The details presented under the node <RegistrationExtendedAttribute> are in two sections.

"TfsServiceAccount” displays TFSservice account and "RsDataSourceAccount" displays the Tfsreports account (the data reader account) name.

 clip_image008

 5)      Project List

            To get the List of Projects in a TFS instance

SQL Query:

Use Tfsintegration

Select Project_name 'Existing Projects', State 'Project Status'

From dbo.tbl_projects

Order by state, project_name

Web Service call:

Browse to http://<servername>:8080/Services/v1.0/CommonStructureService.asmx

Select ListAllProjects and click Invoke

            This lists the projects with Status information

            Status:  The status refers to the “State” of a project.

             New                  When a Project is created thru the wizard, the status would be "NEW".

WellFormed   Once all the components are created

Deleting           If a project was deleted and if any component is left undeleted

clip_image010

Note: It has been a successful year since we have started this blog initiative. Thank you for the continued patronage. Keep visiting us !  – ArunRama