February 24, 2011 at 1:55 pm
Hello all,
I set up transactional replication without using snapshot from backup, i set "Do not replicate deletes" for the article properties. Everything works fine, except that whenever a value is updated at publisher the older value is not deleted at subscriber, instead i see both old and new rows at subscriber.
I thought the problem might be with the foreign key constarints since i configured the subscriber from backup. I dropped the foreign key constraints but still the same problem is persisting.
February 24, 2011 at 2:47 pm
Dj463 (2/24/2011)
Everything works fine, except that whenever a value is updated at publisher the older value is not deleted at subscriber, instead i see both old and new rows at subscriber.
Are you deleting / inserting for your update process or are you actually using UPDATE TableName SET ColumnName... at the publisher?
If you are using delete old record / insert new record then you would need to replicate deletes else you would leave old data out there.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 24, 2011 at 3:23 pm
As i am using SQL Server 2005, i think the update statement is delivered as update only, but not pair of delete/insert.
February 24, 2011 at 9:55 pm
Dj463 (2/24/2011)
As i am using SQL Server 2005, i think the update statement is delivered as update only, but not pair of delete/insert.
Yes, you are correct. I guess I am confused as you stated you have both old and new version of a record on the subscriber. Any idea how that got there? Replication would not be doing this.
What action on you performing on the publisher that you are not seeing on the subscriber? Have you tried running a trace on the subscriber to see if you can catch the command coming through that you executed on the publisher?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 25, 2011 at 1:31 am
You said that you had removed FK constraints... was this on the publisher or subscriber? If you only removed them from the publisher, then you could be suffering from the same issue I saw some time ago. Not sure if it still applies to SQL2005, but it did on SQL2000.
Any update statement that changes the primary key or any other unique constraint is actually done as a DELETE/INSERT pair, and gets written to the transaction log as such.
Transactions to be replicated are read from the transaction log, and as long as you only update 1 row in your statement (and I think you also needed to have a certain trace flag set), SQL Server matches up the DELETE/INSERT pair it finds in the transaction log and replicates it as an UPDATE.
However... if you update more than 1 row in the UPDATE statement, they get written to the transaction log as DELETE,DELETE,DELETE... INSERT,INSERT,INSERT and SQL Server is unable to match them as pairs, so ends up replicating them as separate DELETEs and INSERTs. That leaves you with the possibility of the DELETE failing due to FK constraints, but the INSERT works.
For this situation to apply, you also need to have certain errors ignored at the subscriber... can't remember offhand exactly where you specify that.
This quirk is one reason why I would never recommend CASCADE DELETES... the child rows can suddenly disappear for no apparent reason.
February 25, 2011 at 6:37 am
Thanks Ian! I had not hit that situation, especially since my replication exposure has mostly been with 2005. Still sounds like there could be some issue as you describe.
DJ463 - can you read this post and see if this is your situation. You will have to read all the way through it. http://social.msdn.microsoft.com/forums/en-US/sqlreplication/thread/c8b27a01-4db7-4836-ad00-b59e3d20454b
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 25, 2011 at 6:53 am
David... one of the replies in your link certainly indicates that the same issue exists in SQL2005.
It would appear that all Microsoft have done is to make the catching of a singleton delete/insert pair the default, without having to resort to trace flags, but you are still potentially scuppered with a multi-row update.
February 25, 2011 at 7:01 am
Ian Scarlett (2/25/2011)
David... one of the replies in your link certainly indicates that the same issue exists in SQL2005.It would appear that all Microsoft have done is to make the catching of a singleton delete/insert pair the default, without having to resort to trace flags, but you are still potentially scuppered with a multi-row update.
Agreed. I'm a bit surprised <thankful> that I haven't run into this yet but glad that I have this to stick in the back of my mind in case I do.
Thanks for the help.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply