A couple of months a go I came a cross a Microsoft white paper article http://msdn.microsoft.com/en-us/library/dd263442.aspx that talks about a performance improvement that can be gained by using SQL Server 2008 on top of Windows Server 2008 when replicating data over a WAN (Wide Area Network).
The article talks about an improvement done in Windows Server 2008 to the TCP stack. SQL Server 2008 as an application benefits from these changes in the communication package exposed by the operating system.
The boost in performance is mainly derived from the fact that the TCP Receive Window is now defined dynamically as apposed to a fixed value in earlier versions.
To take advantage of this improvement you got to have SQL Server 2008 on top of Windows Server 2008. No other SQL/OS combination will yield the desired results.
I say it is about time and better latter than never. Think about it, a communication channel established on a WAN is necessarily different in nature and characteristic than a communication channel established on a LAN (Local Area Network). So having the TCP Window Size being the same one value across a given server in operating systems prior to srv 2008 does not make sense. Further more, in my experience no one ever dears touch or tune it as the basic assumption always was that touching this value you win at some connections and loose at others. So there is a real trade off here that has to be dealt with extra care.
Being in a position where I manage 5 data centers around the globe I found that article interesting and relevant enough to set up my own environment and perform some tests.
The bellow table shows the results of a test I have done over a 160 ms WAN with one server located at NY and the other at TLV.
Both servers are running SQL Server 2008 x64 Enterprise Edition (2008 RTM 10.0.1600.22) and SQL Server 2005 sp2 build 3282 (CU6) x64 with similar HW on top of VMware virtual machines running Windows Server 2008 Build 6001 Service Pack 1.
The test results point out some important facts:
Push Replication in sql 2008 becomes as fast as Pull in sql 2005
Pull Replication in sql 2008 performs significantly faster than Pull in sql 2005
| Version | Type | Transactions | Commands | Sec Delivered | Trans/Sec |
| 2005 | Push | 10000 | 10000 | 110 | 91 |
| 2005 | Pull | 10000 | 10000 | 54 | 185 |
| 2008 | Push | 10000 | 10000 | 57 | 175 |
| 2008 | Pull | 10000 | 10000 | 19 | 526 |
If you like to leave a comment to this post pls do so at:
-Yaniv
============================================
