June 1, 2009 at 6:54 pm
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
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
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
Can you please tell me what exactly is your requirement?
June 2, 2009 at 9:58 am
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
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/.
June 3, 2009 at 6:52 am
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
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
You can do replication
June 4, 2009 at 8:01 am
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
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
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