Post reply

Database mirroring with Real-time Reporting capabilities on mirror db

  • July 12, 2008 at 7:54 am

    #193888

    I am planning to have a Database mirror for our DR and use this DR for real time reporting by taking regular snapshots on mirror db.

    Can any one tell me how frequent can we take db snapshots on mirror db to match a real time reporting.

    can we write a script to delete the old snapshot and create a new one every 5min?

    Will this have any performance issues??

  • July 12, 2008 at 10:59 am

    #842009

    with snapshots you are never going to get real time reporting, only NEAR real time. Recreating the snapshot every 5 mins seems excessively frequent.

    Maintaining a snapshot is an overhead as you are effectively maintaining a duplicate of the database, but if this server is for DR and reporting may not be a problem.

    If you want real time reporting I suggest you look into transactional replication.

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

  • July 12, 2008 at 3:48 pm

    #842024

    Thanks for your reply

    what is the impact if we have both mirroring and a transactional replication implemented for the same db.

  • July 12, 2008 at 4:02 pm

    #842025

    can I refer you to this article:

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

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

  • July 12, 2008 at 5:01 pm

    #842029

    Really think about this. Everyone says they need real-time reporting, but very few people need it. Often every hour is fine.

  • July 14, 2008 at 12:16 pm

    #842615

    Hi Sri

    What version of SQL are u using and please provide ur server details(both DB and reporting), so we can see some other options as well...:)

  • July 16, 2008 at 3:29 am

    #843630

    Hi,

    The SQL Version is SQL 2005 sp2

    and the reporting is a third party reporting tool that comes with the application.

  • July 16, 2008 at 5:03 am

    #843675

    as steve said, check with the users how 'real time' the data actually needs to be for reporting.

    If real time go for replication, if not go for mirroring with snapshots. I would not try and create new snapshots every 5 mins.

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

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

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