Migrating TFS Analysis and Warehouse Databases

The Warehouse and Analysis databases for TFS can easily be migrated to a different SQL server. This article details the steps involved in the migration. Screenshots and examples provided are for TFS 2013 versions.

There are two ways to do it:

• Change the SQL server for Analysis or Warehouse, or both the databases and then point TFS to new empty databases. TFS will automatically create new databases with the specified name and then rebuild the warehouse. The time taken to complete the rebuild and the reports to become completely functional will depend on the amount of data.
• The second method will involve backup and restore of either one or both of Analysis and Warehouse databases to the new SQL server and configuring TFS to use them. This approach saves time as a complete rebuild is not required.

It is advised to make a note of the Default Folder Location for reports from the Reports Folder tab, under the Team Project Collections node on the TFS Admin Console and ensure that the same values are present after the migration

Also make a note of the settings for the Report Server details from the Reporting node. (Click on edit and navigate to Reports tab)

Required Permissions:

You should be a member of the following groups to perform the migration:

• Team Foundation Administrators and Admin Console Users in TFS
• sysadmin on the SQL server
• Administrator on the Analysis server
• Report Reader account: The report reader account is the identity that is used to gather information for reports. This account should have the Allow log on locally permission. The account that is currently being used as the reader account can be found from the Reporting Services Summary section in the Application Tier node of the TFS Admin console.

Use New Analysis and Warehouse Databases

• Open TFS Admin console and navigate to the reporting tab and click on Edit.

• To move the Analysis database, on the Analysis tab of the new window, specify the new SQL server instance and then the name of the new Analysis database.
• Also specify the Report Reader account.

• Click on Test Connection. It should succeed and display a message saying that the database does not exist but will be created.
• If you are planning to move the Warehouse database as well, specify the new SQL server and database names in the Warehouse tab.
• Test connection should succeed with a message that the new database will be created.
• Accept the settings for the Report Server details as that will remain the same and click on OK.
• Click on Start Jobs.
• Initiate the processing of Warehouse from the warehouservicecontrol page and wait for it to complete, URL for WareHouseControl service: https://localhost:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx

• • Click on Invoke

• Process Analysis Database with the processing type as Full.

• The processing status can be checked by invoking GetProcessing status. Give the parameter value for includeOnlineHostsOnly as True and click Invoke (This will show the job status for all online collections)

• Once the processing is completed, validate the move by browsing the Reports and SharePoint site

Backup and Restore the existing Analysis and Warehouse databases

• On the Edit window of the Reporting tab, uncheck Use Reporting.
• Backup either Analysis or Warehouse or both the databases, form SQL management studio.
  The backup of Analysis database will be .abf file and Warehouse database will be .bak file
• Restore these databases to the new SQL server. Warehouse database should be restored to Database Engine and Analysis to the Analysis Services.
• Open the Edit window for Reporting from the TFS Admin console and check the Use Reporting check box.
• In the Analysis and Warehouse tabs, specify the new SQL server and the restored database names. Also specify the report reader account.
• Test connection should succeed and say the databases exist and has a valid schema.
• Do not change the report server details and click OK.
• Start jobs from the Reporting tab.
• Validate the move by browsing the Reports and SharePoint site

Content created by – Sreeraj Rajendran
Content reviewed by – Romit Gulati