Can I Get A Witness! - High Availability (HA) Database Mirroring
We have blogged a good deal about clustering and HA concepts as they relate to ColdFusion and JRun. In 2009 I will be blogging a lot more about database issues because almost all web applications and RIA's have dependencies and in my experience the most oft required dependency is a database. This blog piece relates to SQL Server and database mirroring which was introduced in SQL Server 2005 and which is also available in SQL Server 2008. There were some significant enhancements mainly related to mirroring performance in SQL Server 2008.
Transaction logs are a critical part of a SQL Servers overall operations. The transaction log records almost all transactions that take place in the operations if adding, changing and reading data in SQL Server. As I noted in a previous article, the location of these logs along with managing their size are important considerations in building out enterprise level SQL Server installations. Before database transactions cause any changes to actual data pages, the transaction log is created in log-buffer memory and eventually "hardened" to permanent disk storage on the hard drive. In SQL Server 2005 and 2008 the transaction log is copied from one database server to another in the mirrored set. In this sort of HA schema we are employing an active-passive paradigm; one database will serve all requests and
this is known as the "principal" in the mirrored set. In database mirroring, as the principal server writes the principal database's log buffer to disk, it simultaneously sends that block of log records from that transaction log to the mirror instance. At the mirror instance those log details reside first in log-buffer memory and are subsequently "hardened" to permanent disk storage on the hard drive.
In this mirrored scenario there are two servers, a "principal" and a "mirror" member. The principal member serves all requests and copies all changes to the mirror member from the transaction log. If the principal database fails the fail-over has to be actioned manually. There is another server which can be introduced to the mix to make fail-over automatic, this is known as the "witness" server. Basically the principal, mirror and witness servers are in constant communication, to determine if the principal (active) server is available and in not a promotion of the mirror to principal can be achieved in seconds.
Here is a very long article on Microsoft's web site which goes into great detail about all this. It is a good read if you want to know the granular details and use this feature of SQL Server 2005 and 2008.