Rebuild TFS Warehouse and Analysis Databases From Scratch
This is a known issues in TFS environments, especially when upgrading TFS or moving to different server.
Please consider that all the image and samples on article was built with TFS 2012 update 3.
As starting for this article, I will delete the existing tfs warehouse and analysis services databases.
Please skip the below 6 steps if you don't have these databases
- Open SQL Server management studio, and connect the SQL instance that host the tfs_warehouse.
- Take backup of tfs_warhouse. "this is just in case that this will not work with you"
- Then right click tfs_warehouse and select delete.
- In delete object windows, select “close existing connection” and select OK.
- From SQL Server management studio object explorer, select connect, then choose analysis services, and connect to the analysis services that hosts tfs_analysis .
- Right click tfs_analysis and select delete.
Then follow the below step to reconfigure the reporting and create the warehouse database.
- Then login to TFS server as administrator, and open “Team Foundation Server Administration Console”
- Select Application Tier , Reporting node:
- In reporting tab, choose Edit.
- If you received “take offline” windows, select OK
- In the reporting window, select “analysis services” tab, and re enter the username and password, and select OK.
- TFS will try to create a mew tfs_warehouse, however the creation will fail with the following error.
- As a workaround for this, click** OK**, and in reporting windows, enter another name for tfs warehouse name, for example tfs_warehouse2. Then choose OK.
- Please wait for a few minutes until the console create tfs_warhouse2 database.
- After that, in reporting node of tfs administration console, click edit again, and change the name of warehouse again to be tfs_warehouse again. “Just to keep the default name of tfs_warehouse”
- Reenter the usernames and passwords in analysis services and reporting tab. And click OK.
- Please wait for a few minutes until the console create tfs_warhouse database.
- In repotting node of tfs administration console, select “start rebuild”
Check the processing status and create the analysis database.
- Then login to TFS server as administrator.
- Open IE, and browse to the following url:
- Select GetProcessingStatus method, enter true in the includeOnlineHostsOnly field, and click invoke
- Most probably you will receive error in warehouse processing, as below.
- After that, in IE, navigate back to the web service
- Select ProcessWarehouse web service as select invoke.
- Be sure that the response is OK.
- Then go back to the web service and select GetProcessingStatus web method again. Enter true in the includeOnlineHostsOnly field, and click invoke
- Please be sure that “JobRunning and JobQueued” are 0, if not, please wait for a minute and run the web services again.
- At the end, “JobRunning and JobQueued” should be zero, and the result for last run of the warehouse should be “Succeeded” please ignore the errors on other sections
- Then go back to the web service, and select ProcessAnalysisatabase, enter Full in the proceeingType and click Invoke.
- Then go back to the web service and select GetProcessingStatus web method again. Enter true in the includeOnlineHostsOnly field, and click invoke
- Please be sure that “JobRunning and JobQueued” are 0, if not, please wait for a minitue and run the web services again.
- At the end, “JobRunning and JobQueued” should be zero, and the result for last run of the Full Analysis Database Fync should be “Succeeded”
- Then go back to the web service, and select ProcessAnalysisatabase, enter Incremental in the proceeingType and click Invoke.
- Then go back to the web service and select GetProcessingStatus web method again. Enter true in the includeOnlineHostsOnly field, and click invoke
- Please be sure that “JobRunning and JobQueued” are 0, if not, please wait for a minitue and run the web services again.
- At the end, “JobRunning and JobQueued” should be zero, and the result for last run of the Incremental Analysis Database Sync should be “Succeeded”
Now the warehouse and analysis databases has been configured and running
Just one think, remember to add tfs_warehouse to your backup database, as it’s mandatory.
http://msdn.microsoft.com/en-us/library/vstudio/ms253151.aspx