SQL server数据库镜像同步技术

SQL Server数据库镜像技术提供了两种同步模式：高安全模式（同步）和高性能模式（异步）。高安全模式确保事务在主体和镜像服务器上都提交，保证数据一致性但会增加延迟；高性能模式则允许主体服务器立即响应客户端，不等待镜像确认，提高了性能但存在数据丢失风险。镜像配置支持一个主体服务器和一个镜像服务器，若需自动故障转移功能，则需要额外配置见证服务器。见证服务器不存储数据，仅用于监控主体服务器状态并在故

MrZhou周

353人浏览 · 2026-05-15 13:47:33

MrZhou周 · 2026-05-15 13:47:33 发布

SQL sever数据库镜像同步技术

参考网址：
https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server?view=sql-server-ver17

1.概述

Operating Modes
A database mirroring session runs with either synchronous or asynchronous operation. Under asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance. Under synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.

There are two mirroring operating modes. One of them, high-safety mode supports synchronous operation. Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.

The second operating mode, high-performance mode, runs asynchronously. The mirror server tries to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database. However, typically, the gap between the databases is small. However, the gap can become significant if the principal server is under a heavy work load or the system of the mirror server is overloaded.

In high-performance mode, as soon as the principal server sends a log record to the mirror server, the principal server sends a confirmation to the client. It doesn’t wait for an acknowledgment from the mirror server. This means that transactions commit without waiting for the mirror server to write the log to disk. Such asynchronous operation enables the principal server to run with minimum transaction latency, at the potential risk of some data loss.

All database mirroring sessions support only one principal server and one mirror server. This configuration is shown in the following illustration.

在这里插入图片描述

High-safety mode with automatic failover requires a third server instance, known as a witness. Unlike the two partners, the witness doesn’t serve the database. The witness supports automatic failover by verifying whether the principal server is up and functioning. The mirror server initiates automatic failover only if the mirror and the witness remain connected to each other after both have been disconnected from the principal server.