July 8, 2024 at 4:19 pm
I'm planning to migrate an SSRS 2016 server (with scale-out deployment) to a new set of hardware in order to upgrade the OS from Win 2012 to a modern OS. I would like to upgrade from SQL/SSRS 2016 to 2019 in the process. I've found and read all of the Microsoft documentation on migrating SSRS. Where I'm stuck is guidance / things to plan for in restoring the SQL 2016 ReportServer and ReportServerTempDB databases onto a SQL 2019 server.
The instructions for Upgrading SSRS say "To upgrade from Reporting Services 2016 and older versions to Reporting Services 2017 and later, follow the Migrate a Reporting Services Installation (Native Mode) article, with Reporting Services 2017 or later as your destination instance." The Migrate a Reporting Service Installation page that is referenced has no information on using a newer SQL version as the destination instance.
It says "There are restrictions on the editions of SQL Server that host the report server database. Review the following topic (Create a Report Server Database) if you are reusing a report server database that was created in a previous installation." But that page is about creating a new RS database. I need to migrate the existing one.
There is a page referenced for moving the report server databases to another computer, but that also contains no information regarding moving the DBs to a newer SQL version.
And that's the dead end.
Has anyone done this specific thing recently (migrate SSRS to new server and go from SSRS 2016 to SSRS 2019 in the process) and have any advice on this missing information?
Thanks!
July 9, 2024 at 3:46 pm
I've found https://www.sqlservercentral.com/articles/migrating-sql-server-reporting-services
To migrate from 2014 to 2016. 2016 to 2019 should be similar.
Don't have experience with scale-out.
Beware, from 2017/2019 MS disabled multiple SSRS instances on the same server https://chad-franklin.com/2020/11/16/installing-multiple-ssrs-instances-on-a-server-is-a-thing-of-the-past/
July 10, 2024 at 3:40 pm
Thanks Jo! From what I understand from Microsoft documentation, SSRS 2017 and newer is significantly different from SSRS 2016 (it's a separate installer vs being included in the SQL server installer) and the upgrade path is different.
I found these instructions, which say that the Reporting Services DBs should be restored from the SQL 2016 server and then set to DB Compatibility to 2019.
https://www.mssqltips.com/sqlservertip/6768/side-by-side-ssrs-install-upgrade-minimize-downtime/
July 11, 2024 at 7:59 am
That's how we did it. Most important things are the encryption keys
July 17, 2024 at 7:05 am
You can use backup and restore to move the SSRS DBs. Each time SSRS starts up it checks a SSRS DB version number against the desired number and automatically upgrades the DBs if they are at a lower level. This is needed to cope with SSRS changes when you install a CU, and works just as well across SQL versions.
It is a similar process to what SQL Server does for all DBs on startup, but SSRS has its own extra step to do what it needs.
If you are on Enterprise Edition with SA then consider installing in-house PowerBI instead of SSRS. You get a much more capable reporting tool for no extra cost.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply