排查 SQL 数据同步的问题Troubleshoot issues with SQL Data Sync

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

本文介绍如何排查 Azure 中 SQL 数据同步的已知问题。This article describes how to troubleshoot known issues with SQL Data Sync in Azure. 如果某个问题有了解决方法,会在本文中提供。If there is a resolution for an issue, it's provided here.

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

重要

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

同步问题Sync issues

门户 UI 中针对与客户端代理关联的本地数据库执行同步失败Sync fails in the portal UI for on-premises databases that are associated with the client agent

SQL 数据同步门户 UI 中针对与客户端代理关联的本地数据库执行同步失败。Sync fails in the SQL Data Sync portal UI for on-premises databases that are associated with the client agent. 在运行代理的本地计算机上,事件日志中出现 System.IO.IOException 错误。On the local computer that's running the agent, you see System.IO.IOException errors in the Event Log. 这些错误指出磁盘空间不足。The errors say that the disk has insufficient space.

  • 原因Cause. 驱动器空间不足。The drive has insufficient space.

  • 解决方法Resolution. 在 %TEMP% 目录所在的驱动器上腾出更多的空间。Create more space on the drive on which the %TEMP% directory is located.

我的同步组停留在正在处理状态My sync group is stuck in the processing state

SQL 数据同步中的同步组长时间处于“正在处理”状态。A sync group in SQL Data Sync has been in the processing state for a long time. 该同步组不响应 stop 命令,并且日志中未显示新条目。It doesn't respond to the stop command, and the logs show no new entries.

以下任何条件均可导致同步组停滞在“正在处理”状态:Any of the following conditions might result in a sync group being stuck in the processing state:

  • 原因Cause. 客户端代理处于脱机状态The client agent is offline

  • 解决方法Resolution. 请确保客户端代理处于联机状态,然后重试。Be sure that the client agent is online and then try again.

  • 原因Cause. 客户端代理已卸载或缺失。The client agent is uninstalled or missing.

  • 解决方法Resolution. 客户端代理是否已卸载或缺失:If the client agent is uninstalled or otherwise missing:

    1. 将代理 XML 文件(如果存在)从 SQL 数据同步安装文件夹中删除。Remove the agent XML file from the SQL Data Sync installation folder, if the file exists.
    2. 在某台本地计算机(可以是相同或不同的计算机)上安装代理。Install the agent on an on-premises computer (it can be the same or a different computer). 然后,提交门户中针对显示为脱机的代理生成的代理密钥。Then, submit the agent key that's generated in the portal for the agent that's showing as offline.
  • 原因Cause. SQL 数据同步服务已停止。The SQL Data Sync service is stopped.

  • 解决方法Resolution. 重启 SQL 数据同步服务。Restart the SQL Data Sync service.

    1. 在“开始”菜单中,搜索“服务”。 In the Start menu, search for Services.
    2. 在搜索结果中,选择“服务”。In the search results, select Services.
    3. 找到“SQL 数据同步”服务。Find the SQL Data Sync service.
    4. 如果服务状态为“已停止”,请右键单击服务名称,选择“启动”。If the service status is Stopped, right-click the service name, and then select Start.

备注

如果上述信息无法使同步组摆脱“正在处理”状态,可让 Azure 支持部门重置该同步组的状态。If the preceding information doesn't move your sync group out of the processing state, Azure Support can reset the status of your sync group. 若要重置同步组状态,请在 Azure SQL 数据库论坛中发帖。To have your sync group status reset, in the Azure SQL Database forum, create a post. 在贴子中,请包含自己的订阅 ID,以及需要重置的组的同步组 ID。In the post, include your subscription ID and the sync group ID for the group that needs to be reset. Azure 支持工程师将会回复帖子,并在重置状态后予以通知。An Azure Support engineer will respond to your post, and will let you know when the status has been reset.

在表中发现错误数据I see erroneous data in my tables

如果在同步操作中包含来自不同数据库架构的同名表,则同步后,表中会出现错误数据。If tables that have the same name but which are from different database schemas are included in a sync, you see erroneous data in the tables after the sync.

  • 原因Cause. SQL 数据同步预配过程针对不同架构中的同名表使用相同的跟踪表。The SQL Data Sync provisioning process uses the same tracking tables for tables that have the same name but which are in different schemas. 因此,这两个表中的更改会反映在同一个跟踪表中。Because of this, changes from both tables are reflected in the same tracking table. 这会导致同步期间发生错误的数据更改。This causes erroneous data changes during sync.

  • 解决方法Resolution. 确保同步中涉及的表名称不同,即使这些表属于数据库中不同的架构。Ensure that the names of tables that are involved in a sync are different, even if the tables belong to different schemas in a database.

同步成功后发现主键数据不一致I see inconsistent primary key data after a successful sync

系统报告同步成功,且日志中未显示失败或跳过的行,但是,发现同步组中数据库间的主键数据不一致。A sync is reported as successful, and the log shows no failed or skipped rows, but you observe that primary key data is inconsistent among the databases in the sync group.

  • 原因Cause. 此结果是设计使然。This result is by design. 任何主键列中的更改都会导致被更改主键所在行中的数据不一致。Changes in any primary key column result in inconsistent data in the rows where the primary key was changed.

  • 解决方法Resolution. 若要避免此问题,请确保主键列中的数据没有进行任何更改。To prevent this issue, ensure that no data in a primary key column is changed. 若要在发生此问题后予以解决,请从同步组中的所有终结点内删除包含不一致数据的行。To fix this issue after it has occurred, delete the row that has inconsistent data from all endpoints in the sync group. 然后重新插入该行。Then, reinsert the row.

发现性能显著降低I see a significant degradation in performance

性能显著降低,甚至达到了无法打开数据同步 UI 的程度。Your performance degrades significantly, possibly to the point where you can't even open the Data Sync UI.

  • 原因Cause. 最可能的原因是同步循环。The most likely cause is a sync loop. 当同步组 A 的同步触发了同步组 B 的同步,进而又触发了同步组 A 的同步时,就会出现同步循环。实际情况可能更为复杂,其中可能涉及循环中两个以上的同步组。A sync loop occurs when a sync by sync group A triggers a sync by sync group B, which then triggers a sync by sync group A. The actual situation might be more complex, and it might involve more than two sync groups in the loop. 问题在于,存在因同步组彼此重叠而导致的同步循环触发。The issue is that there is a circular triggering of syncing that's caused by sync groups overlapping one another.

  • 解决方法Resolution. 最佳解决方案就是预防。The best fix is prevention. 确保同步组中没有循环引用。Ensure that you don't have circular references in your sync groups. 由一个同步组同步的任何行都不能由其他同步组同步。Any row that is synced by one sync group can't be synced by another sync group.

我看到以下消息:“无法在列 <column> 中插入 NULL 值。I see this message: "Cannot insert the value NULL into the column <column>. 此列不允许 null 值。”Column does not allow nulls." 这是什么意思,如何解决该错误?What does this mean, and how can I fix it?

此错误消息表示发生了两个以下问题之一:This error message indicates that one of the two following issues has occurred:

  • 某个表没有主键。A table doesn't have a primary key. 若要解决此问题,请将主键添加到要同步的所有表。To fix this issue, add a primary key to all the tables that you're syncing.
  • CREATE INDEX 语句中可能存在 WHERE 子句。There's a WHERE clause in your CREATE INDEX statement. 数据同步不会处理这种情况。Data Sync doesn't handle this condition. 若要解决此问题,请删除 WHERE 子句,或手动对所有数据库进行更改。To fix this issue, remove the WHERE clause or manually make the changes to all databases.

数据同步如何处理循环引用?How does Data Sync handle circular references? 也就是说,如果在多个同步组中同步相同的数据,这些数据是否不断更改?That is, when the same data is synced in multiple sync groups, and keeps changing as a result?

数据同步不会处理循环引用。Data Sync doesn't handle circular references. 请务必避免循环引用。Be sure to avoid them.

客户端代理问题Client agent issues

若要排查客户端代理问题,请参阅排查 Data Sync Agent 问题To troubleshoot issues with the client agent, see Troubleshoot Data Sync Agent issues.

设置和维护问题Setup and maintenance issues

有消息指出“磁盘空间不足”I get a "disk out of space" message

  • 原因Cause. 需要删除残留的文件时,可能会显示“磁盘空间不足”消息。The "disk out of space" message might appear if leftover files need to be deleted. 出现此消息的原因可能是使用了防病毒软件,或者在尝试执行删除操作时文件处于打开状态。This might be caused by antivirus software, or files are open when delete operations are attempted.

  • 解决方法Resolution. 手动删除 %temp% 文件夹中的同步文件 (del \*sync\* /s)。Manually delete the sync files that are in the %temp% folder (del \*sync\* /s). 然后,删除 %temp% 文件夹中的子目录。Then, delete the subdirectories in the %temp% folder.

重要

同步正在进行时,请不要删除任何文件。Don't delete any files while sync is in progress.

无法删除同步组I can't delete my sync group

尝试删除同步组失败。Your attempt to delete a sync group fails. 以下任何情况都可能导致删除同步组失败:Any of the following scenarios might result in failure to delete a sync group:

  • 原因Cause. 客户端代理处于脱机状态。The client agent is offline.

  • 解决方法Resolution. 确保客户端代理已联机,然后重试。Ensure that the client agent is online and then try again.

  • 原因Cause. 客户端代理已卸载或缺失。The client agent is uninstalled or missing.

  • 解决方法Resolution. 客户端代理是否已卸载或缺失:If the client agent is uninstalled or otherwise missing:
    a.a. 将代理 XML 文件(如果存在)从 SQL 数据同步安装文件夹中删除。Remove the agent XML file from the SQL Data Sync installation folder, if the file exists.
    b.b. 在某台本地计算机(可以是相同或不同的计算机)上安装代理。Install the agent on an on-premises computer (it can be the same or a different computer). 然后,提交门户中针对显示为脱机的代理生成的代理密钥。Then, submit the agent key that's generated in the portal for the agent that's showing as offline.

  • 原因Cause. 数据库处于脱机状态。A database is offline.

  • 解决方法Resolution. 确保所有数据库都处于联机状态。Ensure that your databases are all online.

  • 原因Cause. 同步组正在预配或同步。The sync group is provisioning or syncing.

  • 解决方法Resolution. 等待预配或同步过程完成,然后重试删除同步组。Wait until the provisioning or sync process finishes and then retry deleting the sync group.

无法注销 SQL Server 数据库I can't unregister a SQL Server database

  • 原因Cause. 很可能是因为正在尝试注销一个已被删除的数据库。Most likely, you are trying to unregister a database that has already been deleted.

  • 解决方法Resolution. 若要注销某个 SQL Server 数据库,请选择该数据库,再选择“强制删除”。To unregister a SQL Server database, select the database and then select Force Delete.

    如果此操作无法从同步组中删除数据库:If this operation fails to remove the database from the sync group:

    1. 请停止再重启客户端代理主机服务。Stop and then restart the client agent host service:
      a.a. 选择“开始”菜单。Select the Start menu.
      b.b. 在搜索框中输入 services.mscIn the search box, enter services.msc.
      c.c. 在搜索结果窗格的“程序”部分,双击“服务”。 In the Programs section of the search results pane, double-click Services.
      d.d. 右键单击“SQL 数据同步”服务。Right-click the SQL Data Sync service.
      e.e. 如果该服务正在运行,请将其停止。If the service is running, stop it.
      f.f. 右键单击该服务,并选择“启动”。Right-click the service, and then select Start.
      g.g. 检查数据库是否仍已注册。Check whether the database is still registered. 如果已不再注册数据库,则操作完成。If it is no longer registered, you're done. 否则,请继续执行下一步。Otherwise, proceed with the next step.
    2. 打开客户端代理应用 (SqlAzureDataSyncAgent)。Open the client agent app (SqlAzureDataSyncAgent).
    3. 选择“编辑凭据”,输入数据库的凭据。Select Edit Credentials, and then enter the credentials for the database.
    4. 继续执行注销。Proceed with unregistration.

没有足够的特权启动系统服务I don't have sufficient privileges to start system services

  • 原因Cause. 在两种情况下会发生此错误:This error occurs in two situations:

    • 用户名和/或密码不正确。The user name and/or the password are incorrect.
    • 指定的用户帐户没有足够的特权作为服务登录。The specified user account doesn't have sufficient privileges to log on as a service.
  • 解决方法Resolution. 向用户帐户授予“作为服务登录”凭据:Grant log-on-as-a-service credentials to the user account:

    1. 转到“开始” > “控制面板” > “管理工具” > “本地安全策略” > “本地策略” > “用户权限管理”。 Go to Start > Control Panel > Administrative Tools > Local Security Policy > Local Policy > User Rights Management.
    2. 选择“作为服务登录”。Select Log on as a service.
    3. 在“属性”对话框中添加用户帐户。In the Properties dialog box, add the user account.
    4. 依次选择“应用”、“确定” 。Select Apply, and then select OK.
    5. 关闭所有窗口。Close all windows.

数据库具有“过期”状态A database has an "Out-of-Date" status

  • 原因Cause. SQL 数据同步会从服务中删除已脱机达 45 天或更长时间的数据库(从数据库脱机时算起)。SQL Data Sync removes databases that have been offline from the service for 45 days or more (as counted from the time the database went offline). 如果数据库已脱机达 45 天或更长时间,然后重新联机,则其状态会变为“过期”。If a database is offline for 45 days or more and then comes back online, its status is Out-of-Date.

  • 解决方法Resolution. 确保所有数据库都不会脱机达 45 天或更长时间,即可避免“过期”状态。You can avoid an Out-of-Date status by ensuring that none of your databases go offline for 45 days or more.

    如果数据库的状态为“过期”:If a database's status is Out-of-Date:

    1. 从同步组中删除处于“过期”状态的数据库。Remove the database that has an Out-of-Date status from the sync group.
    2. 将数据库添加回同步组。Add the database back in to the sync group.

    警告

    在数据库脱机时,将丢失对该数据库所做的所有更改。You lose all changes made to this database while it was offline.

同步组具有“过期”状态A sync group has an "Out-of-Date" status

  • 原因Cause. 如果在 45 天的整个保留期内未能应用一个或多个更改,则同步组可能会过期。If one or more changes fail to apply for the whole retention period of 45 days, a sync group can become outdated.

  • 解决方法Resolution. 若要避免“过期”状态,请定期在历史记录查看器中检查同步作业的结果。To avoid an Out-of-Date status for a sync group, examine the results of your sync jobs in the history viewer on a regular basis. 调查并解决未能应用的任何更改。Investigate and resolve any changes that fail to apply.

    如果同步组的状态为“过期”,请删除同步组并重新创建。If a sync group's status is Out-of-Date, delete the sync group and then re-create it.

在卸载或停止代理后的三分钟内无法删除同步组A sync group can't be deleted within three minutes of uninstalling or stopping the agent

在卸载或停止关联的 SQL 数据同步客户端代理后的三分钟内无法删除同步组。You can't delete a sync group within three minutes of uninstalling or stopping the associated SQL Data Sync client agent.

  • 解决方法Resolution.

    1. 在关联的同步代理处于联机状态时删除同步组(推荐)。Remove a sync group while the associated sync agents are online (recommended).
    2. 如果代理处于脱机状态但已安装,请在本地计算机上将其联机。If the agent is offline but is installed, bring it online on the on-premises computer. 等待代理状态在 SQL 数据同步门户中显示为“联机”。Wait for the status of the agent to appear as Online in the SQL Data Sync portal. 然后删除同步组。Then, remove the sync group.
    3. 如果代理因为被卸载而处于脱机状态:If the agent is offline because it was uninstalled:
      a.a. 将代理 XML 文件(如果存在)从 SQL 数据同步安装文件夹中删除。Remove the agent XML file from the SQL Data Sync installation folder, if the file exists.
      b.b. 在某台本地计算机(可以是相同或不同的计算机)上安装代理。Install the agent on an on-premises computer (it can be the same or a different computer). 然后,提交门户中针对显示为脱机的代理生成的代理密钥。Then, submit the agent key that's generated in the portal for the agent that's showing as offline.
      c.c. 尝试删除同步组。Try to delete the sync group.

还原丢失或损坏的数据库时,会发生什么情况?What happens when I restore a lost or corrupted database?

如果从备份还原丢失或损坏的数据库,数据库所属的同步组中的数据可能不收敛。If you restore a lost or corrupted database from a backup, there might be a non-convergence of data in the sync groups to which the database belongs.

后续步骤Next steps

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

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