Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Are there any triggers on the table? Any cascaded deletes? –  SqlACID Sep 9 '11 at 14:26
    
No, there is no triggers or cascades. It's just a plain denormalized table. –  xelibrion Sep 9 '11 at 19:55

1 Answer 1

up vote 4 down vote accepted

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

share|improve this answer
    
Regarding replication type - our DBAs told so. If I got it right, SQL Server Enterprise Edition provides peer-to-peer replication on top of transactional replication. So in my case, I think, it is really transactional replication. Deletes are done using ORM (NHibernate), so there is no way to replicate procedure call. –  xelibrion Sep 9 '11 at 13:35
    
As the deletes are done using the ORM there's nothing you can do to batch them up. You are stuck with replication moving the deletes one row at a time. –  mrdenny Sep 9 '11 at 20:27
    
Okay, we'll try to do it using stored procedure. By the way, can be call of sp_executesql replicated? Or it's just for custom stored procedures? –  xelibrion Sep 10 '11 at 5:25
    
No the calls to sp_executesql can't be replicated as that's an object in another database. Only calls to user procedures within the user database can be replicated. –  mrdenny Sep 10 '11 at 5:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.