对 Azure SQL 数据库中的单一数据库、共用数据库和实例数据库进行事务复制Transactional replication with single, pooled, and instance databases in Azure SQL Database

事务复制是 Azure SQL 数据库和 SQL Server 的一项功能,用于将 Azure SQL 数据库或 SQL Server 中表的数据复制到远程数据库中的表。Transactional replication is a feature of Azure SQL Database and SQL Server that enables you to replicate data from a table in Azure SQL Database or a SQL Server to the tables placed on remote databases. 使用此功能可以同步不同数据库中的多个表。This feature allows you to synchronize multiple tables in different databases.

何时使用事务复制When to use Transactional replication

在以下情况下,事务复制非常有用:Transactional replication is useful in the following scenarios:

  • 发布数据库的一个或多个表中所做的更改,并将其分发到订阅了更改的一个或多个 SQL Server 或 Azure SQL 数据库。Publish changes made in one or more tables in a database and distribute them to one or many SQL Server or Azure SQL databases that subscribed for the changes.
  • 将多个分布式数据库保持同步状态。Keep several distributed databases in synchronized state.
  • 通过持续发布更改,将数据库从一个 SQL Server 或托管实例迁移到另一个数据库。Migrate databases from one SQL Server or managed instance to another database by continuously publishing the changes.

概述Overview

下图显示了事务复制的关键组件:The key components in transactional replication are shown in the following picture:

SQL 数据库的复制

发布服务器是通过将更新发送到分发服务器,来发布某些表(项目)所发生的更改的实例或服务器。The Publisher is an instance or server that publishes changes made on some tables (articles) by sending the updates to the Distributor. 以下版本的 SQL Server 支持从本地 SQL Server 发布到任何 Azure SQL 数据库:Publishing to any Azure SQL database from an on-premises SQL Server is supported by the following versions of SQL Server:

  • SQL Server 2019(预览版)SQL Server 2019 (preview)
  • SQL Server 2016 到 SQL 2017SQL Server 2016 to SQL 2017
  • SQL Server 2014 SP1 CU3 或更高版本 (12.00.4427)SQL Server 2014 SP1 CU3 or greater (12.00.4427)
  • SQL Server 2014 RTM CU10 (12.00.2556)SQL Server 2014 RTM CU10 (12.00.2556)
  • SQL Server 2012 SP3 或更高版本 (11.0.6020)SQL Server 2012 SP3 or greater (11.0.6020)
  • SQL Server 2012 SP2 CU8 (11.0.5634.0)SQL Server 2012 SP2 CU8 (11.0.5634.0)
  • 对于不支持发布到 Azure 中的对象的其他 SQL Server 版本,可以利用重新发布数据方法将数据转移到较新版本的 SQL Server。For other versions of SQL Server that do not support publishing to objects in Azure, it is possible to utilize the republishing data method to move data to newer versions of SQL Server.

分发服务器是从发布服务器收集项目中的更改,并将其分发到订阅服务器的实例或服务器。The Distributor is an instance or server that collects changes in the articles from a Publisher and distributes them to the Subscribers. 分发服务器可以是 Azure SQL 数据库托管实例或 SQL Server(可以采用等于或高于发布服务器版本的任何版本)。The Distributor can be either Azure SQL Database managed instance or SQL Server (any version as long it is equal to or higher than the Publisher version).

订阅服务器是接收发布服务器上发生的更改的实例或服务器。The Subscriber is an instance or server that is receiving the changes made on the Publisher. 订阅服务器可以是 Azure SQL 数据库或 SQL Server 数据库中的单一数据库、共用数据库和实例数据库。Subscribers can be either single, pooled, and instance databases in Azure SQL Database or SQL Server databases. 单一数据库或共用数据库上的订阅服务器必须配置为推送订阅服务器。A Subscriber on a single or pooled database must be configured as push-subscriber.

角色Role 单一数据库和共用数据库Single and pooled databases 实例数据库Instance databases
发布者Publisher No Yes
分发服务器Distributor No Yes
提取订阅服务器Pull subscriber No Yes
推送订阅服务器Push Subscriber Yes Yes
     

Note

当分发服务器是实例数据库而订阅服务器不是时,不支持请求订阅。A pull subscription is not supported when the distributor is an Instance database and the subscriber is not.

有不同的复制类型There are different types of replication:

复制Replication 单一数据库和共用数据库Single and pooled databases 实例数据库Instance databases
标准事务Standard Transactional 是(仅用作订阅服务器)Yes (only as subscriber) Yes
快照Snapshot 是(仅用作订阅服务器)Yes (only as subscriber) Yes
合并复制Merge replication No No
对等Peer-to-peer No No
双向Bidirectional No Yes
可更新订阅Updatable subscriptions No No
     

Note

  • 尝试使用旧版来配置复制可能导致错误 MSSQL_REPL20084(过程无法连接到订阅服务器)和 MSSQ_REPL40532(无法打开登录名所请求的服务器 <name>。Attempting to configure replication using an older version can result in error number MSSQL_REPL20084 (The process could not connect to Subscriber.) and MSSQ_REPL40532 (Cannot open server <name> requested by the login. 登录失败。)The login failed.)
  • 若要使用 Azure SQL 数据库的所有功能,必须使用最新版本的 SQL Server Management Studio (SSMS)SQL Server Data Tools (SSDT)To use all the features of Azure SQL Database, you must be using the latest versions of SQL Server Management Studio (SSMS) and SQL Server Data Tools (SSDT).

实例数据库和本地系统的可支持性矩阵Supportability matrix for Instance Databases and On-premises systems

实例数据库的复制可支持性矩阵与本地 SQL Server 的相同。The replication supportability matrix for instance databases is the same as the one for SQL Server on-premises.

发布者Publisher 分发服务器Distributor 订阅服务器Subscriber
SQL Server 2019SQL Server 2019 SQL Server 2019SQL Server 2019 SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2017SQL Server 2017 SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2016SQL Server 2016 SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2014SQL Server 2014 SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2008 R2SQL Server 2008 R2
SQL Server 2008SQL Server 2008
SQL Server 2012SQL Server 2012 SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2008 R2SQL Server 2008 R2
SQL Server 2008SQL Server 2008
SQL Server 2008 R2SQL Server 2008 R2
SQL Server 2008SQL Server 2008
SQL Server 2019SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2008 R2SQL Server 2008 R2
SQL Server 2008SQL Server 2008
SQL Server 2014SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2008 R2SQL Server 2008 R2
SQL Server 2008SQL Server 2008
     

要求Requirements

  • 连接时,在复制参与者之间使用 SQL 身份验证。Connectivity uses SQL Authentication between replication participants.
  • 复制功能使用的工作目录的 Azure 存储帐户共享。An Azure Storage Account share for the working directory used by replication.
  • 需要在托管实例子网的安全规则中打开端口 445(TCP 出站)才能访问 Azure 文件共享。Port 445 (TCP outbound) needs to be open in the security rules of the managed instance subnet to access the Azure file share.
  • 如果发布服务器/分发服务器位于托管实例上,而订阅服务器没有位于其上,则需要打开端口 1433(TCP 出站)。Port 1433 (TCP outbound) needs to be opened if the Publisher/Distributor are on a managed instance and the subscriber is not. 对于端口 1433 目标服务标记 ,你可能还需要将 allow_linkedserver_outbound 的托管实例 NSG 出站安全规则从 virtualnetwork 更改为 internetYou may also need to change the managed instance NSG outbound security rule for allow_linkedserver_outbound for the port 1433 Destination Service tag from virtualnetwork to internet.
  • 所有类型的复制参与者(发布服务器、分发服务器、拉取订阅服务器和推送订阅服务器)都可以放置在托管实例上,但发布服务器和分发服务器必须同时在云中或同时在本地。All types of replication participants (Publisher, Distributor, Pull Subscriber, and Push Subscriber) can be placed on managed instances, but the publisher and the distributor must be either both in the cloud or both on-premises.
  • 如果发布服务器、分发服务器和/或订阅服务器位于不同的虚拟网络中,则必须在每个实体之间建立 VPN 对等互连,使发布服务器与分发服务器之间存在 VPN 对等互连,并且/或者分发服务器与订阅服务器之间存在 VPN 对等互连。If either the publisher, distributor, and/or the subscriber exist in different virtual networks, then VPN peering must be established between each entity, such that there is VPN peering between the publisher and distributor, and/or there is VPN peering between the distributor and subscriber.

Note

  • 当分发服务器为实例数据库且订阅服务器位于本地时,如果阻止出站网络安全组 (NSG) 端口 445,则会在连接到 Azure 存储文件时遇到错误 53。You may encounter error 53 when connecting to an Azure Storage File if the outbound network security group (NSG) port 445 is blocked when the distributor is an instance database and the subscriber is on-premises. 更新 vNet NSG 以解决此问题。Update the vNet NSG to resolve this issue.

将数据同步与事务复制进行比较Compare Data Sync with Transactional Replication

数据同步Data Sync 事务复制Transactional Replication
优点Advantages - 主动-主动支持- Active-active support
- 在本地和 Azure SQL 数据库之间双向同步- Bi-directional between on-premises and Azure SQL Database
- 更低的延迟- Lower latency
- 事务一致性- Transactional consistency
- 迁移后重用现有拓扑- Reuse existing topology after migration
缺点Disadvantages - 5 分钟或更长的延迟- 5 min or more latency
- 无事务一致性- No transactional consistency
- 更高的性能影响- Higher performance impact
- 无法从 Azure SQL 数据库单一数据库或共用数据库发布- Can't publish from Azure SQL Database single database or pooled database
- 维护成本高- High maintenance cost

常用配置Common configurations

一般情况下,发布服务器和分发服务器必须都在云中,或者都在本地。In general, the publisher and the distributor must be either in the cloud or on-premises. 支持以下配置:The following configurations are supported:

带本地分发服务器的发布服务器位于托管实例上Publisher with local Distributor on a managed instance

用作发布服务器和分发服务器的单个实例

发布服务器和分发服务器在单个托管实例中配置,并将更改分发到本地的其他托管实例、单一数据库、共用数据库或 SQL Server。Publisher and distributor are configured within a single managed instance and distributing changes to other managed instance, single database, pooled database, or SQL Server on-premises.

带远程分发服务器的发布服务器位于托管实例上Publisher with remote distributor on a managed instance

在此配置中,由一个托管实例将更改发布到能够为许多源托管实例提供服务的另一个托管实例上的分发服务器,并将更改分发到托管实例、单一数据库、共用数据库或 SQL Server 上的一个或多个目标。In this configuration, one managed instance publishes changes to distributor placed on another managed instance that can serve many source managed instances and distribute changes to one or many targets on managed instance, single database, pooled database, or SQL Server.

发布服务器和分发服务器的独立实例

在两个托管实例上配置发布服务器和分发服务器。Publisher and distributor are configured on two managed instances. 此配置存在一些约束:There are some constraints with this configuration:

  • 两个托管实例位于同一 vNet 中。Both managed instances are on the same vNet.
  • 两个托管实例都位于同一位置。Both managed instances are in the same location.

发布服务器和分发服务器位于本地,订阅服务器位于单一数据库、共用数据库和实例数据库上Publisher and distributor on-premises with a subscriber on a single, pooled, and instance database

Azure SQL DB 用作订阅服务器

在此配置中,Azure SQL 数据库(单一数据库、共用数据库和实例数据库)是订阅服务器。In this configuration, an Azure SQL Database (single, pooled, and instance database) is a subscriber. 此配置支持从本地迁移到 Azure。This configuration supports migration from on-premises to Azure. 如果订阅服务器位于单一数据库或共用数据库上,则它必须处于推送模式。If a subscriber is on a single or pooled database, it must be in push mode.

使用故障转移组With failover groups

如果对故障转移组中的发布服务器分发服务器实例启用了异地复制,则托管实例管理员必须清理旧的主节点上的所有发布内容,然后在故障转移后,在新的主节点上重新配置这些发布内容。If geo-replication is enabled on a publisher or distributor instance in a failover group, the managed instance administrator must clean up all publications on the old primary and reconfigure them on the new primary after a failover occurs. 在此方案中,需要执行以下活动:The following activities are needed in this scenario:

  1. 停止数据库上运行的所有复制作业(如果有)。Stop all replication jobs running on the database, if there are any.

  2. 通过在发布服务器数据库上运行以下脚本,删除发布服务器中的订阅元数据:Drop subscription metadata from publisher by running the following script on publisher database:

    EXEC sp_dropsubscription @publication='<name of publication>', @article='all',@subscriber='<name of subscriber>'
    
  3. 删除订阅服务器中的订阅元数据。Drop subscription metadata from the subscriber. 对订阅服务器实例上的订阅数据库运行以下脚本:Run the following script on the subscription database on subscriber instance:

    EXEC sp_subscription_cleanup
       @publisher = N'<full DNS of publisher, e.g. example.ac2d23028af5.database.chinacloudapi.cn>', 
       @publisher_db = N'<publisher database>', 
       @publication = N'<name of publication>'; 
    
  4. 通过在已发布的数据库中运行以下脚本,强制删除发布服务器中的所有复制对象:Forcefully drop all replication objects from publisher by running the following script in the published database:

    EXEC sp_removedbreplication
    
  5. 强制删除原始主实例中的旧分发服务器(如果故障回复到曾经具有分发服务器的旧主实例)。Forcefully drop old distributor from original primary instance (if failing back over to an old primary that used to have a distributor). 在旧的分发服务器托管实例中的 master 数据库上运行以下脚本:Run the following script on the master database in old distributor managed instance:

    EXEC sp_dropdistributor 1,1
    

如果对故障转移组中的订阅服务器实例启用了异地复制,则应将发布配置为连接到订阅服务器托管实例的故障转移组侦听器终结点。If geo-replication is enabled on a subscriber instance in a failover group, the publication should be configured to connect to the failover group listener endpoint for the subscriber managed instance. 发生故障转移时,托管实例管理员执行的后续操作取决于发生的故障转移类型:In the event of a failover, subsequent action by the managed instance administrator depends on the type of failover that occurred:

  • 如果在不丢失数据的情况下进行故障转移,则故障转移后复制将继续工作。For a failover with no data loss, replication will continue working after failover.
  • 如果在丢失数据的情况下进行故障转移,复制也能正常工作。For a failover with data loss, replication will work as well. 它会再次复制丢失的更改。It will replicate the lost changes again.
  • 如果在丢失数据的情况下进行故障转移,但数据丢失发生在分发数据库保留期以外,则托管实例管理员需要重新初始化订阅数据库。For a failover with data loss, but the data loss is outside of the distribution database retention period, the managed instance administrator will need to reinitialize the subscription database.

后续步骤Next steps

有关配置事务复制的详细信息,请参阅以下教程:For more information about configuring transactional replication, see the following tutorials:

另请参阅See Also