Data Sync fails when recreating sync group with similar schema

Question

Hello.

I have a problem with Data sync that have occured several times when I'm trying to change a Sync Group, this is what happens:
1. A change needs to be made on the sync group, like adding a table or modifying path to Azure database
2. I Remove the sync group and deprovision the sync server.
3. I create a new sync group with the correct schema, and start syncing
4. Syncing takes extreme long time (like 40.000 seconds instead of 300 seconds), and it doesn't sync at all, instead giving me this message:"Sync completed with warnings in 48281.32 seconds.Upload: 0 changes applied/26317 failed"
5. After that I get the same error message every day in the log, and no syncing is done

This has happens several times, for several subscriptions, and the only workaround I've managed to do is:
1. Create a new empty database that i call "DB_Proxy"
2. Set up Data sync between on-site database and Proxy
3. Set up Data sync between Proxy-database and "real" database

Although this works fine, it is of course not a conveniant and cost-efficient way, so I would like to have an easier solution for this problem, without losing any data in my database.

It feels like even when I remove the sync group, there is some sync-data kept that makes the next sync group creation fail, but that's just my "guess"...

Below are some detailed error messages that perhaps can help you:

Example 1:
Sync completed with warnings in 3312.31 seconds.
Upload:   0 changes applied/5 failed 
Download: 0 changes applied/0 failed

For more information, provide tracing id ‘d673f828-69b1-48b5-97d1-2b472abd6831’ to customer support.

Example 2 (from another subscription):
Sync completed with warnings in 48281.32 seconds.
Upload:   0 changes applied/26317 failed 
Download: 0 changes applied/0 failed

For more information, provide tracing id ‘4218edbc-e0f2-4a73-9ea7-4f846dc0ef31’ to customer support.

We are using the sync Agent version that was release on january 24th.

Thankful for help, 

Joel

Answer 1

when you deprovision, you are just removing the sync metadata. your databases however contains data already (identical)

when you provision a new sync group, the provisioning will populate the _tracking tables in each database with the existing data on that database. Data sync however has no idea that both data in your databases are one and the same. so the tracking table in one of your database will record all existing rows in the tables as inserts in the tracking table. the same thing happens in the other database.

when you sync, data sync will ask the destination database what it knows about the source database. since they havent sync yet, the destination database has no knowledge of the rows from the source database. so sync sends all the rows in the source to the destination and the sync attempts to insert them. since the rows already exists, the inserts will fail and this will be raised as a conflict. sync now has to resolve each conflict based on the conflict resolution you specified.

so your initial sync takes longer.

in your case, you probably specified hub wins, so all rows uploaded are marked as failed because they were not applied.

i would advise before deprovisioning that you sync the members first to make sure unsync changes are applied, even sync twice.
deprovision, empty one of the databases (assuming you only have two dbs) making sure only one db has initial copy of data, provision, then sync.

Answer 2

Hi June, thanks for your quick answer that gave me more insight to this problem.

There are still some issues though. Whenever a change is made on the on-site database I want it to be reflected to the Hub-database.

This does not happen although I always put "client wins" as the conflict resolution.

Answer 3

how many databases are you synching?

Answer 4

Can you give an even more detailed example of how to resolve the problem. I'm not sure what you mean with this line:

"i would advise before deprovisioning that you sync the members first to make sure unsync changes are applied, even sync twice."

Assumption is correct that I only have two dbs.

Answer 5

if you only have two databases, synching once is fine.

for the row change that is not reflected on hub even if you specify client-wins, do you get any error in the log? does it show up as failed in the stats as well? what type of change is it? update, delete or insert?

Answer 6

Hi, the error I get in the log are the ones i described in the first post, I would guess it is an update-change, but I don't know how to tell?

I will try the "Sync-Deprovision-Clear-Provision-Sync"-strategy later this week.

Do I only have to clear the table in the dbo-namespace, or do I also need to clear anything in the Datasync-namespace, like the tracking table?

Answer 7

how many sync groups do you have defined against the same table?

if you only have one sync group, the deprovisioning should have cleaned up the sync created objects and the clean up should have happen on both databases.

if the deprovisioning didnt completely remove the DataSync objects, there is a utility in the same folder where you installed the agent to deprovision them manually.

am guessing your updates are failing because of some mismatch in these objects.

for example, you deprovision a sync group. it cleaned up the SQL Azure data sync objects. but then the clean up on the on-premise failed for some reason.

when you reprovision, the sql azure part get's all brand new DataSync objects. for the on-premise though, it will try to reuse the existing DataSync objects there.

so let's say you have a 5 column table in the old scope, then you decided to exclude one column. so you deprovision. if the deprovisioning didnt clean up the sync objects, when you reprovision with just 4 columns, it will re-use the old 5-column object UDTs, stored procs, etc...

provisioning would succeed, but sync will fail for example in an insert because the stored proc is expecting a 5-col UDT and its being passed 4 columns only.

Answer 8

Hi, I just tried the solution and it worked like a charm.

"Sync completed successfully in 50.78 seconds. Upload:   39510 changes applied"

So in the future I will always clear the target database before recreating a new sync group.

Big thanks for your help!