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://media.amazonwebservices.com/AWS_RDBMS_MS_SQLServer.pdf
I was confused by this chart on page 11:
which 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.
Best Answer
A SQL Server 2008 R2 mirror is not itself readable.
If you are using Enterprise Edition, you can create a named snapshot database from the mirror. This allows you to create a point-in-time readable database that can be used for pretty much any query that does not do any updates. (Though you could use another database (tempdb or a workdb) to do auxiliary work that would not fit the read-only snapshot.)
See: https://msdn.microsoft.com/en-us/library/ms175158(v=sql.105).aspx
When the snapshot gets 'old' enough you create a new snapshot database. You will eventually need to drop the older snapshot, but it is possible to support several snapshots. (This depends on the amount of activity in your database and server.)
If you use Standard Edition and have the same need, look into log shipping using RESTORE with STANDBY. You will need to manage when the next restores get made in order to use the STANDBY feature.
See: https://msdn.microsoft.com/en-us/library/ms178615.aspx
It says of the STANDBY option: "The standby file allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores."
Therefore, this method requires you to delay the restore of logs to match the schedule for how long you want to keep this point-in-time in action. A LOG Restore will end the STANDBY and restore to the following point, which could be restored with a new STANDBY period.
Depending on your needs one of these approaches can give you a readable point-in-time database. No updates become visible in the database for the period of (a) the snapshot, or (b) the period between restoring updates.