Microsoft SQL Server 2005 (SP1) introduced database mirroring as a failover solution. This process moves database transactions from one SQL Server database to another SQL Server database on a different instance of SQL Server. The mirrored copy is a standby copy and cannot be accessed directly; it is only used as a failover solution. Mirroring offers three different modes of implementation, depending on how you want to handle failover processing:
-
High-safety with a witness (synchronous)—failover is automatic
-
High-safety without a witness (synchronous)—failover is manual
-
High-performance (asynchronous)—failover is manual
In high-safety mode, transactions are not considered committed until they are hardened on both the mirror and primary server. While this practice ensures the databases remain synchronized, it can result in decreased performance for the principal database. In high-performance mode, as soon as the transaction is sent to the mirror server and hardened on the principal, it is considered complete. The principal does not wait for any acknowledgment from the mirror server. Therefore, the two databases are not synchronized and there is a potential for data loss. However, overall performance of the primary database is less affected.
For more information on database mirroring, see the following Microsoft documents:
-
Database Mirroring Administration (Microsoft Docs) at http://msdn.microsoft.com/en-us/library/ms177412(v=sql.105).aspx.
-
Implementing Application Failover with Database Mirroring (SQL Server Best Practices Article) at http://technet.microsoft.com/en-us/library/cc917713.aspx.
-
Database Mirroring Best Practices and Performance Considerations at http://technet.microsoft.com/en-us/library/cc917681.aspx.
-
Things to Consider When Setting up Database Mirroring in SQL Server at http://support.microsoft.com/kb/2001270.
-
Troubleshooting Database Mirroring Deployment (Microsoft Docs) at http://msdn.microsoft.com/en-us/library/ms189127(v=sql.105).aspx.