locked
Whether and how to use the Sync Framework for a 'Sql Server' to 'Sql Server' Client-Server Scenario RRS feed

  • Question

  • I'm currently looking at Sync Framework & Sql Server Replication to decide whether one of them fits our scenario or whether we should build our own Synchronization.

    Scenario:

    • The software is sold, not an in-house thing.
    • Client-Server (Offline Scenario)
    • Server: SQL Server. The express Edition should be possible for smaller customers
    • Client: SQL Server Express. Will be used to allow offline-work on laptops.

    Key points:

    • There are small customers with 200mb database and no IT-guy as well as bigger ones with databases with more than 20 GB
    • Sync must allow filtering. For some tables this will be a rather complex join based on our permission system
    • All tables have an [uniqueidentifier] primary key.
    • Some master data tables have an auto-increment index that is used as a foreign key. These tables should only be synced in one direction: from server to client.
    • We heavily utilize foreign keys constraints in our database. In some places even self-referencing foreign keys.

    So...

    Is it a good idea to do this with sync framework? From what i tried, SQL Server can only be used in Collaboration scenario (SqlSyncProvider & SqlSyncScopeProvisioning..) so is trying to abuse it for a client-server scenario going to work? Are there any other deal-breakers i'm not aware of? Can Sync Framewok handle auto increment IDENTITY INSERT? Or do i have to write my own complex SyncProvider which would probably be more work than writing a complete custom syncronization.

    SQL Server change tracking seems not to be supported by sync framework, too. I hoped it would since i guess it's more reliable and faster than massive amounts of triggers.

    (I tried out SQL Server replication. Seems nice but it always requires a Standard Edition on server-side and a replication is easily created. However it may not be that customizable and seems more meant for inhouse solutions than embedding in a program.)

     

    Any advise is very much appreciated!

    Thanks,
    Daniel

     

     

    • Edited by Daniel K. IW Thursday, June 24, 2010 12:28 PM i forgot to mention self-referencing foreign keys
    Thursday, June 24, 2010 12:17 PM

All replies

  • you might want to check this out for a quick comparison between SQL Server merge replication and Sync Fx: http://blogs.msdn.com/b/sqlservercompact/archive/2009/11/09/merge-replication-vs-sync-services-for-compact.aspx

    also, afaik, prior to SQL Express R2, the maximum DB size is just 4GB which was increased to 10GB in the R2 release.

    SQL Server is supported in both offline and collaboration providers. It is SQL Express which doesnt have an official client provider for the offline scenario.

    SQL Change Tracking is also not supported in the collaboration providers, only by the offline providers.

    you would still benefit a lot by building your custom providers on Sync Fx than building a complete custom sync from scratch. For one, Sync Fx provides you with base framework and interfaces already and you only need to worry about the providers.

    you may check out "Selecting an Appropriate Primary Key for a Distributed Environment" in the documentation around guidelines for using Identity and Guids as keys.

    Thursday, June 24, 2010 1:32 PM
  • It's a product already delivered and the database is generally backward-compatible, so adding stuff is ok, but breaking alterations (especially disabling AutoIncrement ID) aren't. I hope it's customizable enough to get it to work with identity insert. Btw: The guys with the > 20GB database can afford a SQL Server Standard on the server  :-) Client sync has to be filtered down for them, of course.

    Most stuff i've seen so far has something to do with SQL Server CE on the client. It would be great if there was an offical example app of how to basically do with Sync Framework what the SQL Server Replication gets done easily. Sync everything, Client-Server.

    However i think i am am going to choose the Sync Framework. At least if I positivly find out I can make the identity insert work by *customizing* SqlSyncProvider. I very much prefer a solid .NET solution that works with any SQL Server... if it can get it to work.

    Thanks,

    Daniel 

    Friday, June 25, 2010 12:25 PM
  • you can find samples here: http://code.msdn.microsoft.com/sync

    one of your best option to cater to identity columns is to segment the identity range (e.g., client A gets identity values 1-10000, client B, 10001 to 20000, and so on). check out the discussions around identity here: http://social.microsoft.com/Forums/en-US/syncdevdiscussions/thread/175d48f6-b083-440c-bb7f-4cf8b6d6b70a

    Friday, June 25, 2010 3:28 PM