4. DATABASE MIRRORING

Question

Tell me all i need to know about database mirroring in MSSQL and some example code

Answer 1

Database mirroring in SQL Server is a high-availability and disaster-recovery feature that maintains two copies of a single user database on different server instances.

Key concepts

1. Roles

• Principal database: The production (read-write) copy of the database.
• Mirror database: A copy of the principal database, kept in a restoring state (NORECOVERY) and not directly accessible for user queries.
• Witness (optional): A third SQL Server instance used only in high-safety mode with automatic failover to support automatic role switching.

2. Session and partners

• A mirroring session is established between two server instances (partners) for a single database.
• Each database is mirrored independently; databases cannot fail over as a group.
• A server instance can be principal for some databases and mirror for others at the same time.

3. Operating modes

• High-safety mode (synchronous)
  • SAFETY = FULL.
  • Transactions commit on principal only after the log record is written on the mirror.
  • Zero or near-zero data loss.
  • Can be configured with or without a witness.
  • With a witness, supports automatic failover.
• High-performance mode (asynchronous)
  • SAFETY = OFF.
  • Principal does not wait for mirror acknowledgment.
  • Better performance over high-latency links but possible data loss on failover.
  • No automatic failover.

4. Benefits

• Increases availability: Fast failover to the mirror in high-safety mode with automatic failover.
• Increases data protection: Maintains a redundant copy of the database; supports automatic page repair between partners.
• Supports rolling upgrades: Upgrade one instance, fail over, then upgrade the other, minimizing downtime.

5. Client connections

• Clients typically connect using a connection string that specifies both principal and failover partner.
• The .NET Data Provider for SQL Server supports automatic redirection to the current principal. See “Connect Clients to a Database Mirroring Session (SQL Server)” in the documentation.

6. Transaction log behavior and pausing

• Mirroring works by sending log records from principal to mirror.
• If the mirroring session is paused, the principal stops sending log records; the log on the principal cannot be truncated and will grow until mirroring is resumed.

7. Limitations and scope

• Mirroring is configured per database, not per instance.
• Mirrored database is not readable (in standard mirroring) because it remains in NORECOVERY.
• Mirroring does not replace backups; backups are still required.

End-to-end setup overview

To establish a mirroring session, the high-level steps are:

1. Prepare the mirror database

• On the principal server:
  1. Ensure the database is in FULL recovery model.
  2. Take a full backup of the principal database.
  3. Typically take at least one transaction log backup after the full backup.
• On the mirror server: 4. Restore the full backup WITH NORECOVERY. 5. Restore the latest differential backup (if any) WITH NORECOVERY. 6. Restore all log backups in sequence WITH NORECOVERY.

The mirror database must have the same name as the principal database.

2. Configure endpoints and security

• Create a database mirroring endpoint on each partner (and witness if used).
• Configure authentication (Windows or certificates) and grant CONNECT permissions to the appropriate logins.

3. Establish the mirroring session

• Use Transact-SQL or the Database Mirroring Wizard in SSMS to configure:
  • Principal server name and endpoint.
  • Mirror server name and endpoint.
  • Optional witness server.
  • Operating mode (SAFETY FULL/OFF, witness for automatic failover).

4. Start mirroring

• Once both partners are configured and the mirror database is in NORECOVERY with all required log backups applied, start the mirroring session.

Example: Preparing the mirror database (T-SQL)

On the principal server (PARTNERHOST1):

-- 1. Full database backup
BACKUP DATABASE AdventureWorks
    TO DISK = 'C:\AdventureWorks_full.bak'
    WITH FORMAT;
GO

-- 2. Log backup after the full backup
BACKUP LOG AdventureWorks
    TO DISK = 'C:\AdventureWorks_log.bak';
GO

Copy the backup files to the mirror server.

On the mirror server (PARTNERHOST5):

-- 3. Restore full backup WITH NORECOVERY
RESTORE DATABASE AdventureWorks
    FROM DISK = 'D:\AdventureWorks_full.bak'
    WITH NORECOVERY;
GO

-- 4. Restore log backup(s) WITH NORECOVERY
RESTORE LOG AdventureWorks
    FROM DISK = 'D:\AdventureWorks_log.bak'
    WITH NORECOVERY;
GO

At this point, the mirror database exists in a restoring state and is ready for mirroring.

Example: Creating mirroring endpoints (Windows authentication)

On principal (PARTNERHOST1):

USE master;
GO

CREATE ENDPOINT Endpoint_Mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = 7022)
    FOR DATABASE_MIRRORING (ROLE = ALL);
GO

On mirror (PARTNERHOST5):

USE master;
GO

CREATE ENDPOINT Endpoint_Mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = 7022)
    FOR DATABASE_MIRRORING (ROLE = ALL);
GO

If using certificates instead of Windows authentication, create logins, users, and certificates on each host and grant CONNECT on the endpoint, for example:

-- On HOST_A, to allow HOST_B to connect
USE master;
GO

CREATE LOGIN HOST_B_login WITH PASSWORD = '<StrongPassword1>';
GO

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
GO

CREATE CERTIFICATE HOST_B_cert
    AUTHORIZATION HOST_B_user
    FROM FILE = 'C:\HOST_B_cert.cer';
GO

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO

Repeat the symmetric steps on HOST_B for HOST_A.

Example: Establishing the mirroring session (outline)

After endpoints and security are configured and the mirror database is prepared, use the Transact-SQL pattern described in “Establish a Database Mirroring Session Using Windows Authentication (Transact-SQL)” to:

• On the principal:
  • Set PARTNER = 'TCP://mirrorServer:port'.
• On the mirror:
  • Set PARTNER = 'TCP://principalServer:port'.
• Optionally, on both partners:
  • Set WITNESS = 'TCP://witnessServer:port' for automatic failover.

Example: Pausing and resuming mirroring

-- Pause mirroring (on principal)
ALTER DATABASE AdventureWorks SET PARTNER SUSPEND;
GO

-- Resume mirroring
ALTER DATABASE AdventureWorks SET PARTNER RESUME;
GO

While the session is paused, the principal continues to accumulate log records that cannot be truncated, so the transaction log can grow significantly if the pause is prolonged.

Operational notes

• Failover
  • In high-safety mode with a witness, failover can be automatic.
  • In other modes, failover is manual and may involve potential data loss in asynchronous mode.
• Multiple mirrored databases
  • Each database is independent; failover is per database.
  • A server can be principal for some databases and mirror for others simultaneously.
• Resource planning
  • Ensure sufficient CPU, memory, disk, and network bandwidth on all partners and witness.
  • When a server acts as both partner and witness, ensure the endpoint supports both roles and the system has enough resources to avoid contention.

---

References:

• Database Mirroring (SQL Server)
• Configure database mirroring
• Setting Up Database Mirroring (SQL Server)
• Prepare a Mirror Database for Mirroring (SQL Server)
• Example: Setting Up Database Mirroring Using Certificates (Transact-SQL)