Azure data sync problem

Question

Azure data sync problem

Bob Fischer 71

I am restarting sync after server problem caused us to shut it down for a few days. I created a new sync group, the sync agent on the business server is the same as before the troubles. I am syncing the same columns as before. I have 2 of the 4 tables syncing ok but a third table consistently fails to finish within 2 hours or more with cpu in the 20% range. This table has 4800 records, mostly static data. The hub data and the member data in this table are exactly the same. The table data appears to be clean, PK index is the only index and is 0% fragmented. Sync is MEMBER WINS and bidirectional. What could cause this?

The subscription ID is 56368080-1ade-463c-a6dd-79e6e7969f83
The sync group is Freight_2022_MemberWins

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Alberto Morillo 35,501 MVP Volunteer Moderator

My suggestion is try to delete the sync group, then run the cleanup scripts on the following URL:

https://github.com/vitomaz-msft/DataSyncMetadataCleanup

After that recreate the sync group.

Another suggestion, is to speed up the initial sync, create an empty database schema on the member(s) database(s) so SQL Data Sync does not have o compare data. Scale up the service tier of the databases only for the duration of the initial sync.

Hope this helps.

Bob Fischer 71 Reputation points

2022-07-22T15:25:43.927+00:00

Is there a help article of exactly what to do to set up the member-based empty schema? I am unclear about the details.

Do all of the scripts on that site need to be run? There are several.

And thanks for the astute answer.
Alberto Morillo 35,501 Reputation points MVP Volunteer Moderator

2022-07-22T17:23:04.67+00:00

Please create a dacpac of the existing database and then import it. This will create an empty database for you. SQL Data Sync will then BULK INSERT in that database, instead of comparing rows between the hub and member databases.

Run "Data Sync complete cleanup.sql" on the hub database. For the member database you will be creating the database from a dacpac.

Sorry for my late answer, I am having issues with a laptop and its battery.
Bob Fischer 71 Reputation points

2022-07-22T18:04:22.427+00:00

Many thanks
Bob Fischer 71 Reputation points

2022-07-22T19:04:17.51+00:00

I am preparing for this work to be done off hours. When I load the cleanup script into SSMS I get errors when addressing the member database. I can load the script when addressing the hub (Azure) database without errors. The lines that err are
DROP SCHEMA IF EXISTS [dss]
DROP SCHEMA IF EXISTS [TaskHosting]
DROP SCHEMA IF EXISTS [DataSync]
DROP USER IF EXISTS [##MS_SyncAccount##]
DROP USER IF EXISTS [##MS_SyncResourceManager##]
DROP ROLE IF EXISTS [DataSync_admin]
DROP ROLE IF EXISTS [DataSync_executor]
DROP ROLE IF EXISTS [DataSync_reader]

What am I missing here?
Bob Fischer 71 Reputation points

2022-07-22T19:16:10.587+00:00

I must also mention the member database cannot be created using dacpac because it contains data. It now contains datasync objects as well.
Alberto Morillo 35,501 Reputation points MVP Volunteer Moderator

2022-07-22T19:37:56.09+00:00

Use "Data Sync cleanup hub or member.sql'" to cleanup the member database.

You can also use the deprovision tool as explained on this article.
Bob Fischer 71 Reputation points

2022-07-22T20:46:50.373+00:00

Thanks again
Alberto Morillo 35,501 Reputation points MVP Volunteer Moderator

2022-07-22T20:53:06.48+00:00

You are always welcome. Let me know any obstacles you find. Have a great weekend!
GeethaThatipatri-MSFT 29,597 Reputation points Microsoft Employee Moderator

2022-07-25T14:34:48.527+00:00

@Alberto Morillo Thanks for your continued support

@Bob Fischer Thanks for your feedback and Thank you for visiting the Microsoft QA forums! Have a great day!

Regards
Geetha

Share via

Azure data sync problem

0 additional answers

Your answer