We will be going over the implementation of automatic failover setup for reporting services using availability groups. The report server databases can be added to the High availability group to be part of a replica but in case of failover the reporting services will not be able to use report server database from new primary replica until some manual or automated steps performed. We will cover Automated set up in this article.

Pre-requisites:-

• SSRS feature needs to be installed, refer Install Reporting Services

• Create account for report server database connection and configure it with appropriate permissions, refer Report server service account create and configure

• Add report server Databases to AG group.

Reporting Services Databases setup

We have two node cluster with AG group configured as “AGO1” and reporting services feature is installed. We will add the reporting services databases to the high availability group. To utilize Always On Availability Groups for reporting databases, you need to configure the database connection to use the Listener DNS Name. To configure that, use Reporting Services Configuration Manager.

Click on the Change Database option and select option as below

In the following screen, put the following parameters as

Server Name: – Availability Group Listener Name

Username: – Use the Domain service account which you have created in Pre-requisite section.

In the Database tab, Point to the Report server DB

Click to view slideshow.

Next Steps to complete the failover can be performed manually in case of Always on group failover or they can be automated. These steps are: –

• Stop the SQL server agent service on non-primary instances.

• Start the SQL server agent service on new Primary instance.

• Stop the reporting server services on the reporting server machine.

Automation of these tasks can be done by using windows task scheduler. We will be creating windows job on all the involved cluster instances which will be triggered by certain event types. Windows task can be created as below :-

Fill in the job name and the user account which will be used to run the task. Click on the trigger and fill in the details of the trigger conditions based on event ID as per screen below

a) Bounce the SQL server agent in case of failover is initiated. We will create the windows task which will be triggered when it see event type 41075.

b) On the SQL server replica which is becoming new primary after the failover we need to bounce the SQL server agent services and report services by creating Windows task which is triggered by event ID type 41074 .

c) On all servers in cluster, SQL server agent job needs to be started or stopped based on status of the replica. If the replica is primary SQL agent is started and if the replica is secondary it will stop the SQL server agent service.
Determining Replica Status:-

SQL server replica status can be determined by using SQL attached Check_Replica_Status

Use the reporting web portal url using the AO listener which will be working irrespective of Always on group failover to any replica like http://AOGroupListener/Reports/browse/

Example :-  http://sqlAO1/Reports/browse/