Database Mirroring Operating Modes
We can configure database mirroring for three different operating modes:
High-Safety Mode Or High Availability (synchronous + Automatic Fail Over + Witness Server)
And
High Performance (Asynchronous)
The operating mode governs the way transactions are transferred between the principal and the mirror databases as well as
the failover processes that are available in the database mirroring session.
High Availability Operating Mode
Database mirroring’s High Availability operating mode provides durable, synchronous
transfer between the principal and mirror databases in addition to automatic
failure detection and failover.
SQL Server first writes all transactions into memory buffers within the SQL Server memory
space. The system writes out these memory buffers to the transaction log and then
flushes the log to the data files.
When SQL Server writes the transaction to the transaction log, the system triggers database mirroring to begin transferring the transaction log rows to the mirror. The transaction rows continue to flow to the mirror. When the application issues a commit for the transaction, the transaction is first committed on the mirror database.
An acknowledgement of the commit is sent back to the principal, which then allows the commit to be issued. After the commit is issued on the principal, the acknowledgment is sent back to the application, allowing it to continue processing.
This process guarantees that all transactions are committed and hardened to the transaction log on both the principal and mirror databases before the commit is returned to the application.
High Availability operating mode requires a witness server along with the principal and mirror databases for database mirroring to automatically detect a failure at the principal and fail over to the mirror.
A quorum is necessary to prevent the database from being served on more than one instance within the database mirroring session.
If the principal were to fail, and the mirror could not connect to the witness, it would be impossible to reach a quorum, and SQL Server would then not promote the mirror to the principal.
High Performance Operating Mode
Database mirroring’s High Performance operating mode uses a principal and a mirror
database but does not need a witness server. This operating mode provides a warm
standby configuration that does not support automatic failure detection or failover.
High Performance operating mode does not automatically fail over because the application’s
transactions are sent to the mirror asynchronously. Transactions are committed
to the principal database and acknowledged to the application.
A separate process constantly sends those transactions to the mirror, which introduces latency into the
process. This latency prevents a database mirroring session from automatically failing
over because the process cannot guarantee that SQL Server has received all transactions
at the mirror when a failure occurs.
High Protection Operating Mode
Database mirroring’s High Protection operating mode is the same as High Availability
operating mode, except that you do not configure a witness server.
SQL Server transfers transactions synchronously between principal and mirror, but because a two-outof-three quorum cannot be achieved without a witness, failover is manual.
If the principal fails in High Protection operating mode, you must manually promote the mirror to serve the database.
We can configure database mirroring for three different operating modes:
High-Safety Mode Or High Availability (synchronous + Automatic Fail Over + Witness Server)
High-Safety Mode OR High Protection (synchronous + Manual Fail Over)
And
High Performance (Asynchronous)
The operating mode governs the way transactions are transferred between the principal and the mirror databases as well as
the failover processes that are available in the database mirroring session.
High Availability Operating Mode
Database mirroring’s High Availability operating mode provides durable, synchronous
transfer between the principal and mirror databases in addition to automatic
failure detection and failover.
SQL Server first writes all transactions into memory buffers within the SQL Server memory
space. The system writes out these memory buffers to the transaction log and then
flushes the log to the data files.
When SQL Server writes the transaction to the transaction log, the system triggers database mirroring to begin transferring the transaction log rows to the mirror. The transaction rows continue to flow to the mirror. When the application issues a commit for the transaction, the transaction is first committed on the mirror database.
An acknowledgement of the commit is sent back to the principal, which then allows the commit to be issued. After the commit is issued on the principal, the acknowledgment is sent back to the application, allowing it to continue processing.
This process guarantees that all transactions are committed and hardened to the transaction log on both the principal and mirror databases before the commit is returned to the application.
High Availability operating mode requires a witness server along with the principal and mirror databases for database mirroring to automatically detect a failure at the principal and fail over to the mirror.
A quorum is necessary to prevent the database from being served on more than one instance within the database mirroring session.
If the principal were to fail, and the mirror could not connect to the witness, it would be impossible to reach a quorum, and SQL Server would then not promote the mirror to the principal.
High Performance Operating Mode
Database mirroring’s High Performance operating mode uses a principal and a mirror
database but does not need a witness server. This operating mode provides a warm
standby configuration that does not support automatic failure detection or failover.
High Performance operating mode does not automatically fail over because the application’s
transactions are sent to the mirror asynchronously. Transactions are committed
to the principal database and acknowledged to the application.
A separate process constantly sends those transactions to the mirror, which introduces latency into the
process. This latency prevents a database mirroring session from automatically failing
over because the process cannot guarantee that SQL Server has received all transactions
at the mirror when a failure occurs.
High Protection Operating Mode
Database mirroring’s High Protection operating mode is the same as High Availability
operating mode, except that you do not configure a witness server.
SQL Server transfers transactions synchronously between principal and mirror, but because a two-outof-three quorum cannot be achieved without a witness, failover is manual.
If the principal fails in High Protection operating mode, you must manually promote the mirror to serve the database.
No comments:
Post a Comment