How to: Move the Analysis Services Database to a Separate Server

You can increase the capacity of a data-tier server for Team Foundation by moving the SQL Server Analysis Services database of the data warehouse for Team System to a separate data-tier server. After you complete this procedure, the data tier will comprise one server that hosts the relational databases and one server that hosts the database for Analysis Services.

Before you start this procedure, you must set up the new server for Analysis Services with the same configuration settings that you used on the existing data-tier server. For more information, see the installation guide for Team Foundation, which you can find on the Microsoft Web site.

To move the Analysis Services database to a separate server, you must perform the following procedures:

1. Stop Visual Studio Server Task Scheduler

2. Verify that the Warehouse Controller Web Service is Idle

3. Grant the New Server Access to the Relational Database for the Data Warehouse

4. Update the Registration Database to Use the New Server

5. Rebuild the Analysis Services Database for the Cube

6. Rebuild the Team System Cube

7. Redirect SQL Server Reporting Services to Use the New Data Source

8. Restart Visual Studio Server Task Scheduler

Required Permissions

To perform these procedures, you must have the following permissions in SQL Server:

• You must be a member of the sysadmin security group in the database instance for Team Foundation and in the Analysis Services database of the data warehouse for Team System.

• You must be a user in the TfsWarehouse relational database.

• You must be a member of the TFSEXECROLE database role.

Also, you must be a member of the Team Foundation Administrators security group, or the server-level Administer warehouse permission must be set to Allow. For more information about permissions, see Team Foundation Server Permissions.

In addition to these permissions, you might need to address the following requirements on a computer that is running Windows Server 2008 or Windows Vista:

• To follow a command-line procedure, you might need to open an elevated Command Prompt by clicking Start, right-clicking Command Prompt, and clicking Run as Administrator.

• To follow a procedure that requires Internet Explorer, you might need to start it as an administrator by clicking Start, clicking All Programs, right-clicking Internet Explorer, and then clicking Run as administrator.

• To access Report Manager, reports, or Web sites for SQL Server Reporting Services, you might need to add these sites to the list of trusted sites in Internet Explorer or start Internet Explorer as an administrator.

For more information, see the Microsoft Web site.

Stop Visual Studio Server Task Scheduler

To stop Visual Studio Team Foundation Server Task Scheduler

• On the application-tier server, open Computer Manager, and stop the Visual Studio Team Foundation Server Task Scheduler service.

  For more information about starting and stopping services, see How to: Stop and Start Services, Application Pools, and Web Sites.

Verify that the Warehouse Controller Web Service is Idle

To verify that the warehouse controller Web service is Idle

1. On the application-tier server, open Internet Explorer, type the following string in the Address bar, and press ENTER:

   https://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx

   The ControllerService page opens.

2. Click GetWarehouseStatus.

3. On the GetWarehouseStatus page, click Invoke.

   If the warehouse service is idle, it returns an XML document that indicates the status of the service.

   <WarehouseStatus …>Idle</WarehouseStatus>

   Warning

   If the service is busy, wait until it becomes idle before you continue with the next steps.

Grant the New Server Access to the Relational Database for the Data Warehouse

To grant the new server access to the relational database for the data warehouse

1. On the server that hosts the relational databases for Team Foundation, click Start, point to All Programs, point to Microsoft SQL Server, and then click SQL Server Management Studio.

   The Connect to Server dialog box opens.

2. In Server type, click Database Engine.

3. In Server name, click the name of the existing data-tier server and database instance, and then click Connect.

4. In Object Explorer, expand the node for the existing data-tier server, and then expand the Security folder.

5. Right-click the Logins folder, and then click New Login.

6. On the General page, click Search.

   The Select User or Group dialog box opens.

7. Type the domain account for the server that will host Analysis Services. Click Check Names, verify that the account name resolves to a known account, and click OK.

   Note

   The form of the domain account for a computer is Domain\ComputerName$. For example, the domain account for a computer that is named MyServer in a domain that is named MyDomain would be MyDomain\MyServer$.

8. Click the User Mapping page, and select the TFSWarehouse check box.

9. Under Database role membership for: TFSWarehouse, select the TFSWarehouseDataReader and public check boxes, and then click OK.

Update the Registration Database to Use the New Server

To update the registration database to use the new server

1. On the server that hosts the relational databases for Team Foundation, in SQL Server Management Studio, open Object Browser, expand Databases, expand TFSIntegration, expand Tables, click dbo.tbl_database, and then click New Query.

2. In the query window, create the following query except substitute the name of the new server.

   INSERT INTO [TfsIntegration].[dbo].[tbl_database]
   ([fk_registry_entry_id], [name], [dbname], [servername], [connection], [excludebackup])
   SELECT [fk_registry_entry_id]
         ,'BISANALYSIS DB'
         ,[dbname]
         ,'New Data-tier Server for Analysis Services'
         ,[connection]
         ,[excludebackup]
     FROM [TfsIntegration].[dbo].[tbl_database]
     WHERE [name]= 'BISANALYSIS DB'
   

3. On the Query menu, click Execute.

Rebuild the Analysis Services Database for the Data Warehouse

By rebuilding the Analysis Services database, you create the database on the new server, but you do not populate the database with any data.

To rebuild the Analysis Services database for the data warehouse

1. On the application-tier server, open a Command Prompt window, and change directories to Drive:Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools.

2. Type the following command, and wait for it to finish:

   SetupWarehouse.exe -rebuild -sDataTierServerName-dTFSWarehouse -c warehouseschema.xml -ra TFSReportServiceAccount -a TFSServiceAccount -mturl http://ApplicationTierServerName:Port -edt TfsBuild

   You must replace the arguments as the following table describes:

   Argument	Description
   DataTierServerName	The name of the server that hosts the relational databases for Team Foundation.
   TFSReportServiceAccount	The name of the service account for Reporting Services in Domain\UserName format.
   TFSServiceAccount	The name of the service account for Team Foundation Server in Domain\UserName format.
   ApplicationTierServerName	The name of the application-tier server.
   Port	The port number of Web services for Team Foundation. The default value is 8080.

   Note

   The -d option must specify TfsWarehouse, the name of the data warehouse database for Team System.

Rebuild the Team System Cube

By rebuilding the Team System cube, you populate the Analysis Services database for the data warehouse with data from the operational databases for Team System.

To rebuild the Team System cube

• Rebuild the Team System cube.

  For more information, see How to: Rebuild the Team System Cube.

Redirect Reporting Services to Use the New Data Source

To redirect Reporting Services to use the new data source

1. On the server that hosts Reporting Services, open an Internet browser, type the following string in the Address bar, and press ENTER:

   https://localhost/Reports

   If you have deployed a named instance on the primary data-tier server, type the following string instead:

   **https://localhost/Reports_**TFSInstance

   The Report Manager page opens.

2. Click TfsOlapReportsDS.

3. In Connection String, specify the new server that hosts SQL Server Analysis Services as the data source.

   For example, type the following string:

   Data source=AnalysisServicesServerName\InstanceName;initial catalog=TfsWarehouse

4. Retype the password for the stored credentials, and then click Apply.

Restart Visual Studio Team Foundation Server Task Scheduler

To restart Visual Studio Team Foundation Server Task Scheduler

• On the application-tier server, open Computer Manager, and start the Visual Studio Team Foundation Server Task Scheduler service.

See Also

Tasks

How to: Rebuild the Team System Cube

Other Resources

Team Foundation Server Data Warehouse

Change History