Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Questions sync master-db -> webserver-db -> n-clients-db RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hello,
    at first I have to apologize for my poor english, i'm a native german speaker...

    I have a few questions regarding the following scenario using SyncFx (2.1):
    There is a SQL Server 2008R2 database located in a company,
    a few tables should be synchronised with a SQL Server 2008R2 database running
    on a webserver.
    On the other side there a one or more tablet-pcs or nodebooks with windows7,
    having an application that uses the data synchronised with the data stored in the database on the webserver (via internet access).
    On this clients SQL Express 2008 or maybe SQL CE is in use.

    My questions:
    I have seen the SyncFx examples with n-tier synchronisation with SQL Server and SQL Server Express / SQL CE.
    This would be OK.
    May there be a conflict in the webserver database if it is synchronised from an application/service running
    at the company server, on the one side, and synchronised by the nodebook clients on the other?
    Or should ALL (on clients and the one at the company) applications should use the same scopes?

    Because the database at the company is given by an existing application, it would be very nice if the
    change tracking function could be used instead of the triggers in the original tables. This isn´t supported
    by the 2.1 providers as far as I have read.. Is there any chance to use that feature in a 1-1?

    This scenario is very close to the one shown in the "Introduction to Sync Framework Database Synchronization"
    at the Microsoft Sync Framework Developer Center.

    If more infos are nessesary or my questions are not precise enough please let me know.
    best regards,
    Siggi


    Monday, October 10, 2011 6:16 PM

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote

    Sql Change Tracking is only supported in the older offline providers (SqlCeClientSyncProvider/DbServerSyncProvider/SyncAgent). Note that the offline provider model OOTB only supports Sql Ce as the client.

    I would suggest you use the newer SqlSyncProvider/SqlCeSyncProvider/SyncOrchestrator). 

    Scopes are essentially just a grouping of objects you want to sync as a unit. In your scenario I would suggest using the same scope definition for all.

    You may run into problems having the same set of data belong to more than one scope. for example if you have TableX Row1 in Scope1 and Tablex Row1 in Scope2 again. The changes in Row1 by Scope1 will be detected as changes in Scope2 and changes in Row1 by Scope2 would be detected as a change by Scope1.

    Tuesday, October 11, 2011 2:09 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi JuneT, thank you for your reply.

    So I will check if it is OK to alter the existing database the way SyncFx would do.

    The synchronization between the company "master" database and the web-server database could be done in the same way as the notebook-clients will do, is that right? Maybe it could get a higher "priority", so that changes in the company database "win" over changes made by a client. And it would be interesting if its possible to sync a full table between company - web-server databases, but the clients only get a filtered set of rows (i.e. filtered by employee number or something) of that table.

     

    Tuesday, October 11, 2011 6:38 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    you can filter what goes to each client, but that means having a different scope for the client.

    so for on-premise to web db , you can have unfiltered.  web db  to client, filtered.

    now, you cant really assign priorities to changes, but you can dictate which row wins in case of a conflict (e.g., client updates row and on-premise updated the same row).

    i suggest you go thru the walkthroughs/tutorials in the documentation that gets installed with the SDK so you can evaluate/see how Sync Framework works.

     

    Tuesday, October 11, 2011 6:49 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    OK, thank you again, I will follow your suggestion..
    Tuesday, October 11, 2011 7:10 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    After all I wonder what exactly this part in the "Introduction to Sync Framework Database Synchronization" text means:

    > The Sync Framework database synchronization providers have been built to take advantage of SQL Server 2008 change tracking and provide the following advantages for an OCA environment:   -No schema changes are required to be able to track changes.
    -Triggers are not required for tracking changes, which means that tracking changes has far less of an impact on the server. In certain cases, the DML overhead associated with trigger based change tracking can be 400% greater than that of SQL Server 2008 change tracking. The overhead of enabling SQL Server 2008 change tracking is similar to the overhead of maintaining a second index.
    -All of the logic for tracking changes is internal to the SQL Server engine and as such reduces the complexity for setting up this type of system.
    -Data consistency issues associated with long running transactions are no longer an issue.
    -Includes integrated database administration feature such as Dynamic Management Views and Security.

    Wednesday, October 12, 2011 11:06 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    that refers to the built-in Sql Server Change Tracking feature.

    the Sql Server Change Tracking is only supported in the older offline providers SqlCeClientSyncProvider/DbServerSyncProvider (the one used by the Local Database Cache project item)

    Wednesday, October 12, 2011 12:13 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    OK, I understand that, but the text reads like all providers could use this feature out of the box.

    My problem is that I must sync the source database, maybe with a local copy of it on the same server instance, without changing it´s schema... any hints on that would be very helpful.

     



    • Edited by sullr Tuesday, October 18, 2011 7:53 AM
    Tuesday, October 18, 2011 7:53 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    as i have mentioned, SQL Change Tracking is only supported in the offline providers. the collaboration/peer-to-peer providers uses its own change tracking mechanism which requires creating additional database objects.

    the offline provider OOTB only supports SqlCe on the client. So if you want to sync SQL Server to SQL Server (nevermind that theyre in the same instance) using SQL Change Tracking, you have to write your custom provider to represent one of the SQL Server databases as a client.

    the newer providers SqlSyncProvider does not actually alter the table schemas per se. it simply adds triggers,  tables  and stored procedures to be able to track, select and apply changes.

    if you're just synching on the same instance,   have a look at SSIS or Replication or Database Mirroring as well.

    Tuesday, October 18, 2011 8:00 AM