To achieve synchronous operation for a session, the mirror server must synchronize the mirror database with the principal database. When the session begins, the principal server begins sending its active log to the mirror server. The mirror server writes all of the incoming log records to disk as quickly as possible.
As soon as all of the received log records have been written to disk, the databases are synchronized. As long as the partners remain in communication, the databases remain synchronized. To monitor state changes in a database mirroring session, use the Database Mirroring State Change event class. After synchronization finishes, every transaction committed on the principal database is also committed on the mirror server, guaranteeing protection of the data.
This is achieved by waiting to commit a transaction on the principal database, until the principal server receives a message from the mirror server stating that it has hardened the transaction's log to disk. Note the wait for this message increases the latency of the transaction. The time required for synchronization depends essentially on how far the mirror database was behind the principal database at the start of the session measured by the number of log records initially received from the principal server , the work load on the principal database, and the speed of the mirror system.
After a session is synchronized, the hardened log that has yet to be redone on the mirror database remains in the redo queue. On receiving a transaction from a client, the principal server writes the log for the transaction to the transaction log. The principal server writes the transaction to the database and, concurrently, sends the log record to the mirror server.
The principal server waits for an acknowledgement from the mirror server before confirming either of the following to the client: a transaction commit or a rollback.
The mirror server hardens the log to disk and returns an acknowledgement to the principal server. On receiving the acknowledgement from the mirror server, the principal server sends a confirmation message to the client. High-safety mode protects your data by requiring the data to be synchronized between two places.
All the committed transactions are guaranteed to be written to disk on the mirror server. High-Safety Mode with Automatic Failover. The following figure shows the configuration of high-safety mode without automatic failover. The configuration consists of only the two partners. When the partners are connected and the database is already synchronized, manual failover is supported. If the mirror server instance goes down, the principal server instance is unaffected and runs exposed that is without mirroring the data.
If the principal server is lost, the mirror is suspended, but service can be forced to the mirror server with possible data loss. Automatic failover provides high availability by ensuring that the database is still served after the loss of one server.
Automatic failover requires that the session possess a third server instance, the witness , which ideally resides on a third computer.
The following figure shows the configuration of a high-safety mode session that supports automatic failover. Unlike the two partners, the witness does not serve the database. The witness simply supports automatic failover by verifying whether the principal server is up and functioning.
The mirror server initiates automatic failover only if the mirror and the witness remain connected to each other after both have been disconnected from the principal server. When a witness is set, the session requires quorum -a relationship between at least two server instances that allows the database to be made available.
The failure occurs while all three server instances are connected, and the witness and mirror server remain connected. If the principal server becomes unavailable under the above conditions, automatic failover occurs. The mirror server switches to the role of principal, and it offers its database as the principal database. If the principal server becomes unavailable when those conditions are not met, forcing service with possible data loss might be possible. If the session loses its witness, quorum requires both partners.
If either partner loses quorum, both partners lose quorum, and the database becomes unavailable until quorum is re-established. This quorum requirement makes sure that in the absence of a witness the database never runs exposed , that is without being mirrored.
If you expect the witness to remain disconnected for a significant amount of time, we recommend that you remove the witness from the session until it becomes available. As an alternative to using Transact-SQL, you can control the operating mode of a session in Object Explorer using the Mirroring page of the Database Properties dialog box.
Viewing the Safety Setting and State of the Witness. The operating mode of a session is determined by the combination of its transaction safety setting and the state of the witness. Supporting automatic failover is the only role of the witness.
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 , 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. A given server instance can participate in multiple concurrent database mirroring sessions with the same or different partners.
A server instance can be a partner in some sessions and a witness in other sessions. The mirror server instance must be running the same edition of SQL Server. A database mirroring session runs with either synchronous or asynchronous operation. Under asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance.
Under synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency. 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. In high-performance mode, as soon as the principal server sends a log record to the mirror server, the principal server sends a confirmation to the client. It does not wait for an acknowledgement from the mirror server.
This means that transactions commit without waiting for the mirror server to write the log to disk. Such asynchronous operation enables the principal server to run with minimum transaction latency, at the potential risk of some data loss.
All database mirroring sessions support only one principal server and one mirror server. This configuration is shown in the following illustration. High-safety mode with automatic failover requires a third server instance, known as a witness. Unlike the two partners, the witness does not serve the database. The witness supports automatic failover by verifying whether the principal server is up and functioning. The mirror server initiates automatic failover only if the mirror and the witness remain connected to each other after both have been disconnected from the principal server.
Establishing a new mirroring session or adding a witness to an existing mirroring configuration requires that all involved server instances run the same version of SQL Server.
However, when you are upgrading to SQL Server or a later version, the versions of the involved instances can vary. Whether an operating mode is asynchronous or synchronous depends on the transaction safety setting. Mirror information. If you selected the option to use a Witness. You will be required to press the connect button and select the credentials to connect to the witness Server. Figure 8. Witness configuration. Figure 9. Service Accounts. The Compete Wizard contains a list of all the configurations used.
If you agree, press finish if not, press Back and change the configurations. Figure Configurations made. If everything is OK, a Success message will be display. Otherwise, you will receive error messages. The Success Window. You will receive a message to start the Mirroring. If you are ready, press the Start Mirroring button. The Start Mirroring message. Once you are done, the rest is easy.
Restarting the S ql service. You will find that the Mirror Database is active now. Typical error message. Error , The network address can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. The Database Mirroring Monitor Option.
0コメント