Sync SQL Server 2008 R2 with multiple client SQL 2008 R2 RRS feed

  • Question

  • Hi,

    I am a developer and wanted to use the Sync framework for our application.

    Here is my scenario:

    I have a Server ( running SQL 2008 R2) and multiple clients ( remote machine running SQL 2008 R2). Some of the tables from the Db needs to be always synched. At startup Server and clients db is synched up ( not using the sync framework). When the clients can no longer talk to server. The client will modifying the local db ( add/ insert /update/ delete). Multiple client can potentially modify the same record. I can implement a time stamp column in all tables that can help me resolve the conflict or sync based on the time stamp value. All these client can start syncing the data in the background.I need a way to sync the delta at a regular interval.

    I wanted to know if sync framework supports these scenario?

    I need to know if there is any example that I can use it?

    Do I need to cleanup the data after every successful sync to get only the delta for sync?

    Wednesday, August 15, 2012 4:19 PM

All replies

  • you will encounter issues if you pre-populate the databases outside of sync fx. if you prepopulate two databases for example, even though they may have the same data, sync fx has no idea about this since they were populated before sync fx was setup. so on first sync, you will run into conflicts as it tries to apply rows from one db to the other.

    date or timestamps on the tables is not enough to enable change tracking and enumeration.

    to save yourself some trouble, just follow the walkthrough in the documentation: Tutorial: Synchronizing SQL Server and SQL Express

    that should work for sql server to sql server, sql server to sql express, express to azure or server to azure scenarios.

    Thursday, August 16, 2012 10:27 AM
  • Hi ,

    Thanks for the reply.

    Is there a way to cleanup all the tracking data? For example: In the normal scenario, all the clients will keep continuing with copying the server's changes ( through its own mechanism and not using sync framework) but when it first gets disconnected, it will clear up any tracking that that it has and start fresh.

    I was trying to look at the MetaData cleanup and retention in Days =0. But after this successful cleanup I see the tracking table still has the inserted record in it.

    Moreover, I tried to look at the sample. I would like to know if there is any example that does periodic sync with PK-FK relationship. OR an example that will show how to tweak where condition for Sync.

    Sample  application :

    Server and  2 Clients both operating SQL 2008 and both are provisioned for sync.

    Server 1 adds a user 1 and updates user 2 at time T1

    Client 1 adds user 3 and update user 2 at time T2

    Client 2 deletes user 2 and update user 3 at time T3.

    Client 1 or Client 2 can sync first ( upload only). But as far as resolution is concern , the coinages should be applied as last action wins.

    After successful sync, the Client can clean up its change data. And can even perform the sync at regular interval.

    Thursday, August 16, 2012 7:46 PM
  • metadata data cleanup is for cleaning up tombstone records or metadata for deleted rows.

    if you already have a mechanism for copying server changes not using sync framework, why would you want to put sync framework still?

    as i have mentioned, on initial sync, you will encounter issues if both databases contain the same set of data. Sync framework has no idea and will never have any idea they are the same.

    likewise, sync fx is about incremental changes: what has changed since the last sync. there has to be a point of reference of the last sync.
    its not about doing differential sync of figuring out what differs between two copies.

    the tutorial i pointed to you above has examples on filtering as well.

    for PK-FK relations, make sure you sync the parent table first.

    in your conflict resolution scenario, sync fx will not apply changes based on the time they were made. the sync for client 1 to server is totally independent of  client 2. in your scenario, if you specify client wins, the client 2 changes will be the one applied. if you reverse the order and sync client 2 first, then the changes applied by client one will be the one persisted.

    Friday, August 17, 2012 3:01 AM
  • Hi Thanks for the reply.

    At present the application is handling one way sync. We have already implemented a mechanism where we creates a copy of the required tables and send to the clients.  So for example I have 30 tables that server is sending it to client at every 10 minutes.So basically all the clients will always be in the sync with the server during normal operation.

    Application requires that these 30 tables on Db server and clients has the same exact data.

    For some reason, when the client is not able to connect to server then it stores the data to its local db. Now each client changes ( 10 tables only with max 5K  records) needs to be synched back to server.For this reason , I am trying to explore the sync framework.What if Server can provide a last sync pointer? Is is possible to start tracking after that pointer or delete the old tracking information ie older than this tracking point)?

    Moreover the conflict resolution can not be based on the last / first one to sync. We need to implement this conflict resolution / chnages to be applied based on LAST ACTION ie time stamp. SO I was assuming if it possible to group the PK -FK tables together and apply changes based on the time stamp.

    Friday, August 17, 2012 7:42 PM
  • to use sync framework, you setup the replica's to participate in the sync community. this setup requires provisioning to create the necessary infrastructure to track, enumerate and apply changes (stored procs, triggers, tracking tables, etc...).

    if you dont setup sync framework initially and you have exact copies on both client and server and decided to use sync fx when client is offline, you need to provision at that time. since sync fx was not setup before, how does it know what was changed in the client?

    regarding your conflict resolution, assuming client 1 syncs first before client 2, how does it know it  should not persist because client 2 is the more recent record? how do you know which one is the LAST ACTION? do you query client 2 before applying client 1? so you will have to query all clients then.

    when you're synching, the sync is happening between the client and the server. data in client 2 or other clients is not visible. the comparison between records is between whats in the client vs whats in the server.

    Monday, August 20, 2012 3:57 AM
  • Hi June T,

    Sorry I did not mention the conflict scenario clearly. What I meant by last action wins rule is: When uploading the clients data on server, if the server has the record with high time stamp then server wins else client wins. That way the order in which the client  sync does not really matter.

    We can setup the sync framework initially, but the clients tables gets overwritten with server's copy at a regular interval ( when client is connected to server). So is it possible to re-provision the client during disconnected mode?  Or is it possible to  clear all tracking data from the client and always sync from the client ( upload to server) as if it was never got synched before? I presume, we need to cleanup something on the server so that it does not ignore the changes as if it is already synched. Something similar to reinitializing the client database and provisioning it again for sync.

    Other question: is there a way to provide custom name to the tracking SP? apart from the object prefix and Schema? As I need to use the schema but I do not want these schema in my SP. For SP we would like to use the name as Prefix.SPName and for table _schema.TableName.

    another random question: how does the server's tracking data gets cleanup? What I understand from your previous reply is "metadata data cleanup is only for the deleted records" So If I specify the retention in Days as 2. So Any data changes that is 2 days old gets cleaned up? So what happens if I specify 0 here?

    • Edited by BabiDesai Tuesday, August 21, 2012 11:36 PM more information added
    Monday, August 20, 2012 1:57 PM
  • another random question: how does the server's tracking data gets cleanup? What I understand from your previous reply is "metadata data cleanup is only for the deleted records" So If I specify the retention in Days as 2. So Any data changes that is 2 days old gets cleaned up? So what happens if I specify 0 here?

    The metadata cleanup, as June stated, is for deleted records only (also explained by MSFT here). That means if you have 200 records and you delete 100 of them, then there will be 200 tracking records in the metadata and 100 of them will represent the deleted records (or tombstones). When you do the metadata cleanup, it will remove only those tombstones. Which ones are removed will be determined by the retention days.

    Retaining 0 days will mean that all tombstones are removed from the tracking/metadata.

    Monday, August 20, 2012 6:37 PM