March 9, 2010 at 4:48 pm
I've been reading alot about the TCP/IP improvements made to Server2008 that SQL2008 takes advantage of to dramatically improve performance for replication (snapshot deliveries as well as synchronizing volumes of changes).
Hilary Cotter's article describes the changes I'm referring to:
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1353404_mem1,00.html
I'd read that you won't get any performance gains if the subscriber doesn't support the same improvements in technology that the publisher does. I've also read that these same TCP/IP stack features in Windows Server 2008 are also in Windows 7 (Receive Window Auto-Tuning, Send Buffer Scaling, Compound TCP). I decided to setup a test scenario with Windows7/SQL2K8 as a publisher, and a remote Windows7/SQL2K8Express as a subscriber. I found improvements when generating snapshots, but not in delivery, initialization of a subscriber took virtually the exact same amount of time as my tests between the exact same two machines running Windows7/SQL2K5 and Windows7/SQL2K5Express.
Does Windows 7 not support these performance improvements? Does SQL Express 2008 not support them? Was this a bad test, and would I actually get large performance gains in our live system of Server2008/SQL2K8 and Windows7/SQL2K8Express subscribers?
I'm trying to find some way to justify the costs of upgrading both the OS to Server 2008 and the SQL to SQL2K8 for our publisher, but based on the Windows 7 tests I would gain very very little.
March 9, 2010 at 5:37 pm
The SQL Server Customer Advisory Team has a white paper "Using Replication for High Availability and Disaster Recovery in SQL Server 2008." at http://sqlcat.com/whitepapers/archive/2009/09/23/using-replication-for-high-availability-and-disaster-recovery.aspx
On page 39, you can see that the Windows 2008 TCP/IP Enhancements only resulted in a 4% to 6% performance improvement.
SQL = Scarcely Qualifies as a Language
March 10, 2010 at 4:32 pm
Well that's not good news. My main concern is because each of our 150 subscribers are point-of-sale locations, and if disaster strikes and we need to reinitialize all of our subscribers, it takes about 20 minutes per sub = 50 hours that the point-of-sale won't be operational. I was really hoping to cut down the time it takes to initialize.
Is anybody aware of why the microsoft white paper on geo-replication shows such dramatic improvements using the new TCP/IP stack features available to SQL2008, but real test scenarios shows virtually no gains?
My tests were done between two locations in the same city, would the gains be more noticeable over greater distances? Does it have to do with the type of data being replicated (I noticed in the MS tests, it was varbinary data fields being replicated).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply