SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Super Fast Transactional Replication Repair


Super Fast Transactional Replication Repair

Author
Message
Edward.Polley 76944
Edward.Polley 76944
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 264
Comments posted to this topic are about the item Super Fast Transactional Replication Repair
paul.barbin
paul.barbin
SSC-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
Points: 131 Visits: 106
Great "article" ;-). ...sorry that was just too easy.

I feel like I know a little bit about replication but I admit that solution never crossed my mind. It MIGHT have crossed my mind to create a record that this one is supposed to update, but simply commenting out the error is simple and effective. And takes care of the potential issue where several records have been removed.

Thanks.
Nick_UK
Nick_UK
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1656 Visits: 1212
This is a great article, thank you.

Just in case this helps anyone else, I've just been testing this suggestion and there were a couple of things which are worth noting; the error section in the proc which I needed to update was much larger as it went on to log data into a 'conflict_xxxx' table but commenting out all of the IF @@rowcount = 0 Begin/End section did the job just the same but when I tried to run the new proc in I received an error saying 'Invalid pseudocolumn "$sys_p2p_cd_id".' I was able to get around this by using the DAC connection and running the new proc in, transactions then caught up just as described.

This was using Peer to Peer replication on SQL2012 instances.
Dante C
Dante C
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 9
great article! w00t

I have in the past 8 months spent between 1-5 days figuring out what has gone wrong and tweaking the setup! (luckily its been fine for the past 2 months fingers crossed it continues...)

this will certainly help any future 'mishaps'

thanks! :-)

Dante C
Mike Good
Mike Good
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3877 Visits: 1105
Nice article! We've been doing similar problem solving for years, works fine. 2 things:
- instead of using SSMS to find xact_seqno vlues, we get them by querying distribution.dbo.MSrepl_errors
- rather than editing the replication procedures to ignore errrors (we used to do this) we now adjust the subscription's profille to "continue on data consistency errors" which does just about same thing. When we get past errors, we change back.



husker30
husker30
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 123
LOL...SSC stole my thunder. We change to a different profile as well. What really will bite you is if you forget to change it back.
Divine Flame
Divine Flame
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9379 Visits: 2818
Nice article Edward. Thanks.


Sujeet Singh
philippus.s.vlok
philippus.s.vlok
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 117
Word of caution not to be the creator of your own “next DBA emergency”. There might be triggers firing, batches running and/or applications reference and processing data on your inconsistent data. Even an ETL (without the “L”) running to extract and send data to an extern company, spoiling your boss happiness soon.

Why is it called Transactional Replication? Because it is supposed to guarantee all or none of the unit of work completed and the actions in the same sequence. One of the principal concepts of this type of replication and a major factor in which article goes into which publication. That might be one of the main reasons why you went with this type of replication in the first place, or not? Most applications and developers will rely and take your word for it.

So may be skip each problematic transaction as an unite, one by one. Or as an alternative set the “Agent Profile” to “continue on data consistency errors”, if you gone skip many transactions. This would not take much more effort. Knowledge and understanding of your replication schema, data, systems, company’s business and the root source of the conflict will be crucial on deciding on an appropriate approach to resolve the conflict.

If you encounter one conflict, you should make sure all objects are in synch. Proper steps should also be taken to prevent data conflicts in the future as well.

This article might need a proper disclaimer or may be a red warning. (My apology if I missed it.)
(As English is not my first language, I beg you please to ignore grammar and spelling.)
egpotus
egpotus
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1014 Visits: 1286
hello,

As anyone ever compare C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe from MP & Red Gate solution ?

Also in this solution comparing two very large tables can be very long, don't you agree?


Kindest Regards,

egpotus DBA

Edward.Polley 76944
Edward.Polley 76944
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 264
I use the filter in Redgate to limit the number of rows - this runs quick enough on large table:-). Make sure to pick the primary key for best performance. Haven't tried TableDiff.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search