locked
Mirroring fail over question? RRS feed

  • Question

  • Hi All,

    In mirroring principle,mirror and witness servers pinging each other. If the principle unavailable witness makes mirror server as a principal.

    In this process.

    What are the detailed steps taken by the Witness like what queries it execute on mirror servers??

    Thanks in advance.

    • Moved by Tom Phillips Friday, January 11, 2013 1:43 PM Database mirroring question
    Friday, January 11, 2013 7:49 AM

Answers

  • Hi,

    Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations. Starting database mirroring on a database, initiates a relationship, known as a database mirroring session, between these server instances.

    One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).

    The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role. At any given time, one partner performs the principal role, and the other partner performs the mirror role. Each partner is described as owning its current role. The partner that owns the principal role is known as the principal server, and its copy of the database is the current principal database. The partner that owns the mirror role is known as the mirror server, and its copy of the database is the current mirror database. When database mirroring is deployed in a production environment, the principal database is the production database.

    Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions.

    There are two mirroring operating modes. One of them, high-safety mode supports synchronous operation. Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.

    The second operating mode, high-performance mode, runs asynchronously. The mirror server tries to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database. However, typically, the gap between the databases is small. However, the gap can become significant if the principal server is under a heavy work load or the system of the mirror server is overloaded.

    For Witness the quorum scenarios for high-safety mode are as follows:

    • A full quorum that consists of both partners and the witness.

      Ordinarily, all three server instances participate in a three-way quorum, called a full quorum. With a full quorum, the principal and mirror servers continue to perform their respective roles (unless manual failover occurs).

    • A witness-to-partner quorum that consists of the witness and either partner.

      If the network connection between the partners is lost because one of the partners has been lost, the following cases are possible:

      • The mirror server is lost, and the principal server and witness retain quorum.

        In this case, the principal sets its database to DISCONNECTED and runs with mirroring in a SUSPENDED state. (This is referred to as running exposed, because the database is currently not being mirrored.) When the mirror server rejoins the session, the server regains quorum as mirror and starts resynchronizing its copy of the database.

      • The principal server is lost, and the witness and the mirror server retain quorum.

    You have not mentioned SQL server  version and edition

    For 2012

    http://msdn.microsoft.com/en-us/library/ms188712.aspx

    and

    http://msdn.microsoft.com/en-us/library/ms189852.aspx

    For 2008 R2

    http://serverfault.com/questions/136714/sql-2008-mirroring-how-to-failover-from-the-mirror-database



    Please help and appreciate others by using these features: "Propose As Answer", "Vote As Helpful" and "Mark As Answer"

    Waqas

    MS(SPM), MS(E&F), MCP, MCT, MCTS, MCITP, MCSE, MCPD, MCSD, MCDBA , Author
    Twitter: @waqas8777
    Linked In: http://www.linkedin.com/in/waqasm1


    • Edited by Waqas M Friday, January 11, 2013 12:57 PM
    • Marked as answer by Khushi N Wednesday, January 23, 2013 6:26 AM
    Friday, January 11, 2013 12:54 PM

All replies

  • Hi

     Refer this : http://technet.microsoft.com/en-us/library/ms189902.aspx

    thanks

    Saurabh Sinha

    http://saurabhsinhainblogs.blogspot.in/

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker

    Friday, January 11, 2013 7:56 AM
  • Hi,

    Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations. Starting database mirroring on a database, initiates a relationship, known as a database mirroring session, between these server instances.

    One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).

    The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role. At any given time, one partner performs the principal role, and the other partner performs the mirror role. Each partner is described as owning its current role. The partner that owns the principal role is known as the principal server, and its copy of the database is the current principal database. The partner that owns the mirror role is known as the mirror server, and its copy of the database is the current mirror database. When database mirroring is deployed in a production environment, the principal database is the production database.

    Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions.

    There are two mirroring operating modes. One of them, high-safety mode supports synchronous operation. Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.

    The second operating mode, high-performance mode, runs asynchronously. The mirror server tries to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database. However, typically, the gap between the databases is small. However, the gap can become significant if the principal server is under a heavy work load or the system of the mirror server is overloaded.

    For Witness the quorum scenarios for high-safety mode are as follows:

    • A full quorum that consists of both partners and the witness.

      Ordinarily, all three server instances participate in a three-way quorum, called a full quorum. With a full quorum, the principal and mirror servers continue to perform their respective roles (unless manual failover occurs).

    • A witness-to-partner quorum that consists of the witness and either partner.

      If the network connection between the partners is lost because one of the partners has been lost, the following cases are possible:

      • The mirror server is lost, and the principal server and witness retain quorum.

        In this case, the principal sets its database to DISCONNECTED and runs with mirroring in a SUSPENDED state. (This is referred to as running exposed, because the database is currently not being mirrored.) When the mirror server rejoins the session, the server regains quorum as mirror and starts resynchronizing its copy of the database.

      • The principal server is lost, and the witness and the mirror server retain quorum.

    You have not mentioned SQL server  version and edition

    For 2012

    http://msdn.microsoft.com/en-us/library/ms188712.aspx

    and

    http://msdn.microsoft.com/en-us/library/ms189852.aspx

    For 2008 R2

    http://serverfault.com/questions/136714/sql-2008-mirroring-how-to-failover-from-the-mirror-database



    Please help and appreciate others by using these features: "Propose As Answer", "Vote As Helpful" and "Mark As Answer"

    Waqas

    MS(SPM), MS(E&F), MCP, MCT, MCTS, MCITP, MCSE, MCPD, MCSD, MCDBA , Author
    Twitter: @waqas8777
    Linked In: http://www.linkedin.com/in/waqasm1


    • Edited by Waqas M Friday, January 11, 2013 12:57 PM
    • Marked as answer by Khushi N Wednesday, January 23, 2013 6:26 AM
    Friday, January 11, 2013 12:54 PM