Troubleshooting Azure SQL Data Sync - Classic Portal

Date created: January 22, 2017.

Last updated: June 1, 2017.

After configuring SQL Data Sync one may find that the synchronization is not working or the data sync group status is processing for a long time. The following is a checklist for troubleshooting this situation:

Before a synchronization occurs a provisioning process takes place and sometimes this process hangs on a message saying “processing” for hours. If you want to stop this process because is consuming a great amount of DTUs, go to dialog where you configure the Sync rules on the old portal, at the bottom, there are two buttons: Sync and Stop. Try using the Stop button. If the Stop button is not available try to disable the account that the sync process is using to access the Hub database.

If successful synchronization has not been possible or is stuck at schema generation, make sure name of objects on the databases do not have characters like ".", "[" or "]" and make sure there are no columns of 50 characters in length or more: removing the offending object(s) or setting a supported name or length for the object or objects may work. Additionally, conditional indexes like the one shown below need to be created manually on member databases (on premises databases) since SQL Data Sync creates them as a key constraint index instead.

      CREATE UNIQUE NONCLUSTERED INDEX [IX_MyIndexName] ON [dbo].[MyTableName]

       (

            [ColumnName] ASC

       )

       WHERE ([ColumnName] IS NOT NULL)

       GO

If you get error message "Please enter a valid agent key to proceed. The key must be generated from SQL Data sync web portal" when you add a generated key from Azure to Data Sync Agent, please try to make the time zone and clock time of all servers involved the same. 

If the synchronization was working for a specific period of time and is no longer working, consider to examine the database schema on the hub database (SQL Azure database) and on the member databases (on premises databases). Any change on the database schema of the hub database or any member database should be manually replicated to all other databases because SQL Data Sync does not support dynamic schema changes. Changes to schema include: changing the name of indexes, columns and tables; adding objects, indexes or adding columns to tables. Take in consideration that changes to server names are not supported too.

In some cases Azure Data Sync may fail with one of the following errors:

·         Sync failed with the exception "An unexpected error occurred when applying batch file C:\Users\SVC-AZ~1\AppData\Local\Temp\DSS_f5de50e4-1ebd-491e-a87e-1f347c5a6ab9\de696f04-9feb-4942-8a5b-347fab726eb6.batch. See the inner exception for more details. Inner exception: There is not enough space on the disk."    For more information, provide tracing ID ‘9e8140c8-da11-4a59-8ecc-6c20a1e788e5’ to customer support. Solutions\Workarounds:

o   It seems the C: drive of your on premises computer is running out of space. In the case of a virtual machine, try to expand the C: drive.

o   Change your batching directory to another drive, if another drive is available.  The batching directory is usually the Temp folder on the C:\ drive and resetting the Temp environment variable to another drive may solve the issue.

·         Sync failed with the exception "An unexpected error occurred when applying batch file C:\Users\ADMINI~1\AppData\Local\Temp\DSS_9a0a9343-3283-4fb6-8ac8-b3b42b91ea61\3016743e-cc3e-42b4-8f92-444ca9581a91.batch. See the inner exception for more details.Inner exception: The data type or value for column 'sync_update_peer_key' on table 'dbo.myDepartments' is not valid. The column should be one of the following types: 'Int32', 'UInt32', or a numeric string that can be converted to UInt32."    For more information, provide tracing ID ‘87521125-3718-403e-8180-77662bafa5a4’ to customer support. Solutions\Workarounds:

o   Remove the table involved and then add it back.

o   If removing the table does not work, the try to remove the server from the Sync Group and then add it back.

·         Sync failed with the exception "GetStatus failed with exception:Sync worker failed, checked by GetStatus method. Failure details:SqlException Error Code: -2146232060 - SqlError Number:3952, Message: Snapshot isolation transaction failed accessing database 'SampleDB' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation. "    For more information, provide tracing ID ‘efff2d0b-6c1f-41c8-b0c7-b7f483760d72’ to customer support. Solutions\Workarounds:

o   Read committed snapshot isolation is the default isolation level on SQL Azure. You cannot change the default isolation level. SQL Data Sync is trying to enable on SQL Azure database the same isolation level your database has on premises, but it is not possible to change the isolation level of a SQL Azure database. That is the reason the SQL Data Sync process will fail.

·         SqlException Error Code: -2146232060 - SqlError Number:2627, Message: Violation of PRIMARY KEY constraint 'PK__SoundIma__3214EC07E66AA4D5'. Cannot insert duplicate key in object 'dbo.SoundImage'. The duplicate key value is (0077ca57-a663-40e9-a7f1-c94ee13d0e31). SqlError Number:3621, Message: The statement has been terminated. Solutions\Workarounds:

o   This warning may be found on SQL Data Sync logs when row level security is used. Please examine security policy filters.

·         "This agent cannot be removed because one more databases are still associated with it. Please remove the databases associated with the agent and then try again. TracingId=bf4e192b-35ac-38e9-b734-0672935641bc". Solutions\Workarounds:

o   This error appears when you try to remove/delete a sync agent as workaround remove the agent tools from the on premise computer, then reinstall SQL Data Sync agent on the computer. Regenerate key and unregistered the database then try to delete agent key. After that, agent key successful to remove.

o   Another workaround is trying to install the SQL Data Sync agent on another machine, re-generate key from Azure portal from the previous agent record, and register with the new agent. After that, you may be able to remove the agent.

·         Sync failed with the exception "GetStatus failed with exception:Sync failed due to Windows Azure storage error. Please retry." For more information, provide tracing ID ‘a46f3117-1fdd-40eb-aa4e-ebe53a1b45e9’ to customer support. Solutions\Workarounds:

o   This error may be caused by a transient error when the Sync service tries to communicate with Azure Storage. To mitigate this error retry the sync.

·         Database provisioning failed with the exception "Cannot insert the value NULL into column 'provision_timestamp', table 'Db1.DataSync.provision_marker_dss'; column does not allow nulls. INSERT fails.The statement has been terminated.Inner exception: SqlException Error Code: -2146232060 - SqlError Number:515, Message: Cannot insert the value NULL into column 'provision_timestamp', table 'Db1.DataSync.provision_marker_dss'; column does not allow nulls. INSERT fails. SqlError Number:3621, Message: The statement has been terminated.". Solutions\Workarounds:

o   Delete below sync group which hit the issue.

o   Delete the follow database objects from both Hub and member database
- Delete all tables under “DataSync” schema, which means all tables named like “DataSync.*”
- For each of your table, delete their triggers named like “*_dss_insert_trigger”, “*_dss_delete_trigger”, “*_dss_update_trigger”
- Delete all stored procedures under “DataSync” schema: named like “DataSync.*”
- Delete all user-defined table types under “DataSync” schema

o   Recreate the sync group.

·         "Sync failed with the exception "Blob transfer failedInner exception: One or more errors occurred.Inner exception: One or more errors occurred.Inner exception: The Data Transfer Service has encountered a fatal error when performing the data upload.Inner exception: The remote server returned an error: (403) Forbidden.Inner exception: The remote server returned an error: (403) Forbidden." Solutions\Workarounds:

o   We have seen this error appearing when SQL Data Sync is used with an Internet connection of 300 Kbits/sec or less.

If the synchronization is not successful after trying above solutions/workarounds, please go the “Properties” tab of the synchronization group on Azure portal and capture the Sync Group ID, then contact Azure support and provide them the Sync Group ID.

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.