Export (0) Print
Expand All

Update a Sync Group Schema

Updated: December 2, 2014

 

SQL Azure Data Sync Icon

Over time, you may want to modify a SQL Data Sync (Preview) sync group. This topic walks you through modifying an existing sync group’s schema by adding/removing tables or columns to the sync group or by modifying a column’s width or data type.

If you need to add or remove a database to a sync group, see the topics Add a Database to your Sync Group or Remove a Database from a Sync Group.

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.

Modifications you can make to a sync group include:

Before you can make changes to a deployed sync group you must:

  • Install the latest version of the SQL Data Sync (Preview) Preview Agent.
    See Install an agent for instructions on how to install a client agent.

  • Ensure that no sync group reference databases are provisioning or synchronizating or have unfinished work, such as changes that have not been applied.

SQL Data Sync (Preview) allows the following changes to a sync group:

  • Add a table to the dataset

  • Remove a table from to the dataset

  • Add a column to the dataset

  • Remove a column from the dataset

  • Change a column’s width

The following changes to a database schema cannot be applied to a sync group. If you need to incorporate any of these changes to your sync group you need to delete and re-create the sync group.

  • Change a column’s data type.

  • Add, remove or modify a column’s filter.

  • Remove a column that has a filter

    WarningWarning
    Data Sync prevents you from removing a column from a sync group if the column belongs to a filter that is defined in that sync group.
    When you edit a sync group SQL Data Sync (Preview) does not detect filters defined in other sync groups. You are responsible for not removing a column from a sync group if the column belongs to a filter that is defined in another sync group or another subscription.   Doing so may cause sync errors when inserting data into a table that contains the filtered column.

There are a number of different scenarios for adding a table to a sync group.

Add table W to the sync group. (Figure 1)

Figure 5 - Add a table to the sync group

Figure 1 – Add table W to the sync group

  • Table W is a new table that the user creates in each of the databases but does not populate with data in any of the instances. (Figure 2)

    Fiugre 6 - Add empty table to all databases
    Figure 2 – User creates empty table in all the databases


    When the changes are deployed the databases are re-provisioned. Because there is no data in any of the instances of table W no synchronization takes place.

  • Table W exists in one database. The user creates empty tables in the other databases. (Figure 3)

    Figure 7 - Added table missing in some databases
    Figure 3 – User creates empty table in databases where W does not exist


    When the changes are deployed the databases are re-provisioned. Because there is no data in any instance of table W no synchronization is performed.

  • Table exists in multiple database and has different data among the instances (Figure 4)

    Figure 7 - Add tables with different data
    Figure 4 –Table exists in multiple databases and has difference data among the instances

    When the changes are deployed the databases are re-provisioned and all rows in the table synchronized so that the data in table W is consistent across all the databases.

When a table is removed from a sync group the databases are re-provisioned but a synchronization is not performed since there is no data to be updated.

To add a column to a sync group’s dataset the column’s table must be a member of the sync group dataset. If the table is not a member of the dataset, add it then select the column to add.

User desires to add column D to the sync group. Table T is already a member of the sync group. (Figure 5)

Figure 1 - Add a column

Figure 5: Add column D to a sync group

  • Column D is a new column that the user creates in each of the databases but does not populate with data in any of the instances. (Figure 6)

    Figure 2 - Empty column added to tables
    Figure 6 – New and empty column added to table T in multiple databases

    When the changes are deployed the databases are re-provisioned. Since there is no new data in any instance of column D no synchronizing is done.

  • Column D with data exists in the source table. The user creates empty instances of the column in table T of the other databases in the sync group. (Figure 7)


    ImportantImportant
    The column must allow NULLs or have a DEFAULT for the user to create it in the other tables.

    Figure 3 - Column exists with data in one table
    Figure 7 - Column exists in one table, user creates empty column D in table T of the other databases


    When the changes are deployed the databases are re-provisioned. Since the data in column D in database X does not exist in column D of databases Y or Z all rows that contain data, not NULLs or DEFAULTs are synchronized.

  • Column exists in multiple databases but with differing data. (Figure 8)

    This condition may exist if the column has existed in the different databases but was not synchronized since it did not previously belong to the sync group. As the column is undated in the normal course of business different data is added, updated or deleted from each instance independent of the other instances. When this column is added to the sync group all instances of the column are assumed to be out of sync with the other instances.

    Figure 4 - Add columns with different data
    Figure 8 - Column and disparate data exist across multiple databases

    When the changes are deployed the databases are re-provisioned and column D in table T is synchronized across all the databases.


    ImportantImportant
    If the conflict resolution policy is Client Wins, sync results are indeterminate.

    Each reference database overwrites the contents of table T with its own data, therefore

    1. At the end of the first sync the hub contains a copy of the last reference database to synchronize.

    2. During the next synchronization the hub copies this data to each reference database.

    3. Since the order in which the reference databases are synchronized cannot be guaranteed, the final version of the data cannot be predicted.

When a column is removed from a table the sync group is re-provisioned but a synchronization is not performed since there is no data to be updated.

You cannot change the width of a column in a sync group directly in Data Sync. You must first alter the column in the reference database, and then use the updated database schema as the source for the updating the sync group.

Data Sync supports column width changes for these data types:

  • CHAR

  • VARCHAR

  • NCHAR

  • NVARCHAR

  • BINARY

  • NBINARY

Some data type changes can be incorporated into your sync group. Others cannot. In general, if a data type change can result in lost data, from a float to an int, or from a varchar(10) to a varchar(9), the change cannot be incorporated into the sync group’s schema. If you make a data type change that could result in a loss of data, SQL Data Sync (Preview) gives you an error message when you include that column in the sync group.

This section walks you through how to update the sync group schema to incorporate changes you made in your schema’s database to the sync group.

Whether you need to add or remove a table or column or change a column’s width, the process is the same.

  1. Sign in at the Azure management portal.

  2. After the addins are loaded click SQL DATABASES.

  3. Click the SYNC (PREVIEW) tab.

  4. From the list, select the sync group you want to modify.

  5. Click the CONFIGURE tab.

  6. If AUTOMATIC SYNC is ON set it to OFF (Figure 9) then click SAVE.


    Turn Autosync OFF
    Figure 9 – Turn AUTOMATIC SYNC off

  7. Click the SYNC RULES tab.

  8. Click REFRESH SCHEMA. (Figure 10)


    Click 'Refresh Schema'
    Figure 10 – Refresh schema

  9. From the list of databases, select the database you used for the sync group’s schema.
    This creates a list of the tables in the database.

  10. To expand the table and show the columns, click the down arrow for each table you want to modify.

  11. Use the checkboxes to add and/or remove tables and columns from the sync group. (Figure 11)


    Elements in schema have been modified
    Figure 11 – Add/Remove tables and columns

  12. If you want to keep the changes, click SAVE.

  13. Click SYNC.

  14. If you turned AUTOMATIC SYNC off in step 6, or if you want to change the frequency of your synchronizations, click the CONFIGURE tab and set AUTOMATIC SYNC back to ON.

  • Always use the latest schema when you need to update a sync group’s dataset.

  • If you plan to remove a table, check the results of the most recent sync to make sure there are no unapplied changes for that table.
    If you remove the table before the changes have been applied, Data Sync will retain the pending changes, which may cause sync to fail after some period of time (45 days).

  • Data Sync prevents you from removing a column from a sync group if the column belongs to a filter that is defined in that sync group.
    You are responsible to not remove a column from a sync group if the column belongs to a filter that is defined in another sync group or another subscription.   Doing so may cause sync errors when inserting data into the table that contains the column.

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.

 

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 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 Client Agent

  3. Register a SQL Server database with a Client Agent

  4. Create your sync group

  5. Define your data sync

  6. Configure your sync group (SDS)

See Also

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft