2

I'm just about to setup a SQL mirror but there's one thing I haven't managed to work out yet. If I'm mirroring DBA (principal) to DBB (mirror), and DBA goes offline, it will automatically switch over to DBB. But then as far as I understand it, any updates that occur on DBB won't appear on DBA...so when DBA comes back online and becomes active again, all updates during the outage will be lost? Is that right or have I missed something?

Thanks! Paul

Paul
  • 221
  • 5
  • 15

2 Answers2

3

What you have missed is that MS Programers re no total idiots. When DBA comes back up it knows it is outdated and wont answer until it has taken the data from B. A has no "real" priority, especially not with a principal available. B will be there and know it has a newer authorization.

If A would go on answering and the data on B be lost, the feature would be totally utterly useless.

TomTom
  • 50,857
  • 7
  • 52
  • 134
  • Ok thanks - that's a relief. I thought it was a bit odd, but I could find naaathing just explaining that. Like this article which shows a Data Flow going one way - http://msdn.microsoft.com/en-gb/library/ms189852.aspx – Paul Apr 23 '13 at 09:35
  • I've just been told that one of the "cons" of database mirroring is that if DBA goes offline and DBB kicks in then you have to manually rebuild DBA to bring it back online? I'm confused as to who's right – Paul Apr 23 '13 at 11:11
  • It is right. It will not automatically fallback - and that is what I said. – TomTom Apr 23 '13 at 11:31
  • So A will not automatically come back up? What happens if B fails then? – Paul Apr 23 '13 at 12:36
  • Then you are down. Mirroring has limits. – TomTom Apr 23 '13 at 12:55
1

There are certain situations where something will happen to the principal server and it won't be able to synchronize without reestablishing the mirroring sessions. But if you are in high-safety mode and have a witness server setup it should be possible for DBA to get caught up with DBB.

This is explained a bit more in books online in the Role Switching section under "How Automatic Failover Works"

  1. If the principal server is still running, it changes the state of the principal database to DISCONNECTED and disconnects all clients from the principal database.

  2. The witness and mirror servers register that the principal server is unavailable.

  3. If any log is waiting in the redo queue, the mirror server finishes rolling forward the mirror database.

  4. The former mirror database moves online as the new principal database, and recovery cleans up all uncommitted transactions by rolling them back as quickly as possible. Locks isolate those transactions.

  5. When the former principal server rejoins the session, it recognizes that its failover partner now owns the principal role. The former principal server takes on the role of mirror, making its database the mirror database. The new mirror server synchronizes the new mirror database with the principal database as quickly as possible. As soon as the new mirror server has resynchronized the databases, failover is again possible, but in the reverse direction.

So in your situation if/when DBA comes back online it should eventually get caught up with DBB but how long it will take depends on how fast the system is, the recent work load, and the amount of log in the redo queue.

shiitake
  • 379
  • 1
  • 7