Many problems with SQL Azure Data Sync

Question

We have transaction replication set up to feed data to 'OrderDetail' database which is used
to synchronize data with SQL Azure via Data Sync service CTP2. Synchronization direction is only from SQL Server to SQL Azure. There are 30 tables to be synced and original size is around 7 GB.
Here are the problems we are having

1. It took 12 hours to move 7GB to SQL Azure for the first synchronization which is too long. Our SQL server and SQL Azure databases are on the same region.

2. Then it tookaround 5 hours to move 1200 records to SQL Azure on each synchronization which is too slow to sync data with our souce database.

3. Need more detail on messages on Sync Logs. Below is one of the messages we got on succeed state.
Status=Succeeded,DownloadApplied=0,DownloadFailed=0,UploadApplied=2044,UploadFailed=5
We would like to get more information about UploadFailed. What table that failed and why? How do we fix this? We need to assure that SQL Azure database gets all data from our sQL Server database.

4. After this sync group had run for 3-4 days, our SQL Server database was somehow removed from Sync Group. SQL Azure database is the only member on the sync group. SQL server database ('OrderDetail') is no longer there when I clicked on the Databases tab.

5. Since data sync failed and SQL Server was removed from the group, I have to recreate new sync group again. It seems to be impossible to use the same set of databases on the new Sync Groups due to Datasync objects that are already created on source and destination databases. I have to drop and recreate OrderDetail database on SQL Server and SQL Azure again before recreating new sync group.

Is there any way to improve Data Sync performance, the way to resync without drop and recreate databases, to ensure that source and destination databases are synced?

 

Answer 1

-- Our SQL server and SQL Azure databases are on the same region.

As in same data center? I mean have you hosted your SQL server on a VM role that lies in the same data center as the SQL Azure database. And does by any chance it happens to be South Central US.

 

Also Data SYNC (CTP) is currently hosted in South central US [ Source: http://blogs.msdn.com/b/sqlazure/archive/2010/07/06/10035099.aspx ] and even if your SQL server and SQL Azure are closer to each other as you say, the network latency + encrypted connections are supposed to be factored in when you talk about performance of SQL Azure database.

So say you have a SQL server database at say location x which is (say) 200 miles away from SQL Azure data center. In such scenario's the performance is affected by network/bandwidth latency + encrypted connections.

 

--Is there any way to improve Data Sync performance, the way to resync without drop and recreate databases, to ensure that source and destination databases are synced?

Please have a look at what's coming in CTP3 - this should resolve your problem: http://blogs.msdn.com/b/sync/archive/2011/03/08/sql-azure-data-sync-update.aspx

 

And having said you can i ask you to try building a custom solution - i would not have suggested this but since you mentioned the performance issues, i think if you deploy a custom solution you might have a better control over performance

It uses sync framework and here is the article: http://blogs.msdn.com/b/sync/archive/2010/08/31/sql-server-to-sql-azure-synchronization-using-sync-framework-2-1.aspx

---

Paras Doshi ( Blog | Twitter ) 

Answer 2

Hi BunBv,

Please see below responses

1. It took 12 hours to move 7GB to SQL Azure for the first synchronization which is too long. Our SQL server and SQL Azure databases are on the same region.

When you migrate that bigger database, I would suggest using SQL Azure Migration wizard since it use BCP with Batch upload and also depending upon your internet speed, the upload time increases as well. Based on my personal testing, 1 GB database has uploaded in 12 mins and also some times on 30 mins.

2. Then it tookaround 5 hours to move 1200 records to SQL Azure on each synchronization which is too slow to sync data with our souce database.

It should not take 5 hours to move 1200 records, can you please post the Sync Logs ?

3. Need more detail on messages on Sync Logs. Below is one of the messages we got on succeed state. 
Status=Succeeded,DownloadApplied=0,DownloadFailed=0,UploadApplied=2044,UploadFailed=5
We would like to get more information about UploadFailed. What table that failed and why? How do we fix this? We need to assure that SQL Azure database gets all data from our sQL Server database.

SQL Azure Sync Data Service is still in CTP, in next CTP it might include more detailed logs

4. After this sync group had run for 3-4 days, our SQL Server database was somehow removed from Sync Group. SQL Azure database is the only member on the sync group. SQL server database ('OrderDetail') is no longer there when I clicked on the Databases tab

SQL Server database will be removed only when the database gets deleted from SQL Server or removed from SQL Azure Data Sync Agent locally

5. Since data sync failed and SQL Server was removed from the group, I have to recreate new sync group again. It seems to be impossible to use the same set of databases on the new Sync Groups due to Datasync objects that are already created on source and destination databases. I have to drop and recreate OrderDetail database on SQL Server and SQL Azure again before recreating new sync group

You can actually create Sync group on already used databases, during Provisioning steps it will identify and handle it accordingly

To improve performance, We have Sync only required data, currently we have option to do sync at table levels, in next CTP, there will be option to define Conflict resolution policy and row filters.

---

Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, Please "Mark as Answer" or "Vote as Helpful"

Answer 3

Hi Arunraj,

1. Yes. SQL Azure Migration wizard is a lot faster. It took around 40 minutes to move 7GB of data to SQL Azure. However, we want to feed data continuously and Data Sync is still too slow to sync data after that.

2. Our 'OrderDetail' database on SQL Server got 14,183 replicated commands from publisher. Then Data Sync synced data from our 'OrderDetail' database to database in SQL Azure. Below is the sync log of moving 1187 records (according to rowcount comparision) in 4.5 hours to SQL Azure.

Status=Succeeded,DownloadApplied=0,DownloadFailed=0,UploadApplied=2044,UploadFailed=5

4. This has happened more than once that SQL Server database was somehow removed from the Sync Group and Databases tab
even though database still exists on SQL Server. In order to add SQL Server database back to Databases tab again, we need to remove that SQL Server database from Data Sync Agent and add it back. However, we cannot add this database back to the Sync Group. We have to create new Sync Group for this.

5. When I created new Sync group on already used databases with the same set of tables, it failed during Provision step.Below is the message from the sync log.
Provisioning Failed: 'Type=Microsoft.SqlAzureDataSync.SharedLibs.DssInvalidOperationException,
Message=Endpoint token 3f6d64f8-145e-4a65-a693-b6a3a846756b was incorrect. Exception = Sequence contains no matching element,'

 

 

Answer 4

Hi Arunraj,

1. Yes. SQL Azure Migration wizard is a lot faster. It took around 40 minutes to move 7GB of data to SQL Azure. However, we want to feed data continuously and Data Sync is still too slow to sync data after that.

2. Our 'OrderDetail' database on SQL Server got 14,183 replicated commands from publisher. Then Data Sync synced data from our 'OrderDetail' database to database in SQL Azure. Below is the sync log of moving 1187 records (according to rowcount comparision) in 4.5 hours to SQL Azure.

Status=Succeeded,DownloadApplied=0,DownloadFailed=0,UploadApplied=2044,UploadFailed=5

4. This has happened more than once that SQL Server database was somehow removed from the Sync Group and Databases tab
even though database still exists on SQL Server. In order to add SQL Server database back to Databases tab again, we need to remove that SQL Server database from Data Sync Agent and add it back. However, we cannot add this database back to the Sync Group. We have to create new Sync Group for this.

5. When I created new Sync group on already used databases with the same set of tables, it failed during Provision step.Below is the message from the sync log.
Provisioning Failed: 'Type=Microsoft.SqlAzureDataSync.SharedLibs.DssInvalidOperationException,
Message=Endpoint token 3f6d64f8-145e-4a65-a693-b6a3a846756b was incorrect. Exception = Sequence contains no matching element,'

 

 

Answer 5

Hi BunBv,

As i suggested earlier, you can use SQL Azure Migration wizard for initial data load and use SQL Azure Data Sync for Continuous synchronization.

Regarding the Time taken for Data Sync, it sounds like there might be some performance issue when it retrieves the data for Sync, did you ran Profiler and checked how much time it takes for queries to fetch data when Sync runs. If not i would suggest to do so, so that if there is any performance issue on Database you can fix it.

Reg (4) and (5), Please report the issue to [e-mail]

---

Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, Please "Mark as Answer" or "Vote as Helpful"

Answer 6

Thank you bunbv, arunraj chandrasekaran, paras doshi.

Answer 7

I know this is an old thread, but wanted to clarify things as we're seeing that customers are finding this thread and acting on the advice.

Data Sync users should NOT use Migration Wizard, Import/Export, BCP, or any other mechanism to pre-populate the non-source member databases.  When the non-source members are re-populated Data Sync detects rows with the same PK's as in the source database and treats each row as a conflict.  This will mean that the initial sync takes much, much longer and it does not save time.

We plan to improve the performance of the initial sync over time and may, in the future, allow out-of-band initialization.

For the moment, do not use SQL Azure Migration wizard (or any other mechanism) for the initial data load.

Regards, Mark