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 B, C and 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 A to B, C and 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?
Best Answer
First there's no such thing as one way peer to peer (that I know of). Are you sure you aren't using transactional replication?
Are the deletes done via stored procedures? If so you can replicate the stored procedures as well as the tables (all in the same publication, this will require a reinit of the subscribers). This way the stored procedure call will be replicated instead of the actual row by row delete.
With only 700k rows in the table, even if you blew them all away and reloaded them the replication shouldn't get that far behind unless you have a really slow link between the servers (which you didn't specify).