Export (0) Print
Expand All
4 out of 4 rated this helpful - Rate this topic

Conflict Resolution when Synchronizing

Updated: February 12, 2014

[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

 

SQL Azure Data Sync Icon

A SQL Data Sync (Preview) data conflict occurs whenever the same data row in two or more databases within a sync group is changed between syncs. No matter what policy you adopt, whenever a conflict situation arises one of the changed rows is kept and the others are lost. This article describes the behavior of each conflict resolution policy.

The Windows Azure SQL Data Sync plug-in on the Windows Azure Silverlight portal has been decommissioned. Going forward, use the Windows Azure Management portal, for Windows Azure SQL Data Sync.

You access SQL Data Sync (Preview) via the SYNC tab under SQL Database at the Windows Azure Management portal. The SYNC tab is only available when you have one or more sync groups. See the topic How To: Create a Sync Group (SDS) for guidance on creating and modifying a sync group from this portal.

See the Navigation section below for links to topics you should read before you start and guidance on creating and modifying sync groups.

Conflict Resolution Policies

SQL Data Sync (Preview) enables you to select between two conflict resolution policies: Hub Wins and Client Wins.

Hub Wins

The first row change written to the hub is kept. Subsequent attempts to write to the same row in the hub are discarded. Thus the first change in a row written to the hub is propagated out to all the member databases by the end of the sync.

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 sync.

Case Study

Initial State

Using Figure 1 let's assume the following:

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

  • In this illustration, the sync order is Remote Office #1 then Remote Office #2. In reality, the sync order is indeterminate and can vary from sync to sync.

  • 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 Sync

Changes are made in multiple versions of the same row. The conflict resolution policy determines which version of the row is kept.

 

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 Sync 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.
Row is marked as changed in the hub.

1

Bill

Vong

(206)555-1212

Contoso

Third

Any other rows marked as changed in the hub are written to RO#1, completing the sync with RO#1.

Fourth

RO#2 polled for changes.

1

Will

Vong

(270)555-1234

Contoso, Inc.

Fifth

Since row 1 is already marked as changed in the hub, all of RO#2's changes in row 1 are discarded.

Sixth

Hub writes all changed rows to RO#2.

1

Bill

Vong

(206)555-1212

Contoso

State of all three databases after this sync
There is data congruence following this sync cycle. If a different row had been changed in RO#2, that change would not be written to RO#1 until the next sync cycle.

Hub

1

Bill

Vong

(206)555-1234

Contoso

RO#1

1

Bill

Vong

(206)555-1234

Contoso

RO#2

1

Bill

Vong

(206)555-1234

Contoso

Client Wins Sync 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.
Row is marked as changed in the hub.

1

Bill

Vong

(206)555-1212

Contoso

Third

Any other rows marked as changed in the hub are written to RO#1, completing the sync with RO#1.

Fourth

RO#2 polled for changes.

1

Will

Vong

(270)555-1234

Contoso, Inc.

Fifth

Changed row written to the hub, overwriting prior changes.

1

Will

Vong

(270)555-1234

Contoso, Inc.

Sixth

Any other rows marked as changed in the hub are written to RO#2, completing the sync with RO#2.

1

Will

Vong

(270)555-1234

Contoso, Inc.

State of all three databases after this sync
There is data incongruence after this sync cycle. On the next sync cycle, the changed row in the hub is written to RO#1 bringing that row into congruence with the other two databases.

Hub

1

Will

Vong

(270)555-1234

Contoso, Inc.

RO#1

1

Bill

Vong

(206)555-1234

Contoso

RO#2

1

Will

Vong

(270)555-1234

Contoso, Inc.

Navigation

SQL Data Sync (Preview) is a feature of SQL Database. From the Windows Azure Management portal you can perform all tasks necessary to create, deploy, and modify a sync group.

 

Before you start

Before you begin to design and implement your synchronizations, you should be familiar with these topics.

How to create a sync group

There are six steps to creating a sync group from the Windows Azure Management portal. Details on each step can be found by following these links.

  1. Sign in to the Windows Azure SQL Database Management portal
    SQL Data Sync (Preview) is found as a tab under SQL Database only after you create a sync group.

  2. Install a SQL Data Sync (Preview) Client Agent

  3. Register a SQL Server Database with a Client Agent

  4. Create your Sync Group (SDS)

  5. Define your sync data (SDS)

  6. Configure your sync group (SDS)

 

How to modify a sync group

You can modify a sync group’s schema by adding/removing tables or columns in the sync group; or by altering a column’s width or data type. Details can be found by following the links.

See Also

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.