I'm getting the following error after I attempt to save my Sync Rules:

"Cannot deploy the sync group because there is no Clustered Index on table"

However, in SQL Object Explorer I can see there is a clustered index on the table.

Hi,

Thanks for posting here.

have you installed the Sync Agent for the on-premise database, registered it and added an on-premise database?

Provisioning fails in the following scenarios :

• The primary key column has a collation defined on it.

• The SQL 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 name of a table contains a period character (.).

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

• The column of a table is a computed column.

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

Sync fails in the following scenarios :

• 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

• Two sync groups use different columns from the same table. In this case, one of the sync groups, the one synchronizing last, will fail to sync.
  For example, if sync group 1 uses columns col1 and col2 and sync group 2 uses col2 and col3 from the same table, table A, one of the groups will fail during sync. However, if both the sync groups use exact same columns from a table, the sync would succeed.

• Two tables with the same name are involved in sync, even if they belong to different schemas.
  The SQL Data Sync service uses the same tracking table for tables with same name even if they belong to different schemas. As a result, changes from both tables are reflected in the same tracking table, potentially causing erroneous data changes during sync.

• A non-nullable column with no default value is filtered for sync.

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

Also, please check whether you just have one table in the clustered index.

Lastly, you can try syncing the group using the old portal https://manage.windowsazure.com/

Hope this helps.

Girish Prajwal

• Marked as answer by GW-AEON Saturday, February 28, 2015 2:17 AM

Hi,

Thanks for posting here.

have you installed the Sync Agent for the on-premise database, registered it and added an on-premise database?

Provisioning fails in the following scenarios :

• The primary key column has a collation defined on it.

• The SQL 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 name of a table contains a period character (.).

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

• The column of a table is a computed column.

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

Sync fails in the following scenarios :

• 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

• Two sync groups use different columns from the same table. In this case, one of the sync groups, the one synchronizing last, will fail to sync.
  For example, if sync group 1 uses columns col1 and col2 and sync group 2 uses col2 and col3 from the same table, table A, one of the groups will fail during sync. However, if both the sync groups use exact same columns from a table, the sync would succeed.

• Two tables with the same name are involved in sync, even if they belong to different schemas.
  The SQL Data Sync service uses the same tracking table for tables with same name even if they belong to different schemas. As a result, changes from both tables are reflected in the same tracking table, potentially causing erroneous data changes during sync.

• A non-nullable column with no default value is filtered for sync.

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

Also, please check whether you just have one table in the clustered index.

Lastly, you can try syncing the group using the old portal https://manage.windowsazure.com/

Hope this helps.

Girish Prajwal

• Marked as answer by GW-AEON Saturday, February 28, 2015 2:17 AM

Hi,

Thanks for posting here.

have you installed the Sync Agent for the on-premise database, registered it and added an on-premise database?

Provisioning fails in the following scenarios :

• The primary key column has a collation defined on it.

• The SQL 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 name of a table contains a period character (.).

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

• The column of a table is a computed column.

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

Sync fails in the following scenarios :

• 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

• Two sync groups use different columns from the same table. In this case, one of the sync groups, the one synchronizing last, will fail to sync.
  For example, if sync group 1 uses columns col1 and col2 and sync group 2 uses col2 and col3 from the same table, table A, one of the groups will fail during sync. However, if both the sync groups use exact same columns from a table, the sync would succeed.

• Two tables with the same name are involved in sync, even if they belong to different schemas.
  The SQL Data Sync service uses the same tracking table for tables with same name even if they belong to different schemas. As a result, changes from both tables are reflected in the same tracking table, potentially causing erroneous data changes during sync.

• A non-nullable column with no default value is filtered for sync.

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

Also, please check whether you just have one table in the clustered index.

Lastly, you can try syncing the group using the old portal https://manage.windowsazure.com/

Hope this helps.

Girish Prajwal

• Marked as answer by GW-AEON Saturday, February 28, 2015 2:17 AM

Hi,

Thanks for posting here.

have you installed the Sync Agent for the on-premise database, registered it and added an on-premise database?

Provisioning fails in the following scenarios :

• The primary key column has a collation defined on it.

• The SQL 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 name of a table contains a period character (.).

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

• The column of a table is a computed column.

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

Sync fails in the following scenarios :

• 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

• Two sync groups use different columns from the same table. In this case, one of the sync groups, the one synchronizing last, will fail to sync.
  For example, if sync group 1 uses columns col1 and col2 and sync group 2 uses col2 and col3 from the same table, table A, one of the groups will fail during sync. However, if both the sync groups use exact same columns from a table, the sync would succeed.

• Two tables with the same name are involved in sync, even if they belong to different schemas.
  The SQL Data Sync service uses the same tracking table for tables with same name even if they belong to different schemas. As a result, changes from both tables are reflected in the same tracking table, potentially causing erroneous data changes during sync.

• A non-nullable column with no default value is filtered for sync.

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

Also, please check whether you just have one table in the clustered index.

Lastly, you can try syncing the group using the old portal https://manage.windowsazure.com/

Hope this helps.

Girish Prajwal

• Marked as answer by GW-AEON Saturday, February 28, 2015 2:17 AM

Yes, I have installed Sync Agent, registered it, and added a database.

How do I determine if the primary key has collation defined on it?  Looking at the portal I see the following collation "SQL_Latin1_General_CP1_CI_AS", but I can't see details about it.

The table does not have a period in the name.  The total length of the column and schema is less than 100 characters.  None of the columns are computed columns.

Yes, there is only one table in the clustered index.  I was trying to create the sync group on the old portal too.  I'm at a loss.

Hi GW-AEON,

You can view the properties of the Primary Key (PK) column to verify its collation.  And please also check if you have the same collation across all members including the hub.

In addition, please ensure that you make the PK on the table a clustered index.

There are similar threads for your reference.

https://social.msdn.microsoft.com/Forums/azure/en-US/6f6886b9-c3ad-409d-b927-8524d4a579fd/cannot-deploy-sync-group?forum=ssdsgetstarted

http://answers.flyppdevportal.com/categories/azure/sqlazure.aspx?ID=ce4b8729-d19a-4001-ae75-1d4e76746eaa



Thanks,
Lydia Zhang

The following steps helped me to get past this:

1)  Create a new table with clustered index on the PK
2)  Backup existing table with BCP
3)  Restore data to new table with BCP
4)  Rename old table
5)  Rename new table to the old table's name

After this I was able to create the sync group and then run the sync.  Hope this helps someone.

• Edited by GW-AEON Saturday, February 28, 2015 2:17 AM

The following steps helped me to get past this:

1)  Create a new table with clustered index on the PK
2)  Backup existing table with BCP
3)  Restore data to new table with BCP
4)  Rename old table
5)  Rename new table to the old table's name

After this I was able to create the sync group and then run the sync.  Hope this helps someone.

• Edited by GW-AEON Saturday, February 28, 2015 2:17 AM

The following steps helped me to get past this:

1)  Create a new table with clustered index on the PK
2)  Backup existing table with BCP
3)  Restore data to new table with BCP
4)  Rename old table
5)  Rename new table to the old table's name

After this I was able to create the sync group and then run the sync.  Hope this helps someone.

• Edited by GW-AEON Saturday, February 28, 2015 2:17 AM

The following steps helped me to get past this:

1)  Create a new table with clustered index on the PK
2)  Backup existing table with BCP
3)  Restore data to new table with BCP
4)  Rename old table
5)  Rename new table to the old table's name

After this I was able to create the sync group and then run the sync.  Hope this helps someone.

• Edited by GW-AEON Saturday, February 28, 2015 2:17 AM