Azure SQL 托管实例的事务复制(预览)Transactional replication with Azure SQL Managed Instance (Preview)

适用于: Azure SQL 托管实例

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

事务复制目前提供了用于 SQL 托管实例的公共预览版。Transactional replication is currently in public preview for SQL Managed Instance.

概述Overview

还可以使用事务复制将 Azure SQL 托管实例中发生的更改推送到:You can use transactional replication to push changes made in an Azure SQL Managed Instance to:

组件Components

事务复制的关键组件是“发布服务器”、“分发服务器”和“订阅服务器”,如下图所示 :The key components in transactional replication are the Publisher, Distributor, and Subscriber, as shown in the following picture:

SQL 数据库的复制

角色Role Azure SQL 数据库Azure SQL Database Azure SQL 托管实例Azure SQL Managed Instance
发布者Publisher No Yes
分发服务器Distributor No Yes
提取订阅服务器Pull subscriber No Yes
推送订阅服务器Push Subscriber Yes Yes
     

“发布服务器”通过将更新发送到分发服务器,来发布某些表(项目)所发生的更改。The Publisher publishes changes made on some tables (articles) by sending the updates to the Distributor. 发布服务器可以是 Azure SQL 托管实例或 SQL Server 实例。The publisher can be an Azure SQL Managed Instance or a SQL Server instance.

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

“订阅服务器”接收在发布服务器上发生的更改。The Subscriber receives changes made on the Publisher. SQL Server 实例和 Azure SQL 托管实例都可以是推送和请求订阅服务器,但当分发服务器是 Azure SQL 托管实例而订阅服务器不是时,则请求订阅不受支持。A SQL Server instance and Azure SQL Managed Instance can both be push and pull subscribers, though a pull subscription is not supported when the distributor is an Azure SQL Managed Instance and the subscriber is not. Azure SQL 数据库中的数据库只能是推送订阅服务器。A database in Azure SQL Database can only be a push subscriber.

Azure SQL 托管实例可以支持成为以下版本的 SQL Server 的订阅服务器:Azure SQL Managed Instance can support being a Subscriber from the following versions of SQL Server:

  • SQL Server 2016 和更高版本SQL Server 2016 and later

  • SQL Server 2014 RTM CU10 (12.0.4427.24)SP1 CU3 (12.0.2556.4)SQL Server 2014 RTM CU10 (12.0.4427.24) or SP1 CU3 (12.0.2556.4)

  • SQL Server 2012 SP2 CU8 (11.0.5634.1)SP3 (11.0.6020.0)SQL Server 2012 SP2 CU8 (11.0.5634.1) or SP3 (11.0.6020.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.
    • 尝试使用旧版本来配置复制可能导致错误 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.)

复制类型Types of replication

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

复制Replication Azure SQL 数据库Azure SQL Database Azure SQL 托管实例Azure SQL Managed Instance
标准事务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
     

可支持性矩阵Supportability Matrix

Azure SQL 托管实例的事务复制可支持性矩阵与 SQL Server 的相同。The transactional replication supportability matrix for Azure SQL Managed Instance is the same as the one for SQL Server.

发布者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
     

何时使用When to use

在以下情况下,事务复制非常有用: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 databases in a SQL Server instance or Azure SQL Database that subscribed for the changes.
  • 将多个分布式数据库保持同步状态。Keep several distributed databases in synchronized state.
  • 通过持续发布更改,将数据库从一个 SQL Server 实例或 Azure SQL 托管实例迁移到另一个数据库。Migrate databases from one SQL Server instance or Azure SQL Managed Instance to another database by continuously publishing the changes.

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

类别Category 数据同步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
- 维护成本高- 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:

带本地分发服务器的发布服务器位于 SQL 托管实例上Publisher with local Distributor on SQL Managed Instance

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

发布服务器和分发服务器在单个 SQL 托管实例中配置,并将更改分发到其他 SQL 托管实例、SQL 数据库或 SQL Server 实例。Publisher and distributor are configured within a single SQL Managed Instance and distributing changes to another SQL Managed Instance, SQL Database, or SQL Server instance.

带远程分发服务器的发布服务器位于 SQL 托管实例上Publisher with remote distributor on SQL Managed Instance

在此配置中,由一个托管实例将更改发布到能够为许多源 SQL 托管实例提供服务的另一个 SQL 托管实例上的分发服务器,并将更改分发到 Azure SQL 数据库、Azure SQL 托管实例或 SQL Server 上的一个或多个目标。In this configuration, one managed instance publishes changes to a distributor placed on another SQL Managed Instance that can serve many source SQL Managed Instances and distribute changes to one or many targets on Azure SQL Database, Azure SQL Managed Instance, 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.

本地发布服务器/分发服务器与远程订阅服务器On-premises Publisher/Distributor with remote subscriber

Azure SQL 数据库作为订阅服务器

在此配置中,Azure SQL 数据库或 Azure SQL 托管实例中的数据库是订阅服务器。In this configuration, a database in Azure SQL Database or Azure SQL Managed Instance is a subscriber. 此配置支持从本地迁移到 Azure。This configuration supports migration from on-premises to Azure. 如果订阅服务器是 Azure SQL 数据库中的数据库,则它必须处于推送模式。If a subscriber is a database in Azure SQL Database, it must be in push mode.

要求Requirements

  • 使用 SQL 身份验证实现复制参与者之间的连接。Use SQL Authentication for connectivity between replication participants.
  • 对复制功能使用的工作目录使用 Azure 存储帐户共享。Use an Azure Storage Account share for the working directory used by replication.
  • 在子网安全规则中打开 TCP 出站端口 445 以访问 Azure 文件共享。Open TCP outbound port 445 in the subnet security rules to access the Azure file share.
  • 当 SQL 托管实例是发布服务器/分发服务器,而订阅服务器不是时,请打开 TCP 出站端口 1433。Open TCP outbound port 1433 when the SQL Managed Instance is the Publisher/Distributor, and the Subscriber is not. 对于端口 1433 目标服务标记,你可能还需要将 allow_linkedserver_outbound 的 SQL 托管实例 NSG 出站安全规则从 virtualnetwork 更改为 internetYou may also need to change the SQL Managed Instance NSG outbound security rule for allow_linkedserver_outbound for the port 1433 Destination Service tag from virtualnetwork to internet.
  • 将发布服务器和分发服务器都置于云中,或都置于本地。Place both the publisher and distributor in the cloud, or both on-premises.
  • 如果虚拟网络不同,请配置复制参与者的虚拟网络之间的 VPN 对等互连。Configure VPN peering between the virtual networks of replication participants if the virtual networks are different.

备注

当分发服务器为 Azure SQL 托管实例数据库且订阅服务器位于本地时,如果阻止出站网络安全组 (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 Azure SQL Managed Instance database and the subscriber is on-premises. 更新 vNet NSG 以解决此问题。Update the vNet NSG to resolve this issue.

使用故障转移组With failover groups

使用事务复制的 SQL 托管实例不支持活动异地复制Active geo-replication is not supported with a SQL Managed Instance using transactional replication. 使用自动故障转移组而不是活动异地复制,但请注意,必须从主托管实例中手动删除发布,并在故障转移后在辅助 SQL 托管实例上重新创建。Instead of active geo-replication, use Auto-failover groups, but note that the publication has to be manually deleted from the primary managed instance and re-created on the secondary SQL Managed Instance after failover.

如果对故障转移组中的“发布服务器”或“分发服务器”SQL 托管实例启用了异地复制,则 SQL 托管实例管理员必须清理旧的主节点上的所有发布内容,然后在故障转移后,在新的主节点上重新配置这些发布内容 。If geo-replication is enabled on a publisher or distributor SQL Managed Instance in a failover group, the SQL 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. 对订阅服务器 SQL 托管实例上的订阅数据库运行以下脚本:Run the following script on the subscription database on subscriber SQL Managed 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. 强制删除原始主 SQL 托管实例中的旧分发服务器(如果故障回复到曾经具有分发服务器的旧主实例)。Forcefully drop old distributor from original primary SQL Managed Instance (if failing back over to an old primary that used to have a distributor). 在旧的分发服务器 SQL 托管实例中的 master 数据库上运行以下脚本:Run the following script on the master database in old distributor SQL 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.
  • 如果在丢失数据的情况下进行故障转移,但数据丢失发生在分发数据库保留期以外,则 SQL 托管实例管理员需要重新初始化订阅数据库。For a failover with data loss, but the data loss is outside of the distribution database retention period, the SQL 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