Mirror Database

A mirror database is a backup copy created on another server to ensure continuous data availability and avoid downtime.

According to Microsoft SQL Server TechNet, database mirroring only works with databases that are configured for the full recovery model. Neither the simple or the bulk-logged recovery models support database mirroring. Note that database mirroring is supported in both SQL Server Standard and Enterprise.

With database mirroring, copies of a single database reside on different computers called server instances, typically remote from each other. The primary server instance provides copies of the database to clients. The mirror server instance is available as a backup in case of maintenance, upgrading or a problem with the principal server instance.

Synchronous vs Asynchronous Operation

With synchronous operation, the system immediately copies every change in the primary database to the mirror database and vice-versa. The mirror database is typically called a “hot standby” with synchronous operations. Frequent updating of a hot standby in synchronous operation offers maximum data protection, but frequent updates can also slow down database performance.

Many enterprises use asynchronous operation, which means updating less frequently and does not degrade the performance of the database. The mirror database is typically called a “warm standby” when in asynchronous operation and only being updated every hour or so.

You can easily keep track of all of your mirror database operations with Idera’s Diagnostic Manager. Just open the Mirroring view of the Databases tab by selecting the appropriate SQL Server instance, and then clicking Databases and Mirroring. With this view, you can get details on the entire roster of mirrored databases on all monitored SQL Server instances, including current status and history.