Cleaning up after disabling Azure SQL Data Sync

If you are a SQL Server enthusiast like me, you were probably excited when Microsoft introduced Azure SQL Data Sync. It allows you to setup synchronization between two databases with a simple point & click interface.

What I liked about it:

• You can synchronize in multiple directions and your databases are available for use while synchronization is running (Options available: Bi-directional, Sync to the Hub or Sync from the Hub)
• No need to change your model to accommodate any special requirements – it just works with SQL Azure compatible databases
• For small databases you can be up and running within minutes

What I didn’t like:

• Synchronization is tightly bound to the schema at the time you created your SQL Data Sync Group’s Sync Rules (I know, a mouthful, right?). Anytime you make schema changes you have to update your synchronization configuration. Look under “Sync Rules” for this. Don’t forget to Refresh your Schema every time you use this tool as it seems to cache your schema. Note that this will only replicate the schema for your tables. You have to manually synchronize other objects (views, stored procedures, functions, etc).
• Synchronization is performed by an agent that runs periodically. The lowest frequency available is 5 minutes. Feature request: Allow for a continuous option to sync changes as they happen.
• SQL Data Sync adds quite a few objects in your database (the purpose of this article). A new schema will be added to your database – DataSync. With supporting objects. Based on my experience, at least 11 stored procedures, 3 triggers and 1 type is created for every table in your database.
• You can only synchronize within the same Azure account. I had a use case that necessitated doing this across two accounts (one in China and one in the US) and was not able to use this technology.

OK, so you tried it out and decided its not for you. Disabling it is simple – Delete the SQL Data Sync Group and you’re done, right?

Almost… Say you didn’t do that, but simply dropped one of the databases or for some reason the group deletion failed to fully cleanup your database. In either case, you’ll be left with quite a few objects in your database. In my testing, as of 6-27-2014, when you delete the sync group, you’re still left with 4 tables and the schema “DataSync” isn’t dropped, this is probably a bug that will be fixed eventually, but in the meantime below are some scripts to help you out.

This is how you can find all the objects created by DataSync:

-- Find all SQL Data Sync triggers
select name from sys.triggers where name like '%_dss_%_trigger';
-- Find all SQL Data Sync tables
select table_schema + '.' + table_name from information_schema.tables where table_schema='DataSync'
-- Find all SQL Data Sync procedures
select 'DataSync.' + name from sys.procedures where schema_id = SCHEMA_ID('DataSync')
-- Find all SQL Data Sync types
select name from sys.types where schema_id = SCHEMA_ID('DataSync')

Note that in the case of triggers, I am relying on the current naming convention. Be careful that this doesn’t match any of your triggers. If it does, exclude them by adding additional where criteria. The rest should be safe, assuming you haven’t created any of your own objects under the DataSync schema.

This script will generate the drop statements you need:

-- Hit CTRL-T for "Results to Text"
GO
set nocount on
-- Generate drop scripts for all SQL Data Sync triggers
select 'drop trigger [' + name + ']' from sys.triggers where name like '%_dss_%_trigger';
-- Generate drop scripts for all SQL Data Sync tables
select 'drop table ' + table_schema + '.[' + table_name + ']' from information_schema.tables where table_schema='DataSync'
-- Generate drop scripts for all SQL Data Sync procedures
select 'drop procedure DataSync.[' + name + ']' from sys.procedures where schema_id = SCHEMA_ID('DataSync')
-- Generate drop scripts for all SQL Data Sync types
select 'drop type DataSync.[' + name + ']' from sys.types where schema_id = SCHEMA_ID('DataSync')
-- COPY output and run in a separate connection

Finally, drop the schema:

-- Once you run the drop scripts above, drop the schema
drop schema DataSync

You are done!

Here is the complete script: azure_sql_data_sync_clean.sql

‍