Rajendra Gupta
SSRS components

Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups

September 22, 2020 by

This is the 25th article in SQL Server Always On Availability Groups series. In this article, we will explore the high availability configuration for reporting services databases.

Configure SQL Server Reporting Services for standalone databases

SQL Server Reporting Services (commonly known as SSRS) is a service to deploy paginated reports for web and mobile. Data representation is an art, and SSRS helps you to visualize the data in an informative way using various graphs, charts, KPI, matrix.

SQL Server Reporting Services all together

Image Source: Microsoft docs

Until SQL Server 2016, the reporting services comes with the SQL Server installation. If we choose the reporting services feature, it installs the reporting services and configures its databases in the local instance.

Starting from SQL Server 2017, Microsoft separated the reporting services from the standard SQL Server installation. It is a standalone application that you can download, install, upgrade independently.

In the below image, you see the SSRS architecture diagram with various components involved. You can refer to the article, SQL Server Reporting Services Architecture and Component Topology to understand these components in detail.

SSRS components

As per best practice, we should use the reporting services separated from the database instance. For this article, firstly, we configure the reporting services for the following SQL environment.

  • Reporting Services: SQLNode3\INST1
  • Reporting services database: SQLNode1\INST1

It creates the [ReportServer] and [ReportServerTempDB] database on the SQLNode1\INST1. As of now, these databases are not part of any SQL Server Always On Availability Groups.

You can download the Microsoft SQL Server 2019 Reporting Services from the Microsoft.

Microsoft SQL Server 2019 Reporting Services

Launch the SSRS installation with the following welcome screen.

Launch the SSRS installation

Choose an edition. We use the developer edition of SQL Server Reporting Services 2019.

Choose an edition

On the next page, it shows that it installs the reporting services only. It does not install the SQL database engine for you that is required to store SSRS databases. You should install SQL Services on it or a separate instance. As highlighted earlier, we use the SQLNode1\INST1 for the SSRS databases.

install the reporting services

Select the reporting services installation directory. We can go ahead with the default directory.

installation directory

After installation, it asks you to configure the report server using the Reporting Services Configuration Manager. Click on Configure Report Server.

It opens the Reporting Services Configuration Manager. Click on Start to run the reporting services.

configure the report server

We do a minimum required configuration in this article. You can refer to this article, SQL Server Reporting Service Configuration Manager to understand complete SSRS configurations.

  • Service account configuration: In the service account page, specify a domain account to run the reporting service. It automatically restarts the reporting services once you apply the service accounts

    Service account configuration

  • Web Service URL: It prefills the default configuration for the SSRS web service URL

    Web Service URL

    Click on Apply, and it configures the web service URL. You can click on the hyperlink to open the web URL.

    Web URL

  • Report Server Database configuration

    First, we need to create the reporting services databases on the SQL instance that works as a primary replica. Currently, it does not show any SSRS database details

    Report Server Database configuration

    Click on Change Database. We do not have any existing database so choose “Create a new report server database”

    Create a new report server

    Specify the SQL instance name (Current primary replica) and authentication type. I use Windows authentication for the DB connection purpose

    Specify the SQL instance

    You get the flexibility to specify the database name in the report server wizard. By default, it creates a new database [ReportServer]. You should not change the database names unless you have any specific requirements

    report server wizard

    Specify the service credentials to connect with the report server database. SSRS wizard automatically assigns the required permissions for the SQL account we specify here

    service credentials

    Verify your configurations in the report server database configuration. You can note here that it creates two SSRS databases – ReportServer and ReportServerTempDB

    Verify your configurations

    On the next page, it completes the SSRS configurations, as shown below.

    SSRS configurations

    You can see the database name, SQL instance and the credentials in the database page.

    database name, SQL instance

    Similar to the Web Server URL, configure the Web Portal URL. You get the hyperlink for URL after configuration

    configure the Web Portal URL

    Connect to the SQL Server instance, and you can see both databases in the object explorer

    Connect to the SQL Server

Change the recovery model for SSRS databases

As shown above, reporting services databases have the following recovery models:

  • ReportServer – Full
  • ReportServerTempDB- Simple

As you know, for a database in SQL Server Always On Availability groups, we require databases in the full recovery model. Open the ReportServerTempDB database properties, click on the options and change the recovery model to FULL.

Our both SSRS databases are in the Full recovery model now.

Change recovery model for SSRS databases

SQL Server Always On Availability Groups for Reporting services database

In this article, we create a new availability group for both reporting services databases. You can add databases in an existing availability group as well. You can refer to earlier articles for detailed steps on creating a new availability group.

In the new availability group wizard, specify the AG group name.

availability group wizard

I took full backups of both databases; therefore, it meets prerequisites to be part of an AG.

SQL Server Always On Availability Groups

We have two nodes in the cluster. AG should be in the Synchronized mode.

Specify replicas

Specify listener name and IP address for this SQL Server Always On Availability Group. SQL listener is a must for the reporting services database in AG configuration.

SQL listener configurations

Once it creates the new SQL Server Always On Availability Group, launch the dashboard, and we see reporting services databases added successfully.

AG dashboard

Deploy a sample report and create a subscription

Deploy a report in your new SSRS configuration. You can refer to SSRS articles on SQLShack for reference purposes.

For this article, I deployed a sample SSRS report. This report connects to the [AdventureWorks] database and pulls the required data.

Deploy a sample report

I have also configured a report server subscription. This subscription export the report in PDF format and stores at a shared location on a scheduled time.

Report schedule

SSRS schedule creates a SQL Server agent job on the SSRS database instance. Connect to your SQL instance and verify that the job exists. You can filter the agent jobs using the Report Server category. Do not modify this job manually in SSMS. You must use SSRS reports for any changes like report schedule, frequency, shared location or email id specifications.

SQL Server agent job

Reconfigure Report Server database configurations to SQL listener

At this step, we have done the following configurations so far in the article.

  • Our reporting services databases are configured in the SQL Server Always On Availability Group
  • We deployed a sample SSRS report

Think of a question now- If the AG failover happens and SSRS databases become active on the current secondary ( after failover – primary) what happens to my report subscriptions?

As you have seen earlier, the report subscription is a SQL agent job. Do you need to create these jobs on the new primary replica manually? I have hundreds of report subscriptions; Do I need to create all jobs?

We instructed earlier to not perform any changes to these jobs manually. If we script out jobs and configures on the new primary replica, Does it work?

Hold your horses! We have configured the databases in the high availability, but our report server is not aware of any changes? If you perform a failover, the report server won’t recognize the new primary replica instance.

Switch to the Report Server Configuration Manager -> Database -> Change Database ->Choose an existing report server database.

Report Server database configurations

In the server name, specify the SQL listener that we created earlier for SSRS SQL Server Always On Availability Group. A SQL listener always points to the primary replica. Once we configure the listener in the report server configuration, in case of failover, it automatically connects to the databases on a new primary replica.

SQL listener

Select the report server database from the drop-down.

the report server database

Specify the service credentials.

service credentials

Verify your configuration. You can verify the SQL Server instance is the SQL listener name along with the port number.

SQL Server instance

It reconfigures your report server configuration.

report server configuration

Verify the listener’s name in the database tab.

Verify the listener

Failover testing for SQL Server Reporting Services (SSRS) in a high availability mode

For testing purposes, perform a manual failover. It makes the SQLNode2\INST1 as the new primary replica.

Failover testing for SQL Server Reporting Services

Our first test passed. AG dashboard is healthy for both SSRS databases after failover as well.

Healthy dashboard

Launch the SSRS portal and view the sample report we deployed. It is also working as expected. It passes the second validation.

Launch the SSRS portal

The third validation is for the report server subscription. Select the report subscription and run it manually.

server subscription

It runs fine. You can verify the last run and the result. In case of any error, you get an error message in the result column.

Subscription status

For the final validation, instead of running the report subscription manually, schedule it to run after 2-3 minutes from the current time. It also runs successfully.

final validation

Now it is time to recall your earlier questions. We can see the report schedule SQL agent job on the new primary replica. We have not created it manually.

Job status

SSRS automatically creates the report subscription jobs on the new primary replica. Microsoft recommends restarting the SQL Server reporting services after the AG failover as well. You should do the following tasks in case any failover happens using a SQL agent job.

  • Restart SQL Server reporting services after failover
  • Remove the SSRS jobs from the old primary replica. It ensures that during failback, all SSRS jobs are created with the latest configurations

Conclusion

In this article, we configured the SQL Server Reporting Services in a high availability solution using SQL Server Always On Availability Configuration. It ensures your reports are always running if one of your database instances has some issues. You can go through these Microsoft documentations: High availability in SQL Server Reporting Services and Reporting Services with Always On Availability Groups (SQL Server).

Table of contents

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups
Add a new node into existing SQL Server Always On Availability Groups
Configure Managed Service Accounts for SQL Server Always On Availability Groups
Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts
Database-level health detection in SQL Server Always On Availability Groups
Automatic Page Repair in SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
An overview of distributed SQL Server Always On Availability Groups
Deploy a distributed SQL Server Always On Availability Group
Monitor and failover a Distributed SQL Server Always On Availability Group
Transparent Data Encryption for SQL Server Always On Availability Groups
Configure SQL Server replication for a database in SQL Server Always On Availability Groups
Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups
Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups
Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups
Restore an existing availability group database participating in SQL Server Always On Availability Groups
Exploring AG dashboards for monitoring SQL Server Always On Availability Groups
Backup compression in TDE enabled databases in SQL Server Always On Availability Groups
Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups
Suspend and Resume Data Movement in SQL Server Always On Availability Groups
Explore failover types in SQL Server Always On Availability Groups
Explore SQL Server PolyBase External Tables in SQL Server Always On Availability Groups
SQL Server Always On Availability Groups for SQL Server Linux instances
Column-level SQL Server encryption with SQL Server Always On Availability Groups
Make the most of secondary replicas in SQL Server Always On Availability Groups
Apply SQL Server patches or cumulative updates in SQL Server Always On Availability Groups

Rajendra Gupta
676 Views