none
SqlSyncScopeProvisioning Filterclause in Sync Framework RRS feed

  • Question

  • Hi,

    We are trying to Provision and Sync a 12 GB table from SQL Server to SQL Database using the Sync Framework. However, because the Provisioning step is running
    forever, we decided to reduce the dataset to half (6 GB) that would be used for Sync purposes. So, the source table would still have 12GB of data but we would like to Setup and Sync only 6GB. We are trying to use the SqlSyncScopeProvisioning Filterclause to achieve the same. Here is the code snippet we have our question about.

    SqlSyncScopeProvisioning
    sqlServerProv = new SqlSyncScopeProvisioning(sqlServerConn, myScope);
    sqlServerProv.CommandTimeout = 60 * 60;
    sqlServerProv.Tables["RejectHistory"].AddFilterColumn("UpdateDate");
    sqlServerProv.Tables["RejectHistory"].FilterClause = "[side].[UpdateDate] > '2012-06-01'";

    Should we use the [side] as table alias to refer the tracking table or use the [base] to refer the source table so that we can reduce the dataset and thereby increase
    our Provisioning speed? Any other suggestions / thoughts to improve our Provisioning speed would also be greatly appreciated.

    Thanks  


    • Edited by CalvinTS Monday, January 28, 2013 7:32 PM
    Monday, January 28, 2013 7:31 PM

Answers

  • you should always filter on the side table. 

    having said that, i don't think filters apply during provisioning. filters get applied during change enumeration.

    if you're having issues provisioning, i suggest you script out the provisioning. when you script it out, you will find the T/SQL that populates the tracking tables. you can then run it manually yourself and batch them.

    • Marked as answer by CalvinTS Wednesday, February 13, 2013 7:21 PM
    Tuesday, January 29, 2013 1:26 AM
    Moderator

All replies

  • you should always filter on the side table. 

    having said that, i don't think filters apply during provisioning. filters get applied during change enumeration.

    if you're having issues provisioning, i suggest you script out the provisioning. when you script it out, you will find the T/SQL that populates the tracking tables. you can then run it manually yourself and batch them.

    • Marked as answer by CalvinTS Wednesday, February 13, 2013 7:21 PM
    Tuesday, January 29, 2013 1:26 AM
    Moderator
  • Thanks June for the suggestion. I understand that I could script out the Setup / Provisioning process itself. However, can I script out the Sync as well. Because the initial sync for this huge table is taking forever.
    Wednesday, January 30, 2013 6:02 PM
  • unfortunately, you cant script the actual synchronization.

    if the target is another SQL Server or SQL Database, you can do a back-up of the previously provisioned database and restore it on the target.

    when you restore the database, do a PerformPostRestoreFixup on the restored database before the first sync. just lookup the docs on how to call PerformPostRestoreFixup

    Thursday, January 31, 2013 1:11 AM
    Moderator
  • Hi June & Calvin,

    Another thing you can do instead of the backup/restore on the destination server if it is SQL server is to do bulk inserts making sure that you enable triggers during the load. There are several ways you can do that (the bulk inserts, that is).

    We do this all the time to load multi-GB databases during initialization prior to the subsequent on-going synchronization of data.

    Steve

    Thursday, February 14, 2013 1:45 PM