I 'm testing implementing transactional replication . The replication works fine if I insert/update handful of rows. The problem occurs when I have a sql job running which deletes/updates & inserts 1000's 15000 plus rows in each database(more than 100) on the server. And this job runs every n minutes. It eventually causes locking issues and slows down replication.
The main objective of doing replication is to able to scale horizontally and also improve performance.
Following is a quick overview of test environment
2 database server
Each server has 100 unique databases & 5 shared database(which are replicated)
Any suggestion how to get around the locking issues?
Thanks
Best Answer
There are two steps to consider when tuning replication:
The first one can be achieved by grouping multiple statements in a stored procedure and then replicate each procedure execution as a single command instead of multiple separate commands. @ThomasStringer recommended http://msdn.microsoft.com/en-us/library/ms152754.aspx as a good starting point to learn about this feature.
The second can often be achieved by tuning the Distributor. The Distributor is doing most of the work in transactional replication. But it is also the most neglected part in replication. Often then Publisher is tasked to do the work of the Distributor too. If the Publisher has a lot to do already to keep up with the application workload, It cannot handle the additional work required to execute the distribution tasks. In high performance environments it is therefore a good idea to make the Distributor its own machine with its own disk. This will reduce CPU load, Disk load and network load on the Publisher.
If that is not possible make sure the distribution database is on a different spindle than the application database. You can also try to setup the subscription(s) as pull subscriptions instead of push subscriptions.
The above are general recommendations. As always, first make sure you understand what the root cause is. You can use sys.do_os_wait_stats and sys.dm_db_virtual_file_stats to see wether your disks are to slow, the processor is overloaded you have memory pressure or the network is to slow. Then look at the recommendations above to see what can help in your case.