Let’s get to the facts. SQL Server transactional replication is am impressive technology for synchronizing data between two databases: the publisher and the subscriber. Data can be relatively up-to-date between the two databases and in some cases near real-time performance can be achieved with high network speed resulting in low latency. Its wizard-based setup is easy to follow for those who tend to shy away from T-SQL. Best of all, replication is free – included with the SQL Server license.
Often, database administrators get the unfortunate misfortune of pulling every hair of their heads when latency becomes an issue, and the subscriber database(s) is falling behind. The lack of recently committed data at the subscriber can be detrimental to a business whose operations are dependent on recently committed transactions, at the publisher, making its way to the subscriber database. The lack of updated data could affect reports, dashboards, and KPIs, which could negatively impact the company’s bottom line. At this point, management will be asking, “what can be done to reduce latency and increase replication throughput?”
Recently, I had a client that was experiencing painkilling latency issues with replication. The company that I work for replicate data to the client using transactional replication. The publisher and distributor database servers reside in Washington, and the client’s subscriber database server is in Virginia. Per Google Maps, that’s over 2,700 miles of distance between the two servers. The subscriber database consistently falls behind during regular business hours. We needed a solution and one that will accelerate performance beyond our wildest dreams. That’s where NitroAccelerator Turbo comes into the equation.
NitroAccelerator “accelerates and secure end-to-end SQL Server network traffic with easy to deploy endpoint-based software that requires no configuration or downtime.” Per the company, NitroSphere, the software works by:
- Adaptive packet compression dynamically adjusts TCP packet size based on the data
- HyperCache provides substantial performance benefits by minimizing the transfer of data between endpoints
- Intelligent protocol detection enables compression of non-native TDS implementations like Java applications
- Encryption secures network traffic between endpoints
The following are some typical implementation scenarios provided by the company.
- Perform real-time replication over WAN connections around the world to maintain performance data for websites
- Move project data using replication and SSIS between two remote sites, eliminating data transfer times as a project bottleneck
- Decrease bandwidth while synchronizing datacenters, lowering ISP costs over 70%
- Reduce end-user load times over 60% for a point-of-sale system communicating over satellite
- Replicate data between California and China, achieving up to 9000 transactions per second
- Achieve 90% performance improvement and enhance security of end-user applications over the Internet
What I found out during my testing was that NitroAccelerator wasn’t only impressive; it’s a must-have complement with SQL Server replication. A before (without NitroAccelerator enabled) and after comparison is below.
Small transaction test Nitro: 2.63 seconds at 380.92 commands/second
Small transaction test non-Nitro: 102.98 seconds at 9.71 commands/second
39.23x performance improvement!
Mixed transaction test Nitro: 33.53 seconds at 29.82 commands/second
Mixed transaction test non-Nitro: 103.58 seconds at 9.65 commands/second
3.09x performance improvement!
Large transaction test Nitro: 2.41 seconds at 41,555.17 rows/second
Large transaction test non-Nitro: 4.69 seconds at 21,331.63 rows/second
1.95x performance improvement!
Actual Outbound Compression (Distributor)
Actual Inbound Compression (Subscriber)
NitroAcclerator is available for purchase from NitroSphere at www.nitrosphere.com