Basic hardware requirement

• Minimum two servers with windows server 2003 datacenter, enterprise or standard edition (Two node clustering only).
• Shared storage (SQN, ISCSI) etc. with Minimum 3 partitions (for Quorum (500 MB), MSDTC(2 GB) and Data files (Depends))
• Minimum 2 Network card per server for public and private network with 500-millisecond or less round-trip response time
• Nodes must be part of windows domain.
• Minimum 5 Static IP address on public network and 2 IP address with private network

1. Static IP address on each node (Two IP address)
2. One Cluster virtual IP address (One Address)
3. One IP address for MSDTC on clustering (One IP)
4. One IP address as SQL virtual IP address (One IP)

• Minimum 2 Active Directory Account as part of local administrator group on each node

1. Windows cluster service account (1 account)
2. One for SQL server service (1 account)

• Minimum Two Unique names

1. MSDTC virtual name (1 Name)
2. SQL Server Virtual Name (1 Name)

Prerequisites

• All disks must be basic disks (not Dynamic) with NTFS partitions
• Under the 64-bit editions of Windows Server 2003 that support clustering, all shared disks must be configured as master boot record (MBR) partition-style disks
• Each SQL Server or Analysis Services will require its own dedicated drives.
• Each single LUN must have only one logical drive
• As per MFT (master File table) each disk has a limitation of 2TB, use 64KB as the Block size if you are doing a mix of read and write or 8KB if you are doing mostly writes. Never use default 4 KB Size.
• Please do sector aligning of shared disk (for SANs) which means for every I/O issued by Windows and SQL Server, there is one corresponding I/O on the hardware side of things.
• Always use hardware-based RAID.

Rights needed for cluster administrator service account

• Act as Part of the Operating System
• Adjust Memory Quotas for a Process
• Back Up Files and Directories
• Increase Scheduling Priority
• Log on a Service
• Restore Files and Directories

Firewall considerations for SQL server clustering

• 135: This is the port used by the remote procedure call (RPC) endpoint mapper.
• 139: This is the port used by the NetBIOS session service.
• 445: This is the port used by Windows file sharing (SMB).
• 3343: This is the port used by the cluster network driver and private heartbeat network.
• 5000 to 5099: Although you might not need to open up all these ports, some of these might need to be opened to allow remote RPC connectivity to Cluster Administrator. If they are not open, you might see a 1721 error in the Event Log. The actual Cluster Service needs at least 100 ports for communicating through RPCs. When other services such as SQL Server 2005 are using some of the ports, there might not be enough ports open for the cluster to function properly.
• 8011 to 8031: If the cluster nodes are separated via a firewall, these ports must be opened to allow RPC connectivity between the nodes. If these are not opened, errors that refer to a “Sponsor” will be found in the cluster log. As with the previous point, the reason this might occur is that there are not enough ports required for clustering to allow RPC communication between a node that is trying to join the cluster and its sponsoring node.

Installing cluster service

• Make sure, all the hardware is identical (such as HBAs) on each node, and make sure all firmware and BIOS levels for the components match the versions on each node.
• Create and configure the Cluster Service Account through AD
• Add Cluster Service Account to Each Node and provide local administrative rights by adding them to local administrator group.
• Provide user right assignment through local security policies to Cluster Service Account on each node (gpedit)
• Configure Public Network.
• Configure Private Cluster Network by Disabling NetBIOS Over TCP/IP.
• Set Network Priorities (Publicàprivate…) on each node.
• Configure Shared Disks by sector aligning using diskpart.( create partition primary align=o)
• Format the Disks with NTFS file system
• Verify Disk Configuration
• Running the Microsoft Cluster Configuration Validation Wizard (optional)
• Log off all other nodes except the first node.
• Add First Node to a New Server Cluster by using cluster administrator and by entering cluster IP address cluster name and cluster service account and by selecting quorum disk.
• Power up a node to be joined to the cluster. Start Cluster Administrator, which is located under Administrative Tools. You do not need to run Cluster Administrator from the node that you will be joining. It can be run on the first node
• From cluster administrator, right click cluster name and select properties and set the network priorities (private à public).
• Set the public network to all communication (private and public) and private network to Internal Cluster Communications only.
• Resize the Quorum Log from cluster properties. Default is 4 MB.
• Test the environment by changing the owner of groups , resources

Creating a Clustered Microsoft Distributed Transaction Coordinator

• Start Cluster Administrator.
• In the left pane, expand Groups. Right-click the group that contains the dedicated disk for use with MS DTC, and Rename, it to somewhat more appropriate
• Right-click the newly renamed resource group, select New, and then select Resource, enter an appropriate name for the IP resource that will be associated with MS DTC such as MS DTC IP Address. In the Resource Type drop-down list, select IP Address. In the Group drop-down list, make sure the right resource group is selected.
• In the Possible Owners dialog box, verify that all nodes of the server cluster are listed as possible owners of this resource. If a node is not listed on the right side, select it from the Available Nodes list, and click Add. Click next in the Dependencies dialog box, select the drive from Available Resources, and click Add to add it to the Resource Dependencies list. Click Next.
• In the TCP/IP Address Parameters dialog box, enter a static IP in the Address input box, and select the proper public network to use. Click Finish. Click OK. And the resource now appears in the resource group and has a state of Offline.
• Right-click MS DTC’s resource group, selects New, and then selects Resource. In the New Resource dialog box, enter an appropriate name for the network name resource that will be associated with MS DTC such as MS DTC Network Name. In the Resource Type drop-down list, select Network Name. In the Group drop-down list, make sure the right resource group is selected.
• In the Possible Owners dialog box, verify that all nodes of the server cluster are listed as possible Owners of this resource. If a node is not listed on the right side, select it from the Available Nodes list, and click Add. Click Next.
• In the Dependencies dialog box, select the MS DTC IP address from Available Resources, and click Add to add it to the Resource Dependencies list.
• In the Network Name Parameters dialog box, enter a name for MS DTC that will be used by other applications. Click OK. The resource now appears in the resource group and has a state of Offline.
• Right-click MS DTC’s resource group, selects New, and then selects Resource. In the New Resource dialog box, enter an appropriate name for the MS DTC resource itself such as MS DTC. In the Resource Type drop-down list, select Distributed Transaction Coordinator. In the Group drop-down list, make sure the right resource group is selected.
• In the Possible Owners dialog box, verify that all nodes of the server cluster are listed as possible owners of this resource. If a node is not listed on the right side, select it from the available Nodes list, and click Add. Click Next.
• In the Dependencies dialog box, select the physical disk and the MS DTC network name from Available Resources, and click Add to add them to the Resource Dependencies list. Click Finish. And Click OK. The resource now appears in the resource group and has a state of Offline.
• Start the newly created resources by right-clicking the MS DTC group and selecting Bring Online, All resources should now have a status of Online

Enabling Network MS DTC Access

• To ensure that external processes can utilize the clustered MS DTC, you must enable network MS DTC access. Follow these steps:
• Log on to the node that currently owns MS DTC.
• Open Add or Remove Programs in Control Panel.
• Click Add/Remove Windows Components.
• Select Application Server, as shown in Figure 5-120. Click Details.
• In the Application Server dialog box, make sure that the Enable Network DTC access option is checked. Click OK. Click Next. You might be prompted for your Windows installation media, so you should have it nearby or on an accessible network share.
• Click Finish on the Completing the Windows Components Wizard dialog box, Stop MS DTC by right-clicking the cluster group and selecting the option Bring Offline.
• Restart MS DTC by right-clicking the cluster group and selecting the option Bring Online.

Testing the Server Cluster

• Review the Event Log
• Verifying Network Connectivity and Cluster Name Resolution using ping utility
• Validates Resource Failover
• Check Disk Semantics

Cluster validation wizard

The Cluster validation wizard checks following thing to make sure windows server cluster works properly.

• At least two separate network cards exist per server.
• Each network card has a unique IP address on a separate subnet.
• Each server can communicate with all other intended nodes of the server cluster.
• The shared disks that will be used for your cluster are accessible from all machines, are visibl only once, and are uniquely identifiable.
• The network and disk I/O latencies are within spec.
• The disk bus reset or LUN reset.
• SCSI reserve/release, reservation, breaking, and reservation defense mechanisms.
• Online/offline to simulate failover.

Changing the Cluster Service Account Password

Changing the password is a completely online operation using the command-line CLUSTER.EXE with the /changepassword option

cluster /cluster:clustername /changepassword:newpassword,currentpassword /test /verbose

This tests that the password can be changed successfully.

You can use five options when changing the password and you can combine them:

• /skipdc: If you have already changed the password at the domain level and just need to change it on the cluster nodes so that the cluster service works properly, use this option. If you do not use this option, the password will also be updated at the domain.
• /force: This switch will force CLUSTER.EXE to proceed with the password change even if nodes of the cluster are unavailable.
• /test: Before doing the actual password change, this switch will check to see whether the password is able to be changed on the nodes as well as at the domain (if you are not using/skipdc).
• /quiet: This switch will suppress all output except errors.
• /verbose: This switch will show all output. I recommend using /verbose because it will give you the most information.

Adding a New Disk to the Cluster

• Create the disk space on your shared disk array with the appropriate zoning and masking. Most will support creating the LUN while servers are up and connected. Please check with your array’s vendor to ensure that you can do this because you do not want to potentially ruin your disk configuration. If your array does not support live LUN creation, you will have to shut down the servers connected to it.
• Power down all the nodes except one in your cluster.
• Sector align the disk using DISKPART
• Format the disks using NTFS file System.
• Open Cluster Administrator, right-click the resource group where you want to place the new disk resource, select New, and then select Resource.
• In the New Resource dialog box, enter an appropriate name for the mount point. In the Resource Type drop-down list, select Physical Disk. In the Group drop-down list, make sure the right resource group is selected. Click Next.
• On the Possible Owners dialog box, verify that all nodes of the server cluster are listed as possible owners of this resource. If a node is not listed on the right side, select it from the Available Nodes list, and click Add. Click Next.
• In the Dependencies dialog box, do not make this resource dependent on anything. Click Next.
• In the Disk Parameters dialog box, verify the proper disk is selected. Click Finish. Click OK. The Resource now appears in the resource group and has a state of Offline.
• Start the newly created resource by right-clicking the group and selecting Bring Online. The disk will now have a state of Online.
• If this drive is added after SQL Server is in production, add the additional disk as dependencies to SQL Server or Analysis Services resource.
• In Windows Explorer, check that the new disk is ready for use. Test that you can write to and read from the new drive by creating a text file and opening it.
• Power up one of the other nodes, failover the resource group with the new mount point to the node you just turned on and verify that, you can read and write files from it.Repeat step 14 for all nodes in the server cluster.

Changing the Quorum Disk

To use Cluster Administrator to change which disk the quorum uses, follow these steps:

• Open Cluster Administrator. Select the cluster group that contains the disk that will become the new quorum. In the right pane, right-click the disk resource, select Change Group, and then select Cluster Group. At the confirmation dialog box, click yes.
• In the Move Resources dialog box, click yes.  Verify that the disk is now in the Cluster Group, Select the cluster name in the left pane, right-click the name of the server cluster, and select Properties.
• Select the Quorum tab. In the Quorum Resource drop-down list, select the drive you moved to the Cluster Group, and resize the quorum log to an appropriate size. Click Apply, and then click OK.
• If the disk has no issues, you can move it to another resource group to repurpose it. If the Disk is no longer needed or damaged, delete the resource from the cluster. To delete it, right click the disk resource, and select Delete.

Installing SQL server cluster on windows clustering

• Ensure the Windows Failover Cluster Is Configured Properly
• Create the SQL Server 2005 Failover Clustering Service Accounts and Groups
• Add the newly created Cluster Groups to Each Node in local administrator group.
• Rename the Cluster Resource Group
• Install .NET Framework 2.0
• Install the .NET Framework 2.0 on each node of your cluster,
• Log on to one of the nodes of the cluster as the cluster administrator(log off all other nodes) account and Start Cluster Administrator, and check to see whether the group you renamed earlier is owned by the node you are logged on to. If it is not, move the group to the node.
• Start setup.exe.
• Setup will now do a more exhaustive check of the node that started the installation process,. Any warnings or errors will be detected, and you can see them by clicking Messages next to the appropriate item or by viewing a report via the Report dropdown list. Click Next.
• On the Components to install page, select the options you want to install as part of your new SQL Server 2005 failover clustering instance. Only SQL server database service and analysis service is cluster able. Click on Create SQL server failover cluster below SQL server database services.
• Select default instance or named instance for SQL server database service. If you are installing a named instance, select Named Instance, and enter the second part of the name (that is, if your named instance will be SRV\SQL1, enter SQL1 here)
• On the Virtual Server Name page, enter the name of the SQL Server that will be registered in DNS. This will be the name used to access SQL Server if it is a default instance; it will be the first part before the backward slash (so enter SRV in SRV\SQL1) if it is a named instance.
• On the Virtual Server Configuration page, select the network in the Network to Use list that will be used by SQL Server to allow connectivity from users and applications. This should be an externally (public) facing network in the Windows server cluster. Enter the static IP address that will be used by SQL Server.
• On the Cluster Group Selection page, select the cluster group from the Available Cluster Groups list, which contains the disk that this clustered SQL Server installation, will use for its system databases. Never select the default cluster group, which contains the quorum disk, or the group that contains the Microsoft Distributed Transaction Coordinator.
• On the Cluster Node Configuration page, all available nodes by default will be added to the definition of the failover clustering instance. If nodes are unavailable for some reason, there will be an appropriate entry in the Unavailable nodes section, If you are using the Standard Edition installer on a cluster that has more than two nodes, you can allow only two nodes in total to be part of the definition of the failover clustering instance, click Next.
• On the Remote Account Information page, enter the password of the cluster administrator whom you are logged in as. If the user you are currently logged in as is not an administrator of the cluster nodes, cancel Setup, log off, and log on as the proper user. Click Next.
• The Service Account page is where you enter the service accounts and passwords for the various services depending on the configuration you selected, you should use different accounts for each service or use same account for all services. Click Next to continue.
• On the Domain Groups for Clustered Services page, select the domain groups that will be used for the failover clustering instance. If you know the names of the groups, enter them in the appropriate text box in the format DomainName\GroupName. Click OK when finished.
• On the Authentication Mode page, click either Windows Authentication Mode, or Mixed Mode,. Most installations will use Mixed Mode. If you select Mixed Mode, you will be prompted to enter a password for the sa user. You cannot use a blank password for the sa user in SQL Server. Click Next.
• Select the proper collation you need—or leave the defaults—and then click Next.
• Setup will then start to prepare your install, during the installation process, you can see the progress of the install on each node by selecting the node in the drop-down list, This new feature of the installer is a welcome addition because you can now see what is going on during Setup on each individual node and the progress for each node.

Testing the Failover Cluster

• Reviewing the Event Log
• Verifying Network Connectivity and Cluster Name Resolution
• Performing Failover Validation
• Verifying the Disk Configuration using sys.dm_io_cluster_shared_drives or fn_servershareddrives()
• Verifying the Node Configuration by using  sys.dm_os_cluster_nodes or fn_virtualservernodes()

Administrating SQL server Cluster

• To find out which node currently owns the SQL Server resources, issue the query SELECT SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’).
• To find out whether the instance is cluster or not use SELECT SERVERPROPERTY(‘IsClustered’).
• SQL Server Surface Area Configuration is cluster-aware application and before using it with cluster server we need to configure it to communicate with a failover clustering instance. To connect to a clustered instance of SQL Server, click the Change Computer link. Enter the name of the instance that is registered in DNS.
• The SQL service can be controlled from SQL Server Configuration Manager, SQL Server Surface Area Configuration, and Cluster Administrator.
• SQL Server Configuration Manager is the preferred method of stopping and starting all SQL-related services. SQL Server 2005 SQL Server Configuration Manager is fully cluster-aware.