Post reply

DB Snapshot on DB Mirroring

  • June 1, 2009 at 6:54 pm

    #134722

    we are considering db snapshot on db mirroring for reporting purposes.

    have couple of questions

    1. is there any known issues or caveats other than disk space?

    2. we are considering snapshot from mirror db everyday week , do we get user impact on source db or mirrong disconnect during the snapshot as it needs to rollback the uncommitted transaction?

    Thanks for looking...

  • June 1, 2009 at 9:21 pm

    #1003138

    Hi,

    Please refer to below MSDN article and it will give answer to your questions.

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

    Regards

    GURSETHI

  • June 1, 2009 at 10:50 pm

    #1003158

    Appreciate for the link , actually i am looking some practical problems for this soln if any ,more with the above point 2.

  • June 1, 2009 at 10:57 pm

    #1003164

    Can you please tell me what exactly is your requirement?

  • June 2, 2009 at 9:58 am

    #1003498

    Requirement is for SSRS reporting , we are thinking to create DB snapshot on mirror database ,but not sure when weekly we take new db snapshot from Mirror does it impact availability of reports?

    also if any known issues/caveats/performance issues in this scenario?

    Thanks.

  • June 2, 2009 at 12:15 pm

    #1003571

    You may have followed the other link to http://msdn.microsoft.com/en-us/library/ms189940.aspx which lists a few limitations.

    It mentions performance, and although there should be no noticable impact to creating the snapshot, you should be aware of the following:

    1) as the pages on the mirror change for the first time they need to be written to the snapshot

    2) the reports will hit the mirror for the unchanged data and the snapshot for the changed data ... although this presumably won't have the impact of going directly against the source database, it could introduce some overhead on the mirror server that may be reflected in the mirroring commit delay.

    You should also take a look at the article GilaMonster wrote a few months ago ... http://qa.sqlservercentral.com/articles/Performance/64080/.

  • June 3, 2009 at 6:52 am

    #1003950

    Don't foget that a passive mirror does not have to be licensed. However, when you Snapshot it, a SQL Server license is required.

    Tim White

  • June 3, 2009 at 10:53 pm

    #1004409

    Thanks all for your valuable inputs , so i am thinking that while generating the snapshot daily night it has no disconnect for users to generate reports during snapshot generation, please correct me if that is not true?

  • June 4, 2009 at 12:54 am

    #1004437

    You can do replication

  • June 4, 2009 at 8:01 am

    #1004651

    You have two choices at night :

    1) drop and recreate the existing snapshot (which will obviously disrupt reporting, albeit briefly)

    2) create a new snapshot and direct reporting users to this; drop the existing snapshot at a later time after all users have been redirected.

  • June 4, 2009 at 10:11 am

    #1004759

    Thanks , for first case ,do i need to drop the existing snapshot or i can just overwrite the previous snapshot with new one ?

  • June 4, 2009 at 11:04 am

    #1004804

    You would have to check BOL (or try it) to be certain, but I'm fairly sure that you need to drop it first.

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

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