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
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:
Here are the disadvantages of having one big cluster:
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
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
This was first published in June 2007