Data Sync between tables from SQL Server & Db2 - how to delete rows

sql server sync tables between databases automatically
how to sync data from one database to another in sql server
data synchronization between two databases
real time data synchronization sql server
sql server data synchronization tools
synchronize sql server databases in different remote sources
sql server copy data from one table to another
sync data between sql server and mysql

We have two databases one in SQL Server & one in DB2, we have a scenario where we do some data inserts & data updates and deletes in SQL Server & at the same time we also do data inserts updates & deletes in Db2.

We sync data back & forth using some processes, whenever there is a change from SQL Server we sync data to db2 for insert, update & delete, if we have a change in db2 we sync data to SQL Server, we use IBM MQ messages which we dequeue the messages to sync the changes back and forth.

Everything was good until we had some issues of data sync from Db2 to SQL Server, one of our process was down which sync from db2 to SQL Server, so there is an on demand job that runs every night that will do full data refresh from Db2 to SQL Server but we are only doing Merge Update & insert, we are not doing delete as data which is yet to be synced to db2 is also present in SQL Server, so we cannot directly delete as both databases can have more or less records, so data on SQL Server some of them are left orphan, we have a scoping so data which is getting updated in SQL Server cannot be change in db2 and vice versa.

My question is when we are syncing from Db2 to SQL Server, how to identify records that got deleted from db2 only so that we can delete those from SQL Server, we don't want to delete records that are created in SQL Server but yet to be sent to db2, we have 114 tables and we cannot maintain a flag if that is an option to differentiate.

When you said you are synchronizing data back and forth between MS SQL Server and DB2 Server, how are you capturing the changes? If using some CDC tool (IDR, GoldenGate, Informatica), these tools allow you to detect conflicts so you can decide what records to keep or delete.

If you are capturing your changes by an in-house development (triggers or your own log scrapper), you should keep at least the operation type and timestamp in your temporary change data set, so that you can recognize the operation.

If you are comparing the tables and deal with changes, you won't be able to recognize if missing columns at DB2 side represents rows deleted on DB2 side or rows added to SQL side... But you can fix that, by developing a proper change data capture mechanism.

How to automatically compare and synchronize SQL Server data, Get started. In SQL Server, copying tables between the databases of the same SQL instances are relatively easier than copying the data between� Tutorial: Set up SQL Data Sync between databases in Azure SQL Database and SQL Server. 01/14/2019; 9 minutes to read; In this article. APPLIES TO: Azure SQL Database In this tutorial, you learn how to set up SQL Data Sync by creating a sync group that contains both Azure SQL Database and SQL Server instances.

Change tracking on the sql server side might be a viable option (as long as all the tables you would like to sync/"delete from" have a primary key).

With CT you could track which rows, for each table, were created at the sql server side since the last sync from sql server to db2. Those rows should not be deleted yet:

DELETE 
FROM SQL_SERVER_TABLE
WHERE
  NOT EXISTS(SELECT * FROM CHANGETABLE())
  AND NOT EXISTS(SELECT * FROM DB2_staging)

Six different methods to copy tables between databases in SQL Server, To synchronize data between on-premises SQL Server to the Azure (Hub) Also , a tracking table is created to track the other changes which� In the sync group (e.g. Sync Groupe 1), one database is defined as the Hub database (must be an Azure SQL database) and the rest of databases under the sync group are the members databases. The data synchronization appears between the Hub and individual member database only.

I would connect SQL to DB2 via linked servers (more there : https://docs.microsoft.com/fr-fr/sql/relational-databases/system-stored-procedures/sp-addlinkedserver-transact-sql?view=sql-server-ver15) and then do queries to find out which record are missing on both sides. This can be accomplished with OPENQUERY. You can do something like that :

SELECT * FROM YourSqlTable
EXCEPT 
SELECT * FROM OPENQUERY(YOURDB2SERVER, 'SELECT * FROM YourDB2Table')

And then the same thing inverted :

SELECT * FROM OPENQUERY(YOURDB2SERVER, 'SELECT * FROM YourDB2Table')
EXCEPT 
SELECT * FROM YourSqlTable

You can then send the records on the right server .

If you have a lot of tables to compare you can write these queries with dynamic SQL

DECLARE @TABLENAME nvarchar(200);
DECLARE TABLE_CUR CURSOR FOR
     SELECT TABLE_NAME FROM YourDatabaseName.INFORMATION_SCHEMA.TABLES;

OPEN TABLE_CUR 
FETCH NEXT FROM TABLE_CUR INTO @TABLENAME;

WHILE @@FETCH_STATUS = 0  
BEGIN  
    DECLARE @Query nvarchar(MAX);
    SET @Query =  'SELECT * FROM OPENQUERY(YOURDB2SERVER, ''SELECT * 
                   FROM '+ @TABLENAME + ' '')
                   EXCEPT 
                   SELECT * FROM '+ @TABLENAME 

    -- Don't forget the double '' for openquery

    EXEC sp_executeSQL @Query;

    SET @Query =  'SELECT * FROM '+ @TABLENAME + ' 
                   EXCEPT
                   SELECT * FROM OPENQUERY(YOURDB2SERVER, ''SELECT * 
                   FROM '+ @TABLENAME + ' '')'
    -- Don't forget the double '' for openquery

    EXEC sp_executeSQL @Query;
END
CLOSE TABLE_CUR;  
DEALLOCATE TABLE_CUR;  

What is SQL Data Sync, Learn how to synchronize data in SQL Server database tables using Data Compare tool in dbForge Studio for SQL Server and dbForge Data Compare for SQL� Change tracking on the sql server side might be a viable option (as long as all the tables you would like to sync/"delete from" have a primary key). With CT you could track which rows, for each table, were created at the sql server side since the last sync from sql server to db2. Those rows should not be deleted yet:

Thanks for the suggestions, I am not using CDC, but maintaining changes in a LOG table which are yet to be synced to DB2, so what I will be planning now is to copy all records from Db2 into staging table of SQL Server and make a left join against log table to copy records that are there in only SQL but not in Db2 into a temp table.

Now during merge from staging to SQL main table, I would insert/update based on unique key matching & delete from SQL table if source staging table (db2) doesn't have record but there in SQL main table, after the merge operation now I would re-insert the records that are in temp table so I won't lose records from SQL table with are not synced.

How to synchronize table data in SQL Server databases?, With dbForge Data Compare for SQL Server, you can schedule almost real-time database synchronization. You can set up the process by� ApexSQL Data Diff is a SQL Server data comparison and synchronization tool which detects data differences and resolves them without errors. It can compare and synchronize live databases and native or natively compressed database backups and generate comprehensive reports on the detected differences.

How to automatically synchronize data in two SQL Server databases , One table gets updated with data more often. Now there is a need to keep these 2 table in sync. This can happen as a nightly process. What is the� dbForge Data Compare for SQL Server makes the whole process of data comparison and synchronization simple and self intuiting. In this article, we have touched upon most salient features of the tool. However, there are many other powerful features that will be useful for database administartors and developers.

SQL Server - synchronizing 2 tables on 2 different databases, This overview introduces SQL Data Sync for Azure, which allows you to sync Data Sync creates tables in this database and runs a frequent workload. Migration from SQL Server to Azure SQL Database, Azure Database� The data sync option in Azure does not support temporal tables and I need to find another way of doing the sync between the databases. I want to copy the data from one temporal table to another temporal table of another database using Azure data factory.Will Azure data factory support the data sync between temporal tables? What will be the best

What is SQL Data Sync for Azure?, Synchronizing data in replication refers to data and schema changes propagating between the Publisher and Subscribers in SQL Server.

Comments
  • It sounds like you're manually syncing updates between the systems, both of which are being treated as master data. This has the potential to go more wrong, frankly. Personally, I'd probably make one side "master", and set up replication, then have the other side push updates to master via INSTEAD OF triggers, and wait for the data to filter back to the synced table, which should be more stable.
  • We have two databases one in SQL Server& existing Db2(Mainframes) which is a legacy system. How can we create replication for two different databases in SQL & Db2?, so to fix this we are sending IBM MQ messages and each side is consuming those messages to sync the changes in either systems.DB2 to an extent is like master and SQL has less tables which is scoped for phase 1,the process to load the changes from db2 to sql is broken and we have on demand that refreshes full db by copying to staging tables to SQL,here we can't delete in sql as we may lose data which is yet to be sent to DB2, how ?