This blog post discusses SSRS in an Always On Availability Group. To make sure I'm staying laser focused on this topic, I need to cover some fundamentals.

SSRS installation (SQL Server 2017 and later)

Starting from SQL Server 2017, Microsoft decided to separate SSRS from a SQL Server installation container (ISO). Keeping the pros and cons of this change aside, that means that Microsoft decided that some customers would not need SSRS and adding a separate step to install it would effectively block an accidental "next-next" type of installation. If a separate SSRS installer is triggered, it will add all the necessary SSRS plumbing to SQL Server database engine, such as ReportServer and ReportServerTemp databases to support SSRS , database roles. It will also add many other pieces that are not related to SQL Server, such as web service, web application, etc. You can install it from here: https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/install-reporting-services?view=sql-server-ver15

Always On Availability Groups (a.k.a. Always On AG)

Always On Availability Groups is the latest and greatest Microsoft HADR (High Availability and Disaster Recovery) solution to keep SQL Server running in case of failure. Always On Availability Groups allows to enroll databases into a group that would be kept in sync between the active server (a.k.a. primary, supporting both read and write) database and passive (a.k.a. replica, supporting read only) databases. You can learn more here: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-sql-server?view=sql-server-ver15

SSRS Subscriptions

SSRS subscription is a saved configuration that is designed to simplify report rendering and delivery. You choose a report, format, schedule and a method of delivery, and suddenly your users are more self sufficient and you have more free time. Subscriptions work great when you want to offload some routine reporting needs and send the output data via an email or alternatively drop a file in file-share. You can learn more about SSRS subscriptions here: https://docs.microsoft.com/en-us/sql/reporting-services/subscriptions/subscriptions-and-delivery-reporting-services?view=sql-server-ver15

Problem

Once you have mission critical databases included in Availability Groups, it makes a total sense to add both ReportServer (storage for data sources, datasets, reports, etc.) and ReportServerTempDB (TempDB for SSRS) databases as well. This way, in case of a disaster recovery, your data sources (OLTP databases for example) and reporting platform (SSRS) are up and running at the same time without any manual steps.

With Always On Availability Groups (a.k.a. AG), both the data and schema are constantly (synchronous mode) or almost constantly (asynchronous mode) in-sync, but there is one user requirement. AG requires the same logins (stored in master that is not part of AG) and SQL Server Agent jobs (stored in msdb that is not part of AG). While it might look like adding ReportServer and ReportServerTemp databases to AG would be enough to have SSRS fully supported for HADR, unfortunately that is not entirely true.

With both SSRS databases on AG, most of the functionality is available, so the SSRS Report Portal will serve reports (records from the data sources, datasets, and catalog tables) and obey the security mechanism (users, policies, role tables), but SSRS subscriptions will suddenly stop working (despite the subscription table being in-sync). Any existing SSRS subscriptions will stop working regardless if the SSRS GUID job (unfortunately, Microsoft never got to make SSRS jobs user-friendly) is enabled or not. Trying to create a new SSRS subscription will be greeted with a very cryptic error message (Fig #1).

Here's a little known fact about SSRS: outside of the ReportServer database, SSRS is not only dependent on jobs and logins, but also on a custom database role called RSExecRole that is created by the SSRS installer. This role needs to exist in both master and msdb. Since both of these databases are not normally in AG (per server and not in-sync), and SSRS is not actually installed on the replica, SSRS subscriptions would no longer work.

Solution

The solution is fairly easy and will involve following Microsoft documentation that is designed for a scenario of moving SSRS from one server to another. https://docs.microsoft.com/en-us/sql/reporting-services/security/create-the-rsexecrole?view=sql-server-ver15ecrole?view=sql-server-ver15

The following code will create the RSExecRole database role in both master and msdb databases and will grant all the necessary permissions needed for SSRS subscription functionality.

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

Of course, it would be nice if the RSExecRole database role and permissions were created by default, regardless of the SSRS installation by making it part of the "system" objects. That would be a small price to pay for a happier DBA. Wouldn't it be nice? As the Beach Boys would sing it - "you know its gonna make it much better"!