HowTo: Install a SQL failover cluster (in a virtual lab environment)

I have a couple of times had to set up a SQL Server cluster, both at clients and in my own lab. At clients the underlying Windows cluster setup is often handled by their own infrastructure people, so this is seldom a problem – if we assume they know what they are doing, which I grant, is not always the case. However, as I don’t have an ops person standing around behind glass, to be called upon when I need her – it should of course be a her – I need to do it myself (reminds me of a saying I once heard: it is cheaper to do it yourself, but it is a lot more fun with someone else. Can’t remember what it was about …)

This blog post will describe the different steps required to create a small SQL Cluster with two nodes. The following is assumed:

• You have a domain controller. Mine is running Windows Server 2008, but you can use an earlier version if you wish to have a small footprint.
• You have installed Windows Server 2008 R2 on three servers. Two will be used for the cluster nodes and one as shared storage. All three servers have been joined to the domain and preferable been given a static IP-address.

Until Windows 8 I am running my lab using VirtualBox from Oracle. This allows me to run 64-bit virtual machines on my Windows 7 laptop, without having to boot into WS2008.

In summary we will construct the following:

• Two SQL2008R2 nodes running on WS2008R2
• One WS2008R2 server used as shared storage.

 

First thing to do is to create a Windows cluster. I use iSCSI and install the Microsoft iSCSI Software Target 3.3 on the storage box. There are other options. The important thing is, that you cannot create a SQL cluster, if the nodes are not able to see the same disk.

Run the install to unpack the files. This will open a web-page (index.htm). Select Install.

 

Accept all the default values and the install is completed swiftly.

Start the Microsoft iSCSI Software Target console.

Select the iSCSI Target node, right click and select Create iSCSI Target.

Enter a name for the iSCSI target and alternatively a description. Note, that the name cannot contain spaces.

In the iSCSI Initiators Identifiers dialog, press the Advanced button.

Enter the IP-address of the two nodes on which the SQL cluster will be installed.

Finish the target setup.

Right click the newly created iSCSI Target and select Create Virtual Disk for iSCSI Target.

Set location and size of virtual disk and finish the wizard. Upon completion you should see the newly created vdisk under the iSCSI target.

 

Next step is to connect the just created storage with the two cluster nodes.

On one of the nodes, open the Control Panel and select iSCSI Initiator.

If you see the following dialog

just select Yes.

Enter the IP-address of the Storage box and press Quick Connect.

You should now see the storage server under Discovered targets.

Go to the Volumes and Devices tab a press Auto Configure.

You should now see an entry under Volume List.

Click OK to close the iSCSI configuration.

Go to the second node and repeat the above steps.

Open the Server Manager and go to Storage and Disk Management. You should see the disk here.

Now go back to the first node, set the disk online and initialize it. This step will ensure that the disk is visible in the Cluster Manager when assigning disks.

We now have the foundation to setup a Windows Cluster. Next step is to enable clustering on both nodes. Start the Server Manager on each node, select Features and Add Feature.

Check the Failvoer Clustering click Next and complete the setup.

On one of the nodes go to Start -> All Programs -> Administrative Tools and select Failover Cluster Manager.

Select Create a Cluster in the middle of the page.

Add the two nodes to the list of servers that should be included in the cluster.

In the next step one can run a verification report. It will take some time to complete the report, but I recommend that it is done to avoid any needless frustration later on.

Give the cluster a name and an IP address.

Press Next and finish the setup.

To complete the setup the disk from before needs to be allocated or assigned to the cluster. Select Storage in the tree to the left and click Add a disk to the right.

This should display something similar to the following:

 

Press OK to continue.

So far so good. We now have the foundation on which we can install SQL Server.

SQL Sever 2008 R2 requires the .NET Framework 3.5. If you have not already done so, add it to your servers using the Server Manage and Add Feature.

On the node that “owns” the shared disk, run the installation. I know that it is not really correct to talk about ownership as it is the service that owns the resource and not the node itself, but I think you get the picture.

From the Installation Center, select Installation and then the second option New SQL Server failover cluster installation. The third option will be used to add node(s) to the cluster, once it has been set up.

Continue through the dialogs until the Setup Support files are installed. Once this has completed a “report” is run to identify any issues. There should be no Red Lights, and only Yellow of no importance or  that can be rectified later, e.g. the network binding order.

On the Feature Selection page, select the features that should be installed and press Next.

On the Instanced Configuration page, a SQL Server Network Name must be specified. This is the “virtual” name applications will use to connect to.

Accept the default values for the Cluster Resource Group and the Cluster Disk Selection.

On the Cluster Network Configuration specify an IP-address.

Continue through the setup until the Database Engine Configuration.

Supply the information for the Account Provisioning. On the Data Directories tab it can be seen that the shared or clustered drive has been selected. If you had multiple disks in your cluster and had selected them in the Cluster Disk Selected step, it would here be possible to place the data one one disk and the logs on another. For this test-lab all is installed on the same disk.

Continue with the configuration and end by pressing the Install button.

At this point it would be a good idea to get a cup of coffee as the installation might take some time.

Eventually something like this should be displayed

We have now configured and installed the first node in our failover SQL Cluster and we now just have to add the second node and we are done.

Start the install from the second node, select the Installation menu item, and as previous noted, select the third menu option Add node to a SQL Server failover cluster.

As before the installation process will begin by installing a number of setup files.

On the Cluster Node Configuration screen select the cluster to join.

Continue through the rest of the setup and press Install at the end.

If all goes well, the completion screen will be displayed.

That’s it! We now have a two node SQL failover cluster.