SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability19
Synchronous mirroring with failover
For synchronous mirroring with failover, the primary server confirms the transaction to the application only after acknowledgement from the mirrored server is received. This configuration enables automatic failover of the primary system to the secondary system. This is similar to a Windows clustering scenario.
However, this configuration offers two-phased transactional consistency. Instead of having only a single copy of the data on shared storage, there are two separate and consistent copies.
Automatic failover requires the addition of a witness instance of SQL Server, which can be a server running Microsoft SQL Server 2005 Express Edition. The witness server provides the quorum logic necessary in automatic failover clusters in cases where two of the three servers must agree on failover. If the principal server goes down, the witness and mirrored servers form a quorum and then arbitrate to bring the mirrored server online and redirect clients to the mirrored server.
SQL Server 2005 supports log shipping to feed transaction logs from one database to another on a constant basis. Continually backing up the transaction logs from a primary database, and then copying and restoring the logs to a secondary database, keeps the databases synchronized. This provides a backup server for disaster recovery and also offers a means to offload query processing from the primary server to a read-only destination server.
Log shipping is recommended for use in a variety of scenarios. Some customers use it across geographically distant data centers as part of their disaster-recovery configuration. Note that with log shipping there is no automatic failover and committed transactions can be lost. This is dependent on the frequency with which transaction log backups are performed.
Microsoft Corporation © 2005