locked
Some basic questions... RRS feed

  • Question

  • I'm debating on whether to go with SQL Server merge replication or to use the new ADO.NET Sync Services.  (It's a future project with no deadline, so I can test with the CTP until it's released.)  The server will be some version of SQL Server 2005.  The client will be SQL Compact.

    1.  Tracking changes on the client (SQL Compact):  How is this done, since you can't write triggers in SQLCE?  Must the client application maintain this?

    2.  Schema changes:  Merge replication seems to handle this pretty well in my experience.  Will the ADO.NET Sync Services have a similar feature?

    3.  Merge replication requires a paid-for version of SQL Server to be the server/publisher.  If I understand ADO.NET Sync Services correctly, I could use SQL Express as the server?  (This is assuming I'm OK with the general limitations of Express, of course.)

    4.  On the server, I'd just need to add a few triggers to make ADO.NET Sync Services work, so I wouldn't need to be a sysadmin at the server level (like I need to be with merge replication)?

    5.  Data partitioning with ADO.NET Sync Services:  Could I setup some users to only be able to sync to their filtered view of the data?

    --Troy

    • Moved by Max Wang_1983 Friday, April 22, 2011 11:26 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Monday, January 29, 2007 8:39 PM

Answers

  •  

    Regarding Schema Changes; you can build your custom logic to achieve that; there are few issues to consider though:

    - On the server: the framework does not detect if you made a schema changes on the server database or not, it just executes the set of commands on the sync adapter. So for any schema changes, you have to update the corresponding SyncAdapter command to add a column, delete a column …etc

    - On the client: Your custom needs to check for schema changes with the server prior to kicking off the sync.

    - As for the client updates prior to schema changes, you can set the sync direction to "UploadOnly" to pass existing changes to the server first. Or you can call GetChanges() method on the agent to obtain all pending local changes and apply them on the server through ApplyChanges()

    Obviously, you need to do a little bit of work outside of the framework to sync schema changes :)

    Tuesday, January 30, 2007 4:54 PM

All replies


  • Quick answers to your questions:

    1. We made several changes to SQLCE 3.5 engine to support this scenario. The sync code on the client is very limited, check out the demo apps.
    2. No. Not in this version.
    3. Yes, you could build all-free solution with SQLCE and Express
    J
    4. Yes.
    5. Yes, your query on the server could deploy some of the information that your client can set. During sync time, the client parameters will be shipped to the server and substituted in your query.

    Monday, January 29, 2007 10:22 PM
  • 1.  I'll look at the demo to determine if any code for all the change tracking "triggers" is required on the client app to support bi-directional.

    2.  Schema Changes:  But could I handle the database schema versioning in my code, and not require the subscribers to have to reinitialize?  And could I do this in such a way that I wouldn't lose any changes on the client side from the last sync (similar to the "Upload changes" feature of merge replication reinitialize subscription)?

    3.  Awesome!

    4.  Awesome!  (For shared hosting scenarios)

    5.  This sounds like just what I need.  Although it sounds like a little extra work needs to be added in my code to ensure that only the properly authorized clients can assert their desired parameters for filtering.  The open architecture would probably make this very straight forward.

    --Troy

    Tuesday, January 30, 2007 1:22 AM
  •  

    Regarding Schema Changes; you can build your custom logic to achieve that; there are few issues to consider though:

    - On the server: the framework does not detect if you made a schema changes on the server database or not, it just executes the set of commands on the sync adapter. So for any schema changes, you have to update the corresponding SyncAdapter command to add a column, delete a column …etc

    - On the client: Your custom needs to check for schema changes with the server prior to kicking off the sync.

    - As for the client updates prior to schema changes, you can set the sync direction to "UploadOnly" to pass existing changes to the server first. Or you can call GetChanges() method on the agent to obtain all pending local changes and apply them on the server through ApplyChanges()

    Obviously, you need to do a little bit of work outside of the framework to sync schema changes :)

    Tuesday, January 30, 2007 4:54 PM