什么是SQL Server数据库镜像？

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 镜像的镜像实例。 在特殊情况下，可以有第三个SQL Server实例充当见证人

实施实例 (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

常见的镜像配置之一是具有两个SQL Server（SQLServer-1和SQLServer-2），两个实例（SQLInstance-1和SQLInstance-2）以及一个名为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

第二种常见配置是具有一台SQL Server计算机，两个SQL Server实例和一个名为SQLDB-1的镜像数据库的环境。 该解决方案存在一个主要缺陷，因为如果SQLServer-1发生故障，则两个实例均将不可用。

操作模式 (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

可以将SQL Server数据库镜像设置为提供高可用性或灾难恢复。 根据需要，DBA可以选择三种可用模式

• • Might produce delay and slower operation because transactions must be committed on both databases
    可能会导致延迟并降低操作速度，因为必须在两个数据库上都提交事务
  • • 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
      不执行任何操作–等待委托人再次可用。 在此期间，SQL Server实例不可用。 镜像将在停止的地方继续
    • 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
      将SQL Server实例强制在镜像数据库上-镜像数据库成为主体。 由于原始主体数据库上已提交的事务（尚未在当前充当主体的镜像上落实）而可能导致数据丢失
• • Might produce delay and slower operation because transactions must be committed on both databases
    可能会导致延迟并降低操作速度，因为必须在两个数据库上都提交事务
  • • Let the automatic failover process complete, the mirrored database becomes the principal
      让自动故障转移过程完成，镜像数据库成为主体
• • The high performance mode is only available in the Enterprise edition of SQL Server
    高性能模式仅在SQL Server企业版中可用
  • • Do nothing – wait for the principal to become available again. The SQL Server is unavailable. Mirroring will continue where it has stopped
      不执行任何操作–等待委托人再次可用。 SQL Server不可用。 镜像将在停止的地方继续
    • 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
      将SQL Server实例强制在镜像数据库上-镜像数据库成为主体。 由于数据库之间的异步通信，数据丢失的可能性更大
    • 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
      手动更新–为减少数据丢失，如果发生故障的服务器允许，则进行日志备份的尾部，删除镜像并在先前镜像的数据库上还原日志的尾部

使用SQL Server数据库镜像的优缺点 (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

使用SQL Server数据库镜像有多个好处：内置SQL Server功能，相对容易设置，可以在高安全性模式下提供自动故障转移，等等。数据库镜像可以与其他灾难恢复选项（如群集，日志传送）结合使用和复制

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

在将来的版本中， 将从SQL Server中删除数据库镜像 ，以使用AlwaysOn可用性组。 此外，数据库镜像是每个数据库的唯一解决方案，这意味着必须在镜像上手动重新创建主体SQL Server的登录名和作业。 也存在延迟的可能性，只有通过更好的硬件才能减少延迟

设置数据库镜像环境 (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

数据库镜像功能在SQL Server 2005及更高版本中可用。 操作模式的可用性取决于SQL Server版本。 可以组合不同SQL Server版本，但不建议这样做

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

必须在将充当镜像SQL Server实例上创建并还原将被镜像的数据库的完整数据库和事务日志备份。 必须使用WITH NORECOVERY选项执行还原过程

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

需要使用SQL Server Management Studio向导或T-SQL代码从主体服务器启动数据库镜像设置。 在设置过程的开始，有一个选择见证SQL Server实例的选项，只有在需要具有自动故障转移模式的高度安全性时才需要。 SQL Server实例必须能够通信，这需要使用指定的端口和名称创建所谓的终结点。 在主体和镜像SQL Server实例上都需要这些设置

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

创建端点并检查连接后，请选择高安全性或高性能的操作模式。 如果指定了见证服务器，则选择高安全性模式将导致高安全性并具有自动故障转移功能

翻译自: https://www.sqlshack.com/sql-server-database-mirroring/