Wednesday, September 12, 2007

SQL Server 2005 Performance Tips - Clustering

SQL Server 2005 Performance Tips - Clustering

I found it pretty difficult to find any articles that just gave you the facts when it comes to performance tuning and configuring SQL Server 2005. I have a Microsoft Technology Technology Specialist in SQL Server 2005 but try to read as many tips and technique articles on SQL Server 2005 as I can.

Then I came across these two great articles SQL Server clustering best practices and
Active/Active clusters in SQL Server
by Hilary Cotter with Geoff Hiten. These articles provide information such as:

  • Clustering basics
  • Clustering topologies
  • Clustering dependencies
  • Clustering best practices

  • Distinct cluster configurations
  • Pre-installation
  • Cluster installation
  • Post-installation
  • Minimize fail overtime
  • Performance considerations

One comment that resonates with my experience is

Today, clustering on SQL Server 2000 and SQL Server 2005 is a highly reliable technology, but it still has many dependencies that prevent it from meeting your high-availability goals. Foremost among these dependencies is a staff that is
trained and knowledgeable.

SQL Server clusters can be configured in four distinct configurations:

  • Single instance – formerly called Active/Passive
  • Multi-instance – formerly called Active/Active
  • N+1 – multiple instance clusters, where multiple nodes all share the same failover node
  • N+M – multiple instance clusters where multiple nodes all share the same failover nodes

The articles provide a good overview of clustering topologies, when each is useful and touches on real world issues such as performance vs budget vs Recovery Time Objectives (RTO) requirements.