The Scalability Engineers premium services DBA team was engaged with one of our customers whose teams were working on an issue where they were unable to generate critical reports from the subscriber database due to a delay in the availability of the latest data due to high latency in transactional replication topology.

One of our clients uses SQL Server transactional replication with pull topology for their reporting workload. Timely report generation empowers them to identify trends, optimize performance, and mitigate risks in the payment industry.

A few years into the project, the client's business has catapulted forward, with their customer base surging exponentially which resulted in a very high workload on the OLTP publisher database and the rate of transactions increased to millions of transactions each day.

With this expansion of this workload, during peak business hours, the report generation started getting delayed as the replication latency began to increase.

Our Premium DBA Services team was engaged to look into this situation. Latency pattern analysis by the DBA team identified negligible delays between publisher and distributor, however, there was high latency between distributor to subscriber.

Following in-depth analysis, database experts recommended leveraging multithreading within the Distribution Agent, effectively a resolution to the identified challenge. The team further did a POC on the test environment and thereafter with results suitable to the client, we had the same implemented into the production system, hence taking care of the issue.