SQL Server Scale Out

After a few recent conversations with CIO and CTO types, I was amazed that there is still the concept out there that SQL Server doesn’t scale well. So I sat down to write a lengthy blog to dispel that rumor. After doing some research, I found that there is so much already written and available on the web that I decided to gather all the links in one spot and make this blog a launching point for your own research and reading pleasure.

After reading these, I’m sure you will agree that the SQL Server relational engine will scale to the largest data requirements. In fact, I will go so far to state that SQL Server will take on the toughest database challenges in the world. If you don’t agree post a comment here and let me know your opinion.

If you are already using SQL Server and want to know more about the technical solutions on how to make your situation scale or perform better, go to https://sqlcat.com and read the variety of technical notes and white papers posted there. On this site, you will also find the links to the SQL Server Best Practices site in the upper right corner. Both these sites contain some of the best how-to guides around SQL Server.

If you only have a few minutes to read one or two articles and want to get an overview of most of the scalability options, read the one by Roger Wolter on Scaling out SQL Server 2005 or the one by Bob Beauchemin. These two overlap a little bit but each has a unique perspective on scale out. They will help give you a vision on which option would be right for your project.

Scalability Architecture choices (use for Envisioning and Architecture phases of project)

Scaling Out SQL Server 2005, Roger Wolter, https://msdn.microsoft.com/en-us/library/aa479364.aspx

Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005, https://www.microsoft.com/technet/prodtechnol/sql/2005/scaleout.mspx , Bob Beauchemin

Service-Oriented Data Architecture: App-Server:Lite?, David Campbell, https://research.microsoft.com/research/pubs/view.aspx?tr_id=983

Academic / Conceptual oriented papers

Data on the Outside vs. Data on the Inside. An Examination of the Impact of Service Oriented Architectures on Data, Pat Helland, https://msdn.microsoft.com/en-us/library/ms954587.aspx

Distributed Partitioned Views

SQL Server Megaservers: Scalability, Availability, Manageability, Jim Gray and Richard Waymire. (old article but still has relevant parts) https://www.microsoft.com/technet/prodtechnol/sql/2000/plan/ssmsam.mspx

Implementation and Internals (Use for Detail Design phases of project)

Scalability Internals https://www.microsoft.com/technet/prodtechnol/sql/2005/scaleout.mspx

Architecting Service Broker Applications, Roger Wolter, https://msdn2.microsoft.com/en-us/library/aa964144.aspx

Achieving Scalability and Availability with Peer-to-Peer Transactional Replication, Michael Blythe, https://www.microsoft.com/technet/prodtechnol/sql/2005/p2ptranrepl.mspx

Scalable shared databases are supported by SQL Server 2005, https://support.microsoft.com/default.aspx?scid=kb;en-us;910378

Anything on this site: https://www.sqlskills.com/whitepapers.asp

Peer-to-Peer Replication

Inside Microsoft.com: Managing SQL Server 2005 Peer-to-Peer Replication, David Lindquist https://www.microsoft.com/technet/technetmag/issues/2006/07/InsideMSFT/default.aspx

Service Broker

Inside MSDN: Building the MSDN Content Aggregation System, John Mollman, https://msdn.microsoft.com/en-us/magazine/cc163586.aspx

Data dependent routing

MSN: https://www.microsoft.com/technet/prodtechnol/sql/2005/scddrtng.mspx

Comparison

SQL vs Oracle RAC https://www.microsoft.com/sql/techinfo/whitepapers/oraclerac.mspx  Note: this is a 2-year old doc and some of the RAC problems mentioned in this article have been addressed by Oracle.

SQL Server is being used to build some of the largest database applications in the world. Evidence of some these large projects is documented on https://www.microsoft.com/casestudies. Mission critical systems in core banking, telecom, stock exchanges, retail and many other industries are using SQL Server to solve their tough business requirements.

Kevin