Database Mirroring, a much anticipated high-availability feature that provides database-level failover, has been excluded from the SQL Server 2005 release to manufacturing (RTM). Microsoft has slated it for delivery in 2006. Let’s take a look at what Database Mirroring is and how it works.
In the event that the primary database fails, database mirroring enables a second standby database to be almost instantly available. You can set up Database Mirroring on one database or for multiple databases on the same server. The feature provides zero data loss, and the secondary database will always be updated with the current transaction that’s being processed on the primary database server Database Mirroring provides nearly instant failover, taking only a few seconds, whereas clustering typically requires a minimum of about 30 seconds to fail over—sometimes more, depending on the level of database activity and the size of the databases on the failed server. Database Mirroring provides an added protection against disk failure because it doesn’t require a shared quorum disk like clustering solutions do. In addition, Database Mirroring has virtually no distance limitation. Figure A shows Database Mirroring architecture.
As you can see in Figure A, the principal server is the SQL Server 2005 system currently providing the database services. By default, all incoming client connections are to the principal server. The mirroring server’s job is to maintain a copy of the principal server’s mirrored database. The witness is optional, but it plays a key role in automatic failover and is used to cast a deciding vote about which system essentially acts as the principal server.
The mirror server isn’t restricted to just providing backup services. Other databases on the secondary server can be actively supporting other unrelated applications. For performance reasons, the mirror server should have enough capacity to assume the work of the principal in the event of a failover. That’s not the case with the witness; in fact, you can use a SQL Server 2005 Express system as the witness. Database Mirroring works by sending transaction logs between the principal server and the mirror server. When a client system writes a transaction to the principal server, that request gets written to the principal server’s log file before being written into the data file. That transaction record also gets sent to the mirror server where it gets written to the mirror server’s transaction log.