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

Janet Putnam 50 Reputation points
2024-02-02T14:12:42.7766667+00:00

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.

Azure SQL Database
0 comments No comments
{count} votes

Accepted answer
  1. 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.

    1. Remove the table from the sync.
    2. sync
    3. verify that table was removed from sync.
    4. In prod db add the column (in my case it was already added)
    5. use prod db to create table script
    6. run create table script on Hub db
    7. add column to table in sync db
    8. add the table to the sync
    9. run the sync. This will take a bit because the table in the Hub is empty, so the sync will fill it.

    Regards,

    Oury


3 additional answers

Sort by: Most helpful
  1. 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.


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


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

    1. Remove the table from the sync.
    2. sync
    3. verify that table was removed from sync
    4. In prod db add the column (in my case it was already added)
    5. use prod db to create table script
    6. run create table script on Hub db
    7. add column to table in sync db
    8. add the table to the sync
    9. run the sync. This will take a bit because the table in the Hub is empty, so the sync will fill it.

    Verify everything.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.