Microsoft SQL Server Database Mirroring

Database mirroring offers one additional high-availability solution for the SQL Server database. Database mirroring is available for production use as of SQL Server 2005 service pack 1. It is a software solution, which enables the database availability immediately by failing over or manually redirecting to the mirrored database.

High availability of the database is achieved by keeping a copy of the production database as “mirrored” database in another server and maintaining the transaction-consistent status of the mirror database by updating it regularly and immediately with transaction log records from the principal server. There are different operation modes for database mirroring based on the customer requirements and setup of the servers (principal and mirror).

The following operation modes are available and you can set them in SQL Server Management Studio:

• High performance (asynchronous)

  When database mirroring is configured in this mode, the transaction log records are sent asynchronously to the mirrored server. The primary server does not wait for any confirmation from the mirrored server. This mode does not guarantee that all transactions that are committed on the principal server are saved in the mirrored database.

  You can use asynchronous mirroring if:

  • Possible data loss of the last few transactions is acceptable

  • The database mirror is located in a remote site, which results in too long latency periods

• High safety without automatic failover (synchronous)

  When database mirroring is configured in this mode, the primary server confirms the transaction only after receiving the acknowledgment from the mirrored server. This mode offers high protection of data, since at any point of time the databases in both principal and mirror server are always consistent. But the requirement of two-phase transactional commit in this mode has an impact on the performance.

• High safety with automatic failover (synchronous).

  When database mirroring is configured in this mode, an automatic failover to the mirrored server takes place in the event of problems on the principal server, thereby increasing the availability of your production database. This mode requires a Witness Server to be installed to check the availability and status of the principal server.

  Note

  The DataDirect JDBC driver, that is used in some SAP releases does not support automatic failover in database mirroring scenarios. This means that the SAP Java application server cannot take advantage of this feature, if the DataDirect driver is used. The new Microsoft JDBC driver supports database mirroring.

  Check the Product Availability Matrix (PAM) and SAP Notes to find out whether your specific product release already supports the Microsoft JDBC driver.