SQL Server database mirroring is a disaster recovery and high availability technique that involves two SQL Server instances on the same or different machines. One SQL Server instance acts as a primary instance called the principal, while the other is a mirrored instance called the mirror. In special cases, there can be a third SQL Server instance that acts as a witness

Implementation examples

One of the common mirroring configuration is the environment with two SQL Servers (SQLServer-1 and SQLServer-2), two instances (SQLInstance-1 and SQLInstance-2), and one mirrored database named SQLDB-1

The second common configuration is the environment with one SQL Server machine, two SQL Server instances, and one mirrored database named SQLDB-1. This solution has a major flaw because if SQLServer-1 goes down, both instances will be unavailable

Operating modes

SQL Server database mirroring can be set to provide high availability or disaster recovery. Depending on the needs, a DBA can choose among three available modes

• High safety – Data is written and committed on the principal and mirror databases synchronously. Only after committing on both databases, the database application can continue with activity
  • Might produce delay and slower operation because transactions must be committed on both databases
  • If the principal database goes down, two options are available:
    • Do nothing – wait for the principal to become available again. During that time, the SQL Server instance is unavailable. Mirroring will continue where it has stopped
    • Force the SQL Server instance on the mirror database – the mirror database becomes the principal. Possible data loss due to committed transactions on the original principal database which are not yet committed on the mirror currently acting as the principal
• High safety with automatic failover – Three servers are necessary. Data is written and must be committed synchronously both on the principal and mirror databases. Only after committing on both databases, the application can continue running
  • Might produce delay and slower operation because transactions must be committed on both databases
  • If the principal database goes down, only one option is available:
    • Let the automatic failover process complete, the mirrored database becomes the principal
• High performance – the asynchronous communication, data is written and committed on the principal server, and later sent and committed to the mirror server. Automatic failover isn’t possible and the witness server can’t be used
  • The high performance mode is only available in the Enterprise edition of SQL Server
  • If the principal database goes down, three options are available:
    • Do nothing – wait for the principal to become available again. The SQL Server is unavailable. Mirroring will continue where it has stopped
    • Force the SQL Server instance on the mirror database – the mirror database becomes the principal. Greater possibility for data loss, due to asynchronous communication between databases
    • Manual update – to reduce data loss, take the tail of the log backup if the failed server allows, remove mirroring and restore the tail of the log on the previously mirrored database

Advantages and disadvantages of using SQL Server database mirroring

Using SQL Server database mirroring has multiple benefits: a built-in SQL Server feature, relatively easy to set up, can provide automatic failover in high safety mode, etc. Database mirroring can be combined with other disaster recovery options such as clustering, log shipping, and replication

Database mirroring will be removed from SQL Server in future versions in favor of AlwaysOn Availability Groups. Also, database mirroring is per database only solution, which means that logins and jobs from the principal SQL Server must be manually recreated on the mirror. There is also possibility for delay, which can only be reduced with better hardware

Setting up the database mirroring environment

The database mirroring feature is available in SQL Server 2005 version and greater. Availability of the operating modes depends on the SQL Server edition. Different SQL Server versions can be combined, but it’s not recommended

The database that needs to be mirrored must be in the full recovery model. System databases can’t be mirrored

A full database and transaction log backups of the database which will be mirrored must be created and restored on the SQL Server instance which will act as the mirror. The restore process must be executed using the WITH NORECOVERY option

The database mirroring setup needs to be initiated from the principal server using the SQL Server Management Studio wizard or T-SQL code. At the beginning of the setup process, there’s an option for choosing a witness SQL Server instance which is only required if the high safety with automatic failover mode is desired. SQL Server instances must be able to communicate which requires creation of so-called endpoints with the port and name specified. These setting are required both on the principal and mirror SQL Server instances

After endpoints are created and connection checked, select the operating mode, either high safety or high performance. If the witness server is specified choosing high safety mode will result in high safety with automatic failover