Troubleshooting Azure SQL Data Sync - Classic Portal
Last updated: 2/16/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 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, 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]
WHERE ([ColumnName] IS NOT NULL)
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.
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.
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
questions or comments about this web site.