Processing Tfs_Analysis Database

I was trying to get TFS 2010 to work with SQL 2008 R2 Report Server. Because the order in which these servers were installed, the reporting service in TFS 2010 had to be fixed after it was migrated to a server computer from a test installation on a workstation.

I followed the suggestions at this link:

http://social.msdn.microsoft.com/Forums/en-US/tfsreporting/thread/1d566b21-3082-4aa8-bbc8-d02fb76791de/

I downloaded and uploaded necessary report definitions, I fixed the data sources, and I update security settings. When I tried to open a report, I got errors related to “dsIteration”. I opened a report definition in SQL Report Designer. When I tried to run a query on this dataset, I got a “no cube can be found” error.  

I had to get into “SQL Server Business Intelligence Development Studio” to open the “Tfs_Analysis” database. It is not visible from the “SQL Server Management Studio”. Although the “Tfs_AnalysisDataSource” tested fine, when I tried to process the database from the “Database” menu, I got an error:

“OLE DB error: OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.

Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Tfs_AnalysisDataSource', Name of 'Tfs_AnalysisDataSource'. “

I tried to fix the “Tfs_AnalysisDataSource” datasource. Everthing I tried was of no use. I updated the firewall for SQL Analysis Service. It uses port 2383 access. That was not enough. 

I found an article on the internet that was helpful:

http://blog.accentient.com/2010/04/08/ManuallyProcessingTheTeamFoundationServer2010DataWarehouseAndAnalysisServicesDatabase.aspx 

It taught me how to use a web service to build TFS report databases:

http://localhost:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx?

I went through the steps and they did not solve the problems. I restarted the SQL Analysis Service. It turned out that the “OLE DB error” was caused by the SQL Analysis Service itself. After the restart I got a bunch of SQL errors while processing the database, but none was “network-related”. I went into TFS Management Console and rebuilt the warehouse database, and reprocessed the “Tfs_Analysis” database. The reprocessing worked.

After that, the reports were still not accessible. I restarted the SQL Analysis Service again, and then I went through the web service calls again.

Finally, the reports came up.

A MS SQL Server 2008 Data Recovery Technique

I had some MS SQL Server 2008 database files that became corrupted recently. The files were detached from the SQL Server before they became corrupted. So when I tried to attach them, I get errors saying that the SQL server failed on some assertion at location "logmgr.cpp": 4217. The asserted expression was "lfh->lfh_startOffset == startOffset".

After many many different attempts, I finally got the files to attach. The steps that I took are things most people would normally try, but I added a twist to the first step:

1. Create a new blank database with the same name and schema: tables, constraint, etc. At this point you may have guessed what should follow. But before you go on, do the following: set it into emergency and single mode.

ALTER DATABASE CorruptedDB SET EMERGENCY
GO
ALTER DATABASE CorruptedDB SET SINGLE_USER;
GO

2. The rest of the steps are fairly typical. Stop the SQL Server service from the SQL Server Configuration Manager.
3. Replace the files for this blank database with the corrupted files.
4. Restart the SQL Server service from the SQL Server Configuration Manager.

This allowed me to access a set of database files that were otherwise no longer readable. Although I could not even look at the table definitions in the SQL Server Management Studio, I could selelct all the data rows out of it, and insert into a new database with the same schema:

USE
SET IDENTITY_INSERT ON
INSERT INTO .[dbo].
(
,...
SELECT
,...
FROM CorruptedDB.[dbo].
GO
SET IDENTITY_INSERT OFF
GO