Post reply

Best solution for reporting on a secondary server

  • November 1, 2015 at 10:58 am

    #399259

    I have a prod server running SQL 2012 Standard which host our DB which is about 50 GB . We have enabled logshipping on a secondary sever which is in another region . Now we need to implement reporting solution for which we need to have another copy of DB . I can't implement transactional replication since it works only for tables with primary keys . Merge replication is also out of question.DB mirroring is also out of question.i do not want the logshipped DB to be used for reporting.

    Could you please tell me the best strategy to implement the reporting on secondary server ?

  • November 1, 2015 at 3:01 pm

    #1836949

    Why not using Always ON since you already using 2012. secondaries can be used for reporting with out any changes.

    @JayMunnangi

  • November 1, 2015 at 5:47 pm

    #1836956

    I can't implement transactional replication since it works only for tables with primary keys .

    That's not just a problem with transaction replication. That's one of those basic "code smells" that a lot of databases are made to suffer. What's the objection to having PK's on tables?

    --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 1, 2015 at 8:43 pm

    #1836973

    Hi JakDBA ,

    Thanks for your suggestion . We do not have a dedicated resources and experience to implement and maintain always on .

    Hi Jeff ,

    Thanks for your valuable inputs.

    The engineering do not want to do any changes in the db at the moment . Do you think snapshot replication will be a ideal solution ? we can have a latency of about 1 hour .

  • November 2, 2015 at 6:51 am

    #1837059

    you can logship to more than one secondary so you could set up another server for reporting purposes and logship to that as well with the secondary in read_only mode.

    Mirroring with readable secondaries (snapshots) or always on is out for you as you only have standard edition.

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

  • November 2, 2015 at 7:18 am

    #1837079

    well , in that case why not using backup and restore ... backup to multiple files and restore ... and make it a schedule in or package SSIS.

    @JayMunnangi

  • November 4, 2015 at 5:11 pm

    #1837845

    Depends on the business requirements?

    If they are happy with up to 24 hours data missing just restore your nightly backups to another server? If they want close to live always on is the simplest answer. If they want to bad enough explain you need a contractor to help or to be sent on training. This will help them decide between a free simple solution which might be missing some data or a solution with costs that will give me recent data.

  • November 4, 2015 at 11:23 pm

    #1837869

    rohan1424 (11/1/2015)

    Hi JakDBA ,

    Thanks for your suggestion . We do not have a dedicated resources and experience to implement and maintain always on .

    Hi Jeff ,

    Thanks for your valuable inputs.

    The engineering do not want to do any changes in the db at the moment . Do you think snapshot replication will be a ideal solution ? we can have a latency of about 1 hour .

    My word. It's amazing what "engineering" has become. It used to be that "engineers" had this ethical thing going on about doing it right the first time. :crazy:

    Getting back to your problem, if it takes less than an hour to do a restore, you could do that. Another great method is if the SAN can do a "SAN Snapshot" and both servers are on the same SAN. You can refresh a half TB database in about 6 or 7 seconds that way.

    --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 5, 2015 at 10:42 am

    #1838056

    Thanks Jeff, JackDBA,George and PretendDBA for your valuable suggestions.

    I will mostly go with logshipping in read only mode .

    Appreciate all your help !

Viewing 9 posts - 1 through 8 (of 8 total)

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