SQL Server cluster design: One big cluster vs #sql #server #load #balancing


SQL Server cluster design: One big cluster vs. small clusters

workload to balance utilization and to schedule planned maintenance without downtime.

This cluster design uses software heartbeats to detect failed applications or servers. In the event of a server failure, it automatically transfers ownership of resources (such as disk drives and IP addresses) from a failed server to a live server. Note that there are methods to maintain higher availability of the heartbeat connections, such as in case of a general failure at a site.

MSCS does not require any special software on client computers, so the user experience during failover depends on the nature of the client side of the client-server application. Client reconnection is often transparent, because MSCS has restarted the applications, file shares and so on, at exactly the same IP address. Furthermore, the nodes of a cluster can reside in separate, distant sites for disaster recovery.

SQL Server on a cluster server

SQL Server 2000 can be configured on a cluster with up to four nodes, while SQL Server 2005 can be clustered on up to eight nodes. When a SQL Server instance is clustered, the disk resources, IP address and services are forming a cluster group to allow for failover. For more technical information, refer to the article How to cluster SQL Server 2005.

SQL Server 2000 allows installation of 16 instances on a single cluster. According to Books-On-Line (BOL), SQL Server 2005 supports up to 50 SQL Server instances on a single server or processor, but…, only 25 hard drive letters can be used, so these have to be planned accordingly if you need many instances.

Note that the failover period of a SQL Server instance is the amount of time it takes for the SQL Server service to start, which can vary from a few seconds to a few minutes. If you need higher availability, consider using other methods, such as log shipping and database mirroring. For more information about disaster recovery and HA for SQL Server, go to Disaster recovery features in SQL Server 2005 and Microsoft’s description of disaster recovery options in SQL Server.

One big SQL Server cluster vs. small clusters

Here are the advantages of having one big cluster, consisting of more nodes:

  • Higher Availability (more nodes to failover to)
  • More load balancing options for performance (more nodes)
  • Cheaper maintenance costs
  • Growth agility. Up to four or eight nodes, depending on SQL version
  • Improved manageability and simplified environment (less to manage)
  • Maintenance with less downtime (more options for failover)
  • Failover performance unaffected by the number of nodes in the cluster

    Here are the disadvantages of having one big cluster:

  • Limited number of clustered nodes (what if a ninth node is needed?)
  • Limited number of SQL instances on a cluster
  • No protection against failure — if disk array fails, no failover can take place
  • Can’t create failover clusters at the database level or database object level, such as a table, with failover clustering

    Virtualization and clustering

    Virtual machines can also participate in a cluster. Virtual and physical machines can be clustered together with no problem. SQL Server instances can reside on a virtual machine, but performance may be impacted, depending on the resource consumption by the instance. Before installing your SQL Server instance on a virtual machine, you should stress test to verify that it can hold the necessary load.

    In this flexible architecture, you can load balance SQL Server between a virtual machine and a physical box when the two are clustered together. For example, develop applications using a SQL Server instance on a virtual machine. Then fail over to a stronger physical box within the cluster when you need to stress test the development instance.

    Important links describing Windows and/or SQL Server clustering

  • SQL Server clustering resources (This article contains important links and information about clustering).

    A cluster server can be used for high availability, disaster recovery, scalability and load balancing in SQL Server. It’s often better to have one bigger cluster, consisting of more nodes, than to have smaller clusters with fewer nodes. A big cluster allows a more flexible environment where instances can move from one node to the other for load balancing and maintenance.


    Michelle Gutzait works as a senior database consultant for ITERGY International Inc.. an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills include SQL Server infrastructure design, database design, performance tuning, security, high availability, VLDBs, replication, T-SQL/packages coding, and more.
    Copyright 2007 TechTarget

  • Leave a Reply

    Your email address will not be published. Required fields are marked *