Troubleshoot issues with SQL Data Sync

  • Article

Applies to: Azure SQL Database

Important

SQL Data Sync will be retired on 30 September 2027. Consider migrating to alternative data replication/synchronization solutions.

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.

For an overview of SQL Data Sync, see What is SQL Data Sync for Azure?

SQL Data Sync does not support Azure SQL Managed Instance or Azure Synapse Analytics.

Sync issues

Sync fails in the portal UI for on-premises databases that are associated with the client agent

Sync fails in the SQL Data Sync portal UI for on-premises databases that are associated with the client agent. 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. Create more space on the drive on which the %TEMP% directory is located.

My sync group is stuck in the processing state

A sync group in SQL Data Sync has been in the processing state for a long time. 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. 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. The SQL Data Sync service is stopped.

  • Resolution. Restart the SQL Data Sync service.

    1. In the Start menu, search for Services.
    2. In the search results, select Services.
    3. Find the SQL Data Sync service.
    4. If the service status is Stopped, right-click the service name, and then select Start.

Note

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. To have your sync group status reset, in the Azure SQL Database forum, create a post. In the post, include your subscription ID and the sync group ID for the group that needs to be reset. 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 are from different database schemas are included in a sync, you see erroneous data in the tables after the sync.

  • Cause. The SQL Data Sync provisioning process uses the same tracking tables for tables that have the same name but 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

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 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.

I see this message: "Cannot insert the value NULL into the column <column>. 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.
  • There's a WHERE clause in your CREATE INDEX statement. Data Sync doesn't handle this condition. 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

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. Manually delete the sync files that are in the %temp% folder (del \*sync\* /s). Then, delete the subdirectories in the %temp% folder.

Important

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. Remove the agent XML file from the SQL Data Sync installation folder, if the file exists.
    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.

I can't unregister a SQL Server database

  • Cause. Most likely, you are trying to unregister a database that has already been deleted.

  • Resolution. 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. Select the Start menu.
      b. In the search box, enter services.msc.
      c. In the Programs section of the search results pane, double-click Services.
      d. Right-click the SQL Data Sync service.
      e. If the service is running, stop it.
      f. Right-click the service, and then select Start.
      g. Check whether the database is still registered. If it is no longer registered, you're done. Otherwise, proceed with the next step.
    2. 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 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). If a database is offline for 45 days or more and then comes back online, its status is Out-of-Date.

  • Resolution. 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.

    Warning

    You lose all changes made to this database while it was offline.

A sync group has an "Out-of-Date" status

  • Cause. 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

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. 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. Remove the agent XML file from the SQL Data Sync installation folder, if the file exists.
      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. 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.

Error message "Sync0022 Customer does not have authorization to perform action 'syncGroupOperationResults/read'"

If you receive the error message Sync0022 Customer does not have authorization to perform action 'syncGroupOperationResults/read', the account attempting the operation does not have sufficient subscription-level permissions. Add:

  • "Microsoft.Sql/locations/syncMemberOperationResults/read"
  • "Microsoft.Sql/locations/syncAgentOperationResults/read"
  • "Microsoft.Sql/locations/syncGroupOperationResults/read"

For more information, see Resource provider operations RBAC and SQL Data Sync Database accounts with least required privileges.

Related content