Answered by:
Synchronization between several SQL Server 2008 nodes using Change Tracking

Question
-
Dear All,
I am trying to use MS Sync Framework to establish a bunch of synchronized SQL Server 2008 nodes using Change Tracking feature.
The scenario is upload only not bidirectional so I almost don't need the conflict management.
By the way the reason that I am trying to use the Change Tracking is to have less impact on my Data Base current design.
I have some question regarding this :
1- What is the best provider that I can use for this scenario ? (I thought SqlSyncProvider is the best for SQL Server 2008 synchronization but as far as I know it is not yet supporting the Change Tracking feature).
2- By using the Change Tracking feature do I still need to define Synchronization Knowledge Metadata (Table Based or Scope Based) in my DB or the Change Tracking data (e.g. ChangeTable , ...) includes enough information to make it work !
3- While I am monitoring the changes on one SQL DB and Synchronizing another SQL DB, Do I need to install the Syn Framework on the target machine ?
Looking forward to your responses
Sincerely,
Wednesday, March 31, 2010 7:25 AM
Answers
-
There is a really helpful thread in this address regarding my problem which is using SQLExpressSyncProvider and ChangeTracking :
http://social.microsoft.com/Forums/en/uklaunch2007ado.net/thread/27e43dc3-e271-4b3d-bf0a-0024e739ffef
Although it has problem in large number of changes and requires to be combined with Batching methods to work properly.- Marked as answer by Mohsen Akhavan Nia Tuesday, May 11, 2010 5:26 AM
Tuesday, May 11, 2010 5:24 AM
All replies
-
1- The SQLSyncProvider doesnt support out of the box SQL Change Tracking. The offline providers do. But the problem is that the offline scenario doesnt have an out of the box SQL Client Sync provider. There is however a sample SQLExpressClientSyncProvider at http://code.msdn.microsoft.com/sync/Release/ProjectReleases.aspx?ReleaseId=1200 that you can use.
2- Again, the collaboration providers whose synchronization tracking is based on Knowledge Metadata doesnt support SQL Change Tracking out of the box.
If you go for the collaboration providers which adds some database objects for change tracking and metadata, the Knowledge Metadata is taken care of by Sync Framework. You pretty much just define the scope (which columns, table, filter), provision and then sync.
If you use the offline provider (the sample provider i mentioned above), you still have to create again some tables and objects in the database.
Change Tracking only tells Sync Framework what rows were added/delete/changed, the anchor based metadata for offline providers or knowledge based metadata for collaboration providers takes care of what was sent and what was received.
3- For an n-tier solution (with WCF), you will need Sync Fx on both ends. If you have a client that could establish a direct connection to the remote database, then you only need SyncFx on the client side or where your're initiating the sync.
hth
Wednesday, March 31, 2010 10:46 AM -
There is a really helpful thread in this address regarding my problem which is using SQLExpressSyncProvider and ChangeTracking :
http://social.microsoft.com/Forums/en/uklaunch2007ado.net/thread/27e43dc3-e271-4b3d-bf0a-0024e739ffef
Although it has problem in large number of changes and requires to be combined with Batching methods to work properly.- Marked as answer by Mohsen Akhavan Nia Tuesday, May 11, 2010 5:26 AM
Tuesday, May 11, 2010 5:24 AM