Mirroring and Replication for HA/DR Setup

Question

We are looking at using Mirroring to replace the DR solution set up previously in our Hyper-V environment now that we found Replication is not supported using hyper-v replica which is what was being done. The situation we have, AppDBServer is our application database server, we replicate to RepReportingServer using transactional replication to report off of this server rather than hit the AppDBServer. We only replicate a subset of articles for performance reasons and also have many additional reporting tables in the replicated database so we need to maintain that structure so we do not have to rewrite/touch reports. I understand we can use mirroring for HA/DR of our AppDBServer and set up the replication to handle failover using the PublicationFailoverPartner which would give us 3 servers in the set up, AppDBServer, AppDBServerMirror and RepReportingServer, but how do I "mirror" that RepReportingServer? That seems to be  a problem for me here. I need this reporting server in some kind of DR failover environment also.

Answer 1

Hi abeljda,

You cannot mirror the report server database. Database mirroring is not supported by Reporting Services. Usually, while your data gets mirrored, you may have to manually failover some components.

To avoid data loss and maximize up-time, you can run the report server database in a failover cluster. You must install the failover cluster before you create the report server database. To use the failover cluster, you select the cluster name as the database server when you create the report server database.

Helpful Links:

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

http://technet.microsoft.com/en-us/library/bb630402.aspx

Regards,
Sofiya Li

If you have any feedback on our support, please click here.

---

Sofiya Li
TechNet Community Support

Answer 2

OK kind of what I figured. for the reporting services we are going to need to reinstall SSRS on the new cluster. we have currently a scaled out (not load balanced) reporting server setup. We have 2 servers running SSRS, one targeted for batch processes and the other for interactive. what would you see this turning into with the introduction of a cluster, we would cluster the 2 SSRS servers as well as the SQL Server then? Any other ideas, or things to consider here? THanks