Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Disadvantage of Transactional Replication

Answered Disadvantage of Transactional Replication

  • Thursday, May 03, 2012 12:04 PM
     
     
    Sign In to Vote
    0

    Dear All,



    I would like to find more about the Disadvantages of Transactional Replication as it is the best process of Replication for my Business needs; I would like to know more about what it is not capable of doing, to give more of an example; the Replication will do the following:Updates, Filters, Inserts, Deletes

    Thank you in advance!


    • Edited by ti2 Thursday, May 03, 2012 12:12 PM
    •  
     

All Replies

  • Thursday, May 03, 2012 2:19 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    The number one drawback of transactional replication is its native inability to replicate batch commands quickly and consequently no predictable latency. There are ways of bending this to make it replicate batch commands faster but they have drawbacks.

    If your publisher is under signficant load you may find that replication can add a 5 to 10 % load. Your results may vary depending on your workload and hardware.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

     
  • Thursday, May 03, 2012 2:37 PM
     
     
    Sign In to Vote
    0

    the best way i found to speed up batch replication is to disable indexes on the subscriber

    i have processes in place to monitor for any unused indexes on subscribers. don't just create the same ones as the publisher.

    and on large tables where the batch process is in the tens of millions of rows i will disable the indexes and rebuild them the next morning

     
  • Thursday, May 03, 2012 2:51 PM
    Moderator
     
     
    Sign In to Vote
    0

    The problem with this approach Alen, is that the number one use case for transactional replication is to offload reporting to another server. The reporting queries need their own set of indexes so you may end up with more indexes on the subscriber tables than on the publisher.

    I do agree the subscriber side triggers and indexes should be minimized.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941