Database Mirroring Overview

Database mirroring was first officially supported with SQL Server 2005 SP1. Database mirroring is available in Enterprise and Developer Editions and with some restrictions in Standard and Business Intelligence Editions. Developer Edition can be used only for development purposes and not for production.

The basic concept of database mirroring is simple. Database mirroring maintains a hot standby database (mirror database) kept in sync with the production database (principal database) by transferring transaction log records from the principal database to a mirror database over the network, either synchronously or asynchronously. In case of a failure, the mirror database can be quickly accessed by the clients. Database mirroring has the following benefits:

  • It increases database protection by maintaining a mirror copy of your database.
  • It enables you to choose only the databases that you want to mirror from one SQL Server instance to another. There is a 1:1 ratio from the principal to mirror SQL Server. Multiple databases in a SQL Server instance can be mirrored together using AlwaysOn Availability Groups, which are covered later in the section “High Availability/AlwaysOn.”
  • By default, it compresses and encrypts the data between the principal and mirror server.
  • It improves the availability of your databases during hardware or software upgrades.
  • It can be used for high database availability and disaster recovery purposes. To achieve high database availability, ...

Get Microsoft SQL Server 2012 Bible now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.