syncdata db has this error "Database re-provisioning failed with the exception "

Janet Putnam 50

I have an Azure sql db that I have a data sync with an on prem db. The sync was working and all was good. 1/31 we added a column to a table in the on prem db. I added the same column to my Azure sql db and refreshed the schema for the data sync. The new column appeared in the list of available columns, so I clicked it and hit save. The log message said "Schema information obtained successfully." The next time the sync was scheduled to happen the Azure sql was successful and the on prem db was successful, but the syncdata db has this error "Database re-provisioning failed with the exception "Invalid column name 'LegalHold'.Inner exception: SqlException ID: 0af6effa-f946-48f7-8f66-720c5f1de8a6, Error Code: -2146232060 - SqlError Number:207, Message: Invalid column name 'LegalHold'. For more information, provide tracing ID ‘3965e3ce-1026-4a53-9bf5-b04a836f3320’ to customer support." I un-selected the new column and everything synced successfully. But that isn't a solution. How do I fix this.

Accepted answer

Oury Ba-MSFT 19,806 Reputation points Microsoft Employee

2024-03-05T18:53:14.52+00:00
@Janet Putnam

Solution:

This has been resolved. The advice offered in the above comments were close but did not solve the issue.

Here is the process that I followed that did work. Note, there are 3 databases in my sync Hub, Azure db; Prod, on prem db; Sync db, created by the sync to hold sync data.

Remove the table from the sync.

sync

verify that table was removed from sync.

In prod db add the column (in my case it was already added)

use prod db to create table script

run create table script on Hub db

add column to table in sync db

add the table to the sync

run the sync. This will take a bit because the table in the Hub is empty, so the sync will fill it.

Regards,

Oury
Please sign in to rate this answer.
Phyo Thiha 20 Reputation points

2024-05-23T12:32:13.1033333+00:00

Hi Oury,

Thank you for sharing the steps. I am facing a similar issue when trying to add a column to the table (let's call it 'zipcode' table) from the on-premise server. In my scenario, there are two databases set up in the Azure sync (I inherited that) -- an on-premise db and an azure db which is acting like a Hub although it is supposed to be getting the latest data from the on-premise db.

I see that there is another database that was created seemingly automatically by the Azure sync process. That database is called 'SyncMetadata' in our set up and it has tables like 'dss.action', 'dss.agent', 'dss.agent_instance', ..., 'dss.userdatabase', 'TaskHosting.Job', ..., 'TaskHosting.ScheduleTask'.

I'd like to add a new column to 'zipcode' table in my on-premise db (let's call it 'onprem db'), which is currently set up as a member database, and then sync that new column to my azure db (let's call it 'azure db'), which is the Hub. Based on your answer above, I am guessing this is what I need to do:

a) Remove the table from something.... Here, I am confused; do I need to find some table in 'SyncMetadata' database and delete that or by 'sync', do you mean the sync group details that shows the list of tables to syn from the two databases in Azure web UI?

b) click 'sync' button in Azure web UI

c) verify that the table was removed from (the list of sync tables listed in the Azure web UI??)

d) in my on-premise db, add the new column

e) generate CREATE TABLE script in the on-premise db

f) use the above CREATE TABLE script and run it in azure db

g) add the new column (probably using ALTER statement?) to azure db?? (or 'SyncMetadata' database or Azure web UI that shows table names and their columns that we want to sync??)

h) add the table to the sync list (i.e. Azure web UI that shows table names and columns we want to sync)

i) click 'sync' button in Azure web UI

Could you please review the steps I listed above and help me understand the parts that I am confused? Thank you!

Oury Ba-MSFT 19,806 Reputation points Microsoft Employee

2024-05-23T20:32:18.21+00:00

Phyo Thiha

Thank you for reaching out.

If you are facing a different error message. I would suggest posting your question in a new thread and tag me.

Please check the below steps.

In your on-premises db, add the new column. You can use the following SQL script to generate a CREATE TABLE script in the on-premises db: ALTER`` ``TABLE`` zipcode ``ADD`` new_column_name data_type;

Replace new_column_name with the name of your new column and data_type with the appropriate data type for your column.

Use the above CREATE TABLE script and run it in Azure db. This step is not necessary because you should be adding the column directly to the existing table, not creating a new one.

Add the new column (probably using ALTER statement?) to azure dB?? (or ‘SyncMetadata’ database or azure web UI that shows table names and their columns that we want to sync??)

Add the table to the sync list (Azure web UI that shows table names and columns you want to sync)

Click ‘sync’ button in Azure web UI

Here’s a more detailed guide based on best practices for Azure SQL Data Sync:

Make schema change: Add new tables or columns to both the hub and member databases.

Avoid any data change where the new columns are involved until you’ve completed step 3.

Wait until schema changes are applied to all endpoints.

Refresh database schema and add new column to sync schema.

Data in new column is synced during next sync operation.

New tables and columns don't impact the current sync and Data Sync ignores them until they're added to the sync schema. When adding new database objects, follow the sequence:

Add new tables or columns to the hub and to all sync members.

Add new tables or columns to the sync schema.

Begin inserting values into the new tables and columns.

For more information on setting up SQL Data Sync between databases in Azure SQL Database and SQL Server, you can refer to https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-sql-server-configure?view=azuresql#add-sync-members

Regards,

Oury
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

3 additional answers

Alberto Morillo 34,151 Reputation points MVP

2024-02-02T15:05:52.6333333+00:00

If as you mentioned, you manually replicated the change (the column added) on the hub and the member database, and after that you updated the sync schema, it should have worked. The only option left for me is re-creating the sync group.
Please sign in to rate this answer.
Janet Putnam 50 Reputation points

2024-02-02T15:28:40.21+00:00

I certainly hope that's not the solution. The initial load of all of the tables is quite time consuming. Before I do that I will just drop and recreate the affected table. Although that is more than I want to do it is less than the alternative.

Alberto Morillo 34,151 Reputation points MVP

2024-02-02T17:17:10.1766667+00:00

Maybe another contributor can offer you a better alternative. Please give some time for others to contribute.

Janet Putnam 50 Reputation points

2024-02-14T02:20:47.5266667+00:00

Oury Ba-MSFT I dropped and recreated the table and then when I synced it failed. The error message: Database re-provisioning failed with the exception "Invalid column name 'LegalHold'.Inner exception: SqlException ID: 64c425aa-db9d-4134-9008-625c28d4643e, Error Code: -2146232060 - SqlError Number:207, Message: Invalid column name 'LegalHold'. For more information, provide tracing ID ‘f2b922ee-1ed7-4b85-81f8-999fb3f04152’ to customer support." When I first added the column and synced the sync worked but the error came from the sync data database. It's like it knew what columns were in the sync and adding the new column didn't add it to it's knowledge base. It's like saving didn't update anything in that db.

Janet Putnam 50 Reputation points

2024-02-14T02:52:12.36+00:00

Oury Ba-MSFT I spoke too soon. I removed the table from the sync and re-added it and then I synced. I got some weird error about another table, so I clicked sync again and this time it worked. I'm very glad this worked because we add columns a lot, so we needed a good solution. Thank you.

Janet Putnam 50 Reputation points

2024-02-14T02:57:51.02+00:00

I'm going to add another comment here incase someone else (or future me) has the issue. I actually think that the process should be: Add the column to the spoke db, Add the column to the hub db, Remove the table from the sync, Sync, Add the table back to the sync (the new column should be added automatically), Sync. If this works it would save having to drop/recreate/repopulate the table.

Oury Ba-MSFT 19,806 Reputation points Microsoft Employee

2024-02-14T19:09:08.5+00:00

Thank you Janet Putnam for bringing this in the forum. That will help community members facing the same. Regard, Oury
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Oury Ba-MSFT 19,806 Reputation points Microsoft Employee

2024-02-13T22:05:19.2933333+00:00

Janet Putnam Thank you for reaching out.

Issue:

I have an Azure sql db that I have a data sync with an on prem db. The sync was working and all was good. 1/31 we added a column to a table in the on prem db. I added the same column to my Azure sql db and refreshed the schema for the data sync. The new column appeared in the list of available columns, so I clicked it and hit save. The log message said "Schema information obtained successfully." The next time the sync was scheduled to happen the Azure sql was successful and the on prem db was successful, but the syncdata db has this.

error

"Database re-provisioning failed with the exception "Invalid column name 'LegalHold'.Inner exception: SqlException ID: 0af6effa-f946-48f7-8f66-720c5f1de8a6, Error Code: -2146232060 - SqlError Number:207, Message: Invalid column name 'LegalHold'. For more information, provide tracing ID ‘3965e3ce-1026-4a53-9bf5-b04a836f3320’

Solution 1

Drop and recreate the table with the conflicting column in the hub database even if the data type matches. In case there was a discrepancy with the data types, you will need to recreate the table with the correct data types.

Solution 2

The process should be: Add the column to the spoke db, Add the column to the hub db, Remove the table from the sync, Sync, Add the table back to the sync (the new column should be added automatically), Sync. If this works, it would save having to drop/recreate/repopulate the table.

Please let us know if that works.

Regards,

Oury
Please sign in to rate this answer.
Oury Ba-MSFT 19,806 Reputation points Microsoft Employee

2024-02-14T19:15:21.65+00:00

Please click "Accept Answer". This will be helpful for other community members with the same issue. Regards, Oury

Janet Putnam 50 Reputation points

2024-02-15T03:06:58.0833333+00:00

So it turns out this is not the solution. I dropped the table and ran a sync to make sure it was gone. Then I recreated the table, and added it to the sync. Re-ran the sync. It worked and I thought all was good. But now I look at the list of columns in the sync and it's not selected. Selecting the column results in the same error. I thought it was fixed but it is not. The sync db refuses to drop and re-add that table.

Oury Ba-MSFT 19,806 Reputation points Microsoft Employee

2024-02-15T18:12:32.3766667+00:00

Janet Putnam No problem, could you please verify the permissions and access rights for the sync database. If the issue persists, then I would suggest creating a support ticket so we can further investigate. Regards, Oury

Oury Ba-MSFT 19,806 Reputation points Microsoft Employee

2024-02-22T23:50:25.1566667+00:00

Janet Putnam Did you create a support case. If yes, could you please share the support ticket number. Regards, Oury

Janet Putnam 50 Reputation points

2024-02-26T16:10:40.34+00:00

Oury, Finally got a case created. Support request ID 2402260040007899.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Janet Putnam 50 Reputation points

2024-03-04T14:15:37.0533333+00:00
This has been resolved. The advise offered in the above comments were close but did not solve the issue. Here is the process that I followed that did work. Note, there are 3 databases in my sync Hub, Azure db; Prod, on prem db; Sync db, created by the sync to hold sync data.

Remove the table from the sync.

sync

verify that table was removed from sync

In prod db add the column (in my case it was already added)

use prod db to create table script

run create table script on Hub db

add column to table in sync db

add the table to the sync

run the sync. This will take a bit because the table in the Hub is empty, so the sync will fill it.

Verify everything.
Please sign in to rate this answer.
Oury Ba-MSFT 19,806 Reputation points Microsoft Employee

2024-03-05T18:51:53.1866667+00:00

Janet Putnam

Thank you for sharing this with the community. I am glad it finally worked.

Since you cannot accept your own answer. I will post it in a new answer section and kindly ask you to accept it. This will be beneficial for other community members with the same issue.

Regards,

Oury
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

syncdata db has this error "Database re-provisioning failed with the exception "

3 additional answers

Your answer