Danny Varghese: SQL Server Clustering

[Blog bot]

Источник: http://varghesedanny.com/2011/06/24/...er-clustering/
==============

I’ve had past experience working with IT departments on infrastructure related questions/setup.  When deploying either custom applications, or implementing packages solutions, it’s a good idea for application developers to have some foundational knowledge, especially when installing applications.  One of the more common production designs for mission critical applications is clustering of SQL servers.

What is Clustering?

A Microsoft SQL Server Cluster is simply a collection of two or more physical servers.  These servers or “nodes,”  have the same access to shared storage and provides the resources required to store the database files.  Each of the nodes talk to one another via a network. If one node does not communicate to the other node in the cluster the secondary node will take ownership of any dependent services being run by the node that lost communication.  This process is referred to as “failover”. A failover can occur both automatically (a server stops communication for some reason) or manually.  An example of a manual failover is something like an IT administrator wanting to patch a server, or some other form of maintenance on the physical server.

Unlike other clustering technologies that are implemented for better performance or for increased processing power via load-balancing, SQL clusters are designed for providing highly-available databases; eliminating downtime associated with hardware failure. This architectural concept is referred to as “High Availability Clustering” or “HA Clustering” for short. The service or groups of services that are hosted on a clustered node are respectively referred to as resources and resource groups. Since these resources must be available to all nodes in a cluster then they must reside on a shared disk array. NOTE:  A shared disk array is a disk subsystem that is connected to two or more computers typically via the SCSI interface. When disk subsystems are connected via Fibre Channel switches, they are called storage area networks.

Essentially what happens in a SQL Server cluster is that you assign SQL Server its own virtual name and virtual TCP/IP address. This name and address is shared by both of the servers in the cluster.  A client connects to the SQL Server cluster using the virtual name used by the cluster.  As an end user, there seems to be only one physical SQL Server, not two. Assuming that the primary node of the SQL Server cluster is the node running SQL Server on an Active/Passive cluster design (this design is explained below), then the primary node will respond to the client’s requests. But if the primary node fails, and failover to the secondary node occurs, the cluster will still retain the same SQL Server virtual name and TCP/IP address, although now a new physical server will be responding to client’s requests.

Types of Clustering

There are two types of clustering: Active/Active or Active/Passive.  In an Active/Active cluster there will be two or more nodes, each one “owning” an instance of Microsoft SQL Server. If one node fails, the instance it owns would fail over to the other node, running along side (and contending for resources with) the other instance.  Each copy of SQL Server acts independently.  If one of the SQL Servers in the cluster should fail, then the failed instance of SQL Server will failover to the remaining server. This means that both instances of SQL Server will be running on one physical server, instead of two.  As you can imagine, if two instances have to run on one physical server, performance can be affected, especially if the server’s have not been sized appropriately.

In an Active/Passive setup, at least one node is not the owner of an instance of SQL Server. It is “passive” and only exists to accept a failover of a node hosting a SQL instance in the event of a failover.  From a performance perspective, this is the better solution. On the other hand, this option makes less productive use of your physical hardware, which means this solution is more expensive.

The current Microsoft licensing policies require you to only license the active nodes running Microsoft SQL Server.  The passive node does not need to be licensed.  Depending on the versions of SQL server, you are limited to the number of nodes in a cluster.  Windows Server 2008 and Microsoft SQL Server 2008 Enterprise Edition allows up to sixteen nodes.

SQL Server Installation & Clustering

The SQL Server installation will ask if you want to install SQL server as a cluster or not. If you choose to create a clustered instance of SQL Server, the instance will be hosted on a virtual server. Resources such as data and log files will be created on the shared disk for SQL Server, SQL Server Agent, and Full-Text Indexing.  The necessary program files are created on drives on each node, and registry settings are identically written to each node.  This allows each node to run it’s resources in the same way.

Pros vs. Cons

Some pro’s to SQL Server clustering are the following:

• Clustering allows for the reduction of downtime.
• Allows for an automatic response to a failure in hardware/software.
• Allows you to perform upgrades without forcing users off the system for extended periods of time.
• Clustering doesn’t require any servers to be renamed. So when failover occurs, it is relatively transparent to end-users.
• Failing back is relatively quick, and can be done whenever the primary server is fixed and put back on-line.
• In some cases, clustering can be used to increase the scalability of an application. For example, if a current cluster is getting too busy, another server could be added to the cluster to expand the resources and help boost the performance of the application.

Some cons to SQL Server clustering are the following:

• This can be expensive
• Requires more set up time than other alternatives.
• Requires more on-going maintenance than other alternatives.
• Requires more experienced DBAs and network administrators.

Summary

I hope this article helps those folks that may encounter infrastructure related questions/queries.  Please let me know if you find anything in this article that’s incorrect or if you have any questions.




Источник: http://varghesedanny.com/2011/06/24/...er-clustering/