CODEWITHSUNDEEP
c#sql-servermergemicrosoft-sync-frameworkcompact-database
user5398447
user5398447

Reputation:

Solution for merging data from multiple sql compact databases to a central sql server database using sync framework

I am currently working on an application for tablets that uses a SQL Server Compact 3.5 database. The need has risen to sync data among tablets. After some research (namely this and this), my team and I decided to have a central database hosted by SQL Server 2008 R2.

After implementing this example, there were several drawbacks to this approach:

I believe change tracking can be useful, and would like to leverage such, if applicable, to find a solution for the following scenario:

The server and three tables are currently synchronized with the same database in the db. 

ID | NAME
---------
1  | JIM
2  | BOB
3  | JANE

The three tablets are taken offline to do their thing.

Tablet 1 adds the following:

4 | JOE

Tablet 2 adds the following (assuming after tablet 1):

4 | BILL

Tablet 3 adds the following (assuming after tablet 2):

4 | DAVE

Then, the tablets reconnect online to sync, in any order. After all is said and done with synchronization, the server db and client dbs should have the following data:

ID | NAME
---------
1  | JIM
2  | BOB
3  | JANE
4  | JOE
5  | BILL
6  | DAVE

From a DBA standpoint, there is merge replication, but I do not have any sysadmin rights on the db server (or administrative rights on a pc itself to give myself sysadmin rights in sql server). Thus, I would like to avoid this route if possible and stick with coding a solution using sync framework (or better).

Am I on the right track after implementing the example above toward eventually finding a solution for my scenario, or is there something else I need to do?

Upvotes: 0

Views: 660

Answers (1)

JuneT
JuneT

Reputation: 7860

Sync Fx requires a PK (how else can it uniquely identify which row has changed?)

and likewise, you have to explicitly tell it which tables you want to sync.

your scenario will result in to conflicts though. Sync Fx will no reorder the PK value for you. what it reads from the source is what get's saved on the destination. (how can it tell #4 in client1 is actually #5 on the server?)

Sync Fx or not, using identity values for PK will result to PK value collisions.

Upvotes: 1

Related Questions