SQL Azure Data Sync Conflicts and Conflict Resolution

A data conflict occurs whenever the same data in two or more databases within a sync group is changed between synchronizations. No matter what policy you adopt, whenever a conflict situation arises one of the changed rows is kept and the others are lost. How the conflict resolution policy you select affects the final outcome is the topic of this article.

Important!
This wiki topic may be obsolete.
This wiki topic is no longer updated by Microsoft. We moved the content to the MSDN Library where we keep it current.
To find the latest version of this topic on MSDN click here.

In Figure 1, if both remote offices make a change to the same data row we have a conflict situation in the next synchronization session. If each office made the same change the final result is the same as if only one office made the change - the change is kept. However, if each office made a different changein a row one of the changed rows will be kept and the others lost. Therefore, knowing how these conflicts are resolved is fundamental to knowing how your data is handled.

Figure 1 - Data Conflicts

Conflict Resolution Policies

With the release of CTP3, SQL Azure Data Sync enables you to select between two conflict resolution policies: Hub Wins and Client Wins. In short, this is how the two policies differ.

Hub Wins
The first row change written to the hub is kept. Subsequent attempts to write to the same row in the hub are ignored. Thus the first write to the hub is propagated out to all the member databases by the end of the synchronization.

Client Wins
Every row changed in a client database is written to the hub, overwriting prior changes to the same row. Thus the last write to the hub is propagated out to all the member databases by the end of the synchronization.

Figure 2: Select the Conflict Resolution Policy

Top

Case Study

Initial State

Using Figure 1 let's assume the following:

Both remote offices are in the same sync group with the SQL Azure hub database.

The for this session the synchronization order is Remote Office #1 then Remote Office #2.

Note:
The synchronization order is not user settable and may vary from one synchronization session to another.

The Contacts table has four interesting columns with these values:

Row	FName	LName	Phone	Company
1	William	Vong	(270)555-1234	Contoso

Changes Since Last Synchronization

Four changes are made in three fields. Since all the changes are in the same row of two different databases one changed row is kept and the other changed row is lost. Which is kept and which is lost depends on the conflict resolution policy as illustrated below.

Database/Office	Row	FName	LName	Phone	Company
RO #1	1	Bill	Vong	(206)555-1212	Contoso
RO #2	1	Will	Vong	(270)555-1234	Contoso, Inc.

Top

Hub Wins Synchronization Steps

Step/Changes	Row	FName	LName	Phone	Company
First RO#1 polled for changes.	1	Bill	Vong	(206)555-1212	Contoso
Second Entire changed row written to the hub.	1	Bill	Vong	(206)555-1212	Contoso
Third RO#2 polled for changes.	1	Will	Vong	(270)555-1234	Contoso, Inc.
Fourth Since row 1 is already marked as changed, all of RO#2's changes are ignored.
Fifth Hub writes all changed rows to RO#1.	1	Bill	Vong	(206)555-1212	Contoso
Sixth Hub writes all changed rows to RO#2.	1	Bill	Vong	(206)555-1212	Contoso
Final State Values in all three databases.	1	Bill	Vong	(206)555-1212	Contoso

Top

Client Wins Synchronization Steps

Step/Changes	FName	LName	Phone	Company
First RO#1 polled for changes.	1	Bill	Vong	(206)555-1212	Contoso
Second Entire changed row written to the hub.	1	Bill	Vong	(206)555-1212	Contoso
Third RO#2 polled for changes.	1	Will	Vong	(270)555-1234	Contoso, Inc.
Fourth Changed row written to the hub overwriting prior changes.	1	Will	Vong	(270)555-1234	Contoso, Inc.
Fifth Hub writes all changes to RO#1.	1	Will	Vong	(270)555-1234	Contoso, Inc.
Sixth Hub writes all changes to RO#2.	1	Will	Vong	(270)555-1234	Contoso, Inc.
Final State Values in all three databases.	1	Will	Vong	(206)555-1234	Contoso, Inc.

Top

Feedback

This release was provided in order to gather feedback from our customers. Now that you have previewed what the SQL Azure Data Sync team is doing, please let us know what you think of our direction, and tell us about your experiences.

You can send us your thoughts in any of the following ways: