Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: 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?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •