Matt Mansoomi

SQL Data Sync troubleshooting

Matt Mansoomi -

SQL Data Sync is an effective method on Azure that allow you to synchronize the data you select across multiple databases, as Microsoft explains here that’s bi-direction and you can use on both on premises and cloud.

Sometimes it can be tricky and goes out of sync. I got customer that tried to recreate the sync group however after that they got the following error message.

Database re-provisioning failed with the exception "SqlException Error Code: -2146232060 - SqlError Number:207, Message: Invalid column name 'xxx'. 
SqlError Number:207, Message: Invalid column name 'xxx'.  For more information, provide tracing ID ‘d3442df9-c147-455e-8529-c70854098604’ to customer support."

One thing that we can normally advise is check is there any primary key in the table? Again, in the link which I shared before Microsoft mentions each table must have a primary key. Don’t change the value of the primary key in any row. If you must change a primary key value, delete the row and recreate it with the new primary key value.

Azure SQL Data Sync Health Checker

Always Azure SQL Data Sync Health Checker can be helpful! I’ll try to explain this on a separate topic.

In my case we found the customer even after recreating the sync group added a new column in the database.
This may be caused by columns missing in the table. Data Sync auto provisioning does not create columns if the table already exists. Since the columns are missing this error is raised. Customers should manually create the columns in all databases. Auto provisioning may also not be used if the corresponding tracking table already exists.

Mitigation:

SQL Data Sync lets users synchronize data between databases in Azure SQL Database and SQL Server instances in one direction or in both directions. One of the current limitations of SQL Data Sync is a lack of support for the replication of schema changes. Every time you change the table schema, you need to apply the changes manually on all endpoints, including the hub and all members, and then update the sync schema.