Overview
When upgrading a SQL Server installation to SQL2017, SQL Server Reporting Services (SSRS) is now a stand-alone installation. This short article lists the main issues that we encountered, as best as I can recall.
Background
Earlier versions of SQL Server included SSRS as part of the installation. Now it is a seperate installation, much like SSMS. This article will detail a few of the surprises and alterations that we had to make as a result of this, while upgrading our SQL Server 2012 four node Availability Group.
The First Surprise
When installing SQL Server 2017, as an upgrade to 2012, the installation process informs you that SSRS is no longer part of the SQL Server installation. The recommendation is to backup your SSRS encryption key and ensure the reporting database is backed up, as it states it will remove this.
Actually, it doesn’t remove the reporting database (and I would like to believe that you have a regular, healthy backup process in place anyway) which would have been rather interesting within an AG. This rather ominous message is displayed even you have already installed the new SSRS.
The Second Surprise
Our configuration has two AGs installed on each of four nodes – two separate instances on each server that are part of a separate AG. So, two AGs running on four servers. Imagine Server 1, Server 2, Server 3 and Server 4. Each of these has Instance A and Instance B. Instance A of each server is in one AG and Instance B of each server is in another AG.
When installing SSRS it became apparent that it could not be installed on a particular instance. SSRS now can only be installed once on each server, so you no longer have the option to specify an instance to install against. I have been unable to find official Microsoft documentation that details this but it has been mentioned here and here as well as several other places. It has not proved to be popular.
In our case, we have had to point the Server 1 and Server 2 SSRS to Instance A and the SSRS installation for Server 3 and Server 4 to Instance B. Not ideal but it works.
This also means that you have to be careful with the ‘Scale Out Deployment Status’ tab, ensuring that each installation of SSRS only lists those instances that it is intended for.
The Third Surprise
When installing the SSRS software, it needs to have access to the writable node of the reporting database, so it can only be installed on the Primary node each time. So, to install SSRS on a four-node AG, we had to failover to each of those nodes and install SSRS on the current Primary node. In hindsight, this might have been prevented by the action taken later – during the fourth surprise.
The Fourth Surprise
The ‘Web Portal URL’ tab. Now, it might look like it has the correct entry in there (in our case a virtual directory of ‘Reports’) but that doesn’t mean that it has actually done anything with it. To be safe, force it to refresh by retyping the name within the Virtual Directory field and then click on ‘Apply’. This forces a refresh of various entries and might remove a few peculiar errors.
The Fifth Surprise
Having configured SSRS with the changes detailed above, it did appear to work for a short while. However, a couple of days later we started seeing the error message
“An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.”
There were a couple of issues that produced this error.
One was that we were using an account that was no longer valid, for this new SSRS configuration, as detailed here.
The other was based around the earlier issue, where installing it wasn’t possible unless it could access the writeable copy of this database. This also applies when using SSRS on a day-to-day basis. Therefore, instead of connecting the SSRS software to an instance, connect it to the Listener, so it always points to the Primary node. This might have prevented the installation issue detailed in the Third Surprise, but at this point we weren’t going to back to the beginning to check that theory out. We had pretty much lost the will to live.
And Finally
I normally write my articles with a bit more research and examples than this piece. However, this was all unearthed during the upgrade to the live installation (no, please don’t ask why it couldn’t all be tested thoroughly beforehand – that way lies madness) and it isn’t something that I could repeat without an excessive amount of effort. And it must be said – once was enough.
I’ve written this brief article because SQL Server 2012 is shortly going out of any support from Microsoft (July 12th 2022) and I suspect that several others might have the same issues, as they upgrade away from SQL 2012. There appear to be scant resources for the issues we encountered, so I hope that this may be of additional help.
Notes on SQL 
One thought on “Upgrading SQL 2012 AG to SQL 2017 – the fun to be had with SSRS”