复制到 Azure SQL 数据库Replication to Azure SQL Database

适用于: Azure SQL 数据库

可以在单向事务或快照复制拓扑中将 Azure SQL 数据库配置为推送订阅服务器。You can configure an Azure SQL Database as the push subscriber in a one-way transactional or snapshot replication topology.

备注

本文介绍如何在 Azure SQL 数据库中使用事务复制This article describes the use of transactional replication in Azure SQL Database. 它与活动异地复制无关,后者是一项 Azure SQL 数据库功能,可用于创建单个数据库的完全可读副本。It is unrelated to active geo-replication, an Azure SQL Database feature that allows you to create complete readable replicas of individual databases.

支持的配置Supported configurations

版本Versions

若要将内容成功复制到 Azure SQL 数据库中的数据库,SQL Server 发布服务器和分发服务器须至少使用以下版本之一:To successfully replicate to a database in Azure SQL Database, SQL Server publishers and distributors must be using (at least) one of the following versions:

以下版本的 SQL Server 支持从 SQL Server 数据库发布到任何 Azure SQL 数据库:Publishing to any Azure SQL Database from a SQL Server database is supported by the following versions of SQL Server:

备注

尝试使用不受支持的版本来配置复制可能导致错误编号 MSSQL_REPL20084(进程无法连接到订阅服务器)和 MSSQL_REPL40532(无法打开登录名所请求的服务器 <name>。Attempting to configure replication using an unsupported version can result in error number MSSQL_REPL20084 (The process could not connect to Subscriber.) and MSSQL_REPL40532 (Cannot open server <name> requested by the login. 登录失败)。The login failed.).

若要使用 Azure SQL 数据库的所有功能,必须使用最新版的 SQL Server Management StudioSQL Server Data ToolsTo use all the features of Azure SQL Database, you must be using the latest versions of SQL Server Management Studio and SQL Server Data Tools.

复制类型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
     

备注Remarks

  • 仅支持推送订阅 Azure SQL 数据库。Only push subscriptions to Azure SQL Database are supported.
  • 若要配置复制,可以使用 SQL Server Management Studio,也可以在发布服务器上执行 Transact-SQL 语句。Replication can be configured by using SQL Server Management Studio or by executing Transact-SQL statements on the publisher. 不能使用 Azure 门户来配置复制。You cannot configure replication by using the Azure portal.
  • 复制时,只能使用 SQL Server 身份验证登录来连接到 Azure SQL 数据库。Replication can only use SQL Server authentication logins to connect to Azure SQL Database.
  • 复制的表必须有主键。Replicated tables must have a primary key.
  • 必须已经有 Azure 订阅。You must have an existing Azure subscription.
  • Azure SQL 数据库订阅服务器可以位于任何区域。The Azure SQL Database subscriber can be in any region.
  • SQL Server 上的单一发布可以支持 Azure SQL 数据库和 SQL Server(本地的以及 Azure 虚拟机中的 SQL Server)订阅服务器。A single publication on SQL Server can support both Azure SQL Database and SQL Server (on-premises and SQL Server in an Azure virtual machine) subscribers.
  • 必须从 SQL Server 而不是 Azure SQL 数据库中执行复制管理、监视和故障排除。Replication management, monitoring, and troubleshooting must be performed from SQL Server rather than Azure SQL Database.
  • 在适用于 SQL 数据库的 sp_addsubscription 中,仅 @subscriber_type = 0 受支持。Only @subscriber_type = 0 is supported in sp_addsubscription for SQL Database.
  • Azure SQL 数据库不支持双向、即时、可更新或对等复制。Azure SQL Database does not support bi-directional, immediate, updatable, or peer-to-peer replication.

复制体系结构Replication Architecture

此图显示了具有 Azure SQL 数据库的复制体系结构,其中包含位于不同区域的多个订阅服务器群集,以及本地 Azure 虚拟机(其中包含连接到远程群集的发布服务器、日志读取可执行文件和分发服务器可执行文件)。

方案Scenarios

典型复制方案Typical Replication Scenario

  1. 在 SQL Server 数据库上创建事务复制发布。Create a transactional replication publication on a SQL Server database.
  2. 在 SQL Server 上使用新建订阅向导或 Transact-SQL 语句创建一个推送,以便推送到 Azure SQL 数据库的订阅。On SQL Server use the New Subscription Wizard or Transact-SQL statements to create a push to subscription to Azure SQL Database.
  3. 对于 Azure SQL 数据库中的单一数据库和共用数据库,初始数据集是由快照代理创建并由分发代理分发和应用的快照。With single and pooled databases in Azure SQL Database, the initial data set is a snapshot that is created by the Snapshot Agent and distributed and applied by the Distribution Agent. 使用 SQL 数据库托管实例发布服务器,还可以使用数据库备份来植入 Azure SQL 数据库订阅服务器。With a SQL Managed Instance publisher, you can also use a database backup to seed the Azure SQL Database subscriber.

数据迁移方案Data migration scenario

  1. 使用事务复制将数据从 SQL Server 数据库复制到 Azure SQL 数据库。Use transactional replication to replicate data from a SQL Server database to Azure SQL Database.
  2. 重定向客户端或中间层应用程序以更新数据库副本。Redirect the client or middle-tier applications to update the database copy.
  3. 停止更新 SQL Server 版本的表并删除发布。Stop updating the SQL Server version of the table and remove the publication.

限制Limitations

Azure SQL 数据库订阅不支持以下选项:The following options are not supported for Azure SQL Database subscriptions:

  • 复制文件组关联关系Copy file groups association
  • 复制表分区方案Copy table partitioning schemes
  • 复制索引分区方案Copy index partitioning schemes
  • 复制用户定义的统计信息Copy user defined statistics
  • 复制默认绑定Copy default bindings
  • 复制规则绑定Copy rule bindings
  • 复制全文索引Copy fulltext indexes
  • 复制 XML XSDCopy XML XSD
  • 复制 XML 索引Copy XML indexes
  • 复制权限Copy permissions
  • 复制空间索引Copy spatial indexes
  • 复制筛选的索引Copy filtered indexes
  • 复制数据压缩属性Copy data compression attribute
  • 复制稀疏列属性Copy sparse column attribute
  • 将文件流转换为 MAX 数据类型Convert filestream to MAX data types
  • 将 hierarchyid 转换为 MAX 数据类型Convert hierarchyid to MAX data types
  • 将空间转换为 MAX 数据类型Convert spatial to MAX data types
  • 复制扩展的属性Copy extended properties

限制待定Limitations to be determined

  • 复制排序规则Copy collation
  • 在 SP 的序列化事务中执行Execution in a serialized transaction of the SP

示例Examples

创建发布和推送订阅。Create a publication and a push subscription. 有关详细信息,请参阅:For more information, see:

  • 创建发布Create a Publication
  • 创建推送订阅,方法是将服务器名称用作订阅服务器(例如 N'azuresqldbdns.database.chinacloudapi.cn' ),将 Azure SQL 数据库名称用作目标数据库(例如 AdventureWorks)。Create a Push Subscription by using the server name as the subscriber (for example N'azuresqldbdns.database.chinacloudapi.cn') and the Azure SQL Database name as the destination database (for example AdventureWorks).

另请参阅See Also