We have a SSIS package that will pull from a source database and run a merge statement to put the data into a "Master" database. (Eventually the "Master" database will be the source of truth, but we are a while away from that.)
After the Master Database gets the data, it replicates it down to several other databases.
With Sql Server 2008 R2 this all worked great.
But with SQL Server 2012 we are noticing that there are a bunch of delete statements for these tables being replicated.
The Merge Statement in the SSIS Package does not delete anything.
when NOT MATCHED BY SOURCE then update set IsActive = 0
So I am left to wonder why this is happening. Could SQL Server 2012 be translating my
MATCHED Update to a delete and insert? (that seems insane, but I can't seem to figure out why this is happening).
NOTE: We turned off replication of delete statements and the problem stopped, but I want to know what is going on.
Why would I be seeing so many deletes when the source seems unchanged?
Is there a way I can see the transactions posted against a table?
NOTE: I tried doing a SQL Trace, but it did not show any deletes. Only the merge I described above.