什么是 Azure SQL 数据同步?What is SQL Data Sync for Azure?

使用 SQL 数据同步这项基于 Azure SQL 数据库的服务,可以跨多个本地和云端数据库双向同步选定数据。SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple databases, both on-premises and in the cloud.

重要

目前,Azure SQL 数据同步不支持 Azure SQL 托管实例。Azure SQL Data Sync does not support Azure SQL Managed Instance at this time.

概述Overview

数据同步以同步组的概念为依据。Data Sync is based around the concept of a sync group. 同步组是一组要同步的数据库。A sync group is a group of databases that you want to synchronize.

SQL 数据同步使用中心辐射型拓扑来同步数据。Data Sync uses a hub and spoke topology to synchronize data. 将同步组中的一个数据库定义为中心数据库。You define one of the databases in the sync group as the hub database. 其余数据库均为成员数据库。The rest of the databases are member databases. 仅在中心和各成员之间同步数据。Sync occurs only between the hub and individual members.

  • 中心数据库必须是 Azure SQL 数据库。The Hub Database must be an Azure SQL Database.
  • 成员数据库可以是 Azure SQL 数据库或 SQL Server 实例中的数据库。The member databases can be either databases in Azure SQL Database or in instances of SQL Server.
  • 同步元数据数据库包含用于数据同步的元数据和日志。同步元数据数据库必须是与中心数据库位于同一区域的 Azure SQL 数据库。The Sync Metadata Database contains the metadata and log for Data Sync. The Sync Metadata Database has to be an Azure SQL Database located in the same region as the Hub Database. 同步元数据数据库的创建者和所有者均为客户。The Sync Metadata Database is customer created and customer owned. 每个区域和订阅只能有一个同步元数据数据库。You can only have one Sync Metadata Database per region and subscription. 存在同步组或同步代理时,无法删除或重命名同步元数据数据库。Sync Metadata Database cannot be deleted or renamed while sync groups or sync agents exist. Azure 建议新建一个空数据库,以用作同步元数据数据库。Azure recommends to create a new, empty database for use as the Sync Metadata Database. SQL 数据同步在此数据库中创建表,并经常运行工作负载。Data Sync creates tables in this database and runs a frequent workload.

备注

如果使用本地数据库作为成员数据库,则必须安装并配置本地同步代理If you're using an on premises database as a member database, you have to install and configure a local sync agent.

在数据库之间同步数据

同步组具有以下属性:A sync group has the following properties:

  • “同步架构”描述了在同步的数据。The Sync Schema describes which data is being synchronized.
  • “同步方向”可以是双向同步,也可以仅为单向同步。The Sync Direction can be bi-directional or can flow in only one direction. 也就是说,“同步方向”可以是“从中心同步到成员”和/或“从成员同步到中心”。That is, the Sync Direction can be Hub to Member, or Member to Hub, or both.
  • “同步间隔”描述多久执行一次同步。The Sync Interval describes how often synchronization occurs.
  • “冲突解决策略”是组级别策略,可以是“中心胜出”,也可以是“成员胜出”。The Conflict Resolution Policy is a group level policy, which can be Hub wins or Member wins.

何时使用When to use

如果需要跨 Azure SQL 数据库或 SQL Server 中的多个数据库使数据保持最新,数据同步非常有用。Data Sync is useful in cases where data needs to be kept updated across several databases in Azure SQL Database or SQL Server. 下面是 SQL 数据同步的主要用例:Here are the main use cases for Data Sync:

  • 混合数据同步: 借助数据同步,可以在 SQL Server 和 Azure SQL 数据库中的数据库之间保持数据同步,以便启用混合应用程序。Hybrid Data Synchronization: With Data Sync, you can keep data synchronized between your databases in SQL Server and Azure SQL Database to enable hybrid applications. 此功能可能会吸引在考虑迁移到云中,并希望启用 Azure 应用程序的客户。This capability may appeal to customers who are considering moving to the cloud and would like to put some of their application in Azure.
  • 分布式应用程序: 在许多情况下,跨各个数据库分散不同的工作负载会大有裨益。Distributed Applications: In many cases, it's beneficial to separate different workloads across different databases. 例如,如果有大型生产数据库,但还需要对此数据运行报表或分析工作负载,那么使用第二个数据库来处理此额外工作负载将会有所帮助。For example, if you have a large production database, but you also need to run a reporting or analytics workload on this data, it's helpful to have a second database for this additional workload. 这种方法可最大限度地减轻对生产工作负载造成的性能影响。This approach minimizes the performance impact on your production workload. 可以使用 SQL 数据同步来同步这两个数据库。You can use Data Sync to keep these two databases synchronized.
  • 全局分布的应用程序: 许多企业的业务分布在多个区域。Globally Distributed Applications: Many businesses span several regions. 为了最大限度地缩短网络延迟时间,最好将数据存储在靠近的区域中。To minimize network latency, it's best to have your data in a region close to you. 借助数据同步,可轻松让所有区域中的数据库保持同步。With Data Sync, you can easily keep databases in all regions synchronized.

数据同步不是以下场景的首选解决方案:Data Sync isn't the preferred solution for the following scenarios:

方案Scenario 一些建议的解决方案Some recommended solutions
灾难恢复Disaster Recovery Azure 异地冗余备份Azure geo-redundant backups
读取缩放Read Scale 使用只读副本对只读的查询工作负荷进行负载均衡(预览版)Use read-only replicas to load balance read-only query workloads (preview)
ETL(OLTP 到 OLAP)ETL (OLTP to OLAP) Azure 数据工厂SQL Server Integration ServicesAzure Data Factory or SQL Server Integration Services
从 SQL Server 迁移到 Azure SQL 数据库Migration from SQL Server to Azure SQL Database Azure 数据库迁移服务Azure Database Migration Service

工作原理How it works

  • 跟踪数据更改: SQL 数据同步使用插入、更新和删除触发器来跟踪更改。Tracking data changes: Data Sync tracks changes using insert, update, and delete triggers. 更改记录在用户数据库中的端表内。The changes are recorded in a side table in the user database. 请注意,默认情况下 BULK INSERT 不会激发触发器。Note that BULK INSERT doesn't fire triggers by default. 如果未指定 FIRE_TRIGGERS,则不执行任何插入触发器操作。If FIRE_TRIGGERS isn't specified, no insert triggers execute. 添加 FIRE_TRIGGERS 选项,以便数据同步可以跟踪这些插入。Add the FIRE_TRIGGERS option so Data Sync can track those inserts.
  • 同步数据: 根据设计,数据同步采用中心辐射型模型。Synchronizing data: Data Sync is designed in a hub and spoke model. 中心与各个成员同步数据。The hub syncs with each member individually. 中心内的更改会先下载到成员,然后成员内的更改会上传到中心。Changes from the hub are downloaded to the member and then changes from the member are uploaded to the hub.
  • 解决冲突: SQL 数据同步提供两个冲突解决选项,即“中心胜出”或“成员胜出” 。Resolving conflicts: Data Sync provides two options for conflict resolution, Hub wins or Member wins.
    • 如果选择“中心胜出”,中心内的更改始终覆盖成员内的更改。If you select Hub wins, the changes in the hub always overwrite changes in the member.
    • 如果选择“成员胜出”,成员内的更改覆盖中心内的更改。If you select Member wins, the changes in the member overwrite changes in the hub. 如果有多个成员,最终值取决于哪个成员最先同步。If there's more than one member, the final value depends on which member syncs first.

与事务复制比较Compare 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
- Azure SQL 托管实例支持-Azure SQL Managed Instance support
缺点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

入门Get started

在 Azure 门户中设置数据同步Set up Data Sync in the Azure portal

使用 PowerShell 设置数据同步Set up Data Sync with PowerShell

查看数据同步最佳做法Review the best practices for Data Sync

出现了错误Did something go wrong

一致性和性能Consistency and performance

最终一致性Eventual consistency

由于 SQL 数据同步是基于触发器的服务,因此无法保证事务一致性。Since Data Sync is trigger-based, transactional consistency isn't guaranteed. Azure 保证最终执行所有更改,且数据同步不会导致数据丢失。Azure guarantees that all changes are made eventually and that Data Sync doesn't cause data loss.

性能影响Performance impact

SQL 数据同步使用插入、更新和删除触发器来跟踪更改。Data Sync uses insert, update, and delete triggers to track changes. 它在用户数据库中创建用于跟踪的端表。It creates side tables in the user database for change tracking. 这些更改跟踪活动会对数据库工作负荷产生影响。These change tracking activities have an impact on your database workload. 评估服务层级并根据需要升级。Assess your service tier and upgrade if needed.

在同步组创建、更新和删除期间预配和取消预配也可能会影响数据库性能。Provisioning and deprovisioning during sync group creation, update, and deletion may also impact the database performance.

要求和限制Requirements and limitations

一般要求General requirements

  • 每个表都必须有主键。Each table must have a primary key. 请勿更改任何一行中的主键值。Don't change the value of the primary key in any row. 如果必须更改主键值,请先删除行,再使用新的主键值重新创建此行。If you have to change a primary key value, delete the row and recreate it with the new primary key value.

重要

更改现有主键的值会导致以下错误行为:Changing the value of an existing primary key will result in the following faulty behavior:

  • 即使同步未报告任何问题,中心和成员之间的数据也会丢失。Data between hub and member can be lost even though sync does not report any issue.
  • 由于主键更改,跟踪表的源行不存在,同步可能会失败。Sync can fail because the tracking table has a non-existing row from source due to the primary key change.

一般限制General limitations

  • 表不能包含非主键标识列。A table can't have an identity column that isn't the primary key.
  • 表必须具有聚集索引,才能使用数据同步。A table must have a clustered index to use data sync.
  • 主键不能具有以下数据类型:sql_variant、binary、varbinary、image、xml。A primary key can't have the following data types: sql_variant, binary, varbinary, image, xml.
  • 使用以下数据类型作为主键时请小心谨慎,因为支持的精度仅到秒:time、datetime、datetime2、datetimeoffset。Be cautious when you use the following data types as a primary key, because the supported precision is only to the second: time, datetime, datetime2, datetimeoffset.
  • 对象(数据库、表和列)的名称不能包含可打印字符句点 (.)、左方括号 ([) 或右方括号 (])。The names of objects (databases, tables, and columns) can't contain the printable characters period (.), left square bracket ([), or right square bracket (]).
  • 不支持 Azure Active Directory 身份验证。Azure Active Directory authentication isn't supported.
  • 不支持具有相同名称但架构不同(例如,dbo.customers 和 sales.customers)的表。Tables with same name but different schema (for example, dbo.customers and sales.customers) aren't supported.
  • 不支持具有用户定义数据类型的列Columns with User-Defined Data Types aren't supported

不支持的数据类型Unsupported data types

  • FileStreamFileStream
  • SQL/CLR UDTSQL/CLR UDT
  • XMLSchemaCollection(支持 XML)XMLSchemaCollection (XML supported)
  • Cursor、RowVersion、Timestamp、HierarchyidCursor, RowVersion, Timestamp, Hierarchyid

不支持的列类型Unsupported column types

数据同步无法同步只读列或系统生成的列。Data Sync can't sync read-only or system-generated columns. 例如:For example:

  • 计算列。Computed columns.
  • 系统生成的时态表列。System-generated columns for temporal tables.

服务和数据库维度方面的限制Limitations on service and database dimensions

DimensionsDimensions 限制Limit 解决方法Workaround
任何数据库可属于的同步组的数量上限。Maximum number of sync groups any database can belong to. 55
一个同步组中包含的终结点的数量上限Maximum number of endpoints in a single sync group 3030
一个同步组中包含的本地终结点的数量上限。Maximum number of on-premises endpoints in a single sync group. 55 创建多个同步组Create multiple sync groups
数据库、表、架构和列名称Database, table, schema, and column names 每个名称 50 个字符50 characters per name
同步组中的表Tables in a sync group 500500 创建多个同步组Create multiple sync groups
同步组中的表列Columns in a table in a sync group 10001000
表中的数据行大小Data row size on a table 24MB24 Mb
最小同步间隔Minimum sync interval 5 分钟5 Minutes

备注

如果只有一个同步组,则单个同步组中最多可能有 30 个终结点。There may be up to 30 endpoints in a single sync group if there is only one sync group. 如果有多个同步组,则所有同步组中的终结点总数不能超过 30。If there is more than one sync group, the total number of endpoints across all sync groups cannot exceed 30. 如果数据库属于多个同步组,则该数据库计算为多个终结点,而不是一个。If a database belongs to multiple sync groups, it is counted as multiple endpoints, not one.

SQL 数据同步常见问题解答FAQ about SQL Data Sync

SQL 数据同步服务的价格是多少How much does the SQL Data Sync service cost

对 SQL 数据同步服务本身不收取任何费用。There's no charge for the SQL Data Sync service itself. 但是,对于传入和传出 SQL 数据库实例的数据移动,你仍然收取数据传输费用。However, you still collect data transfer charges for data movement in and out of your SQL Database instance. 有关价格,请参阅 SQL 数据库定价For more info, see SQL Database pricing.

哪些区域支持数据同步What regions support Data Sync

SQL 数据同步在所有区域中都可用。SQL Data Sync is available in all regions.

是否需要 SQL 数据库帐户Is a SQL Database account required

是的。Yes. 必须拥有 SQL 数据库帐户才能托管中心数据库。You must have a SQL Database account to host the hub database.

是否可以使用数据同步仅在 SQL Server 数据库之间进行同步Can I use Data Sync to sync between SQL Server databases only

无法直接配合使用。Not directly. 但是,可以间接地在 SQL Server 数据库之间同步,方法是在 Azure 中创建一个中心数据库,然后将本地数据库添加到同步组。You can sync between SQL Server databases indirectly, however, by creating a Hub database in Azure, and then adding the on-premises databases to the sync group.

是否可以使用数据同步在 SQL 数据库中属于不同订阅的数据库之间进行同步Can I use Data Sync to sync between databases in SQL Database that belong to different subscriptions

是的。Yes. 可以在由不同订阅拥有的资源组中的数据库之间同步。You can sync between databases that belong to resource groups owned by different subscriptions.

  • 如果订阅属于同一租户,并且你对所有订阅都有权限,则可以在 Azure 门户中配置同步组。If the subscriptions belong to the same tenant, and you have permission to all subscriptions, you can configure the sync group in the Azure portal.
  • 否则,必须使用 PowerShell 来添加属于不同订阅的同步成员。Otherwise, you have to use PowerShell to add the sync members that belong to different subscriptions.

是否可以使用数据同步在 SQL 数据库中属于不同云(例如 Azure 公有云和 Azure 中国世纪互联)的数据库之间进行同步Can I use Data Sync to sync between databases in SQL Database that belong to different clouds (like Azure Public Cloud and Azure China 21Vianet)

是的。Yes. 可以在属于不同云的数据库之间进行同步。You can sync between databases that belong to different clouds. 必须使用 PowerShell 来添加属于不同订阅的同步成员。You have to use PowerShell to add the sync members that belong to the different subscriptions.

是否可以使用数据同步将生产数据库中的数据种子植入到空数据库,然后将这两个数据库同步Can I use Data Sync to seed data from my production database to an empty database, and then sync them

是的。Yes. 通过从原始数据库编写脚本,在新数据库中手动创建架构。Create the schema manually in the new database by scripting it from the original. 创建架构后,将表添加到同步组以复制数据并使其同步。After you create the schema, add the tables to a sync group to copy the data and keep it synced.

我应该使用 SQL 数据同步备份和还原数据库吗Should I use SQL Data Sync to back up and restore my databases

建议不要使用 SQL 数据同步创建数据备份。It isn't recommended to use SQL Data Sync to create a backup of your data. 由于 SQL 数据同步的同步不进行版本控制,因此无法备份并还原到特定时间点。You can't back up and restore to a specific point in time because SQL Data Sync synchronizations aren't versioned. 此外,SQL 数据同步不会备份其他 SQL 对象(如存储过程),并且不会快速执行还原操作的等效操作。Furthermore, SQL Data Sync doesn't back up other SQL objects, such as stored procedures, and doesn't do the equivalent of a restore operation quickly.

有关推荐的备份技术,请参阅在 Azure SQL 数据库中复制数据库For one recommended backup technique, see Copy a database in Azure SQL Database.

数据同步是否可以同步加密的表或列Can Data Sync sync encrypted tables and columns

  • 如果数据库使用了 Always Encrypted,则只能同步未加密的表和列。If a database uses Always Encrypted, you can sync only the tables and columns that are not encrypted. 无法同步加密的列,因为数据同步无法对数据进行解密。You can't sync the encrypted columns, because Data Sync can't decrypt the data.
  • 如果某个列使用了列级加密 (CLE),则可以对该列进行同步,只要行大小小于最大大小 24 MB。If a column uses Column-Level Encryption (CLE), you can sync the column, as long as the row size is less than the maximum size of 24 Mb. 数据同步将密钥 (CLE) 加密的列视为普通的二进制数据。Data Sync treats the column encrypted by key (CLE) as normal binary data. 若要解密其他同步成员上的数据,则需要具有相同的证书。To decrypt the data on other sync members, you need to have the same certificate.

SQL 数据同步是否支持排序规则Is collation supported in SQL Data Sync

是的。Yes. SQL 数据同步在以下情况下支持排序规则:SQL Data Sync supports collation in the following scenarios:

  • 如果所选同步架构表不在中心或成员数据库中,则部署同步组时,该服务会使用空目标数据库中选择的排序规则设置自动创建相应的表和列。If the selected sync schema tables aren't already in your hub or member databases, then when you deploy the sync group, the service automatically creates the corresponding tables and columns with the collation settings selected in the empty destination databases.
  • 如果要同步的表已经存在于中心和成员数据库中,则 SQL 数据同步要求中心数据库和成员数据库之间的主键列具有相同的排序规则,以成功部署同步组。If the tables to be synced already exist in both your hub and member databases, SQL Data Sync requires that the primary key columns have the same collation between hub and member databases to successfully deploy the sync group. 主键列以外的列没有排序规则限制。There are no collation restrictions on columns other than the primary key columns.

SQL 数据同步是否支持联合Is federation supported in SQL Data Sync

联合根数据库可用于 SQL 数据同步服务,没有任何限制。Federation Root Database can be used in the SQL Data Sync Service without any limitation. 不能将联合数据库终结点添加到当前版本的 SQL 数据同步。You can't add the Federated Database endpoint to the current version of SQL Data Sync.

是否可以使用数据同步来同步通过自带数据库 (BYOD) 功能从 Dynamics 365 导出的数据?Can I use Data Sync to sync data exported from Dynamics 365 using bring your own database (BYOD) feature?

利用 Dynamics 365 自带数据库功能,管理员可以将数据实体从应用程序导出到其自己的 Azure SQL 数据库中。The Dynamics 365 bring your own database feature lets administrators export data entities from the application into their own Azure SQL database. 如果使用“增量推送”(不支持完全推送)导出数据,并且“在目标数据库中启用触发器”设置为“是”,则可以使用数据同步来将这些数据同步到其他数据库中 。Data Sync can be used to sync this data into other databases if data is exported using incremental push (full push is not supported) and enable triggers in target database is set to yes.

后续步骤Next steps

更新同步数据库的架构Update the schema of a synced database

是否必须更新同步组中数据库的架构?Do you have to update the schema of a database in a sync group? 不会自动复制架构更改。Schema changes aren't automatically replicated. 有关某些解决方案,请参阅以下文章:For some solutions, see the following articles:

监视和故障排除Monitor and troubleshoot

SQL 数据同步是否按预期执行?Is SQL Data Sync doing as expected? 若要监视活动和排查问题,请参阅以下文章:To monitor activity and troubleshoot issues, see the following articles:

了解有关 Azure SQL 数据库的详细信息Learn more about Azure SQL Database

有关 Azure SQL 数据库的详细信息,请参阅以下文章:For more info about Azure SQL Database, see the following articles: