1

What I want is to be able to use Microsoft Sync Framework to sync two SQL 2008 databases that both use the SQL 2008 change tracking feature. One will be a server and the other will be a local SQLExpress instance, using a hub & spoke model.

There are some hints that others have accomplished this - namely Syncing SQL Server 2008 Databases over HTTP using WCF & Sync Framework. However, that solution requires "SqlExpressClientSyncProvider available from MSF's codeplex project site as Client Sync Provider". This same SqlExpressClientSyncProvider is hinted at elsewhere too. However, it either no longer exists or is hiding really well from my attempts to find it.

Most of the links and suggestions I've come across in my searching end up pointing to the MSDN samples site which has several examples (such as this one http://code.msdn.microsoft.com/Database-SyncSQL-Server-a9752fac) which all demonstrate using the sync framework via custom change auditing - which adds a lot of visible overhead to the schema such as tombstone tables, triggers, etc. This is what I want to avoid.

I'd be happy with a link to the bits for the SqlExpressClientSyncProvider, however, I suspect that it is built for the MSF 1.0 way of doing things. If there is a "MSF 2.1" way to do this via the SqlProvider classes or something along those lines, that would be great.

Community
  • 1
  • 1
davidpricedev
  • 2,107
  • 2
  • 20
  • 34
  • which method did you end up choosing to solve your issue? – Brent Jan 23 '14 at 15:49
  • @Brent, I ended up eventually going with a custom sync solution. The guids caused major performance issues and the amount of effort involved in getting MSF going was triple that of just writing my own. Studying how MSF worked did help me understand the problem better though. – davidpricedev Jan 23 '14 at 20:34

1 Answers1

1

SQL Server Change tracking is only supported in the older offline provider (SqlCeClientSyncProvider/DbServerSyncProvider) which is the same providers used by the Local Database Cache project item in Visual Studio.

the newer SqlSyncProvider/SqlCeSyncProvider uses a custom tracking mechanism and it will take a great amount of customization to get it to work with Sql Change Tracking short of writing your own custom provider.

the SqlExpressClientSyncProvider is a sample provider that has since been taken out by MS from the download site.

this link still has some bits of it though: http://www.8bit.rs/blog/2009/05/debugging-sql-express-client-sync-provider/

JuneT
  • 7,840
  • 2
  • 15
  • 14
  • 1
    So you're basically saying that even though Sql Server Change Tracking appears to be the better way MicroSoft has deprecated it with 2.1? Seems like the very limited examples out there seem to be missing parts and rely on the Sync 1.0 SyncAgent and SyncAdapter. Does the Sql Server Change Tracking allow dynamic schema changes? I have implemented 2.1 with traditional SyncOrchestrator method and Deprovision/Re-Provision functionality. However the database bloat from all the extra Tracking tables, etc. is an issue on SqlExpress on the tables we are syncing to. – Brent Jan 23 '14 at 15:48
  • That link is great, the only spot I could find any proper examples. However they only work bidirectional, and I now need to support different directions per table. – Brent Feb 19 '14 at 14:49
  • 1
    sync direction is at sync group or sync scope level. you should group tables with related sync direction in the same sync group otherwise, you will have to create separate sync group per table. – JuneT Feb 20 '14 at 01:03
  • re: SQL Change Tracking, it actually creates system tables, you just don't see it. likewise, coupling with SQL Change Tracking means you can't automatically support editions/flavors that dont support it (.e.g, SQL Azure) – JuneT Feb 20 '14 at 01:05
  • Thanks for the direction, I had multiple SyncDirection in one SyncGroup, however still can't get working. Poking more at it now. In the SqlExpressClientSyncProvider Does the Dictionary m_DownloadedTables; matter? I can fully remove it and server applied anchor and it works fine bidirectional. Only using Sql Server 2008/2012 [Express] – Brent Feb 20 '14 at 14:28
  • Do you know any examples of Sql CT that work with DownloadOnly or UploadOnly? I can't find anything online, and am unable to get anything but Bidirectional working. – Brent Feb 20 '14 at 18:46
  • not sure what error you're getting, but if Bidirectional is working, Download or Upload should work. internally Bidirectional does the same thing. – JuneT Feb 20 '14 at 23:32
  • Got It! Spent nearly a week just on getting other SyncDirections. I had to set SqlSyncAdapterBuilder.SyncDirection = SyncDirection.Bidirectional to generate all the commands. Then I can set for each SyncTable.SyncDirection to UploadOnly/DownloadOnly. – Brent Feb 21 '14 at 12:49
  • Just realized when I got multiple SyncDirections working my 3 sync groups were actually all based off the same string. Using multiple SyncGroups fails because of Foreign Key constraints between tables in different groups. The multi direction sync works fine with a single SyncGroup though. – Brent Feb 26 '14 at 20:27