Working with SQL queries / Web services to retrieve information from TFS
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: https://<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.
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 : https://<servername>:8080/Services/v1.0/Registeration.asmx
Select GetRegisterationEntries
Enter WSS as the value in the new webpage that appears
Click invoke
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
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.
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:
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.
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 https://<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
Note: It has been a successful year since we have started this blog initiative. Thank you for the continued patronage. Keep visiting us ! – ArunRama