We have following setup at the moment:

8 Cluster servers (SQL Server 2012 Standard Edition) talking to 2,000 store servers in the field via Merge Replication.

The 8 servers are replicating to a reporting DB server (SQL Server 2008 R2 Enterprise).

We have partitioned tables using the server ID from 8 servers on this reporting database so we can have data in one database and not going to 8 individual databases. It using Push Transactional Replication.

We would like to do the following:

Create a New Transactional Publication on this reporting DB server which would replicate to 2 SQL Server 2012 Standard nodes for scale-out. We've implemented this, and it's working as expected.

Issue we are running into:

One of the articles gets about 3 million updates (i.e. Delete and Insert coming from 8 cluster servers) twice in the morning and twice in the evening.

This causes a lot of activity in the Distribution database under MSRepl_Commands table, which the reporting server is not keeping up with causing the entire replication to the "scale out" nodes to come to a halt. Commands keep piling up under the Distribution database at the reporting database server.

How can we make sure these Updates under this particular Article are getting cleaned up quickly at the Distribution database and keep replication to scale out nodes in good condition?

UPDATE: I am constantly seeing blocks when Distribution Cleanup job is running:

"Update MSsubscriptions..." is creating block ON this statement "UPDATE MSdistribution_history..."

One of the column in this particular article is VARBINARY(MAX) would that make a difference with large batch of INSERT and DELETE?

| improve this question | | | | |
  • Is your distribution database on the reporting database server? – stacylaray Dec 31 '15 at 15:45
  • Yes it is on the reporting server database – NismoGTR05 Dec 31 '15 at 16:23
  • Need to determine where the latency is first. Is it the logreader reading the tranlog, or writing to the distribution database? Is it the distribution agent reading the transactions, or writing to the subscriber? Have you run a tracer token? Do you have undistributed transactions? It could be that you will need to move the distribution database to another server. – stacylaray Dec 31 '15 at 16:30
  • I have added that article back to the publication. I will check above and post it with that information, thank you Stacy – NismoGTR05 Dec 31 '15 at 16:44
  • After adding article back, I see Distribution Cleanup agent is getting blocked by itself, following proc is creating block: EXEC dbo.sp_MSdistribution_cleanup (retention is set to 72) on to : sys.sp_MSadd_distribution_history;1 this is causing replication transaction not to continue towards subscriber. Any guideance in this would be appriciated. – NismoGTR05 Dec 31 '15 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.