We recently upgraded our test environment from SQL Server 2005 SP3 to SQL Server 2008 R2 SP1 to take advantage of remote blob storage and additional reporting capabilities offered by the newer version (report builder 3.0).

The upgrade went without a hitch so I decided to document the steps I took to get it working.

Procedure

Here are the steps I took, in sequential order:

  1. Perform a complete backup of the farm prior to the upgrade.
  2. Upgrade an existing SQL instance to SQL Server 2008 R2 (Enterprise) and install additional features.
  3. Install the Reporting Services role on our SharePoint Application Server.
  4. Install SQL Server 2008 R2 Service Pack 1.
  5. Configure SQL Server Reporting Services
  6. Microsoft SQL 2008 R2 Reporting Services Add-in for Microsoft SharePoint 2010 on all Web front ends servers.
  7. Configure Reporting Services in SharePoint Central Administration.
  8. Test the report server.
  9. Activate the Reporting Services service in SharePoint.
  10. Start Building Reports
Perform a complete backup of the farm prior to the upgrade.
It is recommended to perform a complete farm backup before proceeding with the following steps. Our organisation uses DPM for backups so I took a complete snap shot of the farm on the night before the upgrade.
Upgrade an existing SQL instance to SQL Server 2008 R2 (Enterprise) and install additional features.
Run the SQL Server 2008 R2 installer. You should always check your hardware and software requirements prior to performing the upgrade. Use the System Configuration checker and upgrade advisor as well to ensure a successful installation.
  1. Under the SQL Server Installation Center, select Upgrade from SQL Server 2000, SQL Server 2005, or SQL Server 2008.
  2. When the installation starts, select Add features to an existing instance of SQL Server 2008 r2.
  3. Choose the instance you wish to upgrade and click Next.
  4. Select the features you wish to install, I chose to install all features except for Reporting Services (more on that later).
  5. Click Next, the installation wizard will perform a few checks.
  6. Under the Server Configuration step, select the account you wish to use if you selected any new services.
  7. Click Next and finish the installation wizard.
Install the Reporting Services role on our SharePoint Application Server.
Next, install the Reporting Services role on one of your SharePoint servers.
  1. Perform the above steps but select only to install Reporting Services.
  2. Select a database instance to install the Reporting database to. I chose the same instance as my SharePoint server.
The reason for this is that SharePoint integration works only if the Reporting Services feature and an installation of SharePoint 2010 reside on the same server. This basically leaves you with two options, install SharePoint on the database server, or install the reporting services role on an application or web frontend server.
Note: You only need to install the reporting services role on 1 server for SharePoint Reporting Services integration.

Install SQL Server 2008 R2 Service Pack 1.
Download and install the latest service pack from Microsoft.

Configure SQL Server Reporting Services
On the server you installed reporting services on, run SQL Server Reporting Services Configuration Manager
Start -> All Programs -> Microsoft SQL Server 2008 R2 -> Configuration Tools -> SQL Server Configuration Manager.
Connect to your reporting services server and complete the wizard as follows:

 

Microsoft SQL 2008 R2 Reporting Services Add-in for Microsoft SharePoint 2010 on all Web front ends servers.

Once Reporting services has been configured, you need to download and install Microsoft SQL 2008 R2 Reporting Services Add-in for Microsoft SharePoint 2010 on the WFE. The add-in must be installed on ALL WFEs in the farm.

Browse to:  http://www.microsoft.com/downloads/details.aspx?FamilyID=B3BEBF9D-D86D-48CD-94E2-0639A846BE80&displaylang=ar&displaylang=en

After installation, run the farm configuration wizard.

Configure Reporting Services in SharePoint Central Administration.

Open Central Administration – > General Application Settings -> Reporting Services Integration

Note: If you are not using Kerberos authentication, you must select Trusted Account, otherwise you will get the following error when you try to Set server defaults:

Exception encountered for SOAP method GetSystemProperties: System.Net.WebException: The request failed with HTTP status
401: Unauthorized.at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.SetConnectionProtocol()
at
Microsoft.SqlServer.ReportingServices2010.RSConnection2010.SoapMethodWrapper`1.ExecuteMethod(Boolean
setConnectionProtocol)

Click on OK, if all is successful you will see the following screen:

If you get an error message on the second step saying “unable to find WSS_WPG group”, it means you need to install SharePoint on the server you have provisioned for reporting services.

To check whether your configuration is successful so far, click on Set Server defaults. You should see the following page:

Click OK to confirm your settings.

Test the Report Server

If you have gotten everything right so far, you should now see a list of all your site collections if you browse to your report server URL: http://server/reportserver.

Activate the Reporting Services service in SharePoint

Go to the web application where you activated Reporting Services. You will see two new services which you need to activate.

Site Collection Features

Site Features

Start Building Reports…

At this stage, your SharePoint farm should be fully configured with reporting services integration. We can now start building our reports. The two tools that are commonly used are:

  • Microsoft SQL Server Report Builder
  • SQL Server Business Intelligence Development Studio

Tagged: ,