How to Remove SQL Azure Data Sync Objects Manually

How to remove SQL Azure Data Sync objects manually

There is an article on msgooroo.com:

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

Essentially the script is as follows:

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

Remove SQL Azure Data Sync objects manually - Cannot drop schema 'DataSync'

Below is example DDL to drop the table type:

DROP TYPE [DataSync].[_TransactionIndex_7c7a43e4-46a5-43b6-8dfb-603c22e00a94_dss_BulkType_c529b2b4-4232-4083-99b3-2240eb2c106e];

Cannot export Azure SQL database

First off, let me thank @alberto-morillo for his help. There is a good chance the SQL Agent would have worked had the situation been different.

In the end, I contacted Azure Support and they gave me the following script. Everything worked fine afterwards.

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'

Is there an easy way to revert the changes that SQL Azure Data Sync Tool does to a local database?

That sync tool is used to keep a SQL Azure database in sync with your local SQL database. The tracking tables are necessary to keep the tables in sync as the data changes. It works in a similar fashion to SQL Server Merge Replication. I am not aware of an automatic way to do that right now. Merge Replication has tools and SPROCS floating around to do this but it took a while for them to come out.

If you just want to copy a database to SQL Azure you should script it, which is easy to do with SSMS, or use some other method. The sync tool is not a good way to just copy a database.

Azure SQL Sync Group Db Isolation Level Error

View the isolation state using this sql:

select name, snapshot_isolation_state, is_read_committed_snapshot_on from sys.databases

You want these settings to be both ON like below and the same on both the Azure and on-premise database.

The sql below is used to change the settings:

ALTER DATABASE {name} SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE {name} SET ALLOW_SNAPSHOT_ISOLATION ON
GO

Azure Sync to database

Azure SQL Data Sync does not support dynamic schema changes. You have to manually replicate them. Below the official answer from Microsoft documentation.

How do I get schema changes into a sync group?

You have to make and propagate all schema changes manually.

1. Replicate the schema changes manually to the hub and to all sync members.
2. Update the sync schema.

Adding new tables and columns. New tables and columns don't impact the current sync. Data Sync ignores the new tables and columns until you add them to the sync schema. When you add new database objects, this is the best sequence to follow:

1. Add the new tables or columns to the hub and to all sync members.
2. Add the new tables or columns to the sync schema.
3. Start to insert values into the new tables and columns.

Changing the data type of a column. When you change the data type of an existing column, Data Sync continues to work as long as the new values fit the original data type defined in the sync schema. For example, if you change the type in the source database from int to bigint, Data Sync continues to work until you insert a value that's too large for the int data type. To complete the change, replicate the schema change manually to the hub and to all sync members, and then update the sync schema.

---

Related Topics