none
How to initialize database before doing first synchronization in Collaboration Scenarios RRS feed

  • Question

  • I have several SQL Server Express databaes, each of them has large amount of data, I need to let them sync with a central database. If I use Sync Framework to upload data, it will cost a lot of time and always cause time out exception. I want to know if I use other method such as BCP command to upload data to central database, how can I make the tracking-Metadata up to date then let sync framework do the incremental sync after that?

    Thanks!
    Wednesday, May 19, 2010 1:49 AM

All replies

  • you can specify SetPopulateTrackingTableDefault when you provision the scope to populate the tracking tables with pre-existing rows on the base tables.
    Wednesday, May 19, 2010 3:34 AM
    Moderator
  • Hi,

    If you use BCP to upload data to central server, the sync knowledge of server and client sync endpoints will still not know each other although they actually contain same set of changes. All changes will be synced to other side during the initial sync, and you will see many insert-insert conflicts.

    Because of this reason, you have to depend on sync to upload data. For timeout exception, I assume that you hit it when populating pre-existing rows to tracking table during database provision time. It is a design limitation in the Sync Framework 2.0 RTM. Please let me know if you hit timeout during sync time as well. In next release, we will allow users to set bigger timeout values. As one possible workaround, you may need to provision an empty client database, and gradually replicate rows into it from your existing client database and incrementally sync to the central server database.

    Thanks,
    Dong


    This posting is provided AS IS with no warranties, and confers no rights.
    Wednesday, May 19, 2010 4:30 AM
    Moderator
  • Hi,

    I encounter timeout exception when do init-sync, it's InsertCommand timeout, not at the database provision time. So I want to find a workaround to upload data in another way.

    Can I use BCP to upload data to central server, then run DBSyncProvion script to provision the server, then run PostRestoreFix to update metadata, then use sync framework to sync subsequent changes?

    Wednesday, May 19, 2010 5:45 AM
  • is the client data to server data initialization a one time activity or will it be regular scenario where you'll have clients with huge amount of data that needs to be uploaded every now and then?

    have you tried synching your SQL Express clients to a SQL Compact DB, uploading the SQL Compact DB then synching it to your Server?

    Wednesday, May 19, 2010 8:43 AM
    Moderator
  • Hi,

    It will not work, because all data uploaded by BCP will be treated as local changes on the central server. Even PostRestoreFix will not be helpful unless you directly restore the client database to the server side. For your scenario, you need to upload several client databases to server, and postRestoreFix is only good for one of them. You may want to either follow the workaround I mentioned in my previous email, or divide your big client database into multiple sync scopes with smaller set of data.

    I assume that you synced with batching. When you saw the InsertCommand timeout excepiton, do you know roughly how many batches have finished and what is the size of your batch? Also, what is the size of your client database?

    Thanks,
    Dong


    This posting is provided AS IS with no warranties, and confers no rights.
    Wednesday, May 19, 2010 4:51 PM
    Moderator
  • Yes, I'm use syncing with batching.  I just set localProvider.MemoryDataCacheSize = 20480, is this Ok? Each batch file generated by sync framwork

    is about 10M,  my client database has 10GB data, I got timout exception when upload a table with 2.38GB data, I found about 1.5GB batches data have been applied when got the exception.

    Thursday, May 20, 2010 12:51 AM
  • Hi,

    We didn't test with a table that has so many data. I will try to repro it and see if it is an defect in the database provider. To sync such a big table, a lot of disk spaces will be used for both batching files and database transaction logs, you disk still has enough spaces when hitting this timeout exception?

    To avoid this issue, please consider the workaround mentioned in my previous replies. For a big table, you can use sync filter to divide it into multiple sync scopes.

    Thanks,
    Dong


    This posting is provided AS IS with no warranties, and confers no rights.
    Thursday, May 20, 2010 6:47 AM
    Moderator
  • Hi,

    May I know the size of each row in that big table? I guess that your row size is big otherwise you will hit change enumeration command timeout first.

    Thanks,
    Dong

     


    This posting is provided AS IS with no warranties, and confers no rights.
    Thursday, May 20, 2010 7:12 PM
    Moderator
  • Row size of the table is not big, and my disk has enough spaces.

    Anyway let 2.38G data commit in one transaction is not a good idea, so I pick out the following script from provsion script generated by sync framework and run with my sync application alternately.

    The script populate tracking table for 200 existing records in base table each time.

    INSERT INTO [tXXX_tracking] ([PKColumns], , [create_scope_local_id], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone]) SELECT TOP 200 [base].[PKColumns], [base].[Domain], NULL, 0, @@DBTS+1, NULL, 0, 0 FROM [tXXX] [base] LEFT OUTER JOIN [tXXX_tracking] [side] ON [base].[PKColumns] = [side].[PKColumns] WHERE [side].[PKColumns] IS NULL

    I have another question, do you have a suggestion for the size of the batch file. How big batch file can get best performance when sync between two Sql server through Gigabit ethernet.

    Thanks.
    Friday, May 21, 2010 1:37 AM
  • Hi,

    With this custom script, you avoided timeout exception when provisioning your big database. If your row size is small such as 100 bytes, you will have about 24,000,000 rows in a table. When a upload sync starts, the client side will enumerate all changes. For 24,000,000 rows, the selection SQL command are expected to hit SqlCommand timeout exception. Since you didn't see it, I would like to know the size of your row.

    Also, depending on the number of rows, the tracking table size can be pretty big as well. If your table have millions of rows, the tracking table size can reach GB level. It means that you try to sync 4-5G data in one transaction (Collaboration providers in Sync Framework v2 RTM apply all changes in one transcation). I tried to sync a database with 3,000,000 rows and the row size is 100 bytes. Plus sync tracking data, my database size is more than 800MB. The sync succeeded with batch size as 10MB.

    The Batch size does not tightly depend on the network speed, the memory on your client and server machines are more important. If you have enough memory, bigger batch size should perform better.

    Thanks,

    Dong


    This posting is provided AS IS with no warranties, and confers no rights.
    Friday, May 21, 2010 7:48 PM
    Moderator
  • Hi

    I have about 1,730,000 rows in the table, total size 2.38GB, each row is about 1.4KB.  It got timeout exception when transfered about 1.7GB.

    Thanks.

    Saturday, May 22, 2010 5:28 AM
  • Hi,

    I tried another scenario that can roughly match your setting -- 3,000,000 rows in a table, size of each row is 1000 bytes, database size is about 3GB. I synced it with batch size as 10MB, and the sync successfully completed 10 hours later. I looked at the batch applied events, and there was no performance degradation during the whole sync session.

    It means that your failure maybe due to some sync unrelated issues. Can you consistently repro the failure in your scenario (If yes, did it always fail at the same spot - 1.7 GB)? Did you verify that the server didn't have any other DML activities when your hit the failure? Can you registry the sync provider events to see if you have a consistent sync progress speed?

    Thanks,
    Dong


    This posting is provided AS IS with no warranties, and confers no rights.
    Tuesday, May 25, 2010 1:27 AM
    Moderator
  • This issue can be reproduced many times, I only succeed uploaded once. and always fail at the nearly same spot,

    I use the following script to populate tracking table for 500000 existing records in base table once, and succeed uploaded the whole table with 173000 records in 1 hour.

    INSERT INTO [tXXX_tracking] ([PKColumns], , [create_scope_local_id], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone]) SELECT TOP 500000 [base].[PKColumns], [base].[Domain], NULL, 0, @@DBTS+1, NULL, 0, 0 FROM [tXXX] [base] LEFT OUTER JOIN [tXXX_tracking] [side] ON [base].[PKColumns] = [side].[PKColumns] WHERE [side].[PKColumns] IS NULL

    I noticed that my table has two nvarchar(64) fields as primary keys, that may reduce the performance, I will modify it.  Thanks you.

     

    Tuesday, May 25, 2010 11:40 AM