The Trouble with Transactional Replication and large articles.

Question

The Trouble with Transactional Replication and large articles.

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

ltatum 62532 Newbie Points: 3 More actions · Answer 1

So the procs should continue to function, as long as they're still in place. We're moving a very large database to a different server, which of course doesn't affect the articles - tables and procs are all there - just the connection.

Thanks, Liston

syed_h_ali2001 SSC Enthusiast Points: 113 More actions · Answer 2

It may be possible to drop the article from the publication. Question is if after dropping article distributor still complain on commands for this article stuck at distributor. May be there is a way to clear the commands stuck at distributor and manually synchronizing the table Or may be when you drop article it is clever enough to clear the commands related to that article from distributor. I think all these situations needs to be tested.

jose_hindi Newbie Points: 1 More actions · Answer 3

I just want to thank you for your post

We needed to reinitialize an expired subscription with 1.2M records over a WAN

So I took your idea, create a new publication with the new subscription

With tablediff command fixed the differences between server/tables

Drop the old expired subscription

Thanks

Jose Hindi

Edward.Polley 76944 SSC Eights! Points: 834 More actions · Answer 4

This was a system I inherited, the articles were in alphabetical order so there were huge and small row count articles mixed together. I won't recommend such a grouping of articles, I prefer to group based upon velocity of change. As to BCP it is already used in the snapshots. In the article I mentioned that you have to understand the application, in my case it was business critical to capture the current activity - any data older that 1 hour didn't really matter.

I used RedGate's data compare to synch the older data after replication was up.

🙂

Edward.Polley 76944 SSC Eights! Points: 834 More actions · Answer 5

Drop the article doesn't remove the bad commands at the distributor, as long as those commands are there replication won't begin. Dropping the publisher is the quickest method to clear the bad commands.

Thanks for reading and commenting.

Edward.Polley 76944 SSC Eights! Points: 834 More actions · Answer 6

Hi Greg:

You'll find replication is quite exact. Setting the pre_cmd='none' only avoids the snapshot and prevent the overlaying of the subscriber data but it by itself is insufficient to get replication back up. In this case I hads bad commands at the distributor that prevented replication from working - those commands have to be cleared out. Dropping the publication is the quickest method.

Thanks for reading and comments.

Edward.Polley 76944 SSC Eights! Points: 834 More actions · Answer 7

Paul:

Thanks for the tip - I'll have to test out table diff and see good it is.

syed_h_ali2001 SSC Enthusiast Points: 113 More actions · Answer 8

Other method could be as follows.

1. Drop article from publication

2. Delete all command at distributor for that article as follows.

save all rows in msrepl_commands before deleting in case some thing goes wrong.

select * into Msrel_commands_bck from MSrepl_commands where article_id= (select article_id

from MSarticles

where article = 'article_name')

Delete all commands at distributor for that article.

delete MSrepl_commands where article_id= (select article_id from MSarticles

where article = 'article_name')

Now you can create a new publication and add article to that with synch type replication support only. Once done you can use tablediff to copy the missing rows from publisher to subscriber.

Thanks

Ali

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 9

jei33 (5/18/2012)
Nice article, thanks.
Question:
if you have detected that huge article as the conflicting one, wouldn't be enough just to drop that article from the publication, instead of deleting the whole publication?
Thanks in advance

This point stuck out to me as well. In a case where your publication has over 360 articles, I don't understand why you'd want to drop the entire publication. Am I missing the point trying to be made here?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Aldo Gonzalez SSC Enthusiast Points: 149 More actions · Answer 10

Thanks to Paul Millar for pointing this out!

paul.millar (5/21/2012)
Hi,
Short of Redgate Data Compare, check this out for generating Tablediff command lines...has saved my bacon a few times, and is surprisingly quick.
(I've got Red Gate tools (which are cool!), but can't install them on isolated customer boxes on the other side of restrictive VPNs or DMZ's - whereas Tablediff ships with all 2008+ sql)
http://blogs.msdn.com/b/repltalk/archive/2010/02/21/how-to-run-tablediff-utility-for-all-replicated-published-tables-in-sql-2005-or-sql-2008.aspx

Edward.Polley 76944 SSC Eights! Points: 834 More actions · Answer 11

There are commands at the distributor which need to be cleared before replication resumes, I've found dropping the publisher the quickest method to clear them all out - but I will test is just dropping the article clear that articles's bad commands.

Thanks for the question.

quackhandle1975 SSChampion Points: 11055 More actions · Answer 12

Excellent article, nice to know the info is there if I need it.

One question, did you use this on SQL 2000 replication? (yes, companies out there are still using it!) :w00t:

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Edward.Polley 76944 SSC Eights! Points: 834 More actions · Answer 13

This replication was on SQL 2005. Alas, my shop still has SQL 2000 despite my best efforts to upgrade. Highly recommend getting off SQL 2000.:-)

quackhandle1975 SSChampion Points: 11055 More actions · Answer 14

Edward.Polley 76944 (2/4/2013)
This replication was on SQL 2005. Alas, my shop still has SQL 2000 despite my best efforts to upgrade. Highly recommend getting off SQL 2000.:-)

Agree 100%, but trying telling that to paranoid IT managers, "It's production system, we best leave it alone!" :w00t: :crazy:

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]