Monday, January 28, 2013 7:31 PM
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.
sqlServerProv = new SqlSyncScopeProvisioning(sqlServerConn, myScope);
sqlServerProv.CommandTimeout = 60 * 60;
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.
- Edited by CalvinTS Monday, January 28, 2013 7:32 PM
Tuesday, January 29, 2013 1:26 AMModerator
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
Wednesday, January 30, 2013 6:02 PMThanks 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.
Thursday, January 31, 2013 1:11 AMModerator
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, February 14, 2013 1:45 PM
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.