[SOLVED] How can i check Data sync group from SSMS?

How can i check Data sync group from SSMS?

I have seen some Azure DataSync tables and procedures in my Database.I'm not sure from where which database they are loaded. Is there any way to check the data sync group from SSMS?

Solution

The following query tells you which database is the hub database. Remove the SyncGroupName on the WHERE clause to bring information of all sync groups.

SELECT ud.server as HubServer, ud.[database] as HubDatabase, [db_schema] 
FROM [dss].[syncgroup] as sg 
INNER JOIN [dss].[userdatabase] as ud on sg.hub_memberid = ud.id 
LEFT JOIN [dss].[syncgroupmember] as m on sg.id = m.syncgroupid 
WHERE [db_schema] IS NOT NULL AND sg.name = 'syncGoupName'

The following query tells you all member databases participating in the sync. Remove the SyncGroupName on the WHERE clause to bring information of all sync groups.

SELECT ud2.[server] as MemberServer ,ud2.[database] as MemberDatabase, [db_schema] 
FROM [dss].[syncgroup] as sg 
LEFT JOIN [dss].[syncgroupmember] as m on sg.id = m.syncgroupid 
LEFT JOIN [dss].[userdatabase] as ud2 on m.databaseid = ud2.id 
WHERE [db_schema] IS NOT NULL AND sg.name = 'syncGoupName'

List all stored procedures related to SQL Data Sync.

SELECT 'DataSync.' + name FROM sys.procedures WHERE schema_id = SCHEMA_ID('DataSync')

List all tracking tables used by SQL Data Sync.

SELECT '['+TABLE_SCHEMA+'].['+ TABLE_NAME + ']' as FullTableName, TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '%_dss_tracking'