Collaborative Synchronization - SQL Server Change Tracking

All replies

• 

  

  0

  Sign in to vote

  Hi,
  
  this link may shed some light on your questions:
  
  http://social.microsoft.com/Forums/en-US/uklaunch2007ado.net/thread/077ef3e2-31ff-4b4e-9954-16a26023c68b
  
  I've been researching on this without success. Started using SqlSyncProvider and learned, from the link above, that it cannot make use of integrated change tracking.
  
  Regards
  

  ---

  C# Devlpr, new to Biztalk 2006 R2.

  Tuesday, October 27, 2009 6:41 PM
• 

  

  0

  Sign in to vote

  Hi,
  
  SqlSyncProvider is a knowledge based provider and it doesn't support SQL Server Change Tracking yet. Decoupled side metadata table is used for change tracking. 
  
  There is no sample for SqlSyncProvider sync with SqlSyncProvider. You can take a look of the sample -- Database Sync: SQL Server and SQL Server Compact listed on http://code.msdn.microsoft.com/sync. It sync SqlSyncProvider with SqlCeSyncProvider. You can easily replace the SqlCeSyncProvider with SqlSyncProvider to match your scenario.
  
  Thanks,
  Dong
  
  

  ---

  This posting is provided AS IS with no warranties, and confers no rights.

  • Proposed as answer by Dong CaoMicrosoft employee, Moderator Tuesday, October 27, 2009 6:44 PM
  • Marked as answer by Garry English Wednesday, October 28, 2009 6:17 AM

  Tuesday, October 27, 2009 6:44 PM

  Moderator
• 

  

  0

  Sign in to vote

  Thanks a lot.
  
  May I ask, does it still need triggers? Is the code for the triggers automatically generated during initialization?
  
  Regards
  
  Miguel

  ---

  C# + BizTalk2009 Developer

  Tuesday, October 27, 2009 7:41 PM
• 

  

  0

  Sign in to vote

  Already found it out from the sample, serverConfig.Apply creates the required triggers also, e.g.
  
  CREATE TRIGGER [orders_insert_trigger] ON [orders] FOR INSERT AS
  UPDATE [side] SET [sync_row_is_tombstone] = 0, [local_update_peer_key] = 0, [restore_timestamp] = NULL, [update_scope_local_id] = NULL, [last_change_datetime] = GETDATE() FROM [orders_tracking] [side] JOIN INSERTED [i] ON [side].[order_id] = [i].[order_id]
  INSERT INTO [orders_tracking] ([i].[order_id], [create_scope_local_id], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime], [restore_timestamp]) SELECT [i].[order_id], NULL, 0, @@DBTS+1, NULL, 0, 0, GETDATE(), NULL FROM INSERTED [i] LEFT JOIN [orders_tracking] [side] ON [side].[order_id] = [i].[order_id] WHERE [side].[order_id] IS NULL
  
  
  

  ---

  C# + BizTalk2009 Developer

  Tuesday, October 27, 2009 8:48 PM
• 

  

  0

  Sign in to vote

  Thanks for everyone’s reply.

   

  The collaborative synchronization isn’t going to work for my needs.
  
  Here are the Collaborative Synchronization API “deal-breakers” for my application:

  ·         Doesn’t support the internal SQL Server Change Tracking.  I know that you can use the custom change tracking but I don’t like the idea of cluttering the database with additional columns, triggers and tables.  As well, the internal SQL Server Change tracking is more efficient.

  ·         Doesn’t support filtering.  Our application needs to filter the data sent to the client based on the active user, as there can be a lot of data in the server database and we only need to pull data what the user has access to.

   

  Because of the above points, I am going to go with an Offline scenario, that uses the custom SQL Server Express provider (SqlExpressClientSyncProvider).  This approach will satisfy the following offline requirements:

  ·         SQL Server Express as the Client database (using internal Change Tracking)

  ·         SQL Server as the Server database (using internal Change Tracking)

  ·         Ability to filter data that is sent to the client database

   

  Thanks again for your help,
  Garry

  Wednesday, October 28, 2009 6:30 AM