Mirroring Support in Flex2SQL for MS SQL Server

Microsoft introduced database mirroring in SQL Server 2005 to increase database availability. Database mirroring maintains two copies of a single database on different server instances. One instance is the primary or principal server, the other instance is the standby or mirror server. If the principal server goes down the mirror server can take over with no loss of committed data.


All SQL Server databases record data changes in a transaction log before any changes are made to the actual database. In database mirroring, the principal server writes the transaction log buffer to disk and simultaneously sends it to a mirror server. The transaction log records are then replayed on the mirror server to keep the two database instances in sync. A third (optional) server witnesses the health of the principal and mirror servers to enable automatic failover.

Setting Up Mirroring in your DataFlex application

1. Create the principal and mirror databases.
   
   
2. Enable FULL Recovery Model.
   
   
3. Create listening endpoints.
   
   
4. Backup the principal database.
   
   
5. Restore the backup to the mirror.
   
   
6. Enable the mirroring session.
   
   
7. Use the Mertech SET_MIRROR_SERVER command to define the failover server.

Note: For additional details see the Mertech whitepaper Database Mirroring with Mertech Flex2SQL for MS SQL Server

Summary

Fast failover with minimal data loss has traditionally involved higher hardware cost and greater software complexity. Database mirroring, however, can fail over quickly with no loss of committed data, does not require proprietary hardware, and is easy to set up and manage. Using SQL Server Mirroring, you can ensure that your application is up 24/7.