While I’m working on my other little problem, let’s talk about database mirroring in SQL Server. Mirroring was first introduced in SQL 2005 as a high availability solution, and the technology remains mostly the same in SQL 2008, with a couple enhancements. With database mirroring, you can have a transactionally consistent hot-spare of your database, ready to take over processing should your primary database go offline.
Components of Mirroring
- Principal – The primary database, the one end users and applications are working on
- Mirror – The standby database
- Witness – A third SQL Server instance who forms a quorum and enable automatic failover should a failure occur
How it works
Unlike database replication, which works at the logical level to replicate transactions to subscriber databases, database mirroring works at the physical level. As active transaction log records on the primary database are written to disk, they are also sent over the network to the mirror database and are reapplied there.
Should the principal database go offline, the mirror can automatically come online and start accepting end user connections. It assumes the role of the new principal database. When the old principal comes back online, it assumes the role of mirror. Depending on how your mirroring is configured and whether your application supports automatic failover, this whole process can be completely transparent to end users.
Mirroring Modes
You have two options for running database mirroring, high-safety or high-performance.
High-Safety. High-safety mode runs synchronously, as a transaction is committed on the principal, the log record is sent to the mirror. The principal waits until the transaction is committed on the mirror before continuing. Depending on the latency between the two servers, this could result in performance degradation on your primary database. The trade-off, however, is that both databases are guaranteed to be transactionally consistent should a failure occur.
High-performance. Unlike high-safety mode, high-performance mode runs asynchronously, meaning the principal does not wait for an acknowledgement from the mirror before continuing its processing. This results in better performance on the principal, but if the mirror is lagging too far behind the principal, you might experience some data loss in the event of a failure.
Can I get a witness?
As I mentioned before, the witness is a third SQL Server instance who’s sole role in the mirroring process is to form a quorum and allow for automatic role switching in the event of a failure. The witness can be any existing instance, you don’t need to install a new instance (and license) just for this.
Why do we need a third instance? As mirroring is running, the principal, mirror, and witness servers are constantly talking to each other and monitoring their status. Let’s imagine we don’t have a witness and we experience a network communications problem between the mirror and the principal. Both servers are still online, but both think the other is offline. Without a third witness server, the mirror would automatically come online to assume the role of principal. But the original principal is still up and running. See where this might cause some problems?
Mirror, mirror, on the wall, who is the fairest DBA of all?
With a witness, if the mirror loses contact with the principal, it contacts the witness to see if the witness can still talk to the principal. If the witness still has contact with the principal, nothing happens. The mirror continues to wait for communications to resume. If the witness has also lost contact with the principal, the mirror and witness form a quorum and switch the mirror database from mirror to principal. This is automatic failover. If the mirror loses contact with both the principal and the witness, no failover can occur.
Therefore, automatic failover in mirroring can only occur if a witness has been configured. You can still mirror databases without a witness, but failover will require human intervention.
Limitations
There are a few limitations to running database mirroring. The first is that, because it relies on the transaction log, mirrored databases must be in FULL recovery mode. Another limitation is that the system databases cannot be mirrored. This means things like logins, jobs, linked servers, etc. need to be manually maintained on both the principal and mirror servers. This, in my opinion, is mirroring’s biggest shortcoming.
Enhancements in SQL 2008 and beyond
SQL 2008 brought some nice new features to database mirroring, the first being compression of the log data before it’s sent over the network, meaning less network traffic. SQL 2008 also gave us automatic page repair. If one of the databases experiences a corrupt page, it will automatically request a copy of that page from the other database and fix the corruption. Unfortunately, this feature is only available in Enterprise Edition.
In SQL Server Denali, we’ll have readable mirrors. That in itself would have been enough for me. But really, Microsoft has taken mirroring to a whole new level with its new AlwaysOn feature. There’s so much going on there, it really deserves a post of its own. Hmm, there’s an idea.