How to use MSCS to build a SQL Server cluster

Clustering is a technology solution that combines hardware and software to provide a highly available and scalable architecture for services such as web, email, and database. This article analyzes the types of clusters and then focuses on how to build a Microsoft-based cluster solution.

Types of clusters

Clusters fall into two types: load Balancing and Failover (Failover). Load-balancing clusters spread the load across resources within the cluster, involving distributed network transmissions spanning multiple front-end servers. The load-balancing cluster makes each available server have a more average load to improve the overall performance and scalability of the system. This type of cluster is typically used for Web and COM + programs, where more than two nodes are supported within the cluster.

The failover cluster is primarily for system availability in the case of hardware and software failures. It monitors system resources to determine when a failover is initiated. When a system fails, the cluster transfers resources from the failed server to other servers in the cluster to restore the accessibility of the resource.

A fault tolerant failover cluster requires a large number of hardware and proprietary software to determine application status, and it can perform real-time failover in the event of hardware and software failure, including recovery to current application status. A high-availability cluster must be a fault-tolerant cluster, but it does not necessarily provide the same error recovery capabilities as the failover cluster.

Microsoft's cluster Service (MSCS) is a better high-availability cluster than a fault-tolerant cluster. It requires less hardware than a dedicated fault-tolerant cluster, but can operate on more types of applications. At the same time, it can also restore hardware and software failures, but the failure of the normal recovery to the application state. High-availability MSCS can support two server nodes within a cluster.

Basic hardware and software components

The server nodes in the cluster adopt their own hardware devices and work together with each other. Each server has its own operating system and is connected to the network independently. External hard disks and private network connections are used between servers. Shared hard disks are connected to each node through a hard disk controller, which typically uses an external SCSI device or a storage area network (SAN) as a shared hard disk, unlike a hard disk controller that contains the operating system.

The MSCS solution can work in active/passive mode. At the same time, only one node in the cluster is active, the Active server stores all the resources in the cluster, and writes the data to the shared hard disk, which is called the quorum drive. It can transfer shared state information from one node to another in the case of failure recovery. The regular sending signal passes through the private network between the server, when the server in the passive mode does not receive this signal, the Active server is considered invalid. At this point, it starts taking over the cluster resource and reads the status information from the quorum partition.

Installing SQL Server in a cluster

To install SQL Server on a cluster, you must first establish an operational cluster by following these steps:

1. Install the Windows Server operating system that supports MSCS on each server.

2. Establish a "public" network connection for each server. This connection is used to receive external query requests.

3. Establish a dedicated network between the two nodes in the cluster to transmit node status signals.

4. Create a user in the domain to operate the cluster. Add the user to the Administrators group on each server.

5. Shut down two servers. Connect to a shared drive and establish a disk resource. Open a node in the cluster (active mode). Establish and initialize logical disk space to set up the quorum drive and establish additional logical partitions.

6. Add and configure the Microsoft Cluster Service through the Windows components in the Add/Remove Programs in the Control Panel. This includes setting up the cluster's virtual name, IP address, and shared disk resources.

7. Open node second (passive mode). When node second is online, return to a node and add node second through the cluster Management program. Adding the second node to the cluster is easy, as long as you can do it by simply configuring it at the first node.

Once the cluster is established, follow these steps to install SQL Server:

1. Insert the installation CD for SQL Server Enterprise Edition. If you do not start the automatic installer, click Setup.exe on the CD.

2. In the confirmation computer name interface, select the virtual server and enter a virtual name.

3. Create a service account that runs SQL Server. Assign administrative permissions to each node in the cluster to this account.

4. Fill in the IP Address, service account, and share the contents of the disk resources.

Setup installs a copy of SQL Server on each node in the cluster and establishes the virtual settings in the cluster. The user can then control SQL Server through the cluster manager.