Disadvantage of Transactional Replication
-
giovedì 3 maggio 2012 12:04
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, DeletesThank you in advance!
- Modificato ti2 giovedì 3 maggio 2012 12:12
Tutte le risposte
-
giovedì 3 maggio 2012 14:19Moderatore
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
- Contrassegnato come risposta Stephanie LvModerator lunedì 14 maggio 2012 08:25
-
giovedì 3 maggio 2012 14:37
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
-
giovedì 3 maggio 2012 14:51Moderatore
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
.png)
