After demo building and testing features out within Azure SQL Database I like to delete the databases to save costs. Very rarely the following happens when trying to delete a database within Azure.
Failed to delete the database: TestDB. ErrorCode: 400 ErrorMessage: Database ‘TestDB’ cannot be deleted because it is used as a sync metadata database which still contains sync groups and/or sync agents.
After some digging around I remembered that I tried to setup a data sync group where this database was involved in bi-directional synchronization with another Azure SQL Database. The other database was deleted a long time ago and clicking on the sync option within the portal confirmed that nothing was actually ever connected or ready.
So I connected to the database via SSMS (SQL Server Management Studio) and had a look around, quite clearly there are sync based objects within my database with the tables naming convention as DataSync.XXXX, DSS.XXXX, TaskHosting.XXXX
Also many DSS. Stored procedures.
Also many scalar functions.
Documentation for this feature is not very detailed and there is no section on “cleaning up” / removing sync groups (even though it is simple). This being a test server I decided (rash decision) to try removing the tables first which proved to be the wrong decision (see later). To remove data sync groups all you need to do is click into the group and delete it which we will see later on. Yes, I admit, I made a bad mistake here.
So I repeat, to cleanly remove sync groups you: Click into the group and delete it…
Anyways, this is what I did before knowing about the correct and simple way. Below is a list of the tables involved with this feature which were deleted.
SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']' AS SchemaTable FROM sys.tables WHERE schema_id 1
DROP TABLE [TaskHosting].[MetaInformation] DROP TABLE [TaskHosting].[Job] DROP TABLE [TaskHosting].[MessageQueue] DROP TABLE [TaskHosting].[Schedule] DROP TABLE [TaskHosting].[ScheduleTask] DROP TABLE [DataSync].[schema_info_dss] DROP TABLE [DataSync].[scope_info_dss] DROP TABLE [DataSync].[scope_config_dss] DROP TABLE [dss].[subscription] DROP TABLE [DataSync].[provision_marker_dss] DROP TABLE [dss].[task] DROP TABLE [dss].[userdatabase] DROP TABLE [dss].[scaleunitlimits] DROP TABLE [dss].[ScheduleTask] DROP TABLE [dss].[taskdependency] DROP TABLE [dss].[syncgroup] DROP TABLE [dss].[agent_instance] DROP TABLE [dss].[UIHistory] DROP TABLE [dss].[EnumType] DROP TABLE [dss].[action] DROP TABLE [dss].[MetaInformation] DROP TABLE [dss].[agent_version] DROP TABLE [dss].[agent] DROP TABLE [dss].[configuration] DROP TABLE [dss].[syncgroupmember]
I Broke The Portal
At this point I really confused the portal. I re-produced the issue (by doing the above) and got the below screen which I faced in real life. It was stuck at a loading state and I needed access to it to delete the group.
So I used point in time restore to recover the database where then I moved the data back into the original Azure SQL Database (Yes – the import/export wizard works great with Azure SQL Databases).
Below is the data movement into the “broken” database.
I now have a fully functional sync screen where I can click into the group and agent to CLEANLY REMOVE IT!
Removing Sync Groups – The right way
The common message from this post? How to properly remove sync groups. Click into the group where then you will be given a delete option.
The outcome you should now get.
Morale of this post – Do not “jump the gun” and do something as rash as deleting tables /objects if you do not know / understand the ramifications of such an action. I have truly learnt a lesson here. I thought I would share this mistake, do not judge me too harshly and I am very thankful for Azure’s Built-in backups!
Thanks for reading.