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 change in 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
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. |
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 |
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. |
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:
- Send us a note on http://twitter.com/syncfx.
- Post a comment to our blog at http://blogs.msdn.com/sync.
- Post a thread to our forum at http://social.msdn.microsoft.com/forums/en-US/ssdsgetstarted/threads/
See Also
- [[SQL Azure Data Sync Overview]]
- [[SQL Azure Overview]]