Throttling SQL server Replication?

Question

We have a performance issue with the current transactional replication setup on sql server 2008. When a new snapshot is created and the snapshot is applied to the subscriber, we see network utilization on the publisher and the distributor jump to 99%, and we are seeing disk queues going to 30 This is causing application timeouts.

Is there any way, we can throttle the replicated data that is being sent over? Can we restrict the number of rows being replicated? Are there any switches which can be set on/off to accomplish this?

Thanks!

Answer 1

Our DBA has forced us to break down dml code to run in batches of 50000 rows at a time with a couple of minutes in between. He plays with that batch size time to time but this way our replicating databases are ok. For batching, everything has to go into temp tables, a new column (name it Ordinal) that does row_number(), and then a BatchID to be like Ordinal / 50000. Finally comes a loop to count BatchID and update target table batch by batch. Hard on devs, easier for DBAs and no need to pay more for infrastructure.

Answer 2

You have an alternative to deal with this situation

• While setting up transaction replication on a table that has millions of records
• Initial snapshot would take time for the records to be delivered to subscriber
• In SQL 2005 we have an option to create the tables on both transaction and publish server, populate dataset and setup replication on top of it
• When you add subscription with command EXEC sp_addsubscription set The @sync_type = 'replication support only'.
• Reference article http://www.mssqltips.com/tip.asp?tip=1117