6

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

5

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.

  • 1
    Per James' edit: Database Mirroring for 2008 R2 specifically states "Unlike the mirror database itself, a database snapshot is accessible to clients." which clearly shows that mirror databases are not read-able. RLF has correctly answered the question. – LowlyDBA Feb 24 '15 at 20:53
  • Given the indirect nature of that sentence, I'm not sure I could have ever found that with any kind of Google search other than a generic search for SQL mirroring, which would have required hours of reading to find that little gem, which is good enough for me. Thanks! – James Feb 24 '15 at 23:06
0

Mirrors are not readable, but replication allow both read and write access to "subscribers". Subscriber (slave) is a copy of the Publisher (master) database.

Copy from Updating Data at Subscribers section.

Merge replication

  • There are a large number of Subscribers.
  • Data is replicated to mobile users.
  • Replicated data is frequently updated at the Subscriber.
  • Data filtering is needed so that Subscribers receive different partitions of data.

Peer-to-peer transactional replication

  • Replication is used to improve scalability and availability.
  • Minimal latency is required.
  • Data is not partitioned among Subscribers.
  • Conflicts typically do not occur, but they must be detected if they do.

Transactional replication with updating subscriptions

  • There are a small number of Subscribers.
  • Replicated data is mostly read-only at the Subscriber.
  • Subscriber, Distributor, and Publisher are connected most of the time (for immediate updating subscriptions).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.