By: Mason Prewett

 

This article covers a Key2 client’s upgrade to SQL 2016 of a SQL Server Reporting Services instance in SharePoint Integrated Mode (in a SharePoint 2013 environment). You can follow the information in this article to conduct your own upgrade.
 

Before You Begin

1. Back up the SSRS encryption key – I cannot stress enough the importance of this step. If you end up having to delete and recreate your SSRS service application, you will lose the encryption key. If the key is lost, all SSRS data sources will lose their connection strings and default to the “Prompt for Credentials” authentication method. See here for how to manage encryption keys for SSRS.

 

2. Backup your reporting databases – Back up your existing report database, the reporting alert database, and the reporting temp database. The report database is the most important for recovery. The alerting database contains report subscription information. The report temp database contains session and service info. The temp database may not even be useful in a recovery situation, but it is better to have these things than not.

 

The Environment We Upgraded

Versions

• SharePoint 2013
• SharePoint system database instance was on SQL Server 2014 (I chose not to upgrade this. Read the section below titled “SQL Server 2016 for a SharePoint System Instance” to see why.)
• SQL Server Reporting Services 2014 in SharePoint Integrated Mode

Servers

• 2 Web Front Ends (WFE)
• 2 Applications – SSRS was installed and running on both
• 1 Central Admin server
• 1 SQL Server – SharePoint content and configuration databases live on this. The Report databases also live on this server, but no action needs to be taken against these for an SSRS upgrade

 

The Upgrade Process

1. Upgrade SQL Server Reporting Services – On all app servers running SSRS, run the main SQL Server 2016 install. Select “Upgrade from Previous Version” and select all Default options. Reporting Services – SharePoint should be selected by default and – if it’s already installed – the Reporting Services Add-In for SharePoint will also be selected. If not, rerun the installer, select “New Installation”, and select the SSRS Add-In from the Feature Selection screen. Reboot when the install is completed.

 

2. SQL Server Reporting Services Add-In For SharePoint 2013 – Run the main SQL Server 2016 install and select “Upgrade from Previous Version”. It should recognize that the SSRS 2014 Add-In is already installed and should show up in the upgrade list. Reboot when the install is completed.
   • Do this on all WFE, APP, and Central Admin servers. Central Admin is important on this step – do not leave it out!
   • Important – Do not install the MSI that is obtained from Microsoft’s website. We had issues with this version and there is a lot on the web about how this version can cause issues.

Those two steps are really all that is needed for the upgrade itself, but much testing is needed to make sure everything is working properly. Primarily checking to see if the SSRS services can be started and stopped from Central Admin. See the next section for troubleshooting tips based on issues we encountered when upgrading.

 

Issues That We Ran Into

SQL Server Reporting Services Service Stuck on Stopping or Starting
Once our installation was complete, everything seemed to be working well until we had to restart the SSRS services on our app servers. The services got stuck when starting or stopping, depending on which state they were in. The issue turned out to be our Central Admin server not having the updated SSRS 2016 Add-In installed. In order for Central Admin to communicate with the app servers with SSRS installed, the add-in must be present on the Central Admin server.

To make matters worse, simply installing it would not resolve the “stuck on starting/stopping” issue. The service needed to be removed from all app servers then added back to resolve the sticking issue. Also, these services must be removed and added back one server at a time to avoid having to remove the service application. To do this, run the following PowerShell commands from each app server in sequence:

1. Install-SPRSService –uninstall
2. Install-SPRSServiceProxy –uninstall

Don’t fret when an error pops up saying that there is a dependency with the service application; as long as there is one server still running SSRS, it will remove the service from SharePoint. After this change our services were able to start and stop without any issues.

Incorrect Formatting and Blank Reports on RSViewerPage
After our installation was complete, the reports in one of our environments were displaying jumbled formatting of the RSViewerPage. The Print, Cancel and Done buttons were all on separate lines, there were a couple of random empty drop-down lists, and the reports were rendering blank. Below is a screenshot of what we were seeing.

This cause of the issue turned to be the web.config file being set to debug mode. To ensure that debug mode is disabled, confirm that the following values are set for the web.config files of all web applications on all WFE servers.

1. CallStack=”false”
2. customErrors mode=”On”
3. debug=”false”

PowerPivot Add-In
We also had PowerPivot installed on our SharePoint system with the SQL Server 2014 PowerPivot Add-Ins for SharePoint 2013 installed on all servers. At one point we removed these and the SSRS Add-Ins stopped installing. It gave an error message that said “Failure 1603”, but the underlying issue turned out to be removing PowerPivot. The reason for this wasn’t clear (especially since it is the 2014 version), but I thought it was worth mentioning.

 

SQL Server 2016 for a SharePoint System Instance

Remember I mentioned at the beginning that we chose not to upgrade the SharePoint system database instance to SQL Server 2016? That’s because the upgrade is technically not supported by Microsoft. Here’s why:

Short Answer: SharePoint 2013 with SQL Server 2016 has not been tested and verified for compatibility by Microsoft.
 
Long Answer: Microsoft only tests version compatibility for SharePoint and SQL Server based on an N+1 basis. If SharePoint is on version 2013, then SQL Server would have been on version 2012 at the time of its release. N+1 for SQL Server 2012 would be SQL Server 2014. Microsoft won’t test compatibility beyond this.

Read more about this topic.

*Note – My team did update the SharePoint 2013 SQL Server system instance to 2016 in our model farm environment before we found out about the potential compatibility issues. Everything seemed to work fine, but – to be safe – we made the decision to not update our Dev and Prod systems.

 
 

Keep your data analytics sharp by subscribing to our mailing list!

Thanks for reading! Keep your knowledge sharp by subscribing to our mailing list to receive fresh Key2 content around data analytics, business intelligence, data warehousing, and more!
 
 

Grow your analytics knowledge.

Leave this field empty if you're human:

 
 
 
 

---

Key2 Consulting is a data warehousing and business intelligence company located in Atlanta, Georgia. We create and deliver custom data warehouse solutions, business intelligence solutions, and custom applications.