SQL Server Replication over Long Distance

In an easy word as I knew, there can be some performance gains from utilizing the improved TCP/IP stack in Windows Server 2008. But I am sure there is more ways to achieve it. Just need some time to dig it out..haha.

Hi zhao

Not sure if this is just a general question regards SQL or are you looking for specifics such as for  RecoverPoint?

Below is collateral from a Solution I worked on last year, RecoverPoint with VMware and SRM.

Michael

CONTINUOUS DATA PROTECTION FOR MICROSOFT SQL SERVER 2008 R2 ENABLED BY EMC RECOVERPOINT, EMC REPLICATION MANAGER, AND VMWARE

A Detailed Review

Thanks Morris for your inputs!  My question is not specific to any replication technology, but the documents provided are very helpful!

Thanks Louis!  Just searched on the internet, the Next Generation TCP/IP used in Windows 2008 does improve WAN utilization which will be benefit to long distance SQL replication.

1) Receive Window Auto-Tuning: Support for Receive Window Auto-Tuning is new in the Next Generation TCP/IP stack. Receiver-side throughput is improved through Receive Window Auto-Tuning because this feature is able to calculate the best possible receive window size for each connection by taking into account bandwidth, latency connection, and application retrieval rate. Bandwidth performance naturally improves with better throughput. Bandwidth performance can improve even more if all applications receive TCP data.

2) Compound TCP/IP (CTCP): Compound TCP/IP, which is most often used for TCP connections which has a large receive window size in addition to a large bandwidth delay product, ultimately improves receiver-side throughput. With CTCP, the amount of data sent across connections is considerably greater; however, TCP connections are not impacted negatively. If CTCP and Receive Window Auto-Tuning are used together, even more benefits, including increased link utilization and performance gains for large bandwidth delay connections, can be witnessed.

3) ECN support: When a TCP segment is lost, TCP assumes that it was because of congestion at a router, so it performs congestion control. This lowers the TCP sender’s transmission rate. With Explicit Congestion Notification (ECN) in the routing infrastructure, routers experiencing congestion mark the packets as they forward them. TCP peers receiving marked packets lower their transmission rate to ease congestion and prevent segment losses. This increases the overall throughput between TCP peers.

4) Improved routing: Path maximum transmission unit (PMTU) black-hole router detection automatically adjusts the PMTU for a connection when large TCP segments are detected.

5) RFC optimizations: The TCP/IP stack has better support for RFCs related to TCP communications.

6) Neighbor detection: The Next Generation TCP/IP stack supports neighbour unreachability detection for IPv4 traffic. A computer such as a branch office maintains status about whether neighbouring computers such as a hub site are reachable. This provides better error detection and recovery when computers are not available.

7) Dead Gateway support: Unlike the previous Windows versions of Dead Gateway Detection, the Next Generation TCP/IP Dead Gateway support now provides a failover and failback mechanism when encountering dead gateways.

Simon,

You are right, tuning on windows TCP/IP stack does help to improve SQL replication performance.

http://msdn.microsoft.com/en-us/library/dd263442(v=sql.100).aspx

on this article, MS provide two test Scenarios, SQL2k5 on Win2k3 and SQL2k8 on Win2k8, as you did not specific configuration, it is good, and this article also cover test result.

You also could consider hardware WAN accelerator to optimize your WAN throughput to make your SQL replication better.

http://sqlcat.com/sqlcat/b/whitepapers/archive/2009/09/23/using-replication-for-high-availability-and-disaster-recovery.aspx

This one is considerable to read, covering hardware WAN accelerator performance with SQL replication.

Thanks,

Eddy