[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

SQL Server Database mirroring

I have a question about Database Mirroring.  If you have a principle server and a mirror server talking to each other and the mirror breaks, at what point does the mirror server become the principle server?  In addition, when the mirror breaks between the two servers, can the mirror or secondary server assume the principle role right away or do you have to perform a restore in order to get the principle server to operate as the primary server altogether?
  • 2
  • 2
3 Solutions
VBBRettAuthor Commented:
Anybody care to comment?
Jagdish DevakuSr DB ArchitectCommented:

With Database Mirroring:

Data Transfer:    Individual T-Log records are transferred using TCP endpoints
Transactional Consistency:  Only committed transactions are transferred
Server Limitation:   Can be applied to only one mirror server
Failover:   Automatic (Only if witness server is configured)
Failover Duration:  Failover is fast, sometimes < 3 seconds but not more than 10 seconds
Role Change:   Role change is fully automatic
Client Re-direction:  Fully automatic (if the alternate server connection string is used.)

Steve BinkCommented:
The mirror will only become a principal server if:

a) the mirror is configured with a witness under high availability rules
b) you manually switch the mirror

After a switch has been made, you need to manually push it back.  This usually means restoring the mirror first.
VBBRettAuthor Commented:
So if the mirror were to break, what happens to the principal role and the mirrored role?  Do they switch?
Steve BinkCommented:
That depends on if you have automatic failover with a witness, and which server becomes unavailable.  I highly recommend you review these pages:

Database Mirroring Overview
Synchronous Database Mirroring
Database Mirroring Witness
Automatic Failover


Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now