High Availability(HA) of Sql Server Reporting Service(SSRS)

MS SQL Server provide Reporting Service (SSRS) which help customers easily generate database status report like login user list or frequency of some event. As long as there are corresponding records in database, customers can schedule report processing or do it on demand by querying.

But when providing such service, providers need to cover this part when planning high availability (HA) on their system. HA of SSRS can be broken down to three parts: service, RS database (RSDB), and main database.

For the service part, you only need to deploy more servers. For the main database, as all database HA, you can apply cluster or mirroring on it. Since you can set failover partner in the connection string of RS datasource, RS will connect to the backup database server when failover without downtime.

The most difficult part is the HA of RS database. Since you can only set one database in RS server, at least not support until 2008, as reference in ReportServer (TempDB) Mirror capability, the setting won't switch to backup database server when failover.

In such situation, you can separate RS databases which means each RS has its own RSDB. When any one of the RS+RSDB failover, others will still work fine. But since there is no sync in the RSDBs, so you need to import the templates and datasource to each RSDB. At the same time, you will hardly query the report history since they will be recorded in different RSDBs.

The second choose is pure mirroring, set all RS to the same RSDB, as reference to How to: Configure a Report Server Scale-Out Deployment (Reporting Services Configuration). By this way, you need to manually set all RS to the backup RSDB when failover. Still, you can use rsconfig.exe to set RSDB by SQL server agent service, as reference to Reporting Services Disaster Recovery, but you may need to save your credential in the script which might not be accessible in most company policies.

The third way, which is also the suggestion in MS official site, is using cluster in RSDB. But you can't install RS on database cluster, so you need more servers to provide HA. Detail can be reference to Planning a Deployment Topology.

--
Reference
SQL Server Reporting Services Disaster Recovery Case Study
How to: Configure SharePoint Integration on Multiple Servers