In this blog post, we will discuss the concept of integrating SQL Server Reporting Services (SSRS) with an Always On Availability Group (AG). This integration ensures that SSRS remains highly available and functional in case of a failure.

SSRS Installation

Starting from SQL Server 2017, Microsoft made the decision to separate SSRS from the SQL Server installation container. This means that SSRS is no longer installed by default, and a separate installation step is required. The separate SSRS installer adds all the necessary components to the SQL Server database engine, such as the ReportServer and ReportServerTemp databases, as well as the web service and web application.

If you need to install SSRS, you can follow the instructions provided in the official Microsoft documentation: Install SQL Server Reporting Services.

Always On Availability Groups

Always On Availability Groups (AG) is a Microsoft High Availability and Disaster Recovery (HADR) solution for SQL Server. It allows you to enroll databases into a group that is kept in sync between an active server (primary) and passive servers (replicas). This ensures that the databases remain available and up-to-date in case of a failure.

If you want to learn more about Always On Availability Groups, you can refer to the official Microsoft documentation: Always On Availability Groups – SQL Server.

SSRS Subscriptions

SSRS subscriptions are a powerful feature that allows you to schedule and automate report rendering and delivery. With subscriptions, you can choose a report, format, schedule, and method of delivery, making your users more self-sufficient and freeing up your time. Subscriptions are particularly useful for routine reporting needs, such as sending reports via email or dropping them in a file-share.

If you want to learn more about SSRS subscriptions, you can refer to the official Microsoft documentation: SSRS Subscriptions and Delivery.

The Problem

When you have mission-critical databases included in an Availability Group, it is important to include the ReportServer and ReportServerTempDB databases as well. This ensures that both the data sources (OLTP databases) and the reporting platform (SSRS) are available in case of a disaster recovery scenario. However, simply adding these databases to the AG is not enough to fully support SSRS for HADR.

With both SSRS databases in the AG, most of the functionality is available, and the SSRS Report Portal can serve reports and obey the security mechanism. However, SSRS subscriptions will stop working, despite the subscription table being in sync. This is because SSRS is dependent not only on jobs and logins but also on a custom database role called RSExecRole, which needs to exist in both the master and msdb databases.

The Solution

The solution to this problem is fairly straightforward. You need to create the RSExecRole database role in both the master and msdb databases and grant it the necessary permissions for SSRS subscription functionality. Microsoft provides documentation that guides you through this process: Create the RSExecRole Database Role.

Here is an example of the code that creates the RSExecRole database role and grants the necessary permissions:

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

It would be ideal if the RSExecRole database role and permissions were created by default, regardless of the SSRS installation. This would simplify the setup process and ensure a smoother experience for DBAs.

As the Beach Boys would sing it – “you know it’s gonna make it much better”!