locked
What is best sample for this scenario RRS feed

  • Question

  • I have a SQL Server 2008 database.  I have marked 11 tables as "Change Tracked"  I also have 100 laptops that clients run.  All these laptops have SQL Server 2008 Express.  The server database is updated via a web app.  The clients want to sync this server database to their laptops and later when there is no Internet connection access the local database via a Winforms app.  Synching needs to fetch only new or changed data from the server to the client

    I am very confused about change tracking (using tracking tables, built in SQL custom etc) also I am not clear of the offline or collaboration which is best in this scenario.  I have seen code that provisions server and that creates triggers etc even though SQL server is supposed to have built in change tracking???  Is there a sample code that would work in my scenario and I would be very grateful if someone can clear up my confusion and direct me to the path please

    Cheers
    H

    Tuesday, January 3, 2012 7:58 PM

Answers

  • that makes sense. if you are sure no changes has occured on the server and you're not restoring a version of the server database that is older than the last copy that the clients has synched with, i think its ok.
    • Marked as answer by Yunwen Bai Tuesday, January 17, 2012 9:47 PM
    Tuesday, January 17, 2012 1:48 AM

All replies

  • Hi there,

    Sync Frameword does not support Sql Change tracking.  It uses separate mechanisms (triggers, tracking tables) to do change tracking.  There are many samples around this on Code Gallery, one of which is here:

    http://code.msdn.microsoft.com/Database-Sync-SQL-Server-de6c8ff8

     

    -Jesse

    Wednesday, January 4, 2012 7:25 PM
  • There are two types of database providers in Sync Framework, the so called offline providers (SqlCeClientSyncProvider/DbServerSyncProvider) and the collaboration/peer-to-peer providers (SqlCeSyncProvider/SqlSyncProvider).

    It's the offline provider that supports Sql Server Change Tracking on the server side, however only Sql Ce is supported in the client side OOTB. The other type of provider uses its own change tracking mechanism.

    In your case where you have Sql Express in the client, i suggest you use the SqlSyncProvider, you will find many samples/walkthroughs in the Sync Framework documentation itself.

    Friday, January 6, 2012 4:57 AM
  • Many thanks to you both Jesse and June.  I have a working solution now usiong SqlSyncProvider as suggested by June.  If the server database is backed up and then restored, do i need to do anything?  I heard about postrestorefix but I am not sure if it applies to my scenario

     

    Cheers
    H

    Monday, January 9, 2012 10:54 AM
  • if you restore from backup, do a performpostrestorefixup
    Monday, January 9, 2012 12:36 PM
  • Thanks June.

    The database is on client's server environment, which I don't have access to after application deployment.  Where do you think Microsoft would like us to keep this code? - obvisouly not in the client.  I am running a 2 tier architecture

     


    Hassan
    Monday, January 9, 2012 5:32 PM
  • i'd create a separate utility that your DBA can run to do post restore fixup when they do restore.
    Tuesday, January 10, 2012 2:31 AM
  • That makes sense, thanks.


    Hassan
    Tuesday, January 10, 2012 1:49 PM
  • I had a thought about this and question whether a performpostrestorefixup is required in my case.

    We make changes to the database from time to time.  It could be just data (look up tables etc) or schema changes for parts of the database that are not in the sync scope.  Our clients do a download only from server.  I am wondering if performpostrestorefixup is/is not required in this scenario.  Only a web application updates the database and during the "backup, apply changes, restore" operation the web application is stopped, so no updates are possible.  A clarification on this would be much appreciated.

    Cheers
    H


    Hassan
    Monday, January 16, 2012 11:13 AM
  • you only need to do a performpostrestorefixup  if you're restoring from a SQL server database backup.

    from the docs:

    Updating Metadata after Restoring a Server

    For every change that occurs in a table, Sync Framework updates metadata on the server to identify the time and origin of the change. This metadata is used by other nodes to determine which rows each node requires from the server. If changes occur at the server after a backup and those changes are propagated to other nodes before a restore, the other nodes will contain metadata for each of the changed rows. After the server is restored from the backup, subsequent changes on the server might be assigned identifiers that collide with ones that the other nodes already contain. This can cause conflicts during synchronization and possible non-convergence.

    Monday, January 16, 2012 11:42 AM
  • June,

    No change is possible in our scenario.  If clients sync it will be download only.  We only update the database via a web app which will be taken offline when changes are to be made to the database manually.  We might directly change database which I think doesn't require performpostrestorefixup or we might backup, make changes then restore - which I still think shouldn't require performpostrestorefixup as the clients next sync will simply get the new data (of tables in the scope) if any. 


    Hassan
    Monday, January 16, 2012 1:33 PM
  • that makes sense. if you are sure no changes has occured on the server and you're not restoring a version of the server database that is older than the last copy that the clients has synched with, i think its ok.
    • Marked as answer by Yunwen Bai Tuesday, January 17, 2012 9:47 PM
    Tuesday, January 17, 2012 1:48 AM
  • Thank you June


    Hassan
    Tuesday, January 17, 2012 10:25 AM