Is it possible to mirror the SQL Reporting Services databases?

Question

Is it possible to mirror the SQL Server (2005 or 2008) Reporting Services databases (ReportServer, ReportServerTemp)? I have a web site that uses a database that is already mirrored, and it also makes use of the Reporting Services. I want to be able to failover the primary server to the mirror, and have the reporting component also failover. Has anybody done this? Any gotchas?

Answer 1

here is another reference link http://sqlcat.com/technicalnotes/archive/2009/10/23/sql-server-reporting-services-disaster-recovery-case-study.aspx

you can certainly mirror the db's, but SSRS needs a handful of other items to failover as well. so, while your data gets mirrored, you may have to manually failover some components.

Answer 2

Database mirroring isn't supported by SQL Reporting Services.

http://msdn.microsoft.com/en-us/library/bb510781%28SQL.90%29.aspx

Answer 3

I run an async mirrored SSRS catalog and tempdb and in the SSRS config I use a DNS alias for the server name so that in order to move the catalog to the mirror server I update the DNS record, switch the mirror mode to sync and then manually fail the databases over. Automatic failover is not supported.

Answer 4

We integrated something like this:

http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/69699

But we used WMI alerts to determine failover, and didn't mirror ReportTempDB

Basically you mirror ReportDB, have the report service set up independently on each server, and then setting up the failover: stop the reporting services on the mirror server, and start it on the principal. However, if you're not using Enterprise, you can't use a scale-out solution (multiple reporting services connected to same DB), so the key piece is to clear the keys out of the ReportService database, and then run a tool to import the key for the current server. It works pretty well.