Azure Data Sync never stops synchronizing

Question

Hi,

I have an on-premises SQL Server 2008 R2 database that I am trying to sync with a SQL Azure database using SQL Azure Data Sync. Both databases already have the exact same structure, which I confirmed with a SQL Compare tool. The two databases have been successfully provisioned (log viewer shows an entry for each database that says "Database provisioned succesfully in xxxxx seconds."

When I click "Ping Sync Service" in the Agent registration window, it says it pings the Sync Service successfully.

In the Sync Group I created, under Sync Schedule options, I have the Enabled checkbox not checked so I can sync on-demand. I started a synchronization about 24 hours ago. The status still says "Synchronizing...". It never completes. This happened the first time I used Data Sync a few days ago and so I deprovisioned both databases using the Deprovisioning Utility tool and removed the sync agent completely and recreated it again. Still show synchronizing without ever stopping. I have yet to get one successful synchronization with Data Sync.

I am able to connect to the Azure database and the SQL Server database fine using Management Studio and using Access 2010 (current front-end application) so there appears to be no connectivity issues. I know that both databases have been provisioned due to all the DataSync schema tracking tables and stored procs, etc. that are in both databases.

As of now it's still synchronizing and I don't know how to stop it. It won't let me remove the sync group while it's synchronizing.

On-premises SQL Server version:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)   Apr 22 2011 19:23:43   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )  

Azure version:
Microsoft SQL Azure (RTM) - 11.0.1831.30   Jan 27 2012 23:11:55   Copyright (c) Microsoft Corporation 

Server id: 19a12c59-60f4-41c1-b460-e66f7233a617

Region: South Central US

Time / Date: Since 3/15/2012 (morning) still going.

Tracing Id: There is no error message in log. Sync just never completes.

Is anybody else having this issue?

Marc

Answer 1

do you have data on both databases before you provisioned?

Answer 2

Yes. Both databases had nearly identical data before I provisioned them. 

Answer 3

the sync is  running into conflicts and resolving them is slowing it down further.

when doing an initial sync, only one database should have data. when you have data on both before the sync, neither of the databases nor the service knows that they have the same identical data. so one of them sends it's data to the other and the inserts on the destination will fail because of PK conflicts. this confict will now have to be resolved individually. if you have a million rows on each side, that's a million row conflict one-way alone and a million conflict to resolve.

see this other thread: http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/bc1bd7ca-8cc5-4298-9847-ba7856d9ba74