DB Snapshot on DB Mirroring

  • 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...

  • 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

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

  • Can you please tell me what exactly is your requirement?

  • 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.

  • 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://www.sqlservercentral.com/articles/Performance/64080/.

  • 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

  • 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?

  • You can do replication

  • 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.

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

  • 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