The problem

The web based interface for SQL Azure Sync leaves a lot to be desired, often taking hours to submit changes to a sync group. After dealing with this frustration for a period of time, I decided it was time to be done away with it completely.


Triggers

SQL Azure Sync uses triggers to determine when tables have been modified. These can be killed off using the following script:

DECLARE @TRIGGERS_SQL VARCHAR(MAX) = (
    SELECT
        'DROP TRIGGER [' + SCHEMA_NAME(so.uid) + '].[' +  [so].[name] + '] ' 
        FROM sysobjects AS [so]
        INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id
        WHERE   [so].[type] = 'TR'
        AND     [so].name LIKE '%_dss_%_trigger'
    FOR XML PATH ('')
)
PRINT @TRIGGERS_SQL
IF LEN(@TRIGGERS_SQL) > 0
BEGIN
    EXEC (@TRIGGERS_SQL)
END 

Tables

Data Sync also creates a series of tables with names like "%_dss_tracking" for trackign changes to each table. These to can be done away with using the following script:

DECLARE @TABLES_SQL VARCHAR(MAX) = (
    SELECT
        'DROP TABLE [' + table_schema + '].[' + table_name + '] ' 
    FROM 
        information_schema.tables where table_schema = 'DataSync' 
    FOR XML PATH ('')
)
PRINT @TABLES_SQL
IF LEN(@TABLES_SQL) > 0
BEGIN
    EXEC (@TABLES_SQL)
END

Stored Procedures

Each table that is a part of a sync group gets about 11 stored procedures in the DataSync schema, which to me seems a little extravagent. Not to worry, this script will get rid of them:

DECLARE @PROC_SQL VARCHAR(MAX) = (
    SELECT 'DROP PROCEDURE [' + routine_schema + '].[' + routine_name + '] ' 
    FROM    INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = 'DataSync' and routine_type = 'PROCEDURE'
    FOR XML PATH ('')
)
PRINT @PROC_SQL
IF LEN(@PROC_SQL) > 0
BEGIN
    EXEC (@PROC_SQL)
END

All done!