SQL2012 SSRS on a SharePoint 2010 Farm using SQL2008R2

 

SQL 2012 SSRS contains two important enhancements.

1. Data can be exported using Excel 2007/2010 file formats – users can now forget the Excel 2003 row limitations when exporting reports.
2. Data Driven Alerts based user specified rules and schedules. Rules can be set on specific datasets and when triggered email can be sent out to individuals or groups

In addition SQL 2012 SSRS

1. Is easier to deploy
2. Is part of the SharePoint Service Application Architecture
3. Uses Claims to Windows Token Service to simplify delegation scenarios
4. Supports PowerView in the browser for a rich reporting experience
5. Managed in SharePoint instead of multiple locations
6. Support for claims-based authentication
7. SSRS events are logged to SharePoint ULS

Most of the SharePoint implementations used are SharePoint 2010 and in most cases after just getting everything stable, businesses do not want to move either to SharePoint 2013 or SQL 2013 just yet. So this article deals with how to make use of SQL2012 SSRS on an existing SharePoint 2010 farm that is using SQL2008R2. This will work on multi-server farms as well as on a single server farm.

There are two methods by which SSRS can be deployed. In both cases and .rdl files that exist are not destroyed. They do need to have their data mapping revised.

 

Method 1: Using the Existing SQL2008R2 database server(s). [Preferred Method]

1. Pre-requisites:

• Windows Server 2008R2 operating system with SP1 as a minimum with all the security patches on all SharePoint servers (WFE and application)
• .Net 4.0 on all SharePoint servers (WFE and application). This is available on the SQL2012 installation media or it can be downloaded from Microsoft.
• SQL 2008R2 SP2 has to be installed at a minimum.
• Check through Central Admin that all the correct servers in the farm are showing up and there are no extra ghost servers.

2. Installation:

• Using the SQL Install Media install install the  Reporting Services – SharePoint and Reporting Services Add-In for SharePoint Products on all  SharePoint servers
• Run the SharePoint Administrative Shell as administrator and register the service by running the two commands “Install-SPSRSService” and “Install-SPSRSServiceProxy”. On successful deployment in each case just the shell prompt will be returned (no message will be displayed).   
• Start the service by using the command “get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance”
• Configure SSRS (through Central Admin) and create the ReportingDB on the SQL2008R2, SP2 database server
• Use SSRS

 

Method 2: Using a SQL2012R2 database for Report Services on one of the Front End servers.

This is a method that should be used for testing purposes only of if a client wants to use it in production they should be made aware that unless there is a proper backup procedure and redundancy this has a single point of failure for the Reporting services.

1. Pre-requisites:

• Windows Server 2008R2 operating system with SP1 as a minimum with all the security patches on all SharePoint servers (WFE and application)
• .Net 4.0 on all SharePoint servers (WFE and application)
• Check through Central Admin that all servers in the farm are showing up and there are no extra ghost servers

2. Installation

• Using SQL 2012 Install Media install the Database Engine,  Reporting Services – SharePoint, Reporting Services Add-In for SharePoint Products and SQL Server Data Tools on one of the SharePoint Servers (preferably an Application Server)
• Using SQL 2012 Install Media install the  Reporting Services – SharePoint and Reporting Services Add-In for SharePoint Products on all the remaining SharePoint servers
• Run the SharePoint Administrative Shell as administrator and register the service by running the two commands “Install-SPSRSService” and “Install-SPSRSServiceProxy”. On successful deployment in each case just the shell prompt will be returned (no message will be displayed).
• Start the service by using the command “get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance”
• When configuring SSRS (through Central Admin), point the database to the SharePoint server that contains the SQL2012 Database instance
• Use SSRS

Notes:

1. Though there is a download called rsSharePoint.msi that is available from Microsoft this represents only the Reporting Services Ad-In for SharePoint Products and by deploying this download on all the SharePoint servers I have not been able to get SSRS working.
2. Since the license key (or evaluation) is required with the SQL component install check with your Microsoft Rep what the licensing requirements of your particular organization are to deploy this in production.

Upgrading to SQL Server 2012 R2

 
This article will provide a step-by-step set of instructions with screenshots wherever possible of the upgrade of a non-clustered SQL 2008R2 server (with SSRS belonging to a SharePoint 2010 farm) to SQL Server 2012 R2. The entire SharePoint farm is on one server.
Hardware Prerequisites: Since this SQL server belongs to a SharePoint 2010 farm, I assume that it has all the required hardware.
Software Requirements: Requires SQL Server 2008R2 with SP1. Also requires the .Net 4.0 frame work. I will show screenshots of what happens if you do not have either installed. As mentioned before, since this is a part of a SharePoint 2010 farm, software will be 64-bit only. In any case you cannot upgrade a 32-bit SQL server to 64-bit.SQL server. A complete list of software and hardware requirements can be found here. There are some errors in this document which will be highlighted in this article.
1. Insert the SQL Server 2012 Installation CD or map a drive to the installation files and click on xxx. The following screen pops up.

 
2. Click on System Configuration Checker. A utility will start that checks the various components to see if there is anything that will prevent a successful upgrade.
One warning appears. Since this development  farm is running everything on a single server including AD, just press OK.
3. From the Installation Center screen that appears again select “Install Upgrade Advisor” and follow the prompts to install the product.

  4. Install Upgrade Advisor by accepting the defaults and clicking Next> and then Install as shown in the following two pictures.




ERROR 1. Here ere is where an error pops up – this should have been picked up by the System Configuration Checker that was run in step 1.

The Microsoft link shown above does not work. So Google (or Bing, or use your favorite search engine) and look for the KB. Download the missing component “Microsoft SQL Server 2012 Transact-SQL Scriptdom” Click OK aboveto cancel the Microsoft Sql Server 2012 Upgrade Advisor Setup and install the downloaded pre-req by clicking Next> on the install window




Error 2 pops up. After doing a lot of confused looking at files and permissions on my system and not finding the dll in question I finally found out that this error is caused because the .Net 4.0 framework has not been installed. If for some reason you have a feature on your SQL server that required the .Net 4.0 framework prior to the upgrade and you installed the framework this error should not come up.

Click on Cancel, install the ,Net 4.0 framework, reboot the server and run the install the Upgrade advisor again. On successful completion, click the Finish button.

5. Run the Upgrade Advisor

On the Upgrade Advisor Screen, click on the link “Launch the Upgrade Advisor Analysis Wizard


Click Next> on the Welcome Screen

Select the items you want checked and click Next>. You will notice that two items have not been checked. Go ahead and try checking them. You will be informed that Notification Services (or Data Transformation Services) have been removed in SQL 2012.


On the Connection Parameters screen, select the SQL instance you want to check and click Next>

The SQL server parameters are brought up as shown, Scroll down the database list and you will see all your SharePoint databases. click Next>



The Reporting Services Parameters will be brought up (only if reporting services is running on the same server. If it is running on a different server the Upgrade Advisor has to be run on that server). Click Next>

The Analysis Service Parameters are brought up. Click Next>

The SQL Server Integration Services Parameters dialog box displays which allows you to select the SSIS packages if they are kept at a location different from the default location and to provide a password if the SSIS packages are password protected. Make your selections and click on Next>

Finally the dialog box confirming what you are about to do pops up and you can look at all the settings and if they appear to be in order, click Run

The upgrade advisor runs and on completion shows the following screen where you notice a warning. Click on the Launch Report Button to look at the upgrade advisor report. [You could also click on close and launch the report by clicking on the second link that is shown in the 2nd picture at the start of this process (item #5)

Strangely, no warnings are shown in the Report. Lets look at the report in detail. Click on Open Report



The report shows the warnings. You are also informed that these changes have to be implemented after the upgrade.

Clicking on the link shown above brings a list of affected objects. Make a note of these objects and close all the windows.

6. Finally, Upgrade SQL Server. Click on the Installation option on the top left and then on the screen that shows up select the Upgrade option


The setup rules will run. If all the rules pass, click on OK

 

Enter the product key and click Next>


Accept the License terms and click Next>



Check Include SQL Server product updates. As you can see SP1 is out so when you click Next> only the changed binaries will be downloaded and installed.

Setup Rules are run. A warning is displayed as I have everything running on one server. Click on Next>


Choose the SQL instance you want to upgrade using the dropdown



You are presented with a dialog box where you cannot make any changes, just look to see that all the items you want are there and click on Next>

Click on OK to upgrade on the next screen and the upgrade runs. After completion the following window appears

Click Close. However you are not done yet. You have to run the upgrade for each instance of SQL server. In this case, the Report Server is also an instance that has to be upgraded. I am not going to go detail the whole process again just show a few screens.
When the screen to select an instance to upgrade appears, notice that they build numbers on SQL Server and Report Server and their respective shared services are different. That is because the Report Server (Power Pivot) has not yet been upgraded. Select Report Server from the drop down and click Next>



The upgrade rules run. When they complete, click Next> and then Upgrade to run the upgrade

When complete , click Close and you are done

Remember to go into SQL 2012 and make any additional changes that are required from the last screen shot in item #5. Since I did not require them I did not make the changes. I may make the changes in future in which case I will update this document.
Note: This upgrade was done with SharePoint Server in read only mode and the search services stopped so that the databases would not change during the upgrade.
____________________________________________________________________________