Reviewed for accuracy on January 23, 2023
One of our client was using SSRS2008R2 (Native Mode) and they decided to upgrade to SSRS2017. There are two methods available that can fulfil this upgrade. One by using some scripting and another one by physically moving ReportServer database to the new server. Physically moving ReportServer database to the new server is a recommended method by Microsoft and we also decided to go with this method. Below are our step-by-step upgrade journey details.
Installation of SQL Server Reporting Services to a New Server
We have suggested to infrastructure team to install Sql Server 2017 on a new server and choose “Install Only” option during Reporting Services Configuration step.
We will configure Reporting Services in below steps after installation of Reporting Services 2017.
Backup Encryption Key
As a next step, backup the encryption key from old report server instance. The report server encryption key is backed up using the Reporting Services Configuration Manager tool. Search for Reporting Services Configuration Manager in old report server instance and connect with the old report server instance name. Reporting Services Configuration Manager tool will look like as following screenshot.
In the navigation panel on the left, select Encryption Keys option (highlighted in above screenshot), then click Backup. Enter a Password in the Encryption Key Information dialog and click the button next to Key File to specify a location and name for the key backup file. At the end, Click Exit to exit from the Reporting Services Configuration Manager.
Backup ReportServer and ReportServerTempDB databases
As a next step, backup the old report server databases using any supported method for backing up SQL Server database. We choose to backup the databases using SQL Management Studio. Microsoft states that both the ReportServer and ReportServerTempDB databases should be backed up and restored to the new server. In SQL Management Studio expand Databases and right click the ReportServer database, select Tasks and select Back Up….
Make sure the Backup Type is set to Full and specify a location and filename for the backup.
Do the same as above to take the backup of ReportServerTempDB database.
Migration of old ReportServer database on New Reporting Services instance
As a first step, you will need to create the RSExecRole on new instance of sql server 2017. Click this link to see the detail instructions. You will see below script in instruction that needs to be executed at new instance of Sql Server 2017.
USE master;
GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [type] = 'R' AND [name] = 'RSExecRole') BEGIN
CREATE ROLE [RSExecRole];
END
GRANT EXECUTE ON dbo.xp_sqlagent_enum_jobs TO [RSExecRole];
GRANT EXECUTE ON dbo.xp_sqlagent_is_starting TO [RSExecRole];
GRANT EXECUTE ON dbo.xp_sqlagent_notify TO [RSExecRole];
GO
USE msdb;
GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [type] = 'R' AND [name] = 'RSExecRole') BEGIN
CREATE ROLE [RSExecRole];
END
GRANT EXECUTE ON dbo.sp_add_category TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_add_job TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_add_jobschedule TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_add_jobserver TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_add_jobstep TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_delete_job TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_help_category TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_help_job TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_help_jobschedule TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_verify_job_identifiers TO [RSExecRole];
GRANT SELECT ON dbo.syscategories TO [RSExecRole];
GRANT SELECT ON dbo.sysjobs TO [RSExecRole];
GO
As a next step, restore the ReportServer and ReportServerTempDB databases to the new instance of Sql server 2017. In SQL Management Studio expand databases and right click the ReportServer database, from the pop up menu select Tasks/Restore/Database.
Once the ReportServer database has been restored repeat the process for the ReportServerTempDB database restore.
Now the databases have been moved, you must use the Report Services Configuration tool to configure Reporting Services. Search for Reporting Services Configuration Manager tool in new report server 2017 instance and connect with the new report server instance name. If Reporting Services service is stopped during the restore process click the Start button to start the service. If needed configure the URLs for the Report Server and Report Manager, instructions can be found in the Migration document under the Configure the Report Server section. For Reporting Services to recognize there is a different database you must go through the Configure Database process. In the Navigation panel click Database then click the Change Database button.
The following images shows the screens and settings when configuring the ReportServer database.
Now restore the Encryption Key that you have backed up earlier. In the Navigation panel of the Reporting Services Configuration Manager select Encryption Keys and click the Restore Button. In the Restore Encryption Key dialog click the … button to locate the file you created when backing up the Encryption Key from the server you are migrating, enter the password you used and click the OK button.
Under Scale-out Deployment in above screen, make sure there is only one instance of reporting services listed. If there is more than one instance exists, delete the old one.
Hurray!!! We have migrated old report server to the new report server instance. After the migration, there are additional challenges that we will discuss in next post.
