How to remove the Sync Metadata Database from Azure

Mike Welborn 56

Before I begin, please note that all of my database are Azure SQL databases. I have no local server or database or installed data sync agent.

I am trying to use the Azure SQL Data Sync tool to replicate data from my transactional database (Azure SQL) to a reporting database(Azure SQL).

I went through the initial configuration in my development environment and am ready to stand it up in my production environment.

The problem is that when I was working on my proof of concept in the development environment I configured the metadata database on my development server.

Now when I attempt to stand this up in my production environment I don't have the option to configure a metadata database. That is, when I go to my production database (on a production server) and attempt to create a new sync group, the Sync Metadata Database option is filled in with the metadata database I created on my development server. I can't rely on that for a production configuration.

I would like to remove everything I did on the development server and start fresh in production, but I cannot find a way to remove the existing Sync Metadata Database.

Looking for some way to remove the existing Sync Metadata Database or transfer it to another server. Any ideas?

3 answers

Alberto Morillo 33,426 MVP

Please run the scripts provided on GitHub. Click here. There you will find the instructions needed to use the scripts.

Mike Welborn 56 Reputation points

2023-07-12T18:15:18.88+00:00

This answer is not applicable to my issue. I can remove all of the data sync elements but that doesn't change whether or not the Sync MetaData Database is present. The question is not how to get rid of the data sync elements, but rather how to get rid of or move the Sync MetaData Database.

Alberto Morillo 33,426 MVP

On the link I provided above you will find below script that will generate a script that you can run to remove all SQL objects created for SQL Data Sync your database, once you remove the Sync Group.

https://github.com/vitomaz-msft/DataSyncMetadataCleanup/blob/master/Data%20Sync%20complete%20cleanup.sql

declare @n char(1)
set @n = char(10)
declare @triggers nvarchar(max)
declare @procedures nvarchar(max)
declare @constraints 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'

-- 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 @FKs 
print @tables
print @udt 

exec sp_executesql @triggers
exec sp_executesql @procedures 
exec sp_executesql @constraints 
exec sp_executesql @FKs 
exec sp_executesql @tables
exec sp_executesql @udt 

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 

DROP SCHEMA IF EXISTS [dss]
DROP SCHEMA IF EXISTS [TaskHosting]
DROP SCHEMA IF EXISTS [DataSync]
DROP USER IF EXISTS [##MS_SyncAccount##]
DROP USER IF EXISTS [##MS_SyncResourceManager##]
DROP ROLE IF EXISTS [DataSync_admin]
DROP ROLE IF EXISTS [DataSync_executor]
DROP ROLE IF EXISTS [DataSync_reader]

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

print 'Data Sync clean up finished'

Mike Welborn 56 Reputation points

2023-07-12T18:47:59.8233333+00:00

I found the answer. I had to go to PowerShell.

Find the SyncGroup

Get-AzSqlSyncGroup -DatabaseName [database name]-ResourceGroupName []resource group] -ServerName [server name]-

Remove the SyncGroup

Remove-AzSqlSyncGroup -DatabaseName [database name] -Name [sync group(from above command)] -ResourceGroupName [resource group] -ServerName [server name]
Please sign in to rate this answer.
Alberto Morillo 33,426 Reputation points MVP

2023-07-12T18:56:33.6733333+00:00

Even if you remove the Sync Group, you may need to run the scripts I provided, or you may run into issues when copying the database or exporting the database in the future.
Sign in to comment
GeethaThatipatri-MSFT 29,017 Reputation points Microsoft Employee

2023-07-12T19:31:53.6033333+00:00

Hi, @Mike Welborn Thanks for posting your question

The sync metadata Db is per sub per region. So if your prod server is under the same subscription as test server, then the same sync Db will be used.

Regards

Geetha
Please sign in to rate this answer.
Mike Welborn 56 Reputation points

2023-07-12T21:42:08.2433333+00:00

Yes - I figured that part out. I still want to put it on a production server. I can do that now after running the PowerShell scripts above

GeethaThatipatri-MSFT 29,017 Reputation points Microsoft Employee

2023-07-14T12:40:40.5733333+00:00

Hi, @Mike Welborn Thanks for your response, did you get a chance to execute the PowerShell script? let us know if you need any other information.

Regards

Geetha

Mike Welborn 56 Reputation points

2023-07-14T13:34:37.82+00:00

Thank you. Yes, I did. They key was that I wasn't seeing the offending sync group in the Azure Portal UI. To be fair, it may be there somewhere but I didn't see it. I had been playing around and created several sync groups and deleted several sync groups in the UI. Somehow the offending group stopped being displayed in the UI. With PowerShell I was able to see that there was a sync group out there so I also used PowerShell to delete that group. With the group gone the Sync Metadata Database stopped automatically appearing.

GeethaThatipatri-MSFT 29,017 Reputation points Microsoft Employee

2023-07-17T14:23:37.18+00:00

@Mike Welborn Great that you figured it out and thanks for sharing the information here.

We noticed that the response on this thread was rated low. Thank you for taking the time to share your feedback.

The survey we are taking is the testimony of our team providing timely attention to the reported issue and partnering with customers to ensure their voice reaches the engineering team to improve the product.

would like to request you to reconsider the feedback. In case you still feel that you are not receiving the right support, please let me know and I will work to further improve it.

Regards

Geetha
Sign in to comment

Share via

How to remove the Sync Metadata Database from Azure

3 answers