4

I have the pleasure of using SyncFx v.2.1 on an application. The client side presently uses SQLCE and the Server side uses Server 2008 r2. I am using a SyncFx proxy and host the server SyncAdapterBuilder code in the WCF service. The client has the SyncAgent and SyncTables and it works fine. I am using the integrated SQL Change Tracking in lieu of the coupled (aka custom / scoped) change tracking because I am not permitted to modify existing schema.

So my issue is that the requirements for the system have changed and I am required to use SQL Express on the client in order to support stored procedures.

Why not merge replication? The requirements also prohibit modification of the schema or the use of triggers. In fact the original version of the app used merge replication with SQLCE before moving to SyncFx for SQLCE.

So how is this done? I've read a lot of conflicting information and I can only assume that this is in response to the ever evolving versions of SyncFx. There are no direct example of how SQL to SQL Express with Change Tracking on both is accomplished. Plus I am trying to transition from a functional SQLCE implementation to Express with as few changes as possible. The client is already capable of using either type of DB, it is just the current sync process that needs to change.

Here is what I've found, but have not had success. I've read every StackOverflow response on the matter and am still not finding a way to do this that actually works.

  1. Database Sync:SQL Server and SQL Express N-Tier with WCF : This MS example works fine with the SyncOrchistrator but provisions side tracking tables and triggers. I was not able to modify this in such a way that change tracking could be used on the client and server.
  2. Sync framework with SQL Server 2008 Change Tracking : StephaneT suggests here that simply by using the normal SQLCE approach with the SQL Express sample sync provider and SyncFx 2.0 techniques only client side table modification would be required. Unfortunately all links to this sample SQL Express provider seem to be removed and other posts from JuneT and even Liam Cavanagh on MSDN suggest moving forward with the new official SqlServerProvider instead of a customized version of the DbServerProvider. Problem is there are no sample implementations of this anywhere and I haven't been able to figure it out through trial and error.
  3. Syncing SQL Server 2008 Databases over HTTP using WCF & Sync Framework : Raj gives the best example (simple and easily translated to SQLCE processes) unfortunately it also uses the SqlExpressClientSyncProvider that seems to have evaporated from the internet. It also requires an anchor table to track the clients, I think I can get away with that as I am not allowed to modify schema on "existing" tables.

So any examples out there that can help me. Essentially I want to port the existing functioning SQLCE SyncFx via proxy with integrated SQL Change Tracking using a SyncAgent to a version that works for SQL Express without changing existing scheama or using triggers. I should also mention that I use filter parameters heavily as there are 150+ tables in the replication and they would be extremely large without filters. I had read some references that said the SqlExpressClientSyncProvider didn't support filters, but this is impossible for me to verify since I can't find a reference to that code that is still good.

Maybe there is a refresh of Raj's example that uses SqlServerSyncProvider

Thanks in advance to anyone that can point me in the right direction!

Community
  • 1
  • 1
opherko
  • 141
  • 3
  • 6

1 Answers1

2

check out this link and you might still find some of the download links in the comments area working: http://www.8bit.rs/blog/2009/05/debugging-sql-express-client-sync-provider/

take note that even the sample SqlExpressClientSyncProvider uses triggers to track the deletes in the tombstone tables. likewise, you need to have columns in your table to track when a row has been inserted or updated (datetime or timestamp columns).

with regards to filtering, you can easily modify the queries in the adapter to include a filter clause.

the newer SqlSyncProvider does not support Sql Change Tracking as it implements its own tracking mechanism. the newer providers works in a peer-to-peer scenario so its tracking as well from which replica a particular change has come from.

JuneT
  • 7,840
  • 2
  • 15
  • 14
  • 1
    I see the source in your provided link. I'll play with this some before I sign off on this as a best answer. I continue to be amazed by the fact that essentially the answer is "No, you can't use change tracking on an Express client with SyncFx, it isn't supported." All my tables have columns that can be used for CRUD tracking. – opherko Aug 15 '11 at 01:40
  • Is there a reference to the schema for the anchor and guid table anywhere? I see that anchor should have a TableName column and guid should have a SentAnchor column per the class methods, but I am sure there are likely other columns necessary. Interestingly I have constructed my AdapterBuilder to use a custom table to get the direction and filter information, but perhaps I can do both with a single table if I know a bit more about these tables. It also appears that the SqlExpressClientSyncProvider does not create schema on the client at initialization. The sample doesnt implement CreateSchema. – opherko Aug 16 '11 at 15:38
  • the sample SqlExpressClientSyncProvider has some scripts to create the anchor and tombstone tables. and yes, it does not automatically provision the database objects for synchronization. the anchor tables should have at least the table name, sent anchor and received anchor. the tombstone table at a minimum should have the pk of the deleted row and a datetime or timestamp to indicate when it was deleted so you can compare it with the anchors. – JuneT Aug 17 '11 at 02:31
  • 1
    Thanks I found a variation of the Sample project in one of the comments in the blog, previously I was just using the source code link for the provider class. Now that I have the sample project, what Raj offers in my 3rd link makes a lot of sense. Thanks for pointing me in the correct direction. Hopefully someday there will be a proper Client Sync Provider for Express. Having to provision the client and using Adapters on the client just seems very awkward. – opherko Aug 18 '11 at 01:52
  • Well, there is, if you're willing to switch to the newer SqlSyncProvider which works with Express, Server and Azure editions of Sql Server and works not just on hub/spoke but on peer to peer topologies as well. caveat is its not using Sql Change Tracking. – JuneT Aug 18 '11 at 03:21
  • Thanks SqlSyncProvider and SyncOrchestrator was my second iteration of SyncFx for this project. It was shot down because of NOT using change tracking. Personally I think SyncOrchestrator works very well. If it supported SQL Change Tracking I'd be set. – opherko Aug 18 '11 at 14:35