May 29, 2013 at 10:14 pm
Comments posted to this topic are about the item Super Fast Transactional Replication Repair
May 29, 2013 at 10:35 pm
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.
May 30, 2013 at 4:52 am
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.
May 30, 2013 at 5:12 am
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
May 30, 2013 at 6:09 am
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.
May 30, 2013 at 6:43 am
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.
May 30, 2013 at 6:58 am
Nice article Edward. Thanks.
May 30, 2013 at 7:48 am
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.)
May 30, 2013 at 9:43 am
May 30, 2013 at 10:26 am
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.
May 30, 2013 at 10:33 am
Thanks for the comments. In my shop it's usually 1 article that has issues due to rows being changed on the subscriber, and that's another article, so I use this table level technique. I'll add data consistency as another DBA tool.
🙂
May 30, 2013 at 11:40 am
Maybe I didn't catch your purpose, but if replication is trying to delete a row at the subscriber, why did you bother with a data sync? Are you assuming there would be other issues?
One of the issues with replication is alerting. I have some custom scripts that will email you all the details you need and have put on my website http://www.sqlwebpedia.com just search for replication scripts and I have a SQL Saturday presentation that was recorded on replication there too for those interested.
-Chuck Lathrope
@SQLGuyChuck
May 30, 2013 at 12:00 pm
Yes the synch would apply on failed updates and inserts. I'll check out your web site. Thanks,
May 30, 2013 at 4:26 pm
Why even spend those few minutes?
Simply add the '-SkipErrors' parameter to the distribution agent startup call.
Then it doesn't matter which article it is causing the problem.
http://technet.microsoft.com/en-us/library/ms151331%28v=sql.105%29.aspx
The errors will be logged, so you can track if they occur. And then go about the task of 'synchronizing' the data. With any replication it is always a good idea to set some time aside to do a consistency check between publisher and subscriber, even with replication defaults - I'll caught my dev's 'practising' what-if senarios by changing what they consider old subscription data. (they have their own databases to play with - and still)
June 1, 2013 at 12:33 pm
Thanks for the comment.
I've had inconsistent result with -skiperrors, altering the stored procedures always works. This approach is at the table level so it limits the impact to just the table with the error. I can make this change in several minutes the business rarely notices the latency.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply