Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Can you check the article property -->Statement Delivery --> Update delivery format -- what is it set to ? –  Kin May 9 '13 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 May 9 '13 at 18:54
    
@Kin - It is set to SCALL <Stored Procedure> –  Vaccano May 9 '13 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 '13 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 May 9 '13 at 23:34
show 2 more comments

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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