Unable to properly restore TFS Warehouse/Cube to new server using new users

Question

I am testing disaster recovery for TFS 2010 and am having troubles with the warehouse and analysis database restore. In this test, I am restoring to a different server and using different users. I started with a restore of both databases and ran through the instructions at  http://msdn.microsoft.com/en-us/library/ms252516(v=vs.100) 

I have been able to run the commands from the instructions successfully

TFSConfig PrepSQL /SQLInstance: ServerName 

TFSConfig Accounts /ResetOwner /SQLInstance: ServerName /DatabaseName: DatabaseName

TfsConfig Accounts /add /AccountType:ApplicationTier /account: AccountName /SQLInstance: ServerName /DatabaseName: DatabaseName

However, when I get to the part where I check the processing status, it showed for the Full Analysis Database Sync job :

TF276000: The current Analysis Database Sync job has exited without making any changes. Either another Analysis Database Sync job is already in progress, or the Analysis Database Sync job cannot run because an Optimize Database job is running...

For the Incremental Analysis Database Sync, it says

<ResultMessage>[Incremental Analysis Database Sync]: ---> AnalysisDatabaseProcessingType=Full, needCubeSchemaUpdate=False. ---> Microsoft.TeamFoundation.Server.WarehouseException: TF221122: An error occurred running job Incremental Analysis Database Sync for team project collection or Team Foundation server TEAM FOUNDATION. ---> Microsoft.TeamFoundation.Server.WarehouseException: Failed to Process Analysis Database 'TFS_Analysis'. ---> Microsoft.TeamFoundation.Server.WarehouseException: The following system error occurred: Logon failure: unknown user name or bad password. 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'. Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Tool Artifact Display Url', Name of 'Tool Artifact Display Url' was being processed. Errors in the OLAP storage engine: An error occurred while the 'Tool Type' attribute of the 'Tool Artifact Display Url' dimension from the 'TFS_Analysis' database was being processed. Internal error: The operation terminated unsuccessfully. The following system error occurred: Logon failure: unknown user name or bad password. 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'. Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Tool Artifact Display Url', Name of 'Tool Artifact Display Url' was being processed. Errors in the OLAP storage engine: An error occurred while the 'TeamProjectCollectionSK' attribute of the 'Tool Artifact Display Url' dimension from the 'TFS_Analysis' database was being processed. Server: The operation has been cancelled. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'TFS_Analysis, Team System, FactCurrentWorkItem', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'TFS_Analysis, Team System, FactWorkItemHistory', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'TFS_Analysis, Team System, v Fact WorkItem To Tree', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'TFS_Analysis, Team System, v Fact Linked Current WorkItem', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'TFS_Analysis, Team System, Fact WorkItem To Category', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'TFS_Analysis, Team System, v Fact WorkItem Changeset', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'TFS_Analysis, Team System, Fact Build Project', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'TFS_Analysis, Team System, Fact Build Details', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'TFS_Analysis, Team System, Fact Code Churn', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'TFS_Analysis, Team System, v Fact Test Result Overlay', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'TFS_Analysis, Team System, Fact Build Changeset', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'TFS_Analysis, Team System, Fact Build Coverage', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'TFS_Analysis, Team System, v Fact WorkItem Test Result', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'TFS_Analysis, Team System, v Fact Linked Current Work Item Test Case', has been specified but has not been used. Warning: Parser: Out of line object 'Binding', referring to ID(s) 'TFS_Analysis, Team System, Fact Run Coverage', has been specified but has not been used. at Microsoft.TeamFoundation.Warehouse.TFSOlapProcessComponent.ExecuteXmla(String finalXmla) at Microsoft.TeamFoundation.Warehouse.TFSOlapProcessComponent.ProcessOlap(AnalysisDatabaseProcessingType processingType, WarehouseChanges warehouseChanges, Boolean lastProcessingFailed, Boolean cubeSchemaUpdateNeeded) --- End of inner exception stack trace --- at Microsoft.TeamFoundation.Warehouse.TFSOlapProcessComponent.ProcessOlap(AnalysisDatabaseProcessingType processingType, WarehouseChanges warehouseChanges, Boolean lastProcessingFailed, Boolean cubeSchemaUpdateNeeded) at Microsoft.TeamFoundation.Warehouse.AnalysisDatabaseSyncJobExtension.RunInternal(TeamFoundationRequestContext requestContext, TeamFoundationJobDefinition jobDefinition, DateTime queueTime, String& resultMessage) at Microsoft.TeamFoundation.Warehouse.WarehouseJobExtension.Run(TeamFoundationRequestContext requestContext, TeamFoundationJobDefinition jobDefinition, DateTime queueTime, String& resultMessage) --- End of inner exception stack trace ---</ResultMessage> 

When I tried to manually run ProcessAnalysisDatabase I received an http 500 error. 

Going back to the Administration console, I reapplied my the service account. This appeared to fix the ProcessAnalysisDatabase error but the sync error log was still there.

I tried deleting the analysis database and going through the administration console to recreate it. This got rid of the GetProcessingStatus error but the http 500 error was back.

Next I deleted both the warehouse and analysis service database and went through the administration console to recreate both. When I clicked on OK, there was an error: "Failed to add <the wrong user account> to the TFSEXECROLE role on the TFS_Warehouse relational database. Clearly it's trying to use the wrong settings in various places but I don't know what needs to be done to fix it.

Answer 1

Hi Lindsey,

There is no new data in the datawarehouse or the SSAS cube. Most people don't opt to do a backup and restore on these and simply allow them to rebuild from the transactional database.

With the exception that you are getting it is possible that the jobs kicked off automatically or that the databases weren't in a stable state when backed up. You can get more information on the status of the warehouse service jobs on the AT from:

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

From there you can choose to start and stop jobs in your deisired order.

I have documented my troubleshooting steps here:

http://ryanhanisco.com/tf221122-tfs-analysis-cube-wont-process/

Good Luck

---

Ryan Hanisco, PMP, PMI-ACP, CISSP
MCSE, MCTS:TFS 2010, SQL 2005
RyanHanisco.com

Answer 2

Thanks for the reply, Ryan,

I actually tried restoring without the cube but the tools like

TFSConfig Accounts /ResetOwner /SQLInstance: ServerName /DatabaseName: DatabaseName

TfsConfig Accounts /add /AccountType:ApplicationTier /account: AccountName /SQLInstance: ServerName /DatabaseName: DatabaseName

and The TFS Application Tier wizard wouldn't work because they couldn't find the database. That's why I included them. The problem seems to be a account setting that I cannot change that I'm guessing is stored in the configuration database. When I deleted both the warehouse and cube and then used the administration console to recreate them, it couldn't because it was using the wrong account.

Any other ideas?

Thanks,
Lindsey

Answer 3

Hi Lindsey,

One step that isn't really documented in the TFS 2010 installation guide is that you need to have the accounts that the warehouse services are using have access to read the datawarehouse and write to the Cube.  It is usually the cube step that is missed.  Try granting write/ create priveleges to the service account wnd then recreate through the Admin console.

---

Ryan Hanisco, PMP, PMI-ACP, CISSP
MCSE, MCTS:TFS 2010, SQL 2005
RyanHanisco.com

Answer 4

Hi Lindsey,

I marked Ryan's reply as answer, if you have any concern with it, please feel free to unmark it.

Best Regards,

---

Cathy Kong [MSFT]
MSDN Community Support | Feedback to us

Answer 5

On the machine I have restored to, I have a simple setup where the service account is a administrator for the local machine, sql server database / analysis.  In other words, the service account has full rights to everything on the machine. I've also tried to go through the instructions to reset owners of the TFS databases all to this one user.

When I delete the warehouse and cube and try to recreate through the admin console, first I click the Test Connection button. The result is "The database that you  specified does not exist. A new database will be created and populated for use with Taam Foundation Server." When I click OK, it says " Failed to add <user_account> to the TFSEXECROLE role on the <database_name> relational database. What I do know is that. The user_account is the network admin's user account who was logged in when we first installed TFS on the real TFS instance and database_name matches what I had in the field for the Warehouse database, which does not yet exist. The user_account was never meant to be used as any TFS service account.

Answer 6

Hi Lindsey,

Is the machine that you're trying to restore to in a different domain or not in a domain?

I think you need to go back to the migration docs and walk through the steps again.  Specifically the ones below.  http://msdn.microsoft.com/en-us/library/ms252516(v=VS.100).aspx

So you did:

TFSConfig PrepSQL /SQLInstance: ServerName

and you did:

TFSConfig Accounts /ResetOwner /SQLInstance:ServerName/DatabaseName:DatabaseName

and

TfsConfig Accounts /add /AccountType:ApplicationTier /account:AccountName/SQLInstance:ServerName/DatabaseName: tfs_Configuration

and

TFSConfig RemapDBs /DatabaseName: ServerName ; TFS_Configuration /SQLInstances: ServerName,ServerName2 /AnalysisInstance:ServerName2 /AnalysisDatabaseName:DatabaseName

---

Ryan Hanisco, PMP, PMI-ACP, CISSP
MCSE, MCTS:TFS 2010, SQL 2005
RyanHanisco.com