The Two modes of Database Mirroring in SQL Server for HA/DR Solutions are
- Synchronous mirroring( High Availability / HA )
- Asynchronous mirroring( Disaster Recovery / DR )
Synchronous mirroring :
In all SQL Server databases, data changes are recorded in the transaction log before any changes to actual data pages are made. The transaction log records are placed first in a database’s log buffer in memory, and then flushed to disk (or ‘hardened’) as quickly as possible. In database mirroring, as the principal server writes the principal database’s log buffer to disk, it simultaneously sends that block of log records to the mirror instance.The mirror receives the log records in mirror database’s log buffer and then hardens them to disk as quickly as possible.
In Synchronous mirroring, as soon as data is committed in the principal it is sent to the mirror server and it is also committed there.After that the mirror sends an Acknowledgement to principal that data has been committed there.The key concept is that the client i.e principal waits until it receives an Acknowledgement from mirror before it commits the next set of datas.
If the network is slow, it will definitely cause performance issues because the principal is waiting for receiving an Acknowledgement to proceed with subsequent transactions.This is the most secure method for assuring that data is absolutely correct between principal and mirror but it is also the slowest method due to overhead of the principal having to wait for the Acknowledgement to be sent back.
High Availability Mode :
It provides the most robust coverage.It consist of the Principal,Witness and Mirror in synchronous communication.In this mode SQL server ensures that each transaction that is committed on the Principal is also committed in the Mirror prior to continuing with next transactional operation in the principal.The cost of this configuration is high as Witness is required.If the network does not have the bandwidth, a bottleneck could form causing performance issue in the Principal. If Principal is lost Mirror can automatically take over.
High Protection Mode :
It is pretty similar to High Availability mode except that Witness is not available, as a result failover is manual.It also has transactional safety FULL i.e synchronous communication between principal and mirror.Even in this mode if the network is poor it might cause performance bottleneck.
High Performance Mode :
It consist of only the Principal and the Mirror in asynchronous communication.Since the safety is OFF, automatic failover is not possible, because of possible data loss; therefore, a witness server is not recommended to be configured for this scenario. Manual failover is not enabled for the High Performance mode. The only type of failover allowed is forced service failover, which is also a manual operation.
ALTER DATABASE SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
The forced service failover causes an immediate recovery of the mirror database. It may involve potential data loss on the mirror when it is recovered, if some of the transaction log blocks from the principal have not yet been received by the mirror. The High Performance mode is best used for transferring data over long distances
Asynchronous mirroring :
The data that is committed in the principal is sent to the mirror but the principal does not wait for any Acknowledgement from mirror server before continuing with the other transactions.The transactional consistency between the principal and mirror might vary slightly.The mirror database might receive the log records after some time may be some 20 to 30 seconds depending on the transaction size and network speed.
Transaction Safety :
If transaction safety (or just ‘safety’) is set to FULL, the principal and mirror servers operate in a synchronous transfer mode. As the principal server hardens its principal database log records to disk, it also sends them to the mirror. The principal then waits for a response from the mirror server. The mirror responds when it has hardened those same log records to the mirror’s log disk. When safety is set OFF, the principal does not wait for acknowledgment from the mirror, and so the principal and mirror may not be fully synchronized (that is, the mirror may not quite keep up with the principal).Synchronous transfer guarantees that all transactions in the mirror database’s transaction log will be synchronized with the principal database’s transaction log, and so the transactions are considered safely transferred. You set safety to FULL using,
ALTER DATABASE [databasename] SET SAFETY FULL
A Quorum is the relationship between the Witness,Principal and the Mirror.Depending on the mode of operation it is divided into 3.
This is when all 3 Witness,Principal and the Mirror can communicate with each other.Since witness is present automatic failover occurs.
This state exist if the Witness and either partner can communicate with it.
When only the Principal and Mirror can communicate with each other.
Operating Modes :
SQL Server provides 3 operating modes for database mirroring and the exact mode is based on the setting of transaction safety and whether a witness server is part of the mirroring session.
Patner timeout setting:
Here is SQL Server Error Log Message on one of SQL Server 2008 R2
“The mirroring connection to “TCP://server_name:5022″ has timed out for database “<databasename> ” after 10 seconds without a response. Check the service and network connections.”
This is a database mirroring timeout message. The message is related to Database Mirroring failover procedure.
With the High Availability mode of synchronous Database mirroring with automatic failover there are three servers that are involved: Principal, Mirror and Witness.
The three servers constantly ping each other, forming a quorum, and if one of the machines is not available the other machines determine how to handle the failover.
In this particular instance the Principal instance didn’t not receive any responses to “Ping” for 10 seconds from Witness, as a result the failover was initiated after 10 sec without a response. 10 sec is a default Partner Time out settings.
Most common reasons why the pinging response failed
- slow network connection or network reliability
- geographically dispersed mirror partners
- hardware/SAN/Vm failure
- What can be done to avoid accidental database failover?
- Increase Partner Time Out value.
What is Partner timeout?
The maximum period of time a server instance waits to get a “ping” message from another SQL Server instance in the mirroring session before considering that other instance to be disconnected
The TIMEOUT option only can be defined on the principal server
Specifies the time-out period in seconds
Option can not be set through the GUI
The default time period is 10 seconds.
If you set it for anything between 0 and 4 seconds it will automatically set the timeout to 5 seconds. If you set it for 5 seconds or greater it will be set to that value.
Number specified must be an integer
To change Partner timeout?
To change Partner Timeout to 20 sec
ALTER DATABASE <databasename> SET PARTNER TIMEOUT 20
Useful queries for troubleshooting Database Mirroring
Checking Timeout value
SELECT Mirroring_Connection_Timeout FROM sys.database_mirroring WHERE database_id = db_id(‘<databasename> ‘)
Query to check the associated ports with DB Mirroring
SELECT type_desc, port FROM sys.tcp_endpoints where type_desc like ‘%MIRROR%’;
Query to check the state of the DB Mirroring
SELECT state_desc FROM sys.database_mirroring_endpoints