Introduction

While SSRS is an old legacy software in today’s Power BI standards, it’s still there in the many company’s tranches. It’s still included with SQL Server licensing, and it’s still good for something.

This blog post is going to cover the necessary steps to accomplish moving SSRS from one server to antoher.

Problem

If you are working for a company that didn’t adopt or didn’t fully adopt PowerBI, you might be asked to migrate SSRS from one server to another.

Moving SSRS between 2 different servers is not hard, but it’s not trivial either. Part of the problem is, some of the SSRS configuration are not actually stored inside ReportServer database, but rather stored into msdb database, especially SSRS email subscription part.

Solution

Currently, there are 7 steps:

1. Backup SSRS encryption key on your old server (Fig #1)

Fig #1

2. Take database backup of both ReportServer and ReportServerTempDB databases on the old server and restore to the new server

Fig #2

3. Download SSRS from Microsoft by clicking on Install SQL Server Reporting Services will effectively take you to the download page (Fig #3)

Fig #3

4. Run SSRS installer (Fig #4) and have your SQL Server license key ready (Fig #5). For some odd reason, Microsoft wants to collect it for SSRS

Fig #4
Fig #5

5. Choose change database (Fig #6) and choose existing report server database (Fig #7)

Fig #6
Fig #7

6. Restore SSRS encryption key (Fig #8)

Fig #8

7. Recreate RS_Exec role

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

Commentary

I wish Microsoft could just include Step #7 in a Step #6 (deploy RS_Exec role to msdb database if needed). This logic is already there when you make a new ReportServer database. Just copy that logic and run it for existing report server database as well.

Disclaimer

This blog post would not be possible without the following resources:

https://learn.microsoft.com/en-us/sql/reporting-services/security/create-the-rsexecrole?view=sql-server-ver16