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:
- Perform a complete backup of the farm prior to the upgrade.
- Upgrade an existing SQL instance to SQL Server 2008 R2 (Enterprise) and install additional features.
- Install the Reporting Services role on our SharePoint Application Server.
- Install SQL Server 2008 R2 Service Pack 1.
- Configure SQL Server Reporting Services
- Microsoft SQL 2008 R2 Reporting Services Add-in for Microsoft SharePoint 2010 on all Web front ends servers.
- Configure Reporting Services in SharePoint Central Administration.
- Test the report server.
- Activate the Reporting Services service in SharePoint.
- Start Building Reports
- Under the SQL Server Installation Center, select Upgrade from SQL Server 2000, SQL Server 2005, or SQL Server 2008.
- When the installation starts, select Add features to an existing instance of SQL Server 2008 r2.
- Choose the instance you wish to upgrade and click Next.
- Select the features you wish to install, I chose to install all features except for Reporting Services (more on that later).
- Click Next, the installation wizard will perform a few checks.
- Under the Server Configuration step, select the account you wish to use if you selected any new services.
- Click Next and finish the installation wizard.
- Perform the above steps but select only to install Reporting Services.
- Select a database instance to install the Reporting database to. I chose the same instance as my SharePoint server.
Install SQL Server 2008 R2 Service Pack 1.
Configure SQL Server Reporting Services
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.
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
