SQL Server Database mirroring

Posted on 2011-10-25
Last Modified: 2012-05-12
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?
Question by:VBBRett

    Author Comment

    Anybody care to comment?
    LVL 14

    Accepted Solution


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

    LVL 50

    Assisted Solution

    by:Steve Bink
    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.

    Author Comment

    So if the mirror were to break, what happens to the principal role and the mirrored role?  Do they switch?
    LVL 50

    Assisted Solution

    by:Steve Bink
    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now