Need recomendations on architecture.

We will be creating a moderately high-volume OLTP database application that needs 24/7 availability. We are planning to offload OLAP processing to a second copy of the system. We will be using SQL Server 2005.

I originally planned to set the second server up with SQL Server 2005 mirroring to cover the 24/7 availability requirement, with the idea that we could also do OLAP reporting off of the mirrored copy of the database. But I've gotten some indications that a mirror database is offline and not available for querying. So I figured I would use transactional replication to keep the OLAP database current. Now I am wondering if I need to use mirroring at all, or if I should just use transactional replication on the entire database and swap to the replicated database if the production server crashes.

What is everyone's opinion?

Replication only, for both OLAP reporting and failover?
Mirroring to one database for failover, with replication to a another database for OLAP reporting?My recommendation is to mirror for redundancy, much like you'd use clustering. Mirroring allows you to set up N servers that appear to the user as a single server. It gives you a quick, easy, and supported way to do something a lot like what log shipping did in previous versions, with automatic failover.

I'd also suggest that you use replication for OLAP processing, but that it go to a completely different server than your mirror does. You really want the replicated copy of your OLTP data to serve as the pre-staging area in your DW architecture, so I'd really want to avoid using a mirrored database for this use. By creating this "pre-staging" image, you can then roll the data into the staging form in whatever is the most convenient fashion, allowing it to flow quickly and easily through your staging area to the atomic level of your DW. At that point you can decide if you want to go to the extra effort to create relational rollups (a good idea for many reasons), or if you want to go directly to cubes (faster, but you lose a lot of options if you take the shortcut).

-PatP|||I'm afraid the company would balk at footing the bill for a third server to house the OLAP copy. What I may suggest is housing the mirrored database and the OLAP database on the same server.|||Understood... While we can figure out ways to provide exactly what users want, we are all constrained by what the users can afford. I think that you need to at least present the users the information that they need in order to make an informed decision... They need to know what you'd recommend as well as what you can live with...

-PatP