Thursday, March 08, 2007 8:38 AM
Firstly, just to say that I'm pleased to have found Sync Services in the past few weeks as we were about to start implementing something in-house to perform a similar task. The Sync Services code in demo 3 seems to meet our needs very well. We need to sync data to and from a smart client app over web services which authenticate against an ASP.NET Membership database. Most of the reference data is modified on the server and one way synched to the client, but transactional data can be created on both sides which is what my question is about.
What is the recommended method for managing primary key values when using [bi-directional] sync services?
I see in the demos and docs that a ConflictType of ClientInsertServerInsert will detect identical PK values that have been created on the client and server, but for us this will be too frequent. In standard replication, the technique is to assign ranges of values to each client subscription but given there is no support for IDENTITY_INSERT or DBCC in SQL Server Compact Edition, it doesn't seem that manually controlling these ranges when using Sync Services is a sensible/feasible method.
Is it simply a matter of using UniqueIdentifier (GUID) columns as the PK type on both client and server? Whilst this would mean modifying our tables on the server, this could be done if necessary. However, since GUIDs are larger than our current Int identifiers, the PK indexes are going to be larger and slower.
Any advice/suggestions would be appreciated.
- Moved by Max Wang_Chinasoft Friday, April 22, 2011 11:12 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
Thursday, March 08, 2007 4:42 PM
It seems to me that you are using identity column as your PK. This is a rather common scenario and the sync services currently provide a mechanism to address this issue.
Recall that in the demo the client schema was brought down from the server during initial sync. This process called “Schema Initialization”. Table schema is generated automatically for you by the ServerSyncProvider.
The SyncSchema type contains the schema of all tables to create on the client. It is basically a dataset that is extended to address some of the sync needs. One of those needs is the identity column management. Through the schema object you can identify a column as DataColumn.AutoIncrement; in addition you can specify the DataColumn.AutoIncrementSeed and DataColumn.AutoIncrementStep. By using these column properties you can assign a range for every client and build your own identity management logic.
So before the GetSchema() method on the server returns to the client, you can update the schema object with the desired range for a given client.
The other option which is a lot simpler to implement is to avoid PK collusion by using GUID as a PK as you pointed out.
I should say that we are looking into new techniques to address this issue in future versions; but nothing to disclose at this point.
Monday, March 12, 2007 3:58 PM
Thanks for your reply and suggestions.
For various reasons, not least because downloading the schema doesn't create the FK constraints, we're synchronising with databases that have already been created using SQL statements. It sounds like our best option to to use GUIDs for our primary keys, although I'd be interested in learning more about your "new techniques" when that information becomes available.
I'll keep watching these forums and your blog!
Best regards, Craig