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.

3 Answers 3


If you can convince the reporting users that they don't really need real-time reports, you can take advantage of the ability to create a snapshot of the mirror database. See Database Mirroring and Database Snapshots on Technet.

Unlike the mirror database itself, a database snapshot is accessible to clients. As long as the mirror server is communicating with the principal server, you can direct reporting clients to connect to a snapshot. Note that because a database snapshot is static, new data is not available. To make relatively recent data available to your users, you must create a new database snapshot periodically and have applications direct incoming client connections to the newest snapshot.

Mirror/snapshot combinations can also be beneficial if you want to run ETL over an OLTP database to load into a data warehouse. Example architecture in this article on SSC.

The advantage in both cases is removing the overhead of running a replication feed, if you can convince your users that an hourly lag on reporting data is acceptable. In the vast majority of cases it is perfectly reasonable but the perception can be tough to change.

  • We are considering this very solution for our read-only needs as well. Thankfully we have the "luxury" (read slow development) of waiting until SQL 2012 is released and we don't have to complicate the scenario with the ugly coding required to juggle snapshots. Dec 2, 2011 at 0:45

In this link there is an article that might help you. SSRS is not clusterable but you can balance. Must be carefull sharing the configuration DB for the servers.http://msdn.microsoft.com/en-us/library/bb522745.aspx


We use log shipping to a second database that SSRS queries. Only downside is the data can be 1 hour out of date (Hourly log shipping) and the restore closes all users connections, so if a user was waiting for a lon running report to return they could have to start again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.