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.
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()
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
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
- Save document library as template option not available
- Short break
- Speed up SharePoint using the IIS Blobcache
- Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter.
- Microsoft SharePoint is not supported with version 4.0.30319.296 of the Microsoft .Net Runtime.
- SharePoint Keeps Prompting for Credentials Problem SharePoint keeps prompting you for credentials in the following scenarios: You ...
- Security Token Service Application- Broken Problem Had an issue today on one of my developer's VMs. ...
- User Profile Service Stuck on Starting Problem You have followed Harbar's Rational Guide to setting up the ...
- Event 8313 Topology – Load Balancer EndpointFailure – SearchService.svc Problem Encountered the following error while analysing the logs on our ...
- Event 6398 and 5586 SharePoint Foundation Problem Event logs were getting filled with the following errors: Event 5586, ...
- The security validation for this page is invalid Problem: After applying SharePoint 2010 Service Pack 1 and June 2011 ...
- The local farm is not accessible. Cmdlets with FeatureDependencyId are not registered. Problem You install .NET Framework 4.0 on your SharePoint 2010 WFE ...
- Start a workflow using PowerShell Requirement Start a workflow on all / specific items in a ...
- Using Export-SPWeb to export libraries / lists This is a simple one but many people get the ...
- Unable to change User Profile Service Account Problem So you made a mistake by trying to change the ...