In my current job as a DBA, one of my missions is making everything as automated as possible and making sure that when things go wrong(trust me, they will), the applications that are using my environment can smoothly continue to run with less intervention possible.

You will make mistakes which is part of the job, learning from those mistakes is what makes you grow. Today I want to share with you a few tips that will make your experience with SSRS Databases and Availability Groups less painful.

Setup the SSRS databases to use the Listener

The very first step to do is to create the Reporting Services Databases in the Database Engine. One important point is to make sure the SQL Server Name used to create the databases is the listener name of the Availability Groups.

As you can see in the above picture, the Report Server Database was created pointing to a listener, which makes our SSRS databases to be accessible in case of a failover.

In this point of time, it is already possible to access your Reporting Services instance, but the same it is not High Available yet.

Adding the SSRS Databases to the Availability Groups

Adding the SSRS DB’s to AG is the same process of adding any other user database, so here you can choose if you want to do backup/restore, seeding, etc..

After you have added the same, you can just check the status of it.

Removing the sysadmin permissions

By default the Service Account used for running the Reporting Services Service, is granted the sysadmin privileges in the instance, which most of the times facilitates the management, but it brings another account with too much permissions. In my environment, I prefer for SSRS to remove the sysadmin permissions of the Reporting Service service account.  The code to execute is below and should be executed in all the cluster nodes.

https://gist.github.com/marcosfreccia/18c8c3e9bd8d6e9dc3600c29aec6da4b.js

Permissions and Roles

When both SSRS Databases are deployed as the first time in the instance, some objects are created outside the SSRS Database scope. Those objects are created in non High Available Database such as: master and msdb. By removing the sysadmin permissions, we need to make sure the Service Account has proper permissions to perform the normal activities.

The following table describes where and which permissions the account should have.

Roles

As noted above, the RSExecRole plays an important part in the whole process and it is vital to the Reporting Services functionality, when the same doesn’t have sysadmin permissions. The following scripts helps to generate the proper script for creating RSExecRole in the other nodes of the cluster.

https://gist.github.com/marcosfreccia/2d12e4e550aaf748c03b93518b089929.js

This script above you should execute in the node of your cluster where the Reporting Services Database has been created for the first time. The results that you get from it, you should execute in the secondary node of your cluster, doing this guarantees that after failovers, SSRS can still continue to work properly.

I don’t have a complex SSRS deployment and this has been working in production for quite some time. We have subscriptions that performs the following activities.

• Send Email
• Create XML files in shares
• Create html files in shares
• KPI’s
• Shared Data Set’s
• Shared Data Sources

Failover of the SSRS Databases

If you have completed the steps before, failovers are just normal as failing over any other user database. This also includes the SSRS jobs for Subscriptions and KPi’s to be recreated in the new primary node. For removing the jobs from the secondary, I use a PowerShell script that takes care of this cleaning up activity.

In this documentation created by Microsoft: Reporting Services with Always On Availability Groups (SQL Server), they have a different set of approaches for failing over the Databases and post failover activities. I honestly never had to do something like that, but make sure to read it and test in your environment.

I hope you enjoyed this blog post. If you do something different and would like to share with me, leave a comment in this post.

Marcos Freccia
Data Platform MVP