Share via

SQL Azure Data Sync - Configure a Sync Group

  • Article

A synchronization group is two or more databases that are logically grouped together for the purpose of synchronizing specified tables, columns and rows common to each.

This article walks you through the sync group configuration process where you specify the databases, tables and optionally rows to synchronize. You also specify the synchronization schedule if you want regular synchronizations and define the collision resolution policy.

See the article SQL Azure Data Sync Create a Sync Group for the flow of all the steps to create a sync group.

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.

Prerequisites

Prior to using the information in this article you should have

  1. Provisioned a SQL Azure Data Sync server - SQL Azure Data Sync Provision a SQL Azure Data Sync Server.

  2. Created and named the sync group - SQL Azure Data Sync Create a Sync Group.

  3. Added a SQL Azure hub database to the sync group - SQL Azure Data Sync Add a SQL Azure Hub Database.

  4. Added one or more other databases to the sync group.

Configure the Sync Group

In order for SQL Azure Data Sync to know how you want the sync group to function you must configure the sync group. A sync group is configured when you set the synchronization schedule, select the databases, tables, columns and/or rows to synchronize, and enable the sync group.

Set the Sync Schedule

The synchronization schedule specifies the number of minutes (minimum of 5), hours, days or months that elapse between synchronization jobs. The synchronization schedule does not specify the local or international time that the synchronization jobs execute.

  1. From the dropdown select the time units of measure for the synchronization schedule. (Figure 1:1)
    There are four options

    • Minutes

    • Hours

    • Days

    • Month

  2. Enter the number of time units between synchronization jobs. (Figure 1:2)

    Note:
    The synchronization schedule must be between 5 minutes and 1 month.

  3. Check the Enable checkbox to enable the sync group.(Figure 1:3)

    Important:
    A sync group synchronizes on schedule only if it is enabled. If you want the sync group to synchronize only when you manually click the Synchronize button make sure the Enable checkbox unchecked.

  4. From the dropdown select the conflict resolution policy for this sync group. (Figure 1:4)
    A conflict exists when changes are made to the same field in multiple databases between synchronizations. As these changes are written to the hub during a sync job the conflict resolution policy determines which changes are kept and which are lost.

    There are two options:

    • Hub Wins
      The first change in a field written to the hub is the change that is kept, all others are lost.

    • Client Wins
      Every attempt to write a change to a field to the hub is successful. Thus only the last written change is kept, overwriting all previous changes.


    Figure 1: Set the sync schedule and conflict resolution policy

Select the Tables, Columns and Rows to Synchronize

When you configure a sync group you select the tables within the databases and the columns within the tables that are synchronized. You can even create value filters on a column so that only rows that satisfy the filter, such as "Region = Northwest", are synchronized.

Critical Constraints:

  • SQL Azure Data Sync Preview does not support multiple tables with the same name when they are in different schemas. Multiple tables with the same name result in erroneous data in the tables.

  • You cannot add tables with circular references, either referring directly to itself or circularly through other tables in this or other sync groups. Circular references result in a synchronization loop.                

  • When you try to add a table whose name contains a period (.) to a sync data set in a sync group, the UI displays only the end name of the table. For example, when you try to add dbo.Test.DataAB table to a sync data set, the UI actually displays dbo.DataAB. If you go ahead and add this table to the sync data set and create the sync group, you won't see any error message. However, provisioning will not occur for the table and also the table dbo.Test.DataAB table is not synchronized at runtime.

  • SQL Azure Data Sync Preview does not support table whose name contains a space character (example: dbo.[Order Details]).

  • Column of a table in sync set must not be a computed column.

  • If the total length of schema name and table name (including any square brackets) cannot be greater than 100.

  • The SQL Azure Data Sync provisioning process does not create a collation on a primary key column in the destination database even though the collation exists on the column in the source database

  • The primary key data is changed in a database in a sync group, the changes do not get synchronized over to the rest of the databases in the sync group

  • When you filter out columns that are non-nullable and have no-default values on the schema to sync, clients that have filtered version will never be able to sync up their changes, and will always get sync failures

 

Information:
If you do not include a table dependency Data Sync will remove the constraint or index if it cannot create it.

 

  1. From the dropdown select the sync group member database that you want to provide the schema for the sync group. (Figure 2:1)
    If you have added or deleted databases to the sync group click Refresh to update the list in the dropdown. (Figure 2:2)

    After the schema is imported check the checkbox in the Tables frame for each table you want to synchronize with this sync group. (Figure 2:3)

  2. As you check a table in the Tables pane that table's columns are displayed in the Columns to sync pane. For each column you want to synchronize with this sync group check the Columns to sync checkbox. (Figure 2:4)

Critical:
Avoid synchronization loops.
Be sure that there are no overlapping sync groups. Any row that is synchronized by this sync group must not be synchronized by any other sync group.

 

  • For each column/field you want to filter on:

    1. Check the checkbox in the Filter column. (Figure 2:5)

      Note: You can only filter on columns which have the following data types.

      • bigint
      • bit
      • char
      • date
      • datetime
      • datetime2
      • datetimeoffset
      • int
      • float
      • nchar
      • numeric
      • nvarchar - but not nvarchar(max)
      • smallint
      • time
      • tinyint
      • uniqueidentifier
      • varchar - but not varchar(max) 

    2. Find the name in the Field Name column of the Row Filtering section. (Figure 2:7)

    3. From the dropdown select the relational operator you want to apply to this filter. (Figure 2:8)

      Operator

       Functionality
      = Equals
      The row in the table is synchronized only if the field's value is identical to the specified value.
      Example: City = "Portland"
      > Greater than
      The row in the table is synchronized only if the field's value is greater than the specified value.
      Example: PostalCode > 98999
      >= Greater than or equal to
      The row in the table is synchronized only if the field's value is greater than or equal to the specified value.
      Example: PostalCode >= 99000
      < Less than
      The row in the table is synchronized only if the field's value is less than the specified value.
      Example: PostalCode < 99000
      <= Less than or equal to
      The row in the table is synchronized only if the field's value is less than or equal to the specified value.
      Example: PostalCode <= 98999
      <> Not equal
      The row in the table is synchronized only if the field's value is not equal to the specified value.
      Example: City <> "Seattle"

    4. Type in the value you want to filter on. (Figure 2:9)
      As you apply filters the query string is shown below the filters. (Figure 2:11)

    5. If you want to add another filter value select AND or OR from the dropdown. (Figure 2:10)

      Note:
      You can only apply a single filter to a column. For example, you can do this Age >= 16 but you cannot do this Age >= 16 AND Age < 40 because it applies more than one filter to the Age column.

  • When all tables, columns and rows are properly configured click OK. (Figure 2:12)


    Figure 2: Configure the dataset tables, columns and rows

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.

Top

See Also

    • [[SQL Azure Data Sync Overview]]
    • [[SQL Azure Overview]]

Top