The TFS Performance Report Pack and SQL Server 2005 Reporting Services
Grant Holliday has created a great set of TFS performance monitoring reports, called the TFS Performance Report Pack. These reports, and instructions for uploading them to SQL Server Reporting Services, are available on his excellent blog at blogs.msdn.com/granth/archive/2009/02/03/announcing-tfs-performance-report-pack.aspx. He lists SQL Server 2008 Reporting Services as a requirement, with a note that they should work in SSRS 2005. It turns out some of the reports function under both SSRS 2008 and 2005, some do not. Several posters have mentioned incompatibilities with some reports and SSRS 2005.
These reports are compatible with either SSRS version:
- Server Status - Historical Performance Trends.rdl
- Server Status - Recent Performance Trends.rdl
- Server Status - Top Users Bypassing Proxies.rdl
These reports require SSRS 2008:
- Execution Time Summary.rdl
- Execution Time for User.rdl
- Server Status - Source Control Request Queue.rdl
I have created versions of the three reports above that are functionally similar to Grant's reports, but also work with SSRS 2005. You can download my updated reports here. To use my reports, install Grant's TFS Performance Report Pack, and replace the three reports with the versions in the zip file I provided.
I also noticed that some of Grant's reports were configured to use a private data source, rather than the shared data source referred to in the instructions he provided with his reports. When I uploaded and tested his reports, I received this error:
An error has occurred during report processing.
Cannot create a connection to data source 'PrivateDataSource'.
with these reports:
- Server Status - Historical Performance Trends
- Server Status - Recent Performance Trends
- Server Status - Top Users Bypassing Proxies
You can hook up the correct data source as follows in SSRS:
- Navigate to the report in SSRS and click on it.
- After you receive the error, click the Properties tab.
- Click the Data Sources link on the left. It will be configured to "A custom data source".
- Click on "A shared data source" and then click the adjacent Browse button.
- In the tree view, navigate the TfsActivityReportDS data source, which you created in Step 4 of Grant's instructions. I created mine in the Server Status folder, so I had to expand Server Status in the tree view to find TfsActivityReportDS. Highlight TfsActivityReportDS and click OK.
- Back in the Data Sources page, click Apply.
- Click View, and the report should display without error.
There is another possible gotcha in his instructions. When configuring the connection string in Step 4.1, the connection string is "Data Source=localhost;Initial Catalog=TfsActivityLogging". The Data Source here assumes a single server TFS installation. If SQL Server is installed as a named instance, and/or TFS is installed as a dual server installation, you need to list the instance name and/or the Data Tier computer in the Data Source.
If the connnection string here is incorrect, you may see an error like this:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'TfsActivityReportDS'. (rsErrorOpeningConnection)
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Connnection String Data Source Examples :
Single Server TFS installation, SQL installed as an instance named Instance1
Data Source=localhost\Instance1;
Dual Server TFS installation, SQL installed as default instance
Data Source=<datatiercomputername>;
Dual Server TFS installation, SQL installed as an instance named Instance1
Data Source=<datatiercomputername>\Instance1;
Grant's reports, and my SSRS 2005 compatible clones if you need them, provide a great view into various aspects of your TFS server performance.
Enjoy!
Jim
UPDATE [NOV 4 2009]: A reader reported the following:
One the Server Status-Source Control Request Queue report, we get the following error: An error has occurred during report processing.
Query execution failed for data set 'SCC_RequestQueue'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors We have made sure that we are indeed using the correct datasource
Jim suggested the following, which resolved the issue:
The data source for this one (the Server Status-Source Control Request Queue report) is different. It uses a web service rather than SQL data. My guess is that SSRS is running on a different PC from the AT, or on a different port, or something. The report is coded to use localhost:8080, so he may want to add the machine name. Also the account the report runs under needs perms to call that web service.
Hope this helps if you see the same.
- Trev