This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .
Issue:
While trying to either establish data sync for the first time or reestablish data sync after any schema/application changes the following common error codes (messages) may be generated. The document describes the error codes & their possible mitigation techniques.
Error 1- >WARNING: Table with object_id 1490872428 was not found, DataSync.xx_dss_updatemetadata was provisoned using this object_id!
>WARNING: owner_scope_local_id 0 was not found for object_id 1490872428
Error 2- >WARNING: Tracking Records for Table [dbo].[aa] may have 2072 invalid records!
>WARNING: Tracking Records for Table [dbo].[bb] may have 2072 invalid records!
>WARNING: Tracking Records for Table [dbo].[cc] may have 2328 invalid records!
Error 3- Sync Failed with the exception “An unexpected error occurred while applying the batch file sync_xxxxxxx12. See the inner exception for more details.
Error 4- Sync failed with exception “SqlException ID: 123456xxx”
Error 5- WARNING (DSS035): Trigger [dbo].[Test_dss_insert_trigger] IS MISSING!
Error 6- |Upload - |errors for first 5 rows that failed to apply:|Error #1: SqlException ID: f2bbb80a-30a5-4335-af7a-89c17f9fc5ec. Error Code: -2146232060 - SqlError Number:13535. Message: SQL error with code 13535 SqlError Number:3621.
Investigation/Analysis:- We greatly recommend running the data sync health checker tool before the errors can be analyzed in detail. The result of the tool should generate 4 log files: the Summary Log, The Hub Log, The member Log & the SyncDB Log. Here’s the link for the same- GitHub - microsoft/AzureSQLDataSyncHealthChecker:
Mitigation:
Error 1- Data sync works off of object ID, which is unique for every object in the database. Deleting and recreating a table, for example, will create a new table with the same schema, but a different object ID, so data sync will no longer work. If any data sync-tracked tables are modified, such as by exporting and re-importing them, even if the resulting schema is identical, that table must be first removed from data sync, then created or modified, and finally re-added to data sync.
To fix this error currently, please remove the table xx from data sync, wait for it to sync and make sure that xx and related objects don't exist on the hub and member DBs, and then add the table back to data sync. This may take a long time due to the size of the table (10 rows/sec of conflict resolution = 5-10 days to sync). During this sync, none of the other tables are being synced, because the xx sync is ongoing. To go faster, following the steps above, after re-adding xx to the data sync, but before clicking"sync", truncate the table on both member DBs, and have them repopulate from the hub.
Error 2- There is a chance that primary key was probably updated on the member database. That is a common cause of this error. If we have auditing enabled on such member DBs, we may very easily find out which rows caused the PK violation.
Error 3- At this moment, for data sync, we can only create an index with 2 properties of the original Index i.e. Unique and non-clustered. We do not handle any other properties like Ignore_dup_key. This is currently a limitation of data sync provisioning and has been documented in Best practices for Azure SQL Data Sync - Azure SQL Database | Microsoft Learn
Error 4- Only UpsertSyncMember with <UsePrivateLinkConnection> true will create a new private link connection, and then the engineer (working on Data sync set up) needs to manually approve private endpoint creation.
However, it appears the engineer may have issued UpsertSyncMember with <UsePrivateLinkConnection> false a few times (the server disallowed public access.)
It is also worth mentioning that such <UsePrivateLinkConnection> requests should be approved ASAP or else they time out.
To mitigate such errors, the engineer (working on Data Sync set up) should either reenable the allow all azure access feature on the server, or set up Private Link for Data Sync, as explained in the link below:
What is SQL Data Sync for Azure? - Azure SQL Database | Microsoft Learn
Error 5- It appears that all the data sync objects are missing from both the member DBs (tracking table, triggers and etc). There must have been some workloads/jobs/processes running on the environment which may have cleaned up these objects. To mitigate the issue, we will have to ensure that all missing objects are recreated and don't get dropped again.
Setting up auditing in these 2 DBs will help us determine the user/session that dropped data sync objects. The link below can help set up Auditing on Azure SQL DB:
https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-overview?view=azuresql
Error 6- When 2 or more workers are modifying the same row, it is possible to encounter 13535. The time of the begin transaction and the modification are the defining properties. When the transaction begin time is before the latest row modification, error 13535 is encountered. The link below describes the error in detail and can also help in avoiding the same.