2

Reading over (and then testing out on a test database) the setup for mirroring it appears that one is supposed to leave the mirror database in restoration mode for normal operations.

is this correct? at whatever point it becomes necessary to initiate the failover (im not using a witness), will the primary database be put into restoration mode? this seems goofy.

Devnull
  • 951
  • 1
  • 7
  • 23

2 Answers2

3

Yes, mirroring is where transactions are replicated from primary to mirror. So one database will be in restoration mode

Perhaps a read of 2 whitepapers may assist One and Two

gbn
  • 6,009
  • 1
  • 17
  • 21
  • its explicitly mentioned in the second link. everywhere just says 'restore with noreceovery' but then doesnt mention a) that it should remain in restoration mode or b) why. thanks! – Devnull Jan 28 '11 at 19:19
  • b is obvious - so that the master can keep pushing changes into the permanent recovery. THis is how the db is kept in sync. By permanently restoring it. – TomTom Jan 28 '11 at 19:44
  • i get that its continually replicating the data, it just seems odd that it has to be in restore mode. if the other server is getting sql queries, why does it have to be 'restored' instead of just shipping the query down to the mirror DB. – Devnull Jan 28 '11 at 19:51
  • It doesn't ship queries as such: it ships data changes. And it is continual. The effect id that the mirror is never consistent enough to query... – gbn Jan 28 '11 at 20:04
  • exactly. The backup mirror is nEVER queried until the primary fails / a switch over happens. Only data changes (log file updates) are shipped and consolidated all the time. – TomTom Feb 01 '11 at 15:24
0

If you have Enterprise Edition, you can create database snapshots of the mirror, and report off these. But these are point-in-time, and read-only. You need to manually implement some way of rolling them over regularly (creating a new one and deleting the oldest etc), and you must devise some way of redirecting the clients to the new snapshot.

Readable mirrors are coming in SQL 11.