Export (0) Print
Expand All

Understand and Avoid Synchronization Loops

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

Welcome to SQL Data Sync (Preview). This topic is the fourth of five topics you should be familiar with before you design and implement a data synchronization plan. The five preparatory topics are:

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

You access SQL Data Sync (Preview) via the SYNC tab under SQL Database at the Microsoft 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.

Causes of Synchronization Loops

A synchronization loop results from an overlapping of databases in two or more sync groups such that a change in a database in one sync group is re-written to the same database by another sync group. Synchronization loops are self-perpetuating and can result in large amounts of data repeatedly overwriting identical data in two of more databases.

Any of the following can cause a synchronization loop.

  • A circular reference within a database or table.

  • A circular reference involving two or more sync groups.

  • A single database registered with different agents and added to a sync group under two of more agents.

Sync Group Design

When you design your synchronization topologies it is perfectly fine to have a single database in multiple sync groups. Figure 1 is an illustration of this. Even though database A is shared by Sync Group 1 and Sync Group 2 there is no feedback loop that perpetuates the same data being written and re-written to the same database. It is also possible to share more than one database between two sync groups and not have a loop. We will discuss those configurations later in this topic.

How Figure 1 works

Let's walk through the sequence of events that take place when a change is made to any database in Figure 1. We'll assume the change is made to database C here.

  1. Row 1 in database C is changed.

  2. In database C's metadata a record is created indicating that row 1 was changed and where the change originated.

  3. Sync Group 1 synchronizes.

    1. It notes that row 1 in database C is changed by someone other than SG1.

    2. Row 1 from Database C is written to SG1 Hub.

    3. A record is created in SG1's metadata indicating that row 1 was changed by SG1.

    4. Row 1 from SG1 is written to database A.

    5. A record is created in database A's metadata indicating that row 1 was changed by SG1. Sync Group 1 is finished synchronizing.

If no new changes are made to any of the databases then the next time they sync no data is written to any of the databases.

Two Sync Groups with no Synchronization Loop

Figure 1: Two sync groups sharing a database. No synchronization loop

The same sequence takes place in the opposite direction (relative to Figure 1) if the change is initiated in database D.

How Figure 2 works (or does not work)

Let's walk through the sequence of events that take place when a change is made to any database in Figure 2. Again we'll assume the change is made to database C.

  1. Row 1 in database C is changed.

  2. In database C's metadata a record is created indicating that row 1 was changed and where the change originated.

  3. Sync Group 1 synchronizes.

    1. It notes that row 1 in database C is changed by someone other than SG1.

    2. Row 1 from Database C is written to SG1 Hub.

    3. A record is created in SG1's metadata indicating that row 1 was changed by SG1.

    4. Row 1 from SG1 is written to databases A and B.

    5. A record is created in database A and B's metadata indicating that row 1 was changed by SG1.

    Sync Group 1 is finished synchronizing.

  4. Sync Group 2 Synchronizes.

    1. It notes that row 1 in database A (or B) is changed by someone other than SG2.

    2. Row 1 from Database A is written to SG2 Hub.

    3. A record is created in SG2's metadata indicating that row 1 was changed by SG2.

    4. Row 1 from SG2 is written to databases B (or A) and D.

    5. A record is created in database B (or A) and D's metadata indicating that row 1 was changed by SG2.

    Sync Group 2 is finished synchronizing.

If no new changes are made to any of the databases the next time that SG1 synchronizes it notes a change in database A (or B) that it didn't make (it was made by SG2 in the last cycle). Even though the data is no different from what is in all the databases in both sync groups it is treated as changed data and the above synchronization cycle is run again, and again, and again without end.

It is worth noting that while our synchronization loop example involves only a single row, what is true for one row is true for any number of rows. Imagine a million-row database where 1% of the unchanged rows are changed in each synchronization cycle. After one cycle you are infinitely looping 10,000 rows. After two cycles you are looping that 10,000 plus an additional 9,900 rows for a total of 19,900 rows. And the number of rows in the loop continues to increase, never decrease.

Two Sync Groups with a Synchronization Loop

Figure 2: Two sync groups with a synchronization loop

How Figure 3 works (big loops are still loops)

Figure 3 illustrates a more complex situation where the loop may not be as easy to identify as in Figure 2.

In Figure 3 there are three sync groups. Each sync group has a hub and two other databases. Each database is shared by two sync groups but no pair of sync groups shares the same two databases.

  • SG1: A and B

  • SG2: B and C

  • SG3: C and A

Let's walk through the sequence of events that take place when a change is made to any database in Figure 3. We'll assume the change is made to database A.

  1. Row 1 in database A is changed.

  2. In database A's metadata a record is created indicating that row 1 was changed and where the change originated.

  3. Sync Group 1 synchronizes.

    1. It notes that row 1 in database A is changed by someone other than SG1.

    2. Row 1 from Database A is written to SG1 Hub.

    3. A record is created in SG1's metadata indicating that row 1 was changed by SG1.

    4. Row 1 from SG1 is written to database B.

    5. A record is created in database B's metadata indicating that row 1 was changed by SG1.

    Sync Group 1 is finished synchronizing.

  4. Sync Group 2 Synchronizes.

    1. It notes that row 1 in database B is changed by someone other than SG2.

    2. Row 1 from Database B is written to SG2 Hub.

    3. A record is created in SG2's metadata indicating that row 1 was changed by SG2.

    4. Row 1 from SG2 is written to database C.

    5. A record is created in database C's metadata indicating that row 1 was changed by SG2.

    Sync Group 2 is finished synchronizing.

  5. Sync Group 3 Synchronizes.

    1. It notes that row 1 in database C is changed by someone other than SG3.

    2. Row 1 from Database C is written to SG3 Hub.

    3. A record is created in SG3's metadata indicating that row 1 was changed by SG3.

    4. Row 1 from SG3 is written to database A.

    5. A record is created in database A's metadata indicating that row 1 was changed by SG3.

    Sync Group 3 is finished synchronizing.

If no new changes are made in any of the databases the next time SG1 synchronizes it notes that row 1 in database A was changed by someone other than SG1. This is treated by SG1 as a completely different change even though the data is identical to that in all the databases and hubs. Thus the entire synchronization cycle is repeated infinitely.

Three Sync Groups with a Synchronization Loop


Figure 3: Three sync groups with a synchronization loop

How to avoid synchronization loops

The key to avoiding synchronization loops is to avoid having any path whereby a data change in one sync group is ultimately written back to the originating database by another sync group. There are three ways to accomplish this.

  • Design your synchronization system so the loops cannot exist.

    For example:

    In Figure 2 you could remove either database A or B from the shared area.

    In Figure 3 you could remove any database from any sync group and you would break the loop.

  • Use row filtering.

    If you create mutually exclusive filters when you configure each sync group then no two sync groups will synchronize the same data.

    For example:

    In Figure 2 you could add the row filter Area = "NA" to one sync group and Area = "EU" to the other.

  • Use synchronization direction.

    For example:

    If you set the sync directions as illustrated in Figure 4 you no longer have a loop.

Sync Direction used to break Synchronization Loop

Figure 4: Use synchronization direction to prevent loops

Navigation

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

 

How to create a sync group

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

  1. Sign in to the 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

Community Additions

ADD
Show:
© 2014 Microsoft