The Trouble with Transactional Replication and large articles.

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

  • 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

  • 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?

  • 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

  • Nice article, thanks.


    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

  • 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.

  • 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.

  • 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.



  • 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)

  • 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?

  • 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

  • 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".

  • 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

  • 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)

  • Of course then you need to add the SPs also

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

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