Upgrade SQL Server Reporting Services to 2017

Posted in: Microsoft SQL Server, Technical Track

In the past, we could run the SQL installer to do an in-place upgrade of SQL server including the SSRS instance to a newer version. As of 2017, SSRS is a separate install from SQL server, so this is no longer possible. In fact, if you do an in-place upgrade of SQL 2014 to 2017 for example, you will see a warning that SSRS will be uninstalled. Before proceeding with the SQL upgrade, follow these steps to upgrade the SSRS instance.

SQL 2017 Upgrade

Steps to Upgrade SSRS to 2017

1. Backup the report server databases.

2. Open Report Server configuration manager and backup the SSRS encryption key. Make sure you remember or save the password used.

Backup SSRS Encryption Key

3. Note down SSRS settings and configurations from Report Server config manager such as service account, web service, and report manager URLs, databases, email settings, etc. You will need this information to configure the new SSRS instance.

Save old SSRS settings and configuration

4. Install SSRS 2017 using the new installer which can be downloaded here. You will need a product key to install on a production environment. You can use the key from the SQL server setup.

Use the product key from the SQL 2017 setup

5. In SQL Server Config Manager, turn off the old SSRS instance and disable it. The URL reservation occurs at the service start, so we must disable the old one to make sure it doesn’t start accidentally.

Disable the old SSRS instance

6. Open the new Report Server configuration manager and connect to the new instance (named SSRS) to configure it using the settings from step 3 (report database, report and web service URLs, service account, etc). Be aware that once you configure the report server database, the database will be upgraded to be compatible with SSRS 2017 and will no longer work with older SSRS versions. The database does not need to be on a SQL 2017 instance. When you configure the URLs, you can use the same ones as the old instance. You may get a warning that the existing URL reservation got overwritten.

7. Restore the SSRS encryption key using the backup from step 2.

Restore SSRS encryption key

8. If you test the report URL at this point, depending on the edition, you may see an error such as “Scale-out deployment is not supported in this edition of Reporting Services”.  This is because there is still a link to the old instance in the ReportServer database. Connect to the report server database and delete the key which points to the old SSRS instance from the reportserver.dbo.keys table. The old instance name on your environment may vary, in our case it is MSSQLSERVER. The new instance name is SSRS, do not delete this one.

Delete the reference to the old SSRS instance in ReportServer database

DELETE [ReportServer].[dbo].[Keys]
WHERE InstanceName LIKE 'MSSQLSERVER'

9. Test your report URL and ensure the reports are functioning.

Rollback

If there are any issues with the new SSRS instance, you can easily rollback to the old version using the following steps:

  1. Turn off and disable the SSRS 2017 instance and re-enable the older SSRS instance.
  2. Restore the report server databases using the backup taken previously.
  3. Restore the SSRS encryption key
  4. Delete the key which points to the new instance from the reportserver..keys table (instance name = SSRS)
DELETE [ReportServer].[dbo].[Keys] 
WHERE InstanceName LIKE 'SSRS'
email

Interested in working with Alexandre? Schedule a tech call.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *