How to: Move the Analysis Services Database to a Separate Server
Updated: July 2010
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:
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.
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.
To verify that the warehouse controller Web service is Idle
On the application-tier server, open Internet Explorer, type the following string in the Address bar, and press ENTER:
The ControllerService page opens.
On the GetWarehouseStatus page, click Invoke.
If the warehouse service is idle, it returns an XML document that indicates the status of the service.
If the service is busy, wait until it becomes idle before you continue with the next steps.
To grant the new server access to the relational database for the data warehouse
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.
In Server type, click Database Engine.
In Server name, click the name of the existing data-tier server and database instance, and then click Connect.
In Object Explorer, expand the node for the existing data-tier server, and then expand the Security folder.
Right-click the Logins folder, and then click New Login.
On the General page, click Search.
The Select User or Group dialog box opens.
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.
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$.
Click the User Mapping page, and select the TFSWarehouse check box.
Under Database role membership for: TFSWarehouse, select the TFSWarehouseDataReader and public check boxes, and then click OK.
To update the registration database to use the new server
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.
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'
On the Query menu, click Execute.
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
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.
Type the following command, and wait for it to finish:
SetupWarehouse.exe -rebuild -sDataTierServerName-d TFSWarehouse -c warehouseschema.xml -ra TFSReportServiceAccount -a TFSServiceAccount -mturl http://ApplicationTierServerName:Port -edt TfsBuild
You must replace the arguments as the following table describes:
The name of the server that hosts the relational databases for Team Foundation.
The name of the service account for Reporting Services in Domain\UserName format.
The name of the service account for Team Foundation Server in Domain\UserName format.
The name of the application-tier server.
The port number of Web services for Team Foundation. The default value is 8080.
The -d option must specify TfsWarehouse, the name of the data warehouse database for Team System.
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.
To redirect Reporting Services to use the new data source
On the server that hosts Reporting Services, open an Internet browser, type the following string in the Address bar, and press ENTER:
If you have deployed a named instance on the primary data-tier server, type the following string instead:
The Report Manager page opens.
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
Retype the password for the stored credentials, and then click Apply.