June 15, 2011 at 12:26 am
Hi All,
I may sound foolish..
A mirrored database can be accessed by creatig a snapshot on it.
And a snapshot is nothing but a copy of the original DB.
Any changes made to the original DB will not reflect in the Snapshot.
My doubt is that, Whenever a transaction occurs in principal DB, same will be
applied to Mirror DB as well. Means pages in Mirror DB will be modified quite frequently.
In such cases what's the point in creating a snapshot on mirror DB .?
How's it going to be useful to users.?
Just got this doubt when I was going through this topic...
Thanks in advance,
San.
June 15, 2011 at 2:40 am
Joy Smith San (6/15/2011)
A mirrored database can be accessed by creatig a snapshot on it.And a snapshot is nothing but a copy of the original DB.
It's not a copy of the original DB.
In such cases what's the point in creating a snapshot on mirror DB .?
How's it going to be useful to users.?
Without a snapshot the mirror is not readable. It's for when you want to run reporting workloads on the mirror and those reports don't need up to the second data but are fine with say end of previous day or similar.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 15, 2011 at 2:49 am
Joy Smith San (6/15/2011)
Hi All,I may sound foolish..
My doubt is that, Whenever a transaction occurs in principal DB, same will be
applied to Mirror DB as well. Means pages in Mirror DB will be modified quite frequently.
In such cases what's the point in creating a snapshot on mirror DB .?
How's it going to be useful to users.?
San.
This is a good question, I had this doubt regarding usage of mirroring snapshot when I first read about this topic.
M&M
June 15, 2011 at 3:23 am
The feature is useful of providing additional platform for reporting and divert the load from production server. To use a mirror database for reporting, you can create a database snapshot on the mirror database and direct client connection requests to the most recent snapshot.
A note from Technet article too that over a period of time the PRINCIPAL server may have performance issues as database snapshot consumes as much resources as a normal database, use it wisely depending upon the number of mirrored databases and number of snapshots too.
-Satya SK Jayanty
SQL Server MVP (Follow me @sqlmaster)
Author of SQL Server 2008 R2 Administration CookBook
SQL Server Knowledge Sharing network
June 15, 2011 at 3:57 am
Thank you all. I will do drilldown into it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply