2

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.

Improve this question
6
  • Can you check the article property -->Statement Delivery --> Update delivery format -- what is it set to ?
    – Kin Shah
    May 9, 2013 at 18:46
  • 1
    One more thing to add -- please verify if any column that is part of a unique constraint is updated. If this is true, this means SQL Server is implementing the Update as a Deferred Update, which means as a pair of DELETE/INSERT operations. This "deferred update" causes replication to send a pair of DELETE/INSERT statements to the subscribers. refer : blogs.msdn.com/b/sqlserverfaq/archive/2009/04/30/…
    – Kin Shah
    May 9, 2013 at 18:54
  • @Kin - It is set to SCALL <Stored Procedure>
    – Vaccano
    May 9, 2013 at 21:08
  • @Kin - The merge statement does have the PK in it. (I did not realize that.) The PK is an enterprise wide value and should never change... But something odd is happening, so maybe it is changing somehow.
    – Vaccano
    May 9, 2013 at 21:10
  • Please refer to the link that I mentioned. To make sure that the Updates are NOT executed as a pair of Delete and Insert statements you can enable trace flag 8207 on Publisher Server.
    – Kin Shah
    May 9, 2013 at 23:34

1 Answer 1

Reset to default
3

Can you check the article property -->Statement Delivery --> Update delivery format -- what is it set to ?

One more thing to add -- please verify if any column that is part of a unique constraint is updated. If this is true, this means SQL Server is implementing the Update as a Deferred Update, which means as a pair of DELETE/INSERT operations. This "deferred update" causes replication to send a pair of DELETE/INSERT statements to the subscribers. refer : here

To make sure that the Updates are NOT executed as a pair of Delete and Insert statements you can enable trace flag 8207 on Publisher Server.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.