First of all, I'm not a DBA, but I'd like to get advice how to deal with the following issue.
We have few database servers based on SQL Server 2008 R2 SP1. They have been organized as shown below, so server
A is the source of data to servers
D. There is one-way peer-to-peer replication between servers. Network is 1Gbps.
__ A __ / | \ B C D
We have some denormalized table in our database with about ~700k rows. Table is about of 1Gb in size (and 5.5Gb of indexes) This table constantly updates - so all content in this table is renewed in ~2 hours (i.e. old rows are deleted and new are inserted). This is done in transactions of 5k records.
All works fine on server
A, but replication of data from
D slows down and delay could be about one hour between these servers.
Our DBAs say that it happens because of big delete transactions. They say that SQL Server executes delete operations on subscriber sequentially on each row, one by one. Is there a way to change this behavior if it does it so?
Actually, I'm not a big fan of replication, I would prefer to use some kind of messaging and publish-subscribe pattern to synchronize data between servers, but replication is the main way to transfer data in a company that I work for now. So it will be kinda hard to change these things.
Any thoughts how to improve performance of replication?