July 7, 2011 at 2:49 am
July 8, 2011 at 2:31 am
July 8, 2011 at 3:05 am
One way of doing this would be to modify the article properties to not replicate deletes.
But if you need this behaviour for only a short period of time you'll need to revert back this property (which you'll need to be careful about so as not to get a new snapshot for the table which would overwrite the data on the subscriber :Whistling:)
Alternatively - you can manually insert back the data being deleted on the subscriber after the delete occurs on the publisher.
July 8, 2011 at 3:29 am
July 8, 2011 at 6:22 am
modify the sp_msdel proc on the subscriber to just return
Simple alter statement which you can easily role back.
July 14, 2011 at 3:48 am
winash (7/8/2011)
One way of doing this would be to modify the article properties to not replicate deletes.But if you need this behaviour for only a short period of time you'll need to revert back this property (which you'll need to be careful about so as not to get a new snapshot for the table which would overwrite the data on the subscriber :Whistling:)
Is this feature possible? If so, how do we do it through Enterprise manager\scripts?
How about update operations. (Delete followed by insert). What would be the impact of such update operations on subscribers?
M&M
July 14, 2011 at 5:18 am
mohammed moinudheen (7/14/2011)
winash (7/8/2011)
One way of doing this would be to modify the article properties to not replicate deletes.But if you need this behaviour for only a short period of time you'll need to revert back this property (which you'll need to be careful about so as not to get a new snapshot for the table which would overwrite the data on the subscriber :Whistling:)
Is this feature possible? If so, how do we do it through Enterprise manager\scripts?
How about update operations. (Delete followed by insert). What would be the impact of such update operations on subscribers?
There is some more information I came across in this link.
http://support.microsoft.com/kb/238254
M&M
July 14, 2011 at 5:31 am
Information on how to configure articles to not replicate deletes - http://www.sqlservercentral.com/articles/Replication/3202/
Regarding deferred updates - as mentioned in the link these happen for updates to the primary key and I don't know what will happen if configuring articles to not replicate deletes is set and an update is made to a primary key.
I'll see if I can test this out and see what happens.
July 14, 2011 at 6:50 am
winash (7/14/2011)
Information on how to configure articles to not replicate deletes - http://www.sqlservercentral.com/articles/Replication/3202/Regarding deferred updates - as mentioned in the link these happen for updates to the primary key and I don't know what will happen if configuring articles to not replicate deletes is set and an update is made to a primary key.
I'll see if I can test this out and see what happens.
Thanks Winash for sharing the link. I tested this scenario of updating primary key values after configuring articles to not replicate deletes-and this worked. It seems SQL Server uses some internal logic.
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sys.sp_MSreplraiserror 20598
I found this detail from this link http://msdn.microsoft.com/en-us/library/ms152489.aspx
Also, I noticed that we need to recreate the snapshot whenever we make the change to the article (i.e when I enabled articles to not replicate deletes). It forces me to create the snapshot or use existing one, is there any way I could defer it?
Well, especially in the case when we need to enable articles to replicate again after stopping deletes for a short period of time - we could recreate a new snapshot right ?
Does this sound ok? Please share your inputs.
M&M
July 14, 2011 at 6:59 am
I'm not sure about the behaviour.
From this link:
Problem 2: Updates to Primary Keys
1) Every row updated on the publisher can also generate up to 2 commands (DELETE/UPDATE) on the subscriber
For example, Table T1 has column C1 (pk) , C2 and 1 row with C1= 1, C2 = ‘value 1’. Subscriber1 performs a synchronous update that sets C1 = 100. The transaction is accepted. Because the primary key or unique index is updated, it is processed as a deferred update. The distribution database has the following transaction:
Delete T1 where C1 = 1
Insert T1 (C1, C2) values (100, 'value 1')
However the sp_MSupd system procedure updates the PK if the PK is modified (and does not send this as a delete/insert pair) - and when I update the primary key it is sent to the distribution DB as a call to the sp_MSupd procedure and not as a delete/insert.
The article mentions "can also generate upto 2 commands" - so maybe this gets triggered under some other circumstances? :unsure:
July 14, 2011 at 7:11 am
Also, I noticed that we need to recreate the snapshot whenever we make the change to the article (i.e when I enabled articles to not replicate deletes). It forces me to create the snapshot or use existing one, is there any way I could defer it?
Well, especially in the case when we need to enable articles to replicate again after stopping deletes for a short period of time - we could recreate a new snapshot right ?
Does this sound ok? Please share your inputs.
You could recreate the snapshot (depends on how much of a hassle this is in your environment) or you could follow the advice provided by mystery jimbo in this thread (modify the sp_msdel procedure for that article to just return by commenting out the rest of the code and then do the deletes and then uncomment to revert to the original code after the deletion is done).
July 14, 2011 at 7:36 am
winash (7/14/2011)
I'm not sure about the behaviour.From this link:
Problem 2: Updates to Primary Keys
1) Every row updated on the publisher can also generate up to 2 commands (DELETE/UPDATE) on the subscriber
For example, Table T1 has column C1 (pk) , C2 and 1 row with C1= 1, C2 = ‘value 1’. Subscriber1 performs a synchronous update that sets C1 = 100. The transaction is accepted. Because the primary key or unique index is updated, it is processed as a deferred update. The distribution database has the following transaction:
Delete T1 where C1 = 1
Insert T1 (C1, C2) values (100, 'value 1')
However the sp_MSupd system procedure updates the PK if the PK is modified (and does not send this as a delete/insert pair) - and when I update the primary key it is sent to the distribution DB as a call to the sp_MSupd procedure and not as a delete/insert.
The article mentions "can also generate upto 2 commands" - so maybe this gets triggered under some other circumstances? :unsure:
Due to this update behaviour I would still prefer my suggestion of ALTERing the default replication stored procedures to contain my own logic.
Comment out existing code in the delete procedure and add an if exists update statment to the insert stored procedure.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply