I am currently unable to delete a sync group in Azure SQL Sync

We currently have a bi-directional sync process between a hub and two reference databases (all Azure SQL, but in three different regions) that was neither completing nor stopping.

The Sync failure was first noticed nearly 72 hours after the last logged sync (examination of sync logs).

Via the Azure portal, the Sync group was shown to be 'processing', but the synchronisation was never completing.

Typical syncs in this group (scheduled for 5 minute intervals) take no more than 20 seconds, with the longest sync rarely exceeding 40 seconds.

This is the second failure of the sync group in the last 4 weeks, and it appears that both failures occurred in close proximity (time-wise) to unscheduled partial SQL Database outages or service degradations in one of the data centres hosting our database instances (this last failure being in the North Europe location on 11th Oct).

This process has previously been running uninterrupted and fault-free for approximately five months on this database. No changes had been made to any of the databases.

Based on the experience of the previous outage, the following steps have been followed:

1. Sync process stopped (no response after 24 hours) and automatic sync disabled via Azure portal interface. Result - after 24 hours, the Service still reported status as Processing (Canceling Synchronisation).

2. 24 hours after step (1), attempts were made to delete the sync group and delete the individual reference syncs but these were prevented as the status was still reported as Cancelling Synchronisation.

2. After step (2), each reference database was manually de-provisioned using the Deprovisioning Utility in an attempt to 'force' a failure of the process. Result - No change, service still reports status as Processing (Cancelling Synchronisation).

3. One hour after step (3), the hub database was manually de-provisioned. Result - No change, service still reports status as Processing (Cancelling Synchronisation).

4. Attempts to delete the references result in failures (e.g. Tracing ID : d3522a38-8918-19b9-8a93-7603150f1bf0).

5. Attempts to delete the sync group also results in a failure (TracingId=1aded73c-21ea-181b-b2e4-b8b42a6ce8b1).

What other steps are required in order to complete the deletion of the existing (failed) Sync Group so that we can re-build a new Sync group? 

Hi Stephen,

just to let you know, you are not alone in having this problem!

I have had exactly the same issue with a synch to West US, not just once but twice.  Eventually, I deleted the database and rebuilt it from the hub with a new synch group.  Not ideal but as I was reminded by MSFT - its preview.

Regards

Peter

Hi Stephen,

The issue about Windows Azure SQL Data Sync is commonly reported recently and the root cause is still under investigation.
You can try to open a case with Microsoft support and post your Subscription ID to the support enginner.

Thank you for your understanding and support.

Regards,
Fanny Liu

If you have any feedback on our support, please click here.

Hi

The previous time I had this problem, I did indeed open a case with Microsoft Support and was promptly informed [REG:113092010801554] that there was no support available as the product is still in Preview.

What would be interesting to understand at this stage is why these problems seem to have started in September. We have been running this Sync configuration without any error for five months so it is surprising that errors should start to show up now. 

Two questions arise:

1. Are the close temporal proximity of these SQL Sync failures and Azure SQL Server availability issues in one of the regions in any way related?

2. What else has changed in the Azure environment?

Rgds

Stephen

Hi Peter

Looking at the forums, there seems to have been an marked increase in reported errors with the Sync service not terminating or provisioning correctly in the past 6 weeks or so.

The last time this occurred, I did indeed end up deleting the database and then restoring it from a copy. Once the database was deleted, the sync process finally failed.

This time, I am trying a slightly different approach which may, in some way, help to pinpoint where the problem actually lies (if only for my own understanding).

Firstly, I manually de-provisioned each of the databases using the de-provisioning utility (References first, then the Hub). This had no effect whatsoever on the synch process although it did (for a brief moment) trigger some front end application errors relating to the dss tables whilst eh Hub de-provisioning was taking place. At this point, some 18 hours after the manual de-provisioning, the Sync process still has not failed. I'm sure this is significant, but not knowing the underlying Azure Sync architecture, I can't say why.

Now, I have just renamed the two Reference databases (via SQL Management Studio) that are in the North Europe and East Asia regions (the Hub is in North America). So far, this has still not had any effect on the Sync processes, but I will post an update here if anything changes.

I well appreciate that Azure Sync is still in Preview, but it begs the question as to what this really means.

If Preview products are not nearly production-ready (from and MS perspective), then other than through dedicated ongoing testing efforts it is not very likely that MS Clients and Partners will uncover any operational issues with the product. On the other had, organisations such as our own (niche ISVs) can, by making careful use of the product in production and 'semi'-production scenarios, potentially uncover some of the real operational defects and issues that can arise and that may not be caught through regular structured testing approaches. In this way MS benefits from our experience, and we get the chance to make use the products than can significantly enhance our services.

My quibble (not a complaint really) is that the level of feedback is limited, and suggestions to open a case with Support for a product that is not supported are a little circular don't you think? Quoting from the last time I DID open a Support case for Azure Synch : 'Please note that SQL Data Sync is in preview and during the preview phase, support is provided via forums only.' 

Hi Stephen and Peter,

Can you leave your sync group id?

We need sync group id to check what's wrong in your sync group.

Thanks.

Hi,

synch group is

2719173d-b67f-49a2-94d3-6d1100ad1a0e_West Europe

Its still trying to synch after 2 months !

Peter

Hi

The Synch Group ID is : 4612a0c0-8451-4dce-b555-6d081613efa1_North Central US

It's still "synching" even though the References are long gone and all the synch tables have been removed from the Hub database.

Rgds

Stephen

Hi,

For sync group id=2719173d-b67f-49a2-94d3-6d1100ad1a0e_West Europe, we cannot find this sync group id in our system. It seems deleted successfully.

For sync group id=4612a0c0-8451-4dce-b555-6d081613efa1_North Central US, we have reset this sync group. You can try again.

Sorry for late reply.