数据库镜像和复制 (SQL Server)Database Mirroring and Replication (SQL Server)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

数据库镜像可以与复制一起使用以改进发布数据库的可用性。Database mirroring can be used in conjunction with replication to improve availability for the publication database. 数据库镜像涉及一个数据库的两个副本,这两个副本通常驻留在不同的计算机上。Database mirroring involves two copies of a single database that typically reside on different computers. 在任何给定时间都只有一个数据库副本可供客户端使用。At any given time, only one copy of the database is currently available to clients. 该副本称为主体数据库。This copy is known as the principal database. 客户端对主体数据库所做的更新应用到数据库的另一副本(称为镜像数据库)。Updates made by clients to the principal database are applied on the other copy of the database, known as the mirror database. 镜像涉及将在主体数据库上执行的每个插入、更新或删除操作的事务日志应用到镜像数据库上。Mirroring involves applying the transaction log from every insertion, update, or deletion made on the principal database onto the mirror database.

对发布数据库完全支持将复制故障转移到镜像数据库的功能;而对订阅数据库则提供有限支持。Replication failover to a mirror is fully supported for publication databases, with limited support for subscription databases. 对于分发数据库则不支持数据库镜像。Database mirroring is not supported for the distribution database. 有关无需重新配置复制就可以恢复分发数据库或订阅数据库的信息,请参阅 备份和还原复制数据库For information about recovering a distribution database or subscription database without any need to reconfigure replication, see Back Up and Restore Replicated Databases.


故障转移后,镜像数据库变为主体数据库。After a failover, the mirror becomes the principal. 在本主题中,“主体”和“镜像”始终是指原始主体和镜像。In this topic, "principal" and "mirror" always refer to the original principal and mirror.

将复制与数据库镜像一起使用的要求和注意事项Requirements and Considerations for Using Replication with Database Mirroring

将复制与数据库镜像一起使用时,注意以下要求和注意事项:Be aware of the following requirements and considerations when using replication with database mirroring:

  • 主体数据库和镜像数据库必须共享分发服务器。The principal and mirror must share a Distributor. 建议此处使用远程分发服务器,如果发布服务器有意外故障转移,则远程分发服务器可以提供较大的容错能力。We recommend that this be a remote Distributor, which provides greater fault tolerance if the Publisher has an unplanned failover.

  • 对于合并复制,以及对于使用只读订阅服务器或排队更新订阅服务器的事务复制,复制支持对发布数据库进行镜像。Replication supports mirroring the publication database for merge replication and for transactional replication with read-only Subscribers or queued updating Subscribers. 不支持即时更新对等拓扑中的订阅服务器、Oracle 发布服务器、发布服务器并重新发布。Immediate updating Subscribers, Oracle Publishers, Publishers in a peer-to-peer topology, and republishing are not supported.

  • 存在于数据库外部的元数据和对象不复制到镜像数据库,包括登录名、作业、链接服务器等等。Metadata and objects that exist outside the database are not copied to the mirror, including logins, jobs, linked servers, and so on. 如果要求镜像数据库中有元数据和对象,则必须手动复制它们。If you require the metadata and objects at the mirror, you must copy them manually. 有关详细信息,请参阅角色切换后登录名和作业的管理 (SQL Server)For more information, see Management of Logins and Jobs After Role Switching (SQL Server).

配置复制以及数据库镜像Configuring Replication with Database Mirroring

配置复制和数据库镜像包括五个步骤。Configuring replication and database mirroring involves five steps. 在下面的部分中将详细说明每个步骤。Each step is described in more detail in the following section.

  1. 配置发布服务器。Configure the Publisher.

  2. 配置数据库镜像。Configure database mirroring.

  3. 配置镜像数据库,使其使用与主体数据库相同的分发服务器。Configure the mirror to use the same Distributor as the principal.

  4. 配置用于故障转移的复制代理。Configure replication agents for failover.

  5. 向复制监视器添加主体数据库和镜像数据库。Add the principal and mirror to Replication Monitor.

也可以相反的顺序执行步骤 1 和步骤 2。Steps 1 and 2 can also be performed in the opposite order.

配置发布数据库的数据库镜像To configure database mirroring for a publication database

  1. 配置发布服务器:Configure the Publisher:

    1. 建议使用远程分发服务器。We recommend using a remote Distributor. 有关如何配置分发的详细信息,请参阅 配置分发For more information about configuring distribution, see Configure Distribution.

    2. 可以为快照发布及事务发布和/或合并发布启用数据库。You can enable a database for snapshot and transactional publications and/or merge publications. 对于将包含多种发布类型的镜像数据库,必须使用 sp_replicationdboption为同一节点上的两种类型都启用数据库。For mirrored databases that will contain more than one type of publication, you must enable the database for both types at the same node using sp_replicationdboption. 例如,可以在主体数据库上执行下面的存储过程调用:For example, you could execute the following stored procedure calls at the principal:

      exec sp_replicationdboption @dbname='<PublicationDatabase>', @optname='publish', @value=true;  
      exec sp_replicationdboption @dbname='<PublicationDatabase>', @optname='mergepublish', @value=true;  

      有关创建发布的详细信息,请参阅 发布数据和数据库对象For more information about creating publications, see Publish Data and Database Objects.

  2. 配置数据库镜像。Configure database mirroring. 有关详细信息,请参阅使用 Windows 身份验证建立数据库镜像会话 (SQL Server Management Studio)设置数据库镜像 (SQL Server)For more information, see Establish a Database Mirroring Session Using Windows Authentication (SQL Server Management Studio) and Setting Up Database Mirroring (SQL Server).

  3. 配置镜像的分发。Configure distribution for the mirror. 将镜像名称指定为发布服务器,并指定主体数据库使用的同一分发服务器和快照文件夹。Specify the mirror name as the Publisher, and specify the same Distributor and snapshot folder that the principal uses. 例如,如果想使用存储过程配置复制,可以在分发服务器上执行 sp_adddistpublisher ,然后在镜像上执行 sp_adddistributorFor example, if you are configuring replication with stored procedures, execute sp_adddistpublisher at the Distributor; and then execute sp_adddistributor at the mirror. 对于 sp_adddistpublisherFor sp_adddistpublisher:

    • 将 @publisher 参数的值设置为镜像的网络名称。Set the value of the @publisher parameter to the network name of the mirror.

    • 将 @working_directory 参数的值设置为主体数据库使用的快照文件夹。Set the value of the @working_directory parameter to the snapshot folder used by the principal.

  4. 为“-PublisherFailoverPartner”代理参数指定镜像名称。Specify the mirror name for the -PublisherFailoverPartner agent parameter. 下列代理在故障转移后需要使用此代理参数来标识镜像:Agent This parameter is required for the following agents to identify the mirror after failover:

    • 快照代理(对于所有发布)Snapshot Agent (for all publications)

    • 日志读取器代理(对于所有事务发布)Log Reader Agent (for all transactional publications)

    • 队列读取器代理(对于支持排队更新订阅的事务发布)Queue Reader Agent (for transactional publications that support queued updating subscriptions)

    • 合并代理(对于合并订阅)Merge Agent (for merge subscriptions)

    • SQL ServerSQL Server 复制侦听程序(replisapi.dll:用于使用 Web 同步进行同步的合并订阅)replication listener (replisapi.dll: for merge subscriptions synchronized using Web synchronization)

    • SQL 合并 ActiveX 控件(对于与控件同步的合并订阅)SQL Merge ActiveX Control (for merge subscriptions synchronized with the control)

    分发代理和分发 ActiveX 控件没有此参数,因为它们不连接到发布服务器。The Distribution Agent and Distribution ActiveX Control do not have this parameter because they do not connect to the Publisher.

    对代理参数所做的更改在下次启动代理时生效。Agent parameter changes take effect the next time the agent is started. 如果代理连续运行,则必须停止该代理,然后重新启动。If the agent runs continuously, you must stop and restart the agent. 可以在代理配置文件中和从命令提示符指定参数。Parameters can be specified in agent profiles and from the command prompt. 有关详细信息,请参阅:For more information, see:

    建议将“-PublisherFailoverPartner”添加到代理配置文件,然后在配置文件中指定镜像名称。We recommend adding the -PublisherFailoverPartner to an agent profile, and then specifying the mirror name in the profile. 例如,如果您通过存储过程配置复制,请执行以下操作:For example, if you are configuring replication with stored procedures:

    -- Execute sp_help_agent_profile in the context of the distribution database to get the list of profiles.  
    -- Select the profile id of the profile that needs to be updated from the result set.  
    -- In the agent_type column returned by sp_help_agent_profile:   
    -- 1 = Snapshot Agent; 2 = Log Reader Agent; 3 = Distribution Agent; 4 = Merge Agent; 9 = Queue Reader Agent.  
    exec sp_help_agent_profile;  
    -- Setting the -PublisherFailoverPartner parameter in the default Snapshot Agent profile (profile 1).  
    -- Execute sp_add_agent_parameter in the context of the distribution database.  
    exec sp_add_agent_parameter @profile_id = 1, @parameter_name = N'-PublisherFailoverPartner', @parameter_value = N'<Failover Partner Name>';  
    -- Setting the -PublisherFailoverPartner parameter in the default Merge Agent profile (profile 6).  
    -- Execute sp_add_agent_parameter in the context of the distribution database.  
    exec sp_add_agent_parameter @profile_id = 6, @parameter_name = N'-PublisherFailoverPartner', @parameter_value = N'<Failover Partner Name>';  
  5. 向复制监视器添加主体数据库和镜像数据库。Add the principal and mirror to Replication Monitor. 有关详细信息,请参阅 从复制监视器中添加和删除发布服务器For more information, see Add and Remove Publishers from Replication Monitor.

维护镜像发布数据库Maintaining a Mirrored Publication Database

维护镜像发布数据库与维护非镜像数据库基本相同,需要注意以下事项:Maintaining a mirrored publication database is essentially the same as maintaining a non-mirrored database, with the following considerations:

  • 管理和监视必须在活动服务器上进行。Administration and monitoring must occur at the active server. SQL Server Management StudioSQL Server Management Studio中,发布仅出现在活动服务器的 “本地发布” 文件夹下方。In SQL Server Management StudioSQL Server Management Studio, publications appear under the Local Publications folder only for the active server. 例如,如果故障转移到镜像数据库,则发布显示在镜像数据库中,而不再显示在主体数据库中。For example, if you failover to the mirror, the publications are displayed at the mirror and are no longer displayed at the principal. 如果数据库故障转移到镜像数据库,则可能需要手动刷新 Management StudioManagement Studio 和复制监视器才能反映更改。If the database fails over to the mirror, you might need to manually refresh Management StudioManagement Studio and Replication Monitor for the change to be reflected.

  • 复制监视器会在对象树中同时显示主体数据库和镜像数据库的“发布服务器”节点。Replication Monitor displays Publisher nodes in the object tree for both the principal and the mirror. 如果主体数据库位于活动服务器,则仅在复制监视器的主体数据库节点下显示发布信息。If the principal is the active server, publication information is displayed only under the principal node in Replication Monitor.

    如果镜像数据库位于活动服务器:If the mirror is the active server:

    • 代理出错时,只在主体数据库节点上指出错误,而不在镜像数据库节点上指出。If an agent has an error, the error is indicated only on the principal node, not on the mirror node.

    • 主体数据库不可用时,主体数据库节点和镜像数据库节点会显示相同的发布列表。If the principal is unavailable, the principal and mirror nodes display identical lists of publications. 这时,应对镜像数据库节点下的发布执行监视。Monitoring should be performed on the publications under the mirror node.

  • 当使用存储过程或复制管理对象 (RMO) 在镜像数据库上管理复制时,对于需要指定发布服务器名称的情况,必须指定已经为复制启用了数据库的实例的名称。When using stored procedures or Replication Management Objects (RMO) to administer replication at the mirror, for cases in which you specify the Publisher name, you must specify the name of the instance on which the database was enabled for replication. 若要确定相应的名称,请使用函数 publishingservernameTo determine the appropriate name, use the function publishingservername.

    如果对发布数据库做了镜像,则镜像数据库中存储的复制元数据与主体数据库中存储的元数据相同。When a publication database is mirrored, the replication metadata stored in the mirrored database is identical to the metadata stored in the principal database. 因此,对于为主体数据库上的复制启用的发布数据库,在镜像数据库上的系统表中存储的发布服务器实例名称是主体数据库的名称,而不是镜像数据库的名称。Consequently, for publication databases enabled for replication at the principal, the Publisher instance name stored in system tables at the mirror is the name of the principal, not the mirror. 如果发布数据库故障转移到镜像数据库,则这种情况会影响复制的配置和维护。This affects replication configuration and maintenance if the publication database fails over to the mirror. 例如,如果故障转移后使用镜像数据库上的存储过程配置复制,并且希望添加对主体数据库上启用的发布数据库的请求订阅,则必须为 sp_addpullsubscription 或 sp_addmergepullsubscription 的 @publisher 参数指定主体数据库名称,而不是镜像数据库名称。For example, if you are configuring replication with stored procedures on the mirror after a failover, and you want to add a pull subscription to a publication database that was enabled at the principal, you must specify the principal name rather than the mirror name for the @publisher parameter of sp_addpullsubscription or sp_addmergepullsubscription.

    如果故障转移到镜像数据库后在镜像数据库上启用发布数据库,则存储在系统表中的发布服务器实例名称是镜像数据库的名称;在此情况下,应将镜像数据库的名称用于 @publisher 参数。If you enable a publication database at the mirror after failover to the mirror, the Publisher instance name stored in system tables is the name of the mirror; in this case, you would use the name of the mirror for the @publisher parameter.


    某些情况下,如 sp_addpublication,只有非 SQL ServerSQL Server 发布服务器支持 @publisher 参数;在这些情况下,它与 SQL ServerSQL Server 数据库镜像无关。In some cases, such as sp_addpublication, the @publisher parameter is supported only for non-SQL ServerSQL Server Publishers; in these cases, it is not relevant for SQL ServerSQL Server database mirroring.

  • 若要在故障转移后在 Management StudioManagement Studio 中同步订阅:请同步来自订阅服务器的请求订阅以及来自活动发布服务器的推送订阅。To synchronize a subscription in Management StudioManagement Studio after a failover: synchronize pull subscriptions from the Subscriber; and synchronize push subscriptions from the active Publisher.

删除镜像后的复制行为Replication Behavior if Mirroring is Removed

如果从已发布数据库中删除了数据库镜像,请谨记以下情况:Keep the following issues in mind if database mirroring is removed from a published database:

  • 如果主体数据库上的发布数据库不再有镜像,则复制依照原主体数据库无改变地继续工作。If the publication database at the principal is no longer mirrored, replication continues to work unchanged against the original principal.

  • 如果发布数据库从主体数据库故障转移到镜像数据库,而且镜像关系随后被禁用或删除,则复制代理不再对镜像数据库起作用。If the publication database fails over from the principal to the mirror and the mirroring relationship is subsequently disabled or removed, replication agents will not function against the mirror. 如果主体数据库永久丢失,请禁用复制,然后用指定为发布服务器的像镜重新配置复制。If the principal is permanently lost, disable and then reconfigure replication with the mirror specified as the Publisher.

  • 如果完全删除数据库镜像,镜像数据库将处于恢复状态,必须还原才能起作用。If database mirroring is removed completely, the mirror database is in a recovery state and must be restored in order to become functional. 就复制而言,已恢复数据库的行为取决于是否指定了 KEEP_REPLICATION 选项。The behavior of the recovered database with respect to replication depends on whether the KEEP_REPLICATION option is specified. 在将已发布数据库还原到创建备份的服务器以外的服务器上时,此选项强制还原操作保留复制设置。This option forces the restore operation to preserve replication settings when restoring a published database to a server other than that on which the backup was created. 仅当另一个发布数据库不可用时才使用 KEEP_REPLICATION 选项。Use the KEEP_REPLICATION option only when the other publication database is unavailable. 如果另一个发布数据库仍然完好且仍在复制,则不支持此选项。The option is not supported if the other publication database is still intact and replicating. 有关 KEEP_REPLICATION 的详细信息,请参阅 RESTORE (Transact-SQL)For more information about KEEP_REPLICATION, see RESTORE (Transact-SQL).

日志读取器代理的行为Log Reader Agent Behavior

下表说明了日志读取器代理对于数据库镜像各种运行模式的行为。The following table describes Log Reader Agent behavior for the various operating modes of database mirroring.

运行模式Operating mode 镜像数据库不可用时日志读取器代理的行为Log Reader Agent behavior if the mirror is unavailable
具有自动故障转移的高安全性模式High-safety mode with automatic failover 如果镜像数据库不可用,则日志读取器代理将命令传播到分发数据库。If the mirror is unavailable, the Log Reader Agent propagates commands to the distribution database. 直到镜像数据库回到联机状态并且具有来自主体数据库的所有事务,主体数据库才能故障转移到镜像数据库。The principal cannot failover to the mirror until the mirror is back online and has all transactions from the principal.
高性能模式High-performance mode 如果镜像数据库不可用,则主体数据库公开(即无镜像)运行。If the mirror is unavailable, the principal database is running exposed (that is, unmirrored). 但是,日志读取器代理仅复制那些在镜像服务器上受保护的事务。However, the Log Reader Agent only replicates those transactions that are hardened on the mirror. 如果是强制服务,并且镜像服务器充当主体服务器的角色,则日志读取器代理将依照镜像服务器工作并开始拾取新事务。If service is forced and the mirror server assumes the role of the principal, the Log Reader Agent will work against the mirror and start picking up the new transactions.

请注意,如果镜像服务器落后于主体服务器,就会加大复制滞后。Be aware that replication latency will increase if the mirror falls behind the principal.
不带自动故障转移的高安全性模式High-safety mode without automatic failover 保证所有已提交的事务均在镜像服务器的磁盘上受到保护。All committed transactions are guaranteed to be hardened to disk on the mirror. 日志读取器代理仅复制那些在镜像服务器上受保护的事务。The Log Reader Agent replicates only those transactions that are hardened on the mirror. 如果镜像服务器不可用,则主体服务器禁止数据库中的进一步活动;因此,日志读取器代理没有事务可以复制。If the mirror is unavailable, the principal disallows further activity in the database; therefore the Log Reader Agent has no transactions to replicate.

另请参阅See Also

SQL Server 复制 SQL Server Replication
日志传送和复制 (SQL Server)Log Shipping and Replication (SQL Server)