Post reply

What is the best way to have Separate Databse for Reporting.

  • January 30, 2012 at 11:34 pm

    #1440352

    Thanks Dev.

    Please tell me that all this need to be done manually?. I would appreciate you if you suggest me some automated way.

    Thanks.

  • January 30, 2012 at 11:43 pm

    #1440354

    so we need to recreate extra indexes on the reporting DB on every restore. What would happen in case if restore fail and index script run succesfully. I want to automate all this with if conditions. would SQL agents jobs support all these things.

    Can I automate the backup of my source DB at Source Server to hard disk of target server. If this happen than I can automate restore routine easily.

    Thanks

  • January 31, 2012 at 3:33 am

    #1440467

    There's been a few over complicated solutions offered here in my opinion.

    Depending on how big the database is and how much data you need for reporting you have the following options, some have been previously mentioned.

    Logshipping

    Pros - all the data, all the indexes, multiple reporting subscribers

    Cons - can take time to restore on a busy system, all the data, read only, possibly not the indexes you need so you need to create on source, need to disconnect users

    Mirroring

    Pros - all the data, fast to regenerate snapshot

    Cons - all the data, read only, possibly not the indexes you need, need to disconnect users

    Snapshot Replication

    Pros - fine tuned articles, read-write to create additional indexes, multiple reporting subscribers

    Cons - Time to generate and apply snapshot, need to disconnect users

    Transactional Replication

    Pros - fine tuned articles, read-write to create additional indexes, multiple reporting subscribers

    Cons - Cant think of any off the top of my head apart from admin and initial setup

    The simplest solutions are logshipping and mirroring but may not be possible depending on your budget.

  • January 31, 2012 at 9:54 pm

    #1441034

    Thanks to reply.

    We just need latest copy of our Database at target Server. We want to do this process at mid night. our DB size is 11GB and we can offord one day delay in reportings then what scenario will suit us.

  • February 1, 2012 at 3:07 am

    #1441102

    azhar.iqbal499 (1/31/2012)

    Thanks to reply.

    We just need latest copy of our Database at target Server. We want to do this process at mid night. our DB size is 11GB and we can offord one day delay in reportings then what scenario will suit us.

    That doesnt really add any new information. All of the above are far more efficient than recopying and restoring a full backup file. Does the database need to have specialised indexes not needed on the OLTP db? Will there be overnight processes that prevent you disconnecting spids?

Viewing 5 posts - 16 through 19 (of 19 total)

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