Post reply

The Trouble with Transactional Replication and large articles.

  • May 17, 2012 at 10:00 pm

    #261164

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

  • May 18, 2012 at 1:15 am

    #1489497

    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

  • May 18, 2012 at 5:57 am

    #1489591

    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?

  • May 18, 2012 at 7:17 am

    #1489662

    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

  • May 18, 2012 at 8:45 am

    #1489729

    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

  • May 18, 2012 at 10:42 am

    #1489828

    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.

  • May 18, 2012 at 10:49 am

    #1489833

    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.

  • May 18, 2012 at 11:39 am

    #1489862

    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

  • May 21, 2012 at 9:26 am

    #1490489

    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

  • May 22, 2012 at 12:21 pm

    #1491286

    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?

  • May 22, 2012 at 12:31 pm

    #1491295

    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

  • May 22, 2012 at 12:33 pm

    #1491298

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

  • May 22, 2012 at 12:45 pm

    #1491312

    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

  • May 22, 2012 at 12:50 pm

    #1491316

    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)

  • May 22, 2012 at 12:51 pm

    #1491317

    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