We have setup data transactional replication from Server-A(SQL Server 2008 R2) to Server-B(SQL Server 2008 R2). We have a SQL Job scheduled to run everyday on Server-A, which executes single UPDATE command which causes ~120K record updates on a specific table. Now the problem here is, when that single UPDATE command gets executed on Server-A it updates nearly 120K records within few seconds. This is a single transaction on Server-A but it is equivalent to ~120K transactions on Server-B. This causes, the table on Server-B gets lock down for more than 20-30 min. everyday, whenever that SQL Job runs.
I read about MaxCmdsInTran option, but it isn't a recommended one, so cant use it. As an alternate option, we are planning to exclude that particular table from Data Replication and we are planning to execute that SQL Job on both Server-A and Server-B.
I am just wondering if there is any better solution available for this problem.
Best Answer
You may want to look into Publishing Stored Procedure Execution in Transactional Replication which can provide significantly better performance for large batch operations since only the procedure execution is replicated, bypassing the operation being sent to Subscribers as a large, multi-step transaction.
If you have anymore questions please let me know. I hope this helps.