I have 2 Physical Servers with SQL Server 2008 R2. – SQL Server 1(Active) & SQL Server 2 (Passive) Web Application is developed using .Net 4.0 Framework.

I want to know the best solution to have high availability and also have SSRS for reporting.

Planned solution: Mirroring for Failover, and Transaction Replication for SSRS as the mirrored database can only be used for failover scenarios. SSRS will be on the Passive server, to reduce the load on the Active server.

Let me know if the solution is correct. Also suggest alternate approaches.


It depends.

Best solution is what provides the optimal support for your needs without requiring extra effort. :)

With two servers, I have to wonder where you plan to put the Replication results? If on the same server as the principal, that might become very interesting. This Microsoft article (Replication and Database Mirroring, http://msdn.microsoft.com/en-us/library/ms151799.aspx) discusses configuring Replication with Mirroring. Believe, this is well documented, but not for the faint of heart.

What is the driving need for Replication?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.