Cluster, Resource, and Resource Group

Affect the Group setting of a resource

As shown, the Restart is selected. If the resource fails, the Cluster service attempts to restart it and all its dependent resources. If the resouce fails again, the Cluster service attempts to restart it again. Because the Affect the Group is selected, which has the Threshold:3 and period:900 seconds, if within 900 seconds the resource fails 3 times, the resouce is brought offline. It causes the Resouce Group failure. The resource group will be moved to the other node, which has the ownership of the resource group. The Cluster Service on that node will bring all the resource online.

Again, if a resource in that group fails 3 times within 900 seconds, it causes the whole resource group to be moved to the other node. This back and forth cannot be forever. The screenshot below shows the settings.

The Default Failover setting of a Resouce Group has Threshold:10 and period:6 hours. Within 6 hours, if a resouce group fails over 10 times back and forth, the whole resouce group is brought offline--failed state.

Create SQL Server 2005 failover cluster
You must read the blog of "Cluster and Shared SCSI Bus" first.


Install SQL Server 2005


Components to Install
SQL Server Database Services
Create a SQL Server failover cluster (selected)
Workstation components, books online and development tools
Default Instance
Virtual Server Name:MYSQL
Virtual Server Configuration:
Network to use: Public
IP address: 24.80.99.181 255.255.252.0
Cluster Group Selection—Select the cluster group where the virtual server resources will be installed.
Cluster Group
QuorumGroup

Data file will be created in Y:\Microsoft SQL Server\

Cluster Node Configuration
--Selected nodes:Node2
--Required Node:Node1

Service Account for SQL server, SQL Agent, browser: VIP\SQLserver

VIP\SQLserverGroup, VIP\SQLagentGroup, and VIP\SQLfulltextGroup are used.

When you do SQL Server 2005 standalone installation, the local group SQLServer2005MSSQLUser$$, etc., is created and granted necessary permissions.
Within a cluster, SQL Server instance can run on any physical machine. Local Windows groups do not have a valid SID across machine. When you install a SQL server failover, the installation does not assume that you have the authority to create groups in the domain. So, you must create the three domain Windows groups before you install failover SQL server. During the installation, when you specify the three groups, the necessary permissions are granted to the three groups and the service account will be assigned as a member of the corresponding group.

I found that SQL Server was installed on both nodes. That is different from installation of exchange server 2003, which needs to be installed twice.

Problem:
Drive Y is not accessible from SQL Server Management Studio. Why? From Disk Management Console, I notice that there was no drive letter assigned to the drive. After I assign the letter Y, the drive Y can fail over and fail back. However, SQL Server Management Studio still can not access it. I uninstall SQL Server and reinstall it. Drive Y is available during the installation procedure.

Install a named instance in the cluster

Turn off both Node1 and Node2;

Create a fixed virtual Hard Disk, named.vhd (800MB);

Create a virtual SCSI adapter and connect it to named.vhd on node1 computer;

create a virtual SCSI adapter and connect it to named.vhd on node2 computer;

Turn on Node1 and open Disk Management console to initialize the newly created disk (don't convert it to dynamic); Format it as NTFS and assign Drive letter W;

Turn off Node1 and turn on Node2; Open DISK MANAGEMENT console to check the newly installed disk; nothing else to be done;

Turn on Node1 (both nodes are on)

At Node1, Cluster Administrator console

create a resource group: AcctGroup

Create a resource: AcctDisk (Physical Disk type) with the association to the drive W;

Install SQL Server 2005 as a named instance--ACCT

Virtual Server Name: YourSQL

Public IP: 24.80.99.182

Pay attention to the Cluster Group Selection, ACCTGROUP and QUORUMgroup are listed in available cluster groups; whereas the unavailable cluster group includes the CLUSTER GROUP--because the CLUSTER GROUP already has a virtual SQL server;

Select ACCTGROUP, the data file has the path of W:\Microsoft SQL Server\

YourSQL\ACCT named instance is installed.

Test the connection to two instances

Inside the cluster (node1 hosts both instances),

SQLCMD -S mySQL --Success

SQLCMD -S YourSQL\Acct --Success

From Host Computer (with Workstation Components installed),

Node1 hosts the two instances

• SQLCMD -S MySQL --Success


• SQLCMD -S YourSQL\Acct --Success

Create an alias for connection to YourSQL\ACCT:SQLCMD -S xxx ---OK

Summary:

• If resource A depends on resource B, both A and B must be in same resource group.
• Resource is a logical representation of components, software and hardware, in a system. A virtual server builds on those resources.
• In a computer, many things can go wrong. For example, If antivirus software can prevent a system from starting quickly, another node in the cluster will take over.

• We talk about RAID 1, 10, 01, and 5 a lot. They are hardware fault tolerance strategies. You can implement them for quorum (RAID 1) and Data (RAID 5 or RAID 10). The Cluster implementation is a software fault tolerance strategy. If a disk (attached on Shared SCSI BUS and which the virtual server relies on) goes wrong, no node can run the virtual server.
• Cluster uses share-nothing strategy.
  
  

Cluster and Shared SCSI BUS

Cluster and Shared SCSI BUS

Host computer and three virtual machines are with the following configuration;

Host Machine:

NIC (Internet)--24.80.99.180 255.255.252.0 (gateway) 24.80.96.1

Memory: 2GB

HD:40GB

Complete the creation and installation of domain VIP.COM

VMachine3:

Public NIC--24.80.99.180 255.255.252.0 preferred DNS: 127.0.0.1

You must make sure that NIC is attached to the Public Network.

Virtual IDE HD:6GB

Memory: 256MB

Install Windows Server 2003 and Active Directory(VIP.COM domain)

Computer Name:DC

Create a user: ClusterAdministrator

Leave the DC on all the time

---

For two-node cluster creation, you must follow the Turn On/Off sequence. If both nodes run at same time before the cluster is created, the shared disks will be corrupted.

---

complete the creation and installation of Node1

VMachine1:

Memory: 512MB

Hard Disks: 4 virtual hard disks installed

Virtual Hard Disk1: primary channel (0):vdisk1.vhd:6GB;

Virtual hard disk2:SCSI 0 ID 0:quorum.vhd:1GB;

Virtual hard disk3:SCSI 0 ID 1:data.vhd:1GB;

Virtual hard disk4:SCSI 0 ID 2:msdtc.vhd:200MB;

SCSI Adapters

Virtual SCSI Adapter1, virtual SCSI adapter2 and virtual SCSI adapter3 are attached a shared SCSI Bus;

Network Adapters

Virtual Network Adapter 1 is attached to External Network (can access physical adapter);

Virtual Network Adapter 2 is attached to Internal Network

Install Windows Server 2003 Enterprise Edition(Local IDE drive)

Computer Name:Node1

rename Virtual NIC 1 to public:24.80.99.175 255.255.252 Preferred DNS:24.80.99.180

rename Virtual NIC 2 to Private:10.237.0.19 255.255.0.0

Private NIC properties:

uncheck "client for Microsoft Network"

uncheck "File & Printer Sharing For Microsoft Network"

uncheck "Register this connection address in DNS"

uncheck "Enable LMHOSTS lookup"

Join it to VIP.COM domain;
Open Disk Management Console, follow the wizard to initialize the shared SCSI drives (3), don't convert them to dynamic (Cluster supports only basic disks), create one primary partition for each disk with the whole disk space, and format them as NTFS. I use the drive letters: X, Y, Z (Microsoft recommends the higher drive letters should be used.).

Users and Computers console and add the VIP\ClusterAdministrator to Local Administrators group;

Backup SystemState; When you configure and test clustering, it is possible that you cannot open the existing cluster and cannot create a new cluster.

Turn off the vMachine1 machine;

Creating vMachine2 and Installing Windows Server 2003 Enterprise

Hardware configuration is same as vMachine1.

Memory: 512MB
Hard Disks: 4 virtual hard disks installed
Virtual Hard Disk1: primary channel (0):vdisk2.vhd:6GB;
Virtual hard disk2:SCSI 0 ID 0:quorum.vhd:1GB;
Virtual hard disk3:SCSI 0 ID 1:data.vhd:1GB;
Virtual hard disk4:SCSI 0 ID 2:msdtc.vhd:200MB;
SCSI Adapters
Virtual SCSI Adapter1, virtual SCSI adapter2 and virtual SCSI adapter3 are attached a shared SCSI Bus;
Network Adapters
Virtual Network Adapter 1 is attached to External Network (can access physical adapter);
Virtual Network Adapter 2 is attached to Internal NetworkInstall Windows Server 2003 Enterprise Edition(Local IDE drive)

Computer Name:Node2
rename Virtual NIC 1 to public:24.80.99.176 255.255.252 Preferred DNS:24.80.99.180
rename Virtual NIC 2 to Private:10.237.0.20 255.255.0.0
Private NIC properties:
uncheck "client for Microsoft Network"
uncheck "File & Printer Sharing For Microsoft Network"
uncheck "Register this connection address in DNS"
uncheck "Enable LMHOSTS lookup"

Join it to VIP.COM domain;

Add VIP\ClusterAdministrator to Local Administrators group;

Open Disk Management console to initialize the SCSI drives; this time, you don't need to create partitions and format them. Even though there are no drive letters for the drives, they are in SCSI drives.

turn off Node2;

Turn on Node1;

CLuster Administrator to create a new cluster:MyCluster

Cluster IP:24.80.99.200

Cluster Service Account: VIP\ClusterAdministrator

Quorum:drive X

Start Node2 (node1 is running);

Cluster Administrator--Add a new node (Node2) to MyCluster;

The warning message does not matter. This is because all the shared disks are currently owned by Node1. Microsoft Cluster uses the share-nothing architecture. Node2 does not have access to the shared storage at the moment.

Set the preferred Owner for both Cluster Group, Group 0 and group 1.

Backup system states of both Node1 and Node2.

Test the clustering

Stop the Cluster Service at Node1; you will find both Cluster Group and group o and group 1 are moved to Node2.

Group 0:Disk Y failed to move to the other node.

I delete the group 0 and create a new physical disk resource under Cluster Group for Disk Y. The failover to the other node works.

--------------

Microsoft Distributed Transaction Coordinator (MS DTC)

MS DTC must be added to a Cluster. Microsoft recommends that MS DTC use a disk different from the quorum disk or any disk used by SQL server or other applications.