Database Mirroring

Overview

Database mirroring is a solution for enhancing the availability and recoverability of a SQL Server database. Database mirroring provides an automatic failover mechanism to a standby copy of a database. It is a feature that enhances the availability of a database by maintaining a duplicate copy of a production database on a separate server instance.

Key Concepts

  • Principal Database: The primary database that receives transactions.
  • Mirror Database: The standby copy of the principal database.
  • Witness Server: An optional SQL Server instance that can automatically fail over the principal role to the mirror server if the principal server becomes unavailable.
  • Redundancy: Database mirroring ensures that data is available even if the primary server fails.
  • High Safety Mode: Transactions are not committed to the transaction log on the principal server until they have been written to the transaction log of the mirror server.
  • High Performance Mode: Transactions are committed to the transaction log on the principal server as soon as possible, without waiting for them to be hardened on the mirror server.

Benefits

  • High availability for critical databases.
  • Disaster recovery capabilities.
  • Reduced downtime during hardware or software failures.
  • Support for automatic or manual failover.

Setting Up Database Mirroring

Setting up database mirroring involves configuring the principal and mirror servers, and optionally a witness server. This can be done through SQL Server Management Studio (SSMS) or Transact-SQL.

Steps using SSMS:

  1. Right-click the principal database, navigate to Tasks, and select Mirror.
  2. On the Database Mirroring page, click Configure Security to set up endpoints and credentials.
  3. Specify the mirror server instance and the witness server instance (if applicable).
  4. Configure the mirroring operating mode (High Safety or High Performance).
  5. Initiate mirroring.

Transact-SQL Example (Simplified):

-- On the Principal Server
ALTER DATABASE YourDatabase
SET PARTNER = 'Server=MirrorServerName;Database=YourDatabase'
GO

-- On the Mirror Server
ALTER DATABASE YourDatabase
SET PARTNER = 'Server=PrincipalServerName;Database=YourDatabase'
GO

Failover and Failback

Database mirroring supports both manual and automatic failover.

  • Automatic Failover: Requires High Safety mode with a witness. The failover occurs automatically when the principal server is unreachable.
  • Manual Failover: Can be initiated from either the principal or mirror server. This is useful for planned maintenance or when the principal server is experiencing issues but is still accessible.
  • Failback: Returning the principal role to the original principal server after a failover.
Important: Database mirroring is superseded by Always On Availability Groups in SQL Server 2012 and later. Always On Availability Groups offer more advanced features and better scalability. It is recommended to use Always On Availability Groups for new deployments requiring high availability and disaster recovery.

Further Reading