Azure Data Sync Failure

Question

Azure Data Sync Failure

Aaron Wong 40

We are facing issue in Azure Data Sync in one of our data sync groups. The sync group had 13 tables that were syncing from the Hub (Azure SQL DB) to the member DB(SQL On-Prem DB) on the client side. We removed a table "RMBS_US_Collat_Detail_Latest_Custom" from the sync group, ran the attached script and removed any residual sync objects for that table. The same script was run by the client and the sync objects and the table itself was dropped so that we could do the schema changes and readd the table back to the sync group. When readding the table , after the schema changes, we are getting the following error on the sync group, even though the table is not added back now. We are just syncing the group to make sure the remaining tables sync successfully, before adding the removed table back again. The Hub and Member DB schema has been refreshed in the syncgroup, made sure there are no sync objects left for the removed table using the script provided by Microsoft support earlier. Still, we keep getting the same error. The sync group itself is not working now. Obviously, we can't remove all the client tables in the sync group again as it will impact their production environment. Please let us know how to fix the issue and proceed further.

Error: Sync failed with the exception "An unexpected exception occurred in a background thread that is enumerating changes. See the inner exception for more details.Inner exception: The stored procedure '[DataSync].[RMBS_US_Collat_Detail_Latest_Custom_dss_selectchanges_ee76f35b-6163-403e-9314-9ee72be12371]' doesn't exist. For more information, provide tracing ID ‘d86906ab-d606-4704-a467-bd22c60879aa’ to customer support."

Member DB.png

Remove DataSync Tables - RMBS_US_Collat_Detail_Latest_Custom.txt

Hub DB.png

Sync Error.png

Looking for your support as soon as possible.

Thanks!

Nikhil Duserla 9,690 Reputation points Microsoft External Staff Moderator

2026-04-05T21:00:35.4733333+00:00
Hello @Aaron Wong ,

it looks like your sync group is still expecting the old Data Sync stored-procedure for RMBS_US_Collat_Detail_Latest_Custom, even though you removed that table. When the next sync run kicks off, the hub tries to call:

[DataSync].[RMBS_US_Collat_Detail_Latest_Custom_dss_selectchanges_]

…but that proc no longer exists, so the entire sync group fails. Here’s what you can try:

Refresh the Sync Group schema in the Azure portal

In the portal, go to your Hub database > “Sync to other databases” > select the sync group.

Click Refresh Schema and wait for it to complete.

Hit Save. This operation will regenerate all DataSync objects (tracking tables, triggers, and the “dss_selectchanges…” procs) for the tables currently in the group. It also removes references to tables you’ve dropped.

Manually clean up any orphaned DataSync procs on the Hub
- Connect to your Hub DB in SSMS and run: SELECT * FROM sys.procedures WHERE name LIKE 'RMBS_US_Collat_Detail_Latest_Custom%' AND schema_id = SCHEMA_ID('DataSync'); - If any show up, explicitly DROP them. - Then do another **Refresh Schema** + **Save** in the portal. If Refresh Schema still fails, re-provision the member endpoint - In the portal, under the same sync group, remove the on-prem member DB. - Save & let the group stabilize (it should show only 12 tables). - Re-add the member DB (this forces a re-initialization for the remaining tables). - Trigger a **Sync Now** and confirm the 12 tables sync successfully. Once all other tables are syncing cleanly, re-add RMBS_US_Collat_Detail_Latest_Custom - Make your schema changes. - Add that table back into the sync group. - Let Data Sync provision its objects. - Run a full sync.

Hope that clears it up! If you still hit the missing-proc error, please let us know:

• Did the Refresh Schema complete without errors? • After Refresh, can you still see any “dss_selectchanges…” procs for the dropped table in your Hub DB? • Are there any errors in the Sync Metadata database (SMD) logs when you re-provision the member endpoint?

Reference docs

Troubleshoot data sync issues: https://learn.microsoft.com/azure/azure-sql/database/sql-data-sync-troubleshoot

Missing data sync objects: https://learn.microsoft.com/azure/azure-sql/database/sql-data-sync-troubleshoot#missing-data-sync-objects

Resolve SyncOperation_GenericFailure: https://learn.microsoft.com/azure/azure-sql/database/sql-data-sync-best-practices#create-sync-group

Was this helpful?
SAI JAGADEESH KUDIPUDI 2,210 Reputation points Microsoft External Staff Moderator

2026-04-06T01:19:27.4366667+00:00

Hi Aaron Wong,
We have reviewed the screenshot you provided. Could you please share the requested details with us via a private message?
Saraswathi Devadula 15,760 Reputation points Microsoft External Staff Moderator

2026-04-07T04:15:35.1833333+00:00
Hi Aaron Wong,

a) Why wasn’t the table removed from sync metadata correctly?
In Azure SQL Data Sync, removing a table or database isn’t immediate—it requires a deploy/apply step and time for deprovisioning. Microsoft guidance is clear: remove the object, deploy the change, and wait for deprovisioning before making further edits. If changes are made back-to-back, the portal state can become inconsistent and operations may fail.

Recovery path: Use the supported reset: remove the table → save/deploy → wait for deprovisioning → re-add the table for a clean re-provision and initial sync. This avoids manipulating internal artifacts directly.

**b) Can we rename a v2 table back to the original name after syncing?
**Creating a v2 table is a safe way to validate sync before cutover. However, Microsoft documentation doesn’t guarantee that simply renaming a synced table will continue working without adjustments. Sync is driven by the Sync Schema, which must be refreshed and redeployed when schema changes occur.

Guidance: If you rename, treat it as a controlled change. Refresh schema and re-select the table if needed. If inconsistencies appear, use the supported remove → re-add path to force a clean re-provision. Plan this as a maintenance-window activity.

**c) Do applications need to update references to v2?
Yes—if you keep the new name (e.g., Table_v2), applications must update queries to point to it. This is why the v2 approach is considered a controlled cutover.
**
Note: Some teams use a view or synonym to preserve the original logical name, minimizing code changes. Feasibility depends on your app and database design.

d) Does refreshing schema reinitialize all tables?
No. Refresh schema updates the list of tables/columns available for sync, but it doesn’t automatically re-seed data.

Supported reinitialize: Happens when you remove a table → deploy → wait for deprovisioning → re-add → allow initial sync. That process reinitializes only the impacted table.

e) When does a full reinitialize occur?

Single table reset: Remove the table → deploy → wait → re-add → initial sync.

Member-level reprovision: If a member DB is inconsistent, remove it from all sync groups → re-add → deploy.

Full sync-group rebuild: If the sync group itself is corrupted, delete and recreate it. This is the most disruptive option.

Suggested compact wording for customers:

Refresh schema updates the schema selection; it doesn’t re-seed all tables.

A table is reinitialized only when removed, deployed, deprovisioned, and re-added.

For v2 cutover, apps point to v2; if you later rename, plan it as a controlled change and refresh schema or re-add the table to ensure clean reprovisioning.
Saraswathi Devadula 15,760 Reputation points Microsoft External Staff Moderator

2026-04-09T04:14:56.23+00:00

Hi Aaron Wong, I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.
Saraswathi Devadula 15,760 Reputation points Microsoft External Staff Moderator

2026-04-10T03:20:58.7833333+00:00

Hi Aaron Wong, I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.

1 answer

Your answer

SAI JAGADEESH KUDIPUDI 2,210 Reputation points Microsoft External Staff Moderator

2026-04-06T01:19:27.4366667+00:00

Hi Aaron Wong,
We have reviewed the screenshot you provided. Could you please share the requested details with us via a private message?
Saraswathi Devadula 15,760 Reputation points Microsoft External Staff Moderator

2026-04-09T04:14:56.23+00:00

Hi Aaron Wong, I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.
Saraswathi Devadula 15,760 Reputation points Microsoft External Staff Moderator

2026-04-10T03:20:58.7833333+00:00

Hi Aaron Wong, I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.

Answer 1

Hi **Aaron Wong,

What happened**:

From the symptoms you described, the sync group kept trying to enumerate changes for the removed table because Data Sync metadata still contained a reference to that table, even after the table was removed from the sync schema and the table-level DataSync objects were dropped. That is consistent with the behavior you observed: once you refreshed the schema and manually removed stale references in DataSync.scope_config_dss (the config_text field), the sync group was able to start again.

In other words, this wasn’t caused by the remaining 12 tables themselves—rather, it was a metadata inconsistency / leftover reference that caused Data Sync to look for a procedure that had already been removed: [DataSync].[RMBS_US_Collat_Detail_Latest_Custom_dss_selectchanges_ee76f35b-6163-403e-9314-9ee72be12371].

1) “Is this the right approach, or was there a better way to handle it?”

Your overall approach is the right high-level approach: remove the table from the sync group, make schema changes, then add it back after schema refresh. However, the key improvement is to ensure the deprovision step completes cleanly before making changes and reintroducing the table.

Recommended “best practice” sequence (to avoid orphaned metadata):

Remove the table from the sync group in the portal.

Deploy/save the sync group change and allow deprovisioning to finish (this step is important—removing and then immediately making additional edits without deploying can leave the portal and backend in an inconsistent state).

Confirm the table is no longer part of the sync schema (and that DataSync artifacts for that table are gone on hub and member).

Perform the schema change on both hub and member (Data Sync expects schema compatibility; failures to apply changes can lead to warnings/out-of-date states).

Refresh schema in the sync group UI and then add the table back.

About manually editing DataSync.scope_config_dss: it can resolve a stuck state (as you proved), but it’s generally better as a last resort because those are service-managed objects. The preferred path is clean deprovision + schema refresh + redeploy (and if needed, remove/re-add the member endpoint).

2) “Can we add the deleted table into another sync group to get it ASAP?”

Yes—this can be a good way to accelerate delivery as long as there is no overlap.

Important constraint: Data Sync does not handle circular references, and specifically:

“Any row that is synced by one sync group can’t be synced by another sync group.”

So the safe design is:

Keep Sync Group A running the existing 12 tables.

Create Sync Group B with only RMBS_US_Collat_Detail_Latest_Custom (and any strictly required dependent tables).

Ensure that this table is not included in both groups at the same time.

This avoids the “same row in multiple groups” scenario that Data Sync explicitly warns against.

3) “If we run two sync groups in parallel, will server resources be impacted? Will tempdb be a problem?”

Running sync groups in parallel will increase workload on:

the hub database,

the member database, and

the sync metadata database (which Data Sync uses continuously and “runs a frequent workload”).

So yes, you should expect higher CPU/IO/log usage during parallel sync. Whether tempdb becomes a bottleneck depends on the workload pattern (volume of DML changes, size of tracking tables, and concurrent user workload). Data Sync does add system-created tracking tables and stored procedures under the DataSync schema, and overall overhead scales with table width and traffic.

Practical guidance:

If your primary goal is “get the removed table to the member ASAP,” make Sync Group B one-directional (Hub → Member) if business allows—this reduces conflict processing and can reduce overall churn.

Monitor CPU, log write %, and tempdb utilization during the parallel run, and throttle by increasing sync interval if needed.

4) “Can we increase the DTU size for the server/pool while sync is happening, without impacting the sync process?”

You can scale Azure SQL Database / Elastic Pool resources while sync is running, but you should plan for a brief connectivity interruption at switchover:

Azure SQL “enables you to dynamically add more resources … with minimal downtime; however, there is a switchover period where connectivity is lost … which can be mitigated using retry logic.”

So, the safest expectation to set is:

Scaling is supported and commonly used to mitigate resource pressure,

but the sync agent/job may need to retry if it hits the brief reconnect window.

Also note: if you encounter schema refresh timeouts, Microsoft explicitly recommends considering scaling up the sync metadata database SKU, because lower SKU can time out on schema refresh for complex schemas.

5) “For future datatype changes / adding columns on a synced table, what is the best approach? Is removing table + clearing objects + deleting client table OK?”

Best practice is to treat schema changes as a planned change process, because Data Sync is primarily a data sync mechanism and schema incompatibilities can break propagation (leading to warning/out-of-date states if not addressed).

Recommended approach for schema changes on an already-synced table:

Remove the table from the sync group and deploy the change (wait for deprovision to complete).

Apply schema changes on both hub and member.

Refresh schema in the sync group UI.

Add the table back and allow initial sync for that table to reinitialize.

Deleting the member-side table can be acceptable if your intention is to fully re-seed it from the hub (Hub → Member), but it should be done carefully (maintenance window / controlled cutover), since it affects applications that read that table.

A lower-risk alternative for large/high-importance tables:

Create a new “v2” table name, sync it, validate, then do a controlled application cutover (rename/switch in a short window). This reduces risk to the ongoing sync group and avoids prolonged downtime for consumers.

Kindly please check and let us know the update.

Share via

Azure Data Sync Failure

1 answer

Your answer