SQL Server 2005 introduced the much awaited Database Mirroring feature. OK, so we had to wait for SP1 for official support and for SQL Server 2008 for some enhancements like better performance and automatic repair. It’s still a great feature if you need failover of a database without the expense of a cluster. One surprising aspect of Database Mirroring is that the clients will transparently redirect themselves no matter what mode we are using.
The default mode of Database Mirroring is High Availability. This requires three SQL Server instances to play the roles of Principal, Mirror and Witness. The Principal hosts the updateable principal database, the Mirror hosts the synchronized mirror database and the Witness determines when an automatic failover is required. The Principal and the Mirror need to be Standard Edition or above. The Witness can be any edition including the Express edition. The updates on the principal are committed synchronously with the mirror so during a failover there is no loss of data – guaranteed.
If you do not require automatic failover, you can dispense with the Witness role and use High Protection mode. Manual failover is still available and again there is no loss of data during failover because of synchronous updates between principal and mirror.
If the synchronous updates are causing performance issues you can use High Performance mode which allows asynchronous updates between principal and mirror databases. This means that a commit on the principal does not have to wait for the two-phase commit from the mirror. The principal can continue undeterred while the mirror catches up; however in this mode there is no guarantee that some last minute transactions may not be lost during a failover. Because there is no Witness option in this mode, only manual failover is available.
But what about the client connections? They will actually redirect themselves using a process called Transparent Client Redirection assuming you are using the SQL Native Client or ADO.Net 2.0 or above. This is because the connection string now has two slots, one for the default Server Name and one for the “Failover Partner”. If one doesn’t work then it will try the second. This works independently of the Witness so is available for all three modes of Database Mirroring.
To me, this was a surprising bonus to the Database Mirroring feature. It just goes to show that if you don’t have a witness, you can simply look in the mirror.