How to: Move the Data Warehouse SQL Server Analysis Services Database to a Separate Server

  • Article

This topic describes how you can increase the capacity of your Team Foundation Server data-tier by moving the SQL Server Analysis Services database to a separate server. After you complete this procedure, the data-tier will be composed of two computers: the relational data-tier server, and the analysis services data-tier server.

Before you start this procedure, you must set up the new server with SQL Server 2005 Analysis Services using the same configuration settings that you used on the existing data-tier server. For more information, see the Team Foundation Server Installation Guide available from the Microsoft Web site (https://go.microsoft.com/fwlink/?LinkId=73908).

Important

This procedure requires that you have installed Visual Studio 2005 SP1.

Required Permissions

To perform this procedure, you must be a member of the Administrators group for the Team Foundation databases and a member of the Team Foundation Administrators group. For more information about permissions, see Team Foundation Server Permissions.

To stop the TFSServerScheduler service and verify that the warehouse Web service is not running

  1. On the application-tier computer, click Start, click All Programs, click Administrative Tools, and then click Services.

  2. In the Services window, select the TfsServerScheduler service.

  3. On the Action menu click Stop.

  4. Click Start, click All Programs, click Administrative Tools, and then click Internet Information Services (IIS) Manager.

  5. In the Internet Information Services (IIS) Manager window, expand the application-tier computer, expand the Web Sites node, expand the Team Foundation Server node, expand the Warehouse node, and then select the v1.0 node.

  6. Right-click the file warehousecontroller.asmx and then click Browse.

  7. On the ControllerService page, click GetWarehouseStatus.

  8. On the GetWarehouseStatus page, click Invoke. If the warehouse service is not busy, it returns an XML document that indicates the status is Idle.

    <WarehouseStatus …>Idle</WarehouseStatus>

    Warning

    If the warehouse is not idle, wait until GetWarehouseStatus returns Idle before moving on to the next steps.

To grant the new computer access to the warehouse relational database

  1. On the application-tier computer, click Start, click All Programs, click Microsoft SQL Server 2005, and then click SQL Server Management Studio.

  2. In the Connect to Server dialog box, select the Database Engine server type, select the name of the existing data-tier server from the server name list, and then click Connect.

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

  4. Right-click the Logins folder and then select New Login.

  5. On the General page, enter the domain account for the new analysis services data-tier computer.

    Note

    The form of the domain account for a computer is [domain]\[computer name]$. For example, the domain account for a computer named MyServer in a domain named MyDomain would be MyDomain\MyServer$.

  6. Select the User Mapping page, select the TFSWarehouse database, select the TFSWarehouseDataReader and public roles, and then press OK.

To update the TFSIntegration database to use the new analysis services data-tier server

  1. In the SQL Server Management Studio Object Browser, expand the TFSIntegration database, expand tables, select dbo.tbl_database, and then click New Query.

  2. In the query window, enter the following query, substituting your new analysis services computer name.

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

  3. In the Query menu, click Execute.

To rebuild the analysis services database

  1. On the application-tier computer, click Start, click All Programs, click Accessories, and then click Command Prompt.

  2. At the command prompt, change directories to the Team Foundation Server tools folder.

    cd %PROGRAMFILES%\Microsoft Visual Studio 2005 Team Foundation Server\Tools

  3. At the command prompt, run the command setupwarehouse as shown here to rebuild the analysis services database.

    setupwarehouse.exe -o -a serviceaccount -ra reportingaccount -c warehouseschema.xml -s primarydatatierserver -d TFSWarehouse -mturl https:// applicationtierserver :8080

To restart the Team Foundation Server data warehouse application pool

  1. Click Start, click All Programs, click Administrative Tools, and then click Internet Information Services (IIS) Manager.

  2. In the Internet Information Services (IIS) Manager window, expand the application-tier computer, expand Application Pools, and then select the TFS AppPool application pool.

  3. On the Action menu, click Stop, and then click Start.

To process the Team Foundation Server data warehouse cube

  1. On the application-tier computer, click Start, click All Programs, click Administrative Tools, and then click Internet Information Services (IIS) Manager.

  2. In the Internet Information Services (IIS) Manager window, expand the application-tier computer, expand the Web Sites node, expand the Team Foundation Server node, expand the Warehouse node, and then select the v1.0 node.

  3. Right-click the file warehousecontroller.asmx and then click Browse.

  4. On the ControllerService page, click Run.

  5. On the Run page, click Invoke.

    Note

    The Run method returns True.

  6. Reopen the ControllerService page, and then click GetWarehouseStatus.

  7. On the GetWarehouseStatus page, click Invoke. If the warehouse service is not busy, it returns an XML document that indicates the status is Idle.

    <WarehouseStatus …>Idle</WarehouseStatus>

    Warning

    If the warehouse is not idle, wait until GetWarehouseStatus returns Idle before moving on to the next steps. This may take minutes or hours, depending on the size of the cube.

To update the data source to use the new analysis services data-tier server

  1. Use your Internet browser to open the SQL Server Reporting Services page on the application-tier server. For example:

    https:// application-tier /Reports

  2. On the SQL Server Reporting Services page, select the TfsOlapReportsDS data source.

  3. Change the Data Source value of the connection string to the new analysis services data-tier server.

  4. Reenter the password for the stored credentials and then click Apply.

To start the TFSServerScheduler service

  1. On the application-tier computer, click Start, click All Programs, click Administrative Tools, and then click Services.

  2. In the Services window, select the TfsServerScheduler service.

  3. On the Action menu, click Start.

See Also

Reference

SetupWarehouse

Other Resources

Team Foundation Server Data Warehouse