Post reply

which High Avaiblity Option is better?

  • November 23, 2011 at 7:04 am

    #251003

    Hi folks,

    I have to keep report server real time updated with prodcution server databse . would like to know the suggestions which HA option is better for that?

    Currently we refreshing once in week every sunday but my manager weants to keep it updated evry night.

    Version :SQL Server 2008 R2

    DB Size in TB

    Any Ideas please?

    Thanks

  • November 23, 2011 at 7:08 am

    #1412170

    The information you provided is not sufficient. Please go through the article given below for better understanding on HA solutions.

    Selecting a High Availability Solution

    http://msdn.microsoft.com/en-us/library/bb510414.aspx

  • November 23, 2011 at 7:19 am

    #1412179

    specifically whats the size of the database, whats the network like and what edition of SQL are you using.

    ---------------------------------------------------------------------

  • November 23, 2011 at 8:16 pm

    #1412538

    logicinside22 (11/23/2011)

    Hi folks,

    I have to keep report server real time updated with prodcution server databse . would like to know the suggestions which HA option is better for that?

    Currently we refreshing once in week every sunday but my manager weants to keep it updated evry night.

    Version :SQL Server 2008 R2

    DB Size in TB

    Any Ideas please?

    Thanks

    If both your servers are on a common SAN, SAN "snapshots" can update a tera-byte database in just a minute or so.... even across instances.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 24, 2011 at 1:55 am

    #1412579

    if your production server can take the load of production OLTP traffic and reporting traffic, you could look at database snapshots

    otherwise I would say your looking at either logshipping or replication, mirroring would do the trick as well but as you can only mirror to one node, you may want to keep this for disaster recovery purposes and not reporting.

    can get logshipping to go into standby mode and disconnect users so it can restore the TX log at regular intervals, could set it up so that it backups prod every 15 minutes and copies to report server every 15, but only restore every 24 hours at a quiet period, so its a day behind, will keep your prod TX log manageable.

    issues on this is that if you do restore the logs every 15 minutes, users will be kicked out every 15 minutes so might get frustrating if reports take longer than that to run.

    or put checksums on the end of all tables and have it computed, then use SSIS to lookup the checksum in reports and the checksum in prod, if they match skip over, if different update, if not exist insert, some conditional logic in the data flow tasks.

  • November 24, 2011 at 2:12 am

    #1412586

    logicinside22 (11/23/2011)

    Hi folks,

    I have to keep report server real time updated with prodcution server databse . would like to know the suggestions which HA option is better for that?

    That's not a high availability problem, that's a scale-out reporting problem. HA is a warm or hot standby server to be used if and when the primary server fails. That's not what you're describing here.

    For scale-out reporting, my preference would be transactional replication.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 24, 2011 at 3:17 am

    #1412611

    please when giving extra info that has been requested do it by adding another post rather than editing your original post. People only get notified you have replied when you add a post.

    It also stops it from looking like I have asked stupid questions.

    ---------------------------------------------------------------------

  • November 25, 2011 at 3:19 am

    #1412912

    GilaMonster (11/24/2011)

    logicinside22 (11/23/2011)

    Hi folks,

    I have to keep report server real time updated with prodcution server databse . would like to know the suggestions which HA option is better for that?

    That's not a high availability problem, that's a scale-out reporting problem. HA is a warm or hot standby server to be used if and when the primary server fails. That's not what you're describing here.

    For scale-out reporting, my preference would be transactional replication.

    Have to agree since the original post pointed out real time information as a requirement.

    SAN replication, Mirroring with snapshots are all well and good but as soon as the data materialises it begins to go stale.

  • December 6, 2011 at 10:06 pm

    #1417764

    Hi ,

    Please go thorugh the below link

    http://msdn.microsoft.com/en-us/library/ms190202.aspx

  • December 6, 2011 at 11:33 pm

    #1417783

    Go with Transactional Replication if you want your report server updated in real time.

    However , as you said that your manager wants the report server to be updated every night you can look for log shipping also.

    Sujeet Singh

  • December 7, 2011 at 5:00 am

    #1417965

    Divine Flame (12/6/2011)

    However , as you said that your manager wants the report server to be updated every night you can look for log shipping also.

    Not a bad idea but SAN replication methods will be much easier and faster for such a thing.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply