Reporting Services and SQL Server Mirroring

In an installation with 2 SQL Server 2008 SP2 S开发者_如何学Ctandard servers we use mirror to have failover partner for the databases.

One of the purposes of the databases are to supply reports through SQL Server Reporting Services.

My question is how I set this up to have failover of the reports AND keep cost at a minimum.

I can see one solution where the reporting databases are mirrored as well as the user databases they use. But will the reporting services failover?

Another way could be to install 2 separate instances of reporting services on each database server where the reporting databases are not mirrored and both are running. But then how do I redirect the users browsers from one entry address to a running reporting service?

There's also a witness server running SQL Server 2008 Express. This could be upgraded to standard and run the reporting services (should be able to failover through native client connection), but then the reporting service it self has no failover.

I don't know if I'm missing something here but is it impossible to do this in a good way without using SQL Server Enterprise and a failover cluster solution?

Any ideas, thoughts and links would be appreciated, but be aware that I have read a lot of guides and best practices around the internet and haven't found any that answers this question so please only post relevant links.

Any info I gather from you post, links and my research and testing will of course go back to Stackoverflow for future reference for others as I can't imagine I'm the only one who would like such a solution.

• Smint

You will need to mirror all data and then manually repoint the report server when the data moves to the mirrored servers.

Moving interrelated databases with mirroring complicates things. If one goes offline for some reason and failes to the mirror, will all other databases fail as well?

You may want to have a human make these decisions and use a manual failover on the mirroring as well.