Hello,

I have a Azure DB with 3.8 GB and around 45 tables. Some tables are containing up to 900,000 rows.

I had a sync running until 5 days ago and then it ran into an out-of-memory error in one of the sync groups (there are two. 1 for daily and one for hourly). Set up and provisioning in March was not a problem at all. Sync was running for several weeks without a problem to an Azure VM based SQL Server 2012 with 14 GB RAM).

Today I found out that there were errors. I removed one table on the Azure DB and tried to refresh the schema in the Sync page. It ran hours with no result. Schema was not refreshed.

I deleted the sync groups and the sync agent. Deleted all data_sync tables on Azure DB and deleted ALL tables in the on-prem DB (Database is now completely empty). To make sure all is wiped out I ran the unprovisioning utility as well on both databases.

Registered a new Agent. Created a new sync group. Wants to refresh (or initial load) the schema from the Azure DB - SAME PROBLEM as before.

Any idea what the reason is?

not sure what the underlying issue is. but as as workaround, script the database schema minus the data, create an empty database either on-premise or in Azure, run the scripts to create the tables, then run the Refresh Schema against that database.

I emptied the tables and deleted all triggers and SPs created by the data sync. Still the same problem. sync runs hours and comes never to a result.

So I de-installed data sync and copy the table to my local Azure VM. I will wait for the final version and hope the issue is solved then.

It seems that instead of reading just the schema, data sync is reading every record in the tables. As I have a couple of tables with millions of records it seems that this creates the issue. Additional is seems that once data-sync started it will continue to do what it once started even if you change the data and schema and restart it. As you cannot kill any process in Azure, I have no clue what it is still doing or not.

Hi Ricardo,

Please let me know if my understanding is wrong:so your on-prime DB is installed in an Azure VM and you are syncing between that with a SQL Azure DB.

Sounds to me there're 2 issues:

1. One of your sync group once hit Out-of-memory issue. Is that happen in your local side(in your case, your own Azure VM I guess) or it reported as internal by data sync service? If that reported by data sync, there should be something like a tracingID, do you still have that? If not avaliable, could you please provide:

• a. Sync group ID hit this issue and where did this sync group located?
• b. When did you hit this?

2. The refresh schema runs for hours on an Azure DB contains 45 tables, even when you remove all the data_sync tables. Could you please provide:

• a. server and database name for that Azure DB.
• b. Sync group ID and where did it located? (If your ever re-create the sync group, should be a different ID with above).
• c. When did you hit this?

Data Sync will only read table schema during schema refresh, no data touched at that phase. It will read data after the sync operation started.

Hi Chao,

unfortunately I deleted all Sync-Groups and Agents now to not use additional CPU and space not needed yet.

To answer your questions:

1) The out of memory happened on the Azure DB side. Not on the VM. Reported internal by the data sync service in the Azure portal. Unfortunately deleted the log with the groups.

2) Server name nrt129x75p, located in Western Europe. I recreated the Sync groups several times. No changes. I deleted all data and kept only the schema. Same problem. I never came to the point to start a sync process because the schema refresh never ended or was successful.

MY INVESTIGATION: I had a large table with 2.1 GB and around 1 million records which I didn't need anymore. I deleted this table in the Azure DB management tool. But I didn't deleted it upfront in the sync group and refreshed the schema. When I looked into my stored procedures after I deleted the table, I still saw SPs using the table which was not there anymore. I GUESS this could create the problem. Schema update still wants to read the deleted table structure but cannot because it was deleted.

You say that Schema update is reading the table schema only and no data are touched at this phase. This would make sense but you can clearly see that schema update is stuck when a table contains a lot of data. Could it be that Schema updates validates each row and field if data are correct for data sync?

Hi Ricardo,

Some thing may help us to trouble shoot:

1. You subscriptionID, you could get that in portal.

2. How many sync groups do you have?

3. Whether the SQL Azure DB only participate in one sync group?

4. Seems the scenario cause refresh schema issue is: a) you have an existing sync group,  b) you delete one of table in Azure management portal but not remove it from sync group configuration, c) you invoke refresh schema on that Azure DB, the operation seems running forever. Please let me know if I missed anything for the scenario.

Per your question, data sync do not validate row or column data during schema refresh, it only touch the scheam then.

Hi Chao,

Here are the information you requested:

1) My subscription ID is 00e2c11e-7ed0-4362-b75a-0c620ec4e6a4

2) I had (as said I deleted them) two groups for different sets of tables. One was for hourly sync the other for daily sync

3) no in both but they were not running at the same time and no table was used in both groups

4) the scenario you describe is right.

I found out additional: I tried to delete from and add data to the table which created the problem but was not able to do this. I checked old triggers or SPs but there were none. I always got a connection time out while other tables were fine.

I was not able to get the insert and delete running. So I dropped the table and recreated it. Now this is running. Seems something was not really clean removed when I deinstalled the sync.

Ricardo