locked
Frequent report in production DB RRS feed

  • Question

  • I have users running the real-time reports in production db very frequent. It slow down the performance a lot.

    It's SQL 2000 server. If I I point it to every 10 min log shipping standby server. it's ok. Users who connected to the standby server oould be disconnected while running log shipping. The standby server it's not good neither.

    What's the best way to have real-time reports running frequently by users and the performance won't degrade when running the reports.

    Thnaks,


    mecn
    Wednesday, December 1, 2010 10:12 PM

Answers

  • Can I do multiple tables as well?
    mecn


    Yes, you can add whichever table/view you want to the publication and replicate from publication database to subscription database. Based on your scenario, Transactional Replication is probably suitable with little time latency (generally several seconds).

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, December 7, 2010 8:17 AM
  • http://www.swynk.com/friends/achigrik/SetupMR.asp ----- Setting Up Merge Replication: A Step by Step Guide

    http://www.mssqlcity.com/Articles/Replic/Replic.htm --------Setting All Replica (Step by step)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, December 7, 2010 8:26 AM

All replies

  • 1. Is that possible to optimize these reports

    2. How powerfull the server is?

    3. Is that possible to move reported data into another db/server ,perhaps by using Transaction Replication?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, December 2, 2010 8:06 AM
  • Thanks for reply.

    Can I do the table replication instead of db replication?

    If yes, what kind of table replication should I do for minimum impact of production DB?


    mecn
    Thursday, December 2, 2010 1:51 PM
  • Yes,  you can create replication but replicate only one table (publisher,subscriber)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, December 2, 2010 2:04 PM
  • Can I do multiple tables as well?
    mecn
    Thursday, December 2, 2010 3:12 PM
  • hi mecn,

      As suggested before, try to optimize the report procedure/code ... so that it will utilize less resource ...and the other processes will be having enough resources to proceed ..and also, it is a good practice to maintain separate report server ...so that it won't affect the transaction databases ..... or else ... separet the related tables ...and replicate the data using replication ..

     


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Saturday, December 4, 2010 4:55 PM
  • Can I do multiple tables as well?
    mecn


    Yes, you can add whichever table/view you want to the publication and replicate from publication database to subscription database. Based on your scenario, Transactional Replication is probably suitable with little time latency (generally several seconds).

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, December 7, 2010 8:17 AM
  • http://www.swynk.com/friends/achigrik/SetupMR.asp ----- Setting Up Merge Replication: A Step by Step Guide

    http://www.mssqlcity.com/Articles/Replic/Replic.htm --------Setting All Replica (Step by step)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, December 7, 2010 8:26 AM