With the release of SQL Server 2005 Service Pack 1 (SP1) in April, database mirroring finally became a reality for SQL Server 2005. Implementing this high-availability feature, which is supported by both 64-bit and 32-bit SQL Server 2005 Enterprise and Standard editions, involves using three SQL Server systems: a principal server, a mirror server, and a witness server. The principal and mirror are required. The witness is needed only if you want automatic-failover capability. Although the principal and mirror must run on the Enterprise or Standard editions, the witness can be any SQL Server edition, including SQL Server 2005 Express,and none of the systems involved are required to have matching or specialized hardware.
Database mirroring doesn't share the distance limitations of Windows clustering. And, unlike Windows clustering, which runs at the system server level, database mirroring is implemented at the database level. This feature works by sending transaction-log records between the principal and mirror servers. The principal server is the source of the database changes. The mirror server maintains a copy of one or more of the principal server's mirrored databases. Note that the mirror server isn't restricted to providing just data backup services but can also actively support other unrelated databases and applications.
Working with the new SQL Native Client (SNAC), database mirroring enables maximum availability for client applications by automatically redirecting SNAC network clients to the mirror server if the database on the principal server is unavailable. Database mirroring operates in two modes, asynchronous and synchronous:
1. Asynchronous
Of the two modes, asynchronous provides the best performance but offers lower transaction consistency and doesn't support automatic failover. Database transactions on the principal server are immediately committed on the principal without waiting for the mirror server to acknowledge that it's written the data to its log. Transactions are written to the mirror server asynchronously.
2. Synchronous
Synchronous mode provides a higher level of data protection than asynchronous mode. To ensure that transactions are committed on both the principal and the mirror servers, the principal waits for an acknowledgement from the mirror before committing transactions. The wait imposes a performance overhead that's influenced by the activity of the systems and their connection speed. This mode is best suited for high-speed LAN links.
Synchronous database mirroring in turn supports two operation modes: high availability and high protection. In high-availability mode, if you have a witness server, database availability is the highest priority. The protected database will be available as long as two of the three servers (principal, mirror, witness) are present. High-availability mode is capable of automatic failover.
In high-protection mode, if you don't have a witness server, protecting the data is the highest priority. If either the principal or the mirror is unavailable, then the database is taken offline.
