Deployment Checklist: Scale-out by adding Power Pivot Servers to a SharePoint 2010 farm

  • [アーティクル]

If you anticipate a high volume of requests for Power Pivot query processing in a SharePoint farm, you can add an extra Power Pivot for SharePoint instance to seamlessly add new query and data processing support.

After you install a new instance, you will have additional capacity for querying Power Pivot data or processing Power Pivot data refresh jobs. Optionally, you will have the choice of configuring each server to handle one type of request: query or data refresh.

Prerequisites

SharePoint Server 2010 is installed and configured.

SharePoint Server 2010 SP1 is applied and the farm is upgraded.

The existing Power Pivot for SharePoint instance in the farm is SQL Server 2016 (either a new installation or upgraded from SQL Server 2008 R2).

The computer on which you are installing the new SQL Server 2016 Power Pivot for SharePoint server is joined to the farm. The computer and the other servers in the farm must be in the same domain.

For additional information regarding system and version requirements, see Guidance for Using SQL Server BI Features in a SharePoint 2010 Farm.

Note

In a multi-server farm, all Power Pivot for SharePoint instances must be at the same version. If you applied service packs or updates to other Power Pivot servers that are already in the farm, the new instance you are adding must be updated to the same version as the existing instance that is already in the farm. The new instance will be unavailable until the updates have been applied.

Steps

Use this checklist to add additional Power Pivot servers to a SharePoint farm. These instructions assume that you already have a Power Pivot for SharePoint server in the farm, and that you are adding a second server to handle additional processing load. Except for differences in installation requirements, post-install configuration, and verification, the steps for deploying a scale-out solution are identical to adding a single Power Pivot server to an existing farm.

Step

Link

Determine the service account of the Analysis Services instance that is already in the farm

Each additional instance you install must run under the same account as the first instance. Use either approach to determine the service account:

  • In Central Administration, in the Security section, click Configure Service Accounts. Select Windows Service – SQL Server Analysis Services. After you select the service, the service account name will appear in the page.

  • On a server that already has a Power Pivot service installation, open the Services console application in Administrative Tools. Double-click SQL Server Analysis Services. Click the Log On tab to view the service account.

    Important

    Only use Central Administration to change service accounts. If you use another tool or approach, permissions will not be updated correctly in the farm.

Run Setup to install a second instance of Power Pivot for SharePoint

Install Power Pivot for SharePoint 2010

Choose an application server that is joined to the farm, but does not have an existing Power Pivot instance on the server.

During Setup, when prompted to specify a service account, enter the account from the previous step. All instances of the Analysis Services service must run under the same domain account. This requirement enables the use of the managed accounts feature in SharePoint that lets you update the password in one place for all service instances of the same type.

Configure the second instance

You can use either approach to configure the instance: Power Pivot Configuration Tools or Power Pivot Configuration using Windows PowerShell

When configuring a second instance, you only need to provision the local services. All other configuration tasks (such as creating service applications or configuring data refresh) are performed during the initial configuration, and used by subsequent instances that you install.

Post-installation tasks

No further steps are specifically required. You do not need to create service applications, activate features, deploy solutions, or change service application identity. Existing Web applications and service applications will discover and use the new server software automatically.

Optionally, if you installed a second server for the purpose of using one server for queries and one for data refresh, you can configure server instance properties now to specify the type of requests handled by each server. For more information, see Configure Dedicated Data Refresh or Query-Only Processing (PowerPivot for SharePoint).

Verify installation of the second instance

You can use the following steps to verify Power Pivot query processing on the server you just installed.

  1. In Central Administration, open the Manage services on server page to confirm that the server and its services appear.

    1. In Server, click the down arrow, click Change Server, and then select the server that has the new Power Pivot for SharePoint installation.

    2. Verify that SQL Server Analysis Services and SQL Server Power Pivot System Service are started.

  2. In Central Administration, stop other Power Pivot for SharePoint servers so that the server you just installed is the only one available. For more information, see Start or Stop a Power Pivot for SharePoint Server.

  3. Click a Power Pivot workbook to open it from the library.

  4. Click a slicer or pivot the data to initiate a query. The server will load Power Pivot data in the background. In the next step, you will connect to the server to verify the data is loaded and cached.

  5. Start SQL Server Management Studio from the Microsoft SQL Server program group in the Start menu. If this tool is not installed on your server, you can skip to the last step to confirm the presence of cached files.

  6. In Server Type, select Analysis Services.

  7. In Server Name, enter <server-name>\powerpivot, where <server-name> is the name of the computer that has the new Power Pivot for SharePoint installation.

  8. Click Connect.

  9. In Object Explorer, click Databases to view the list of Power Pivot data files that are loaded.

  10. On the computer file system, check the following folder to determine whether files are cached to disk. The presence of cached files is further verification that your deployment is operational. To view the file cache, go to the \Program Files\Microsoft SQL Server\MSAS13.POWERPIVOT\OLAP\Backup folder.

  11. Restart the services that you stopped earlier.

See Also

Tasks

Initial Configuration (Power Pivot for SharePoint)

Concepts

Power Pivot for SharePoint 2010 Installation

Power Pivot Server Administration and Configuration in Central Administration