Wednesday 5 May 2010

Geo-Replication Performance Gains with Microsoft SQL Server 2008 Running on Windows Server 2008

MSCOM Ops Team has done a tremendous and excellent work in testing the performance of SQL Server 2005 replication environments on Windows Server 2003 and SQL Server 2008 on Windows Server 2008, and good benefits were achieved as a result of the performance enhancements in the next generation TCP/IP stack. If you open the link, you will read that the team concludes the following:

“… the team discovered that SQL Server 2008 running on Windows Server 2008 yielded up to 100 times faster performance without requiring any expensive wide area network (WAN) acceleration hardware”

Furthermore, scalability and performance have been improved in SQL Server 2008 Native Client, exactly, in the way of invoking stored procedures with ODBC call syntax and OLE DB remote procedure call (RPC) syntax. To find out more details about it, check out Geo-Replication Performance Gains with Microsoft SQL Server 2008 Running on Windows Server 2008. It is also found best practices and baselines, here are some of the them:

"Testing showed that using transactional replication with SQL Server 2008 running on Windows Server 2008 dramatically outperformed SQL Server 2005 running on Windows Server 2003. As illustrated in Table 2, the most substantial performance gains occurred when the Publisher and Subscriber were both running SQL Server 2008 on Windows Server 2008…
…Testing also showed that the scope of the performance gains correlated with the type of replication and the type of data. Push subscription replication of character data with SQL Server 2008 running on Windows Server 2008 yielded a 104 percent increase over SQL Server 2005 running on Windows Server 2003, and pull subscription replication of the same data yielded a 1,298 percent gain.
Both lab and real-life testing by the MSCOM Ops team indicate that highly trafficked Web sites can gain the benefits of geo-replication most effectively when the site is built on SQL Server 2008 running on Windows Server 2008. Based on solid evidence of the feasibility of WAN–based geo-replication, MSCOM Ops plans to expand its implementation of this solution.
In addition, the MSCOM Ops team learned several valuable lessons because of its extensive performance testing of SQL Server 2008 running on Windows Server 2008, including:
  • Windows Server 2008 and SQL Server 2008 with the TCP/IP stack improvements and partnering with application development teams can bolster global user experiences, produce higher availability, higher scalability, and better resiliency for sites, services, and applications through WAN-based geo-replication.
  • Replication performance is significantly better for pull subscription scenarios than push subscriptions.
  • The solution identified in this paper will not work for all applications, particularly applications that cannot handle the inherit latency involved with replicating data between geographically dispersed data centers."

To sum up, there are significant gains in terms of performance, scalability and disaster recovery implemented in SQL Server 2008 on Windows Server 2008. Not only will it be much faster than SQL Server 2005 on Windows Server 2003, but also much secure and cheaper. Therefore, SQL Server Replication technology will be considered a stronger solution inclusive for high availability purposes.
I hope you enjoy reading both document as they are a good read. That is all for now, let me know any remark you may have. Thanks for reading.
HELLO, I'M PERCY REYES! — a book lover, healthy lifestyle lover... I've been working as a senior SQL Server Database Administrator (DBA) for over 20 years; I'm a three-time awarded Microsoft Data Platform MVP. I'm currently doing a PhD in Computer Science (cryptography) at Loughborough University, England — working on cryptographic Boolean functions, algorithmic cryptanalysis, number theory, and other algebraic aspects of cryptography. READ MORE