Many organizations that have been slowly deploying SQL Server databases on different servers and locations – for varied applications and purposes – are starting to consider consolidation by way of SQL Server clusters.
Consolidating SQL Server instances and databases to a central location can reduce costs, especially for maintenance and hardware/software licenses. In addition, when consolidating, and thereby reducing the number of machines needed, these machines can be used for failover.
When looking for a failover, i.e. highly available environment, companies often decide to employ Microsoft's cluster architecture. I am frequently asked which is best: having small clusters (consisting of fewer nodes) housing SQL Server instances or one big cluster as the central solution. I'll let you decide after we compare the roles of the two clustering architectures.
What is Microsoft Cluster Server (MSCS)?
MSCS is a built-in feature of Windows Server, Enterprise Edition. This software supports the connection of two or more server nodes into a "cluster" for higher availability and for easier manageability of data and applications. MSCS can automatically detect and recover from server or application failures. You can also use it to (manually) move server 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 reconnectio...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
n 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
An introduction to SQL Server clustering basics
An introduction to 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.
ABOUT THE AUTHOR:
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
