Geek Answers Handbook

How to remove Azure Data Sync SQL objects manually

Having abandoned SQL Azure Data Sync to synchronize data between two Azure SQL databases, how can I delete all objects associated with DataSync (tables, triggers, etc.)?

For instance:

DataSync.<table>_dss_tracking
DataSync.schema_info_dss
DataSync.scope_config_dss
DataSync.scope_info_dss

And all the other objects? Is there a script that can be run?

+8
source share
4 answers

There is an article on msgooroo.com:

https://msgooroo.com/GoorooTHINK/Article/15141/Removing-SQL-Azure-Sync-objects-manually/5215

Essentially the script looks like this:

-- Triggers
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
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
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    


-- Types
DECLARE @TYPE_SQL VARCHAR(MAX) = (
  SELECT
  'DROP TYPE [' + SCHEMA_NAME(so.uid) + '].[' + [so].[name] + '] ' 
  FROM systypes AS [so]
  where [so].name LIKE '%_dss_bulktype%'
  AND SCHEMA_NAME(so.uid) = 'Datasync'
  FOR XML PATH ('')
)
PRINT @TYPE_SQL
IF LEN(@TYPE_SQL) > 0
BEGIN
  EXEC (@TYPE_SQL)
END    


-- Schema
DROP SCHEMA DataSync
+21
source

, DataSync, dss TaskHosting, , .

Azure, . , :

declare @n char(1)

set @n = char(10)



declare @triggers nvarchar(max)

declare @procedures nvarchar(max)

declare @constraints nvarchar(max)

declare @views nvarchar(max)

declare @FKs nvarchar(max)

declare @tables nvarchar(max)

declare @udt nvarchar(max)



-- triggers

select @triggers = isnull( @triggers + @n, '' ) + 'drop trigger [' + schema_name(schema_id) + '].[' + name + ']'

from sys.objects

where type in ( 'TR') and name like '%_dss_%'



-- procedures

select @procedures = isnull( @procedures + @n, '' ) + 'drop procedure [' + schema_name(schema_id) + '].[' + name + ']'

from sys.procedures

where schema_name(schema_id) = 'dss' or schema_name(schema_id) = 'TaskHosting' or schema_name(schema_id) = 'DataSync'



-- check constraints

select @constraints = isnull( @constraints + @n, '' ) + 'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + ']    drop constraint [' + name + ']'

from sys.check_constraints

where schema_name(schema_id) = 'dss' or schema_name(schema_id) = 'TaskHosting' or schema_name(schema_id) = 'DataSync'



-- views

select @views = isnull( @views + @n, '' ) + 'drop view [' + schema_name(schema_id) + '].[' + name + ']'

from sys.views

where schema_name(schema_id) = 'dss' or schema_name(schema_id) = 'TaskHosting' or schema_name(schema_id) = 'DataSync'



-- foreign keys

select @FKs = isnull( @FKs + @n, '' ) + 'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] drop constraint [' + name + ']'

from sys.foreign_keys

where schema_name(schema_id) = 'dss' or schema_name(schema_id) = 'TaskHosting' or schema_name(schema_id) = 'DataSync'



-- tables

select @tables = isnull( @tables + @n, '' ) + 'drop table [' + schema_name(schema_id) + '].[' + name + ']'

from sys.tables

where schema_name(schema_id) = 'dss' or schema_name(schema_id) = 'TaskHosting' or schema_name(schema_id) = 'DataSync'



-- user defined types

select @udt = isnull( @udt + @n, '' ) +

    'drop type [' + schema_name(schema_id) + '].[' + name + ']'

from sys.types

where is_user_defined = 1

and schema_name(schema_id) = 'dss' or schema_name(schema_id) = 'TaskHosting' or schema_name(schema_id) = 'DataSync'

order by system_type_id desc





print @triggers

print @procedures

print @constraints

print @views

print @FKs

print @tables

print @udt



exec sp_executesql @triggers

exec sp_executesql @procedures

exec sp_executesql @constraints

exec sp_executesql @FKs

exec sp_executesql @views

exec sp_executesql @tables

exec sp_executesql @udt



GO

declare @n char(1)

set @n = char(10)

declare @functions nvarchar(max)



-- functions

select @functions = isnull( @functions + @n, '' ) + 'drop function [' + schema_name(schema_id) + '].[' + name + ']'

from sys.objects

where type in ( 'FN', 'IF', 'TF' )

and schema_name(schema_id) = 'dss' or schema_name(schema_id) = 'TaskHosting' or schema_name(schema_id) = 'DataSync'





print @functions

exec sp_executesql @functions

GO



--update

DROP SCHEMA IF EXISTS [dss]

GO

DROP SCHEMA IF EXISTS [TaskHosting]

GO

DROP SCHEMA IF EXISTS [DataSync]

GO

DROP USER IF EXISTS [##MS_SyncAccount##]

GO

DROP ROLE IF EXISTS [DataSync_admin]

GO

DROP ROLE IF EXISTS [DataSync_executor]

GO

DROP ROLE IF EXISTS [DataSync_reader]

GO





declare @n char(1)

set @n = char(10)





--symmetric_keys

declare @symmetric_keys nvarchar(max)

select @symmetric_keys = isnull( @symmetric_keys + @n, '' ) + 'drop symmetric key [' + name + ']'

from sys.symmetric_keys

where name like 'DataSyncEncryptionKey%'



print @symmetric_keys



exec sp_executesql @symmetric_keys



-- certificates

declare @certificates nvarchar(max)

select @certificates = isnull( @certificates + @n, '' ) + 'drop certificate [' + name + ']'

from sys.certificates

where name like 'DataSyncEncryptionCertificate%'



print @certificates



exec sp_executesql @certificates

GO



print 'Data Sync clean up finished'
+5

Azure SQL Server , SQL, .

+1

Microsoft . ,

declare @TableName nvarchar(max)
set @TableName = 'yourTableName'
--In case you wish to delete objects related to all the tables you can uncomment the following:
--set @TableName = ''

-- Generate the script to drop Data Sync tables
select 'drop table [DataSync].['+ st.name+ '];' from sys.tables as st join sys.schemas as ss on ss.schema_id = st.schema_id 
where ss.name = 'DataSync' and st.name like '%' + @TableName + '_dss_%'

-- Generate the script to drop Data Sync stored procedures
select 'drop procedure [DataSync].['+ sp.name+ '];' from sys.procedures as sp join sys.schemas as ss on ss.schema_id = sp.schema_id 
where ss.name = 'DataSync' and sp.name like '%' + @TableName + '_dss_%'

-- Generate the script to delete Data Sync triggers
select 'drop trigger [' + schema_name(schema_id) + '].[' + name + ']'
from sys.objects where type = 'TR' and name like '%' + @TableName + '_dss_%'

-- Generate the script to delete Data Sync-related udtt
select 'drop type  [DataSync].['+ st.name+ '];' from sys.types as st join sys.schemas as ss on st.schema_id = ss.schema_id 
where ss.name = 'DataSync' and st.name like '%' + @TableName + '_dss_%'
0

More articles:


All Articles