You bet. I'll put an example together with three tables which should explain the issue:
Database
=================================
Create Table Client (ID int identity Primary key,
ClientName varchar(50))
Create Table Person (ID int identity Primary key,
ClientID int,
PersonName varchar(50),
Foreign Key (ClientID) references Client(ID))
Create Table Phone (ID int identity Primary Key,
PersonID int,
PhoneNumber varchar(50),
Foreign Key (PersonID) references Person(ID))
Insert into Client (ID,ClientName) Values (1,'Client A')
Insert into Client (ID,ClientName) Values (2,'Client B')
Insert into Person (ID,ClientID,PersonName) Values (1,1,'Person A')
Insert into Person (ID,ClientID,PersonName) Values (2,1,'Person B')
Insert into Person (ID,ClientID,PersonName) Values (3,2,'Person C')
Insert into Person (ID,ClientID,PersonName) Values (4,2,'Person D')
Insert Into Phone (ID,PersonID,PhoneNumber) Values (1,'111-111-1111')
Insert Into Phone (ID,PersonID,PhoneNumber) Values (2,'222-222-2222')
Insert Into Phone (ID,PersonID,PhoneNumber) Values (3,'333-333-3333')
Insert Into Phone (ID,PersonID,PhoneNumber) Values (4,'444-444-4444')
Scope
===========================================
Scope Upload/Download All: Client and Phone
Scope Upload/Download Client Only: Person
Filter on Person Table: <FilterClause>[side].[ClientID] =1</FilterClause>
Scope sync order: 1) Upload/Download All 2) Upload/Download Client Only
Example
===========================================
Example Insert-Insert conflict: A New person gets added to the database:
Server Machine Insert
Insert into Person (ID,ClientID,PersonName) Values (5,1,'Person E')
Insert into Phone (ID,PersonID,PhoneNumber) Values (5,5,'555-555-5555')
Client Machine Insert
Insert into Person (ID,ClientID,PersonName) Values (5,1,'Person F')
Insert into Phone (ID,PersonID,PhoneNumber) Values (5,5,'666-666-6666')
Sync Process (Sync from Client to Server)
===========================================
1) First scope (ALL) runs trying to make make the following insert and causes a conflit:
Insert into Phone (ID,PersonID,PhoneNumber) Values (5,5,'666-666-6666')
2) We would try to resolve the conflict by changing the ID from 5 to 6. So the insert statement becomes
Insert into Phone (ID,PersonID,PhoneNumber) Values (6,5,'666-666-6666'). Also at this point the PersonID
is pointing to the wrong Person Record pointing to "Person E" rather then "Person F".
If you run this example in a database you'd have to SET IDENTITY_INSERT dbo.Phone ON
3) Because the Person table is out of scope we would not be able to resolve the corrupted record so we put the information into an in-memory
conflict dictionary to be handled later.
4) Second scope runs inserts the Person record with a conflict because ID=5 already exists.
5) Conflict is resolved by setting ID=6
If you run this example in a database you'd have to SET IDENTITY_INSERT dbo.Person ON
6) Code then goes through the in-memory conflict dictionary and find that the child table Phone had a data change (refer to step 2)
7) The following update is made to the Phone Record:
Update Phone SET personid=6 where id=6
Problem
===========================================
As you can probably see this setup is a little precarious to say the least. The biggest problem arises if the second scope (step 4) fails
at some point in the process. Any corrupted records (step 2) that were inserted from previous scopes have already been committed.
We are NEEDING to limit the amout of data that ends up getting synced over to the Client Machine due to limited database space on those machines.
Because of this putting all tables into the "Upload/Download All" scope is not an option with this approach.
Only alternitive we have found so far is to put all tables into one scope and limit the download to DATE instead of CLINIC. But so far this doesn't
look like it will reduce the database size far enough to make it work.
As I mentioned in a previous post, we cannot do dynamic filtering on a single scope because a single client machine may randomly choose which
client data it wants to download.
Question
============================================
Other then the obvious problem with this approach by purposely inserting corrupt data. Can you explain a way that may help us out?
- Maz
