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.
End of Article
