Mirroring (SQL Server)
Mirroring is a term which I have come across often during my time at SQL Services. So I thought that it’s about time I dedicate a blog post to it. So database mirroring is used to increase availability of a database and it involves two instances which can be on the same or different machines. One of the instances will act as a primary instance (called the principal) and the other instance is a mirrored instance (called the mirror). There can also be a third instance which acts as a witness, however I haven’t come across this myself yet.
Advantages and Disadvantages
Just like everything else, database mirroring has it’s advantages and disadvantages. Some of its advantages include:
- It’s easy to set up
- It can provide automatic fail-over
- It increases data protection and availability of a database
- It can improve the availability of the production database during upgrades
- It can be combined with other disaster recovery options e.g. clustering, log shipping, replication
And some of its disadvantages include:
- It only works on databases which use the full recovery model
- It will be removed from SQL Server in the future (so I’ve heard)
- It’s a per database solution (so logins and jobs from the principal must be manually created on the mirror)
- There is a possibility of delay
The Principal Role
The principal role becomes the source of all transactions in a database mirroring session. The principal database is online and allows connections and applications to read and write data to and from it.
The Mirror Role
The mirror role is the partner of the principal database and continuously receives transactions. The database mirroring process constantly replays transactions actioned against the principal database onto the transaction log of the mirror database. This ensures that the mirror database includes the same data as the principal database.
The mirror database is in a recovering state, and does not allow connections of any kind. However, a database snapshot can be created against a mirrored database to give users read-only access to the mirrored database’s data at a specific point in time.
The Witness Role
The witness ensures that the database can only be served one SQL Server instance at a time. If a primary database fails and the witness confirms the failure, the mirror database can take the primary role and make its data available to users. Although database mirroring enables a principal and mirror to occur only in pairs (for example, a principal cannot have more than one mirror, and vice versa), a witness server can service multiple Database Mirroring pairs.
After a quick search online, I found that there are three types of operating modes for database mirroring; high availability mode, high protection mode, and high performance mode. If you would like to check out the article which I found this information, here is the link.
High Availability Mode
High availability provides the strongest coverage. In this mode, SQL Server ensures that the transactions that are committed on the principal, are also committed on the mirror before moving onto the next transaction operation on the principal. If the network does not have the bandwidth, a bottleneck could form. This could cause a performance issue in the principal. And if the principal is lost, the mirror can automatically take over.
High Protection Mode
High protection mode is very similar to high availability mode. The only difference is that the witness is not available. Because of this, fail over is manual. High protection mode also has transnational safety full (synchronous communication between principal and mirror). Even in this mode, a performance bottle neck could occur if the network is poor.
High Performance Mode
High performance mode only consists of the principal and the mirror in asynchronous communication. Because the safety is off, automatic fail over is not possible due to data loss. This means that configuring a witness server is not recommended for this particular scenario.
In high performance mode, manual fail over is not enabled. There is only one type of fail over which is allowed, this is forced service fail over which is also a manual operation. The forced service fail over 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 haven’t been received by the mirror.
High performance mode is best used for transferring data over long distances