Guidance for Using SQL Server BI Features in a SharePoint 2010 Farm

This topic summarizes feature availability based on the versions and editions of the software you are using. It also explains SharePoint 2010 installation requirements for using specific SQL Server features. For information related to SharePoint 2013, see Business Intelligence Features and SharePoint 2013 (SQL Server 2012 SP1).

In this topic:

  • General SharePoint Requirements

  • SharePoint 2010 Service Pack 1 (SP1) Requirement for SQL Server 2012

  • SharePoint Editions and BI Feature support

  • SharePoint 2010 Products Preparation Tool

  • Requirements and suggestions for running the SharePoint installation

General SharePoint 2010 Requirements

  • SharePoint 2010 products are 64-bit only. If you have a 32-bit installation of a previous version of SharePoint and Reporting Services installed in SharePoint integrated mode, you will not be able to upgrade to SharePoint 2010. For more information, review the SharePoint documentation.

  • Reporting Services includes an add-in for SharePoint products. Supported configurations for the add-in and report server are available at a more granular level than what is indicated here. For more information, see Supported Combinations of SharePoint and Reporting Services Components.

  • SharePoint developer tools only support a SharePoint standalone configuration. For more information, see the SharePoint documentation: Requirements for Developing SharePoint Solutions.

SharePoint Editions and BI Feature support

Some SQL Server 2012 Business Intelligence features are only supported on specific editions of SharePoint products.

Supported Features

SharePoint Product

  • Power View, a feature of SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint Server 2010 Enterprise Edition.

  • Reporting Services Data Alerts.

  • PowerPivot.

  • SharePoint Server 2010 Enterprise Edition.

  • General Reporting Services report viewing and feature integration with SharePoint.

  • SharePoint Server 2010 Standard and Enterprise Editions.

  • SharePoint Foundation 2010.

For more information, see Features Supported by the Editions of SQL Server 2012 and System requirements for Power View.

SharePoint 2010 Service Pack 1 (SP1)

It is recommended you update your SharePoint 2010 installation to SharePoint 2010 Service Pack 1 (SP1). SharePoint SP1 is required in the following cases:

  • You want use SQL Server 2012 version of the database engine for the SharePoint content databases or catalog databases.

  • You want to use PowerPivot and the PowerPivot configuration tool.

One of the primary reasons SP1 is required for SharePoint installations running with SQL Server 2012 is the database engine feature sp_dboption, that was deprecated in a previous release, is discontinued in the SQL Server 2012 release. For more information, see Discontinued Database Engine Functionality in SQL Server 2012

SharePoint 2010 SP1 Installation Guidance

Download SharePoint Server 2010 SP1 and apply it on all servers in the farm.

Note

On an existing farm, you will need to use one of the following additional steps to complete the SharePoint SP1 upgrade. For more information, see Known issues when you install Office 2010 SP1 and SharePoint 2010 SP1 and Description of SharePoint Server 2010 SP1:

  • SharePoint Products Configuration Wizard: Run the wizard to complete the SP1 upgrade and configuration.

  • Complete the upgrade with psconfig: Run the command psconfig –upgrade to complete the SP1 upgrade

For more information, see the “upgrade” section of (SharePoint Server 2010) and Resource center: Updates for SharePoint 2010 Products

SharePoint Installation with SQL Server BI Features

SharePoint 2010 Products Preparation Tool

The SharePoint Products Preparation Tool enables server roles in the operating system and installs other prerequisite software required by a SharePoint installation. The following table identifies additional steps to configure your server for SQL Server 2012.

Component

Action

Reporting Services Add-in

The SharePoint 2010 Products Preparation tool installs the SQL Server 2008 R2 version of the Reporting Services add-in. SQL Server 2012 includes a newer version of the add-in that is required for SQL Server 2012 features. The add-in can be installed using the SQL Server installation wizard as well as downloaded from MSDN. For more information on where to get the current version of the add-in and how to install it, see Where to find the Reporting Services add-in for SharePoint Products and Install or Uninstall the Reporting Services Add-in for SharePoint.

Analysis Services OLE DB Provider (MSOLAP)

SharePoint 2010 installs the SQL Server 2008 version of the OLE DB provider as part of an Excel Services deployment. This version does not support PowerPivot data access. You should install later versions of the provider on SharePoint servers that support PowerPivot data connections. For more information, see Install the Analysis Services OLE DB Provider on SharePoint Servers

ADO.NET Services

SharePoint 2010 lists ADO.NET services in the list of prerequisites, but the Prerequisite installer does not install it. To add ADO.NET services, you must install it manually. Installing ADO.NET services is required if you want to use SharePoint lists as data feeds to PowerPivot workbooks or Reporting Services reports. For instructions, see Install ADO.NET Data Services to support data feed exports of SharePoint lists.

Requirements and suggestions for running the SharePoint installation

Which SQL Server 2012 features you install and the installation order of those features will determine what levels of integration with SharePoint are possible. For example, while some level of feature integration is available through Reporting Services on a SharePoint server that uses a built-in database, most feature integration scenarios require a farm installation of SharePoint, because only a farm installation provides the infrastructure required for some BI features.

A SharePoint farm can be a single server or multiple servers. What sets a farm apart from a standalone installation is the presence of additional infrastructure, such as the Claims to Windows Token Service.

A farm is enabled when you choose the Server Farm option in SharePoint setup. You must have a farm installation if you want to use PowerPivot for SharePoint or Power View. The Standalone SharePoint installation is supported and common for development environments of Reporting Services. However a SharePoint farm installation is recommended for a production environment.

GMNI_SetupUI_SharePoint2010InstallType

 

A complete server installation is also required for PowerPivot for SharePoint or the Reporting Services shared service.

GMNI_SetupUI_SharePoint2010ServerType

 

The last page of the setup wizard prompts you to configure the farm and optionally configure a default web application and root site collection. Most setup users follow this installation path. If for some reason you do not configure the farm, you can use the farm configuration option in the PowerPivot Configuration Tool to configure the farm.

In previous releases, leaving the farm un-configured was recommended for installing PowerPivot because it reduced the steps needed when if you used SQL Server installation options to install PowerPivot for SharePoint in a ready-to-use state. In this release, it is no longer important. You can use the PowerPivot Configuration Tool to tune an existing farm so that it uses settings recommended for a PowerPivot for SharePoint installation.

Reporting Services can be installed into an existing SharePoint farm. You can install Reporting Services or SharePoint in any order, but the configuration of will vary depending on the order. For more information, see Add an Additional Report Server to a Farm (SSRS Scale-out) and Install Reporting Services SharePoint Mode in a Single Server Farm

GMNI_SetupUI_DoNotConfigureMOSS

See Also

Concepts

Business Intelligence Features and SharePoint 2013 (SQL Server 2012 SP1)

Other Resources

Installation and Deployment for SharePoint Server 2010

Multiple Servers for a three-tier farm (SharePoint Server 2010)