The Trouble with Transactional Replication and large articles.

  • Edward.Polley 76944

    SSC Eights!

    Points: 834

    Comments posted to this topic are about the item The Trouble with Transactional Replication and large articles.

  • acheras

    SSC Journeyman

    Points: 92

    Thx for your article.

    I have only one question:

    is there a way to find the pending transactional replication commands in order to apply them manually to the remote subscriber and exclude them from the sync proccess? Maybe that would fix the problem in some cases without the need to run any other scripts. For example i usually get the error "the row was not found on the subscriber when applying the replication command"

    On the other hand how could i find the data differences between large tables in different databases and servers, connected just with an isdn phone connection?

    Thx in advance,

    Antonios Cheras

  • Megistal

    SSCrazy Eights

    Points: 8787

    On step four, synchronize the data, I understand that you have, from whatever mean you want to, select missing rows from the published table and insert them into the subscribed table?

    A "manual replicated step" in other words?

  • sjimmo

    SSChampion

    Points: 11139

    A good article on a very complex subject. My question though is why would you have articles that can be reinitialized in a matter of seconds or minutes to an article that can take hours? Also, on publications with hundreds of millions of rows, is bulk loading actually faster and better? What do you do about records that are added or modified while you are loading the initial load?

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • jei33

    Grasshopper

    Points: 15

    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

  • Edward.Polley 76944

    SSC Eights!

    Points: 834

    Yes pending replicaiton commands can be reviewed and rerun but it's a bit involved for a post reply. I will write about that in a following article. If you need help immediately the Stairway To SQL Server Replication articles on this site are a good start. This article is based upon real experience where there were too many failed commands to restore individually and business critical replication was down. This is the fastest method I have found to get replication back up.

  • Edward.Polley 76944

    SSC Eights!

    Points: 834

    Yes step 4 is a manual resync since the delta rows missing on the subscriber are relatively few compared to the total rows in the article. I use Redgate's Data Compare to resynch since it is fast and accurate. Keep in mind in my case the primary business concern was on the current data as this is how the business i smonitoried. Data older than a few hours ago was not critical.

  • Greg Shinder

    SSC-Addicted

    Points: 400

    Hi,

    Thanks for the article. Makes complicated things simple.

    Only one question.

    In case when all the articles in the publication are set to @pre_creation_cmd = 'None', do I still need to drop and re-create the publication?

    In my case, I have approximately 20 articles per publication, they are not as big, but I cannot allow re-initialization, since the subscriber receives data replicated from different servers.

    Thanks,

    Greg

  • paul.millar

    SSC Veteran

    Points: 297

    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

  • Jay Kusch

    SSC Veteran

    Points: 266

    What happens after replication is up and running and later you need to add a new article?

    Having the @sync_method set to 'for replication only" would then mean you would have to take care off adding the article and data to the subscriber ... is this true?

  • ltatum 62532

    Newbie

    Points: 3

    When I tried this (transactional) replication did not create the usual sp_MSIns, sp_MSUpd, and sp_MSDel procs at the subscriber - what am I missing?

    Thanks, Liston

  • Jay Kusch

    SSC Veteran

    Points: 266

    I am going to bet that if you script out the subscriber you are going to see that the sync_method is set to "for replication only".

  • ltatum 62532

    Newbie

    Points: 3

    Yes - sp_addsubscriber 'replication support only' - if I read the article correctly, that's how we avoid pushing the snapshot - but it's not going to work without the procs.

    Thanks, Liston

  • Jay Kusch

    SSC Veteran

    Points: 266

    What that means is that that the necessary replication components are created in order to get the connection up and function between the publisher/distrib/subscriber ... if you want to modify/add any articles you are going to have to manually create and and populate them on the subscriber(s)

  • Jay Kusch

    SSC Veteran

    Points: 266

    Of course then you need to add the SPs also

Viewing 15 posts - 1 through 15 (of 29 total)

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