教程:设置 Azure SQL 数据库和 SQL Server 的数据库之间的 SQL 数据同步Tutorial: Set up SQL Data Sync between databases in Azure SQL Database and SQL Server

适用于:是Azure SQL 数据库 APPLIES TO: yesAzure SQL Database

本教程将介绍如何创建包含 Azure SQL 数据库和 SQL Server 实例的同步组,从而设置 SQL 数据同步。In this tutorial, you learn how to set up SQL Data Sync by creating a sync group that contains both Azure SQL Database and SQL Server instances. 同步组进行了自定义配置,并根据设置的计划进行同步。The sync group is custom configured and synchronizes on the schedule you set.

阅读本教程的前提是,至少具有 SQL 数据库和 SQL Server 领域的一些经验。The tutorial assumes you have at least some prior experience with SQL Database and SQL Server.

有关 SQL 数据同步的概述,请参阅使用 SQL 数据同步跨云和本地数据库同步数据For an overview of SQL Data Sync, see Sync data across cloud and on-premises databases with SQL Data Sync.

有关如何配置 SQL 数据同步的 PowerShell 示例,请参阅如何在 SQL 数据库中的数据库之间Azure SQL 数据库和 SQL Server 中的数据库之间进行同步For PowerShell examples on how to configure SQL Data Sync, see How to sync between databases in SQL Database or between databases in Azure SQL Database and SQL Server

重要

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

创建同步组Create sync group

  1. 若要在 SQL 数据库中查找数据库,请转到 Azure 门户Go to the Azure portal to find your database in SQL Database. 搜索并选择“SQL 数据库”。Search for and select SQL databases.

    搜索数据库,Microsoft Azure 门户

  2. 选择要用作数据同步的中心数据库的数据库。Select the database you want to use as the hub database for Data Sync.

    从数据库列表中进行选择,Microsoft Azure 门户

    备注

    中心数据库是同步拓扑的中央终结点,其中同步组具有多个数据库终结点。The hub database is a sync topology's central endpoint, in which a sync group has multiple database endpoints. 同步组中具有终结点的所有其他成员数据库会与中心数据库进行同步。All other member databases with endpoints in the sync group, sync with the hub database.

  3. 在选定数据库的“SQL 数据库”菜单中,选择“同步到其他数据库” 。On the SQL database menu for the selected database, select Sync to other databases.

    同步到其他数据库,Microsoft Azure 门户

  4. 在“同步到其他数据库”页中,选择“新建同步组” 。On the Sync to other databases page, select New Sync Group. “新建同步组”页随即打开,其中突出显示“创建同步组(步骤 1)” 。The New sync group page opens with Create sync group (step 1) highlighted.

    步骤 1 设置

    在“创建数据同步组”页中,请更改以下设置:On the Create Data Sync Group page, change the following settings:

    设置Setting       说明Description
    同步组名称Sync Group Name 输入新同步组的名称。Enter a name for the new sync group. 此名称不同于数据库本身的名称。This name is distinct from the name of the database itself.
    同步元数据数据库Sync Metadata Database 选择创建数据库(推荐)或使用现有数据库。Choose to create a database (recommended) or to use an existing database.

    如果选择“新建数据库”,请选择“创建新数据库” 。If you choose New database, select Create new database. 然后在“SQL 数据库”页中,命名并配置新数据库,再选择“确定” 。Then on the SQL Database page, name and configure the new database and select OK.

    如果选择“使用现有数据库”,请从列表中选择数据库。If you choose Use existing database, select the database from the list.
    自动同步Automatic Sync 选择“开”或“关” 。Select On or Off.

    如果选择“开”,请在“同步频率”部分中输入数字,然后选择“秒”、“分钟”、“小时”或“天” 。If you choose On, enter a number and select Seconds, Minutes, Hours, or Days in the Sync Frequency section.
    从保存配置的时间开始,经过所选的间隔时间后,第一次同步开始。The first sync begins after the selected interval period elapses from the time the configuration is saved.
    冲突解决方法Conflict Resolution 选择“中心胜出”或“成员胜出” 。Select Hub win or Member win.

    “中心胜出”表示发生冲突时,中心数据库中的数据将覆盖成员数据库中的冲突数据。Hub win means when conflicts occur, data in the hub database overwrites conflicting data in the member database.

    “成员胜出”表示发生冲突时,成员数据库中的数据将覆盖中心数据库中的冲突数据。Member win means when conflicts occur, data in the member database overwrites conflicting data in the hub database.

    备注

    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. 此数据库共享为选定区域和订阅中所有同步组的同步元数据数据库。This database is shared as the Sync Metadata Database for all sync groups in a selected region and subscription. 在未删除区域中所有同步组和同步代理的情况下,无法更改数据库或其名称。You can't change the database or its name without removing all sync groups and sync agents in the region.

    选择“确定”,并等待创建和部署同步组。Select OK and wait for the sync group to be created and deployed.

添加同步成员Add sync members

创建并部署新的同步组后,“新建同步组”页中将突出显示“添加同步成员(步骤 2)” 。After the new sync group is created and deployed, Add sync members (step 2) is highlighted on the New sync group page.

在“中心数据库”部分中,输入中心数据库所在的服务器的现有凭据。In the Hub Database section, enter existing credentials for the server on which the hub database is located. 请勿在此部分中输入新凭据。Don't enter new credentials in this section.

步骤 2 设置

在 Azure SQL 数据库中添加数据库To add a database in Azure SQL Database

在“成员数据库”部分中,视需要通过选择“添加 Azure SQL 数据库”,将 Azure SQL 数据库中的数据库添加到同步组中。In the Member Database section, optionally add a database in Azure SQL Database to the sync group by selecting Add an Azure SQL Database. 此时,“配置 Azure SQL 数据库”页随即打开。The Configure Azure SQL Database page opens.

步骤 2 - 配置数据库

在“配置 Azure SQL 数据库”页中,更改以下设置:On the Configure Azure SQL Database page, change the following settings:

设置Setting       说明Description
同步成员名称Sync Member Name 提供新同步成员的名称。Provide a name for the new sync member. 此名称不同于数据库本身的名称。This name is distinct from the database name itself.
订阅Subscription 选择关联的 Azure 订阅,以用于计费。Select the associated Azure subscription for billing purposes.
Azure SQL ServerAzure SQL Server 选择现有服务器。Select the existing server.
Azure SQL 数据库Azure SQL Database 选择 SQL 数据库中的现有数据库。Select the existing database in SQL Database.
同步方向Sync Directions 选择“双向同步”、“向中心同步”或“从中心同步” 。Select Bi-directional Sync, To the Hub, or From the Hub.
“用户名”和“密码” Username and Password 输入成员数据库所在的服务器的现有凭据。Enter the existing credentials for the server on which the member database is located. 请勿在此部分中输入新凭据。Don't enter new credentials in this section.

选择“确定”,并等待新同步成员创建和部署完成。Select OK and wait for the new sync member to be created and deployed.

添加 SQL Server 数据库To add a SQL Server database

在“成员数据库”部分中,请根据需要选择“添加本地数据库”,从而将 SQL Server 数据库添加到同步组。In the Member Database section, optionally add a SQL Server database to the sync group by selecting Add an On-Premises Database. 随即打开“配置本地”页,可以在其中执行以下操作:The Configure On-Premises page opens where you can do the following things:

  1. 选择“选择同步代理网关”。Select Choose the Sync Agent Gateway. “选择同步代理”页随即打开。The Select Sync Agent page opens.

    创建同步代理

  2. 在“选择同步代理”页中,选择是使用现有代理还是创建代理。On the Choose the Sync Agent page, choose whether to use an existing agent or create an agent.

    如果选择“现有代理”,请从列表中选择现有代理。If you choose Existing agents, select the existing agent from the list.

    如果选择“新建代理”,请执行以下操作:If you choose Create a new agent, do the following things:

    1. 通过提供的链接下载 Data Sync Agent,并将其安装在 SQL Server 所在的计算机上。Download the data sync agent from the link provided and install it on the computer where the SQL Server is located. 还可以直接从 Azure SQL Data Sync Agent 下载代理。You can also download the agent directly from Azure SQL Data Sync Agent.

      重要

      必须在防火墙中打开出站 TCP 端口 1433,以便客户端代理能够与服务器进行通信。You have to open outbound TCP port 1433 in the firewall to let the client agent communicate with the server.

    2. 输入代理名称。Enter a name for the agent.

    3. 选择“创建并生成密钥”并将代理密钥复制到剪贴板。Select Create and Generate Key and copy the agent key to the clipboard.

    4. 选择“确定”,关闭“选择同步代理”页。Select OK to close the Select Sync Agent page.

  3. 在 SQL Server 计算机上,找到并运行客户端同步代理应用程序。On the SQL Server computer, locate and run the Client Sync Agent app.

    数据同步客户端代理应用程序

    1. 在同步代理应用程序中,选择“提交代理密钥”。In the sync agent app, select Submit Agent Key. 此时,“同步元数据数据库配置”对话框打开。The Sync Metadata Database Configuration dialog box opens.

    2. 在“同步元数据数据库配置”对话框中,粘贴从 Azure 门户复制的代理密钥。In the Sync Metadata Database Configuration dialog box, paste in the agent key copied from the Azure portal. 还需要输入元数据数据库所在服务器的现有凭据。Also provide the existing credentials for the server on which the metadata database is located. (如果创建了元数据数据库,则此数据库与中心数据库位于同一服务器上。)选择“确定”,并等待配置完成。(If you created a metadata database, this database is on the same server as the hub database.) Select OK and wait for the configuration to finish.

      输入代理密钥和服务器凭据

      备注

      如果看到防火墙错误消息,请在 Azure 上创建防火墙规则,以允许来自 SQL Server 计算机的传入流量。If you get a firewall error, create a firewall rule on Azure to allow incoming traffic from the SQL Server computer. 可以在门户中或在 SQL Server Management Studio (SSMS) 中手动创建规则。You can create the rule manually in the portal or in SQL Server Management Studio (SSMS). 在 SSMS 中,输入其名称 <hub_database_name>.database.chinacloudapi.cn,即可连接到 Azure 上的中心数据库。In SSMS, connect to the hub database on Azure by entering its name as <hub_database_name>.database.chinacloudapi.cn.

    3. 选择“注册”以向代理注册 SQL Server 数据库。Select Register to register a SQL Server database with the agent. 此时,“SQL Server 数据库配置”对话框打开。The SQL Server Configuration dialog box opens.

      添加和配置 SQL Server 数据库

    4. 在“SQL Server 配置”对话框中,选择是使用 SQL Server 身份验证还是使用 Windows 身份验证进行连接。In the SQL Server Configuration dialog box, choose to connect using SQL Server authentication or Windows authentication. 如果选择 SQL Server 身份验证,请输入现有凭据。If you choose SQL Server authentication, enter the existing credentials. 提供 SQL Server 名称和要同步的数据库的名称,然后选择“测试连接”测试设置。Provide the SQL Server name and the name of the database that you want to sync and select Test connection to test your settings. 然后选择“保存”,注册的数据库将显示在列表中。Then select Save and the registered database appears in the list.

      SQL Server 数据库现已注册

    5. 关闭客户端同步代理应用。Close the Client Sync Agent app.

  4. 在门户的“配置本地数据库”页中,选择“选择数据库” 。In the portal, on the Configure On-Premises page, select Select the Database.

  5. 在“选择数据库”页的“同步成员名称”字段中,输入新同步成员的名称。On the Select Database page, in the Sync Member Name field, provide a name for the new sync member. 此名称不同于数据库本身的名称。This name is distinct from the name of the database itself. 从列表中选择数据库。Select the database from the list. 在“同步方向”字段中,选择“双向同步”、“向中心同步”或“从中心同步” 。In the Sync Directions field, select Bi-directional Sync, To the Hub, or From the Hub.

    选择本地数据库

  6. 选择“确定”,关闭“选择数据库”页。Select OK to close the Select Database page. 再选择“确定”,关闭“配置本地数据库”页,并等待新同步成员创建和部署完成。Then select OK to close the Configure On-Premises page and wait for the new sync member to be created and deployed. 最后,选择“确定”,关闭“选择同步成员”页 。Finally, select OK to close the Select sync members page.

备注

要连接到 SQL 数据同步和本地代理,请将自己的用户名添加到角色 DataSync_Executor。To connect to SQL Data Sync and the local agent, add your user name to the role DataSync_Executor. SQL 数据同步在 SQL Server 实例中创建此角色。Data Sync creates this role on the SQL Server instance.

配置同步组Configure sync group

创建并部署新同步组成员后,“新建同步组”页中将突出显示“配置同步组(步骤 3)” 。After the new sync group members are created and deployed, Configure sync group (step 3) is highlighted in the New sync group page.

步骤 3 设置

  1. 在“表”页中,依次选择同步组成员列表中的数据库和“刷新架构” 。On the Tables page, select a database from the list of sync group members and select Refresh schema.

  2. 从列表中,选择要同步的表。默认情况下,所有列都处于选中状态,因此请禁用不想同步的列的复选框。请务必保持主键列的选中状态不变。From the list, select the tables you want to sync. By default, all columns are selected, so disable the checkbox for the columns you don't want to sync. Be sure to leave the primary key column selected.

  3. 选择“保存” 。Select Save.

  4. 默认情况下,在计划或手动运行数据库之前,不会同步数据库。By default, databases are not synced until scheduled or manually run. 若要运行手动同步,请在 Azure 门户中导航到 SQL 数据库中的数据库,选择“同步到其他数据库”,然后选择同步组。To run a manual sync, navigate to your database in SQL Database in the Azure portal, select Sync to other databases, and select the sync group. “数据同步”页随即打开。The Data Sync page opens. 选择“同步”。Select Sync.

    手动同步

常见问题FAQ

数据同步以什么频率同步数据?How frequently can Data Sync synchronize my data?

同步之间的最短持续时间为五分钟。The minimal duration between synchronizations is five minutes.

SQL 数据同步是否能完全创建表?Does SQL Data Sync fully create tables?

如果目标数据库中缺少同步架构表,则 SQL 数据同步会使用所选择的列创建进行创建。If sync schema tables are missing in the destination database, SQL Data Sync creates them with the columns you selected. 但是,由于以下原因,这不会导致完全保真架构:However, this doesn't result in a full-fidelity schema for the following reasons:

  • 仅在目标表中创建所选的列。Only columns you select are created in the destination table. 将忽略未选中的列。Columns not selected are ignored.
  • 仅在目标表中创建选定的列索引。Only selected column indexes are created in the destination table. 对于未选中的列,将忽略这些索引。For columns not selected, those indexes are ignored.
  • 不会创建 XML 类型列的索引。Indexes on XML type columns aren't created.
  • 不会创建 CHECK 约束。CHECK constraints aren't created.
  • 不会创建源表上的触发器。Triggers on the source tables aren't created.
  • 不会创建视图和存储过程。Views and stored procedures aren't created.

考虑到这些限制,我们的建议如下:Because of these limitations, we recommend the following things:

  • 对于生产环境,请自行创建完全保真架构。For production environments, create the full-fidelity schema yourself.
  • 在试验服务时,请使用自动预配功能。When experimenting with the service, use the auto-provisioning feature.

为什么会看到没有创建的表?Why do I see tables I didn't create?

数据同步在数据库中创建其他表用于跟踪更改。Data Sync creates additional tables in the database for change tracking. 请不要删除这些表,否则数据同步会停止运行。Don't delete these or Data Sync stops working.

同步后的数据是否具有收敛性?Is my data convergent after a sync?

不一定。Not necessarily. 取具有一个中心和三个辐射(A、B 和 C)的同步组,其中同步为中心到 A、中心到 B 和中心到 C。如果在中心到 A 同步后对数据库 A 进行了更改,则在下一次同步任务前,该更改不会写入数据库 B 或数据库 C。Take a sync group with a hub and three spokes (A, B, and C) where synchronizations are Hub to A, Hub to B, and Hub to C. If a change is made to database A after the Hub to A sync, that change isn't written to database B or database C until the next sync task.

如何将架构更改应用到同步组?How do I get schema changes into a sync group?

手动进行所有架构更改并对其进行传播。Make and propagate all schema changes manually.

  1. 将架构更改手动复制到中心以及所有同步成员。Replicate the schema changes manually to the hub and to all sync members.
  2. 更新同步架构。Update the sync schema.

添加新表和新列:For adding new tables and columns:

新表和新列在添加到同步架构之前不会影响当前同步,并且数据同步会将其忽略。New tables and columns don't impact the current sync and Data Sync ignores them until they're added to the sync schema. 添加新的数据库对象时,请遵循以下顺序:When adding new database objects, follow the sequence:

  1. 将新表或新列添加到中心,然后添加到所有同步成员。Add new tables or columns to the hub and to all sync members.
  2. 将新表或新列添加到同步架构。Add new tables or columns to the sync schema.
  3. 开始将值插入新表和新列中。Begin inserting values into the new tables and columns.

更改列的数据类型:For changing the data type of a column:

更改现有列的数据类型时,数据同步会继续运行,前提是新值属于在同步架构中定义的原始数据类型。When you change the data type of an existing column, Data Sync continues to work as long as the new values fit the original data type defined in the sync schema. 例如,如果在源数据库中将类型从 int 更改为 bigint,除非插入的值对于 int 数据类型来说过大,否则数据同步会继续运行 。For example, if you change the type in the source database from int to bigint, Data Sync continues to work until you insert a value too large for the int data type. 要完成此更改,请将架构更改手动复制到中心以及所有同步成员,然后更新同步架构。To complete the change, replicate the schema change manually to the hub and to all sync members, then update the sync schema.

如何使用数据同步导出和导入数据库?How can I export and import a database with Data Sync?

在将数据库导出为 .bacpac 文件,并导入要创建数据库的文件后,请执行以下操作以在新数据库中使用数据同步:After you export a database as a .bacpac file and import the file to create a database, do the following to use Data Sync in the new database:

  1. 使用此脚本清理新数据库上的数据同步对象和其他表。Clean up the Data Sync objects and additional tables on the new database by using this script. 该脚本从数据库中删除所有必需的数据同步对象。The script deletes all the required Data Sync objects from the database.
  2. 重新创建包含新数据库的同步组。Recreate the sync group with the new database. 如果不再需要旧同步组,请删除它。If you no longer need the old sync group, delete it.

在哪里可以找到有关客户端代理的信息?Where can I find information on the client agent?

有关客户端代理的常见问题解答,请参阅代理常见问题解答For frequently asked questions about the client agent, see Agent FAQ.

后续步骤Next steps

祝贺。Congratulations. 你已创建了一个包含 SQL 数据库实例和 SQL Server 数据库的同步组。You've created a sync group that includes both a SQL Database instance and a SQL Server database.

有关 SQL 数据同步的详细信息,请参阅:For more info about SQL Data Sync, see:

有关 SQL 数据库的详细信息,请参阅:For more info about SQL Database, see: