Creating a Windows Server 2008 Microsoft Cluster Service SQL Active/Passive Cluster on a single ESXi 4.0 Server

Written by Sam McGeown
Published on 27/1/2010 - Read in about 6 min (1139 words)

Configuring the Virtual Environment and Virtual Machines

Note – this configuration will work for ESXi 4 upwards due to the server 2008 MSCS requirement for persistent SCSI-3 reservations.

The first step is to create a new vSwitch for the host-only cluster heartbeat network, don’t assign any network adaptors to the switch as it’s going to be local only.

Create a new virtual machine with a single hard disk. For the purposes of this test, I’ve assigned 2 vProcessors, 1GB RAM, 30GB drive for the OS, 1 vNIC in the default vSwitch0.

Add a second vNIC and assign it to the cluster network vSwitch created in step 1.

Install Windows Server 2008 R2 Enterprise and all the Windows Updates, for the example I’ve named it SQLCluster01.

Clone the server and rename the new one to SQLCluster02. In ESXi you can’t clone, so shut down the first server, copy the files to a new folder and right click the VMX file to add it to the inventory. When you boot it the first time VMware will ask if it’s been moved or copied – select copied.

Create a disk for use as the Quorom, this needs to be shared and since I’m using ESXi with local storage only it must be “eagerzeroedthick”. To do this I have to use the unsupported mode in ESXi (Alt+F1, type unsupported and then your root password) and use the vmkfstools command to create it (vmkfstools –c –d eagerzeroedthick –a lsilogic /vmfs/volumes///.vmdk)

Add the new disk to SQLCluster01 using a new SCSI virtual controller (different from the current controller, e.g. my first HD is on SCSI 0:1, the Quorum is on SCSI 1:0)

Check that the new SCSI controller is set to LSILogic (it is for Server 2008 by default) and set the SCSI Bus Sharing to Virtual.

Add the Quorum disk to the second virtual machine, using the same settings.

Edit the .vmx file for both servers, adding in the following lines (edit for your SCSI controller):

_scsi1:0.mode = “independent-persistent”

scsi1:0.shared = “TRUE”_

Create a disk for some shared storage for the cluster too, it will be needed for the DTC application as well as the SQL server – in a production environment you may want to separate logs and data, but for my test, I’m just adding another two 10GB disks. Use the same process as for creating the Quorum disk.

Configuring SQLCluster01 and SQLCluster02 for Microsoft Cluster Services

Once the two VMs are booted, we need to do a number of things:

Activate and format the Quorum disk. In SQLCluster01, open the disk management snap-in and activate the new Quorum disk, create a new partition and format it to NTFS. I mounted it at Q. In SQLCluster02, open the snap-in and activate the Quorum disk – it should pick up the partition and formatting automatically. I re-assigned the disk letter to Q.

Configure the Access Network. I’ve assigned a static IP within the servers range, 192.168.8.0/24 for both servers. They have full network connectivity.

Configure the Heartbeat Network. I’ve assigned the IPs 10.0.0.1 and 10.0.0.2 on a /29 subnet to the Cluster Heartbeat Network Adaptors, and renamed it to Cluster Heartbeat just to keep it tidy.

Join the domain. You can no longer use a workgroup for MSCS, so join the VMs to your domain.

Create DNS records for the Cluster, Virtual-SQL and MSDTC. An A record is required for the cluster itself, the virtual SQL instance and the Distributed Transaction Coordinator. I created vm-cluster, vm-virtualsql and vm-msdtc each with their own IPs.

Install Failover Clustering on both nodes. Launch the “Add Features” wizard and tick to install the Failover Clustering feature for each server (the server may require a restart).

Configure the Cluster. Open the Failover Cluster Management console from Administrative Tools and run the “Validate a Configuration” wizard. Add both servers into the wizard and run all the defaults.

Create the cluster. Run the “Create a Cluster…” wizard, select the servers as you did with the validation wizard, enter the cluster name and IP address that you created earlier (vm-cluster for me). Review the summary and create the cluster. The cluster service should automatically select the best disk for the quorum.

Installing the Distributed Transaction Coordinator on the MSCS

Open the Failover Cluster Management console and right click Services and Applications and select “Configure a service or application”. Select the DTC and click next.

Enter the DNS name you configured earlier for the DTC and the IP address you assigned and click next.

Select the shared storage you’ve assigned to the cluster, and click next.

Complete the wizard and then verify that the DTC is running by selecting it in the Services and Applications window – the Server Name, MSDTC and Drive should all be online.

Installing SQL Server 2008 on a Windows Server 2008 cluster

Assuming you updated the server earlier, you should already have the .Net Framework 3.5 SP1 which is a pre-requisite for SQL Server 2008. I’ll move on to the installation at this point, so open the installed and select a “New SQL Server failover cluster installation”.

Click through the wizard, enter key and license info and install the Setup Support Files. Check and resolve any issues in the setup support rules.

Select the features that you require

Configure the SQL Server Network Name as you created earlier in the DNS records. I’m installing the default instance of SQL.

Check the disk space requirements are OK and click next. Click next to create a new Cluster Resource Group for SQL.

Select the available cluster disk and click next.

Configure the network address you configured earlier for the SQL Cluster

In the Cluster Security Policy dialog box, accept the default value of Use service SIDs (Microsoft’s recommended option). Configure your service accounts and collation, DB Engine Authentication and Data Directories. Again, for production you wouldn’t use the same disk for Logs, Data, TempDB etc, but this is just a test setup.

After that, click through to your installation and complete.

Verify all the resources required for the cluster application are available in the Failover Cluster Management console.

Once that’s all set up, it’s time to install the second cluster node. Run the SQL 2008 installer and select the “Add node to a SQL Server failover cluster” option. As before, go through the setup support rules, check the Cluster Node Configuration, configure the Service Accounts, step through the verifications and install the node.

If it all completed successfully, you now have a working Windows Server 2008 Cluster with an active/passive SQL 2008 database cluster, running on top of a single ESXi 4 Server. I tested connecting with SQL Manager to vm-virtualsql which works as expected. I also tested failing over between hosts with the Cluster console, again it worked as expected.

Thanks for reading and I hope this helps!

Share this post