mardi 24 février 2015

SQL Server 2008 R2 Readable Mirror


I know that SQL Server 2012 and 2014 offer AlwaysOn Availability Groups which provide this functionality, but I'm still stuck on SQL Server 2008 R2 for a while.


I recently came across this AWS whitepaper: http://ift.tt/1DL2sQA


I was confused by this chart on page 11: enter image description herewhich seems to indicate that the mirror database can be used for reading (checks in the 'Readable Copy' feature column), just as it can when using Log Shipping, because it's been my experience that the mirror database cannot be used at all--it's only purpose is for failover.


The answer to this question: Database mirroring is limited to an original database seems to confirm my suspicions that the mirror database is NOT readable, though only in passing.


Is this whitepaper wrong, or are SQL Server 2008 R2 mirror databases indeed readable?


If they are, what does one need to do to do so, as attempting to connect in SSMS results in a window connected to the master database, and running USE [database] gives the following error:



Msg 954, Level 14, State 1, Line 1
The database "database" cannot be opened. It is acting as a mirror database.


UPDATE: I understand that there are ways to kind-of workaround this issue and get something derived from the mirror and time-delayed that is readable, and I suppose one could make an argument that a snapshot is a "Readable Copy", but that's very different from the type of readable copy you'd get through log shipping or transaction replication, as both of those would be automatically updated, even if slightly out-of-date (maybe snapshots can be too--I'm stuck on Standard Edition , so I'm not as familiar with the Enterprise Edition feature set). In addition to that, a truly readable synchronous mirror (like using AlwaysOn Availability Groups in 2012+) would provide a perfectly synchronized readable version--which is FAR more useful because the mirror could be used to distribute read-query load and avoid all the issues with out-of-date data being returned. My primary purpose (as stated in the question above) was really to get a definitive answer about the readability of the mirror itself (not a snapshot or a replica of it). While the provided answer agrees with my own experience, the only links to definitive sources provided are for possible ways to achieve similar results--neither of them definitively state that mirrors are not readable. I will accept the first answer that provides such a link.





Aucun commentaire:

Enregistrer un commentaire