Building a Completely Free Playground for SQL Server – 4 – Creating the Cluster

Run the Cluster Validation Report

At this point, I have to admit that as a part of trying to streamline the configuration of this environment, I made a mistake in the first post when I added the Failover Clustering feature to the template VM that was cloned to create the two cluster node VMs.  To proceed with setting up a supported cluster configuration, the Failover Clustering feature will have to be removed from each of the cluster nodes, the nodes will have to be restarted, and then the feature will have to have the Failover Clustering feature added back to them in order for the Cluster Validation Report to succeed.  If you don’t perform this step, the Network configuration and Active Directory configuration of the servers will fail in the Cluster Validation Report.

UPDATED 09/20/2011 

NOTE FROM EMAILS: You may have to disconnect the NAT adapters, or "unplug the cable from them" in VirtualBox to pass the Cluster Validation Report.  For some reason it seems like some people are hitting duplicate IP Address issues with the DHCP applied IP Address from VirtualBox for the NAT connection.  If you get a failed CVR, disconnect the NAT adapters, run the CVR Tests again and it should pass.

After removing the Failover Clustering feature, rebooting, and then adding the Failover Clustering feature back to both of the cluster nodes, the cluster nodes will be ready to build the failover cluster.  The first step is to run the Cluster Validation Report to ensure that the current configuration is supported for building a Windows Server Failover Cluster.  To do this, open the Failover Cluster Manager from one of the two VMs from Start | Administrative Tools | Failover Cluster Manager. When the Failover Cluster Manager opens click on the Validate a Configuration link in the right hand corner of the Actions pane, or under the Management pane.

image_thumb[58]

When the Validate Configuration Wizard opens, click Next and then add both of the cluster nodes to the wizard on the Select Servers or a Cluster screen.

image_thumb[59]

Click Next to and then leave the option to Run all tests (Recommended) selected and click Next, and Next again to begin running the tests.

image_thumb[60]image_thumb[61]

When the validation tests complete, as long as the Summary screen doesn’t show a Failure, the cluster can be created based on the configuration of the nodes.  The validation report summary should show that a warning icon, and to see the specific tests that resulted in the validation warnings, you can click on the View Report button on the Summary screen.  Otherwise, click on the Finish button to close the Cluster Validation Wizard.

image_thumb[62]

Creating the Cluster

To build the cluster, click on the Create a Cluster link in the Failover Cluster Manager, and then click Next to get to the Select Servers or a Cluster screen, then add both of the cluster nodes to the wizard and click Next.  On the Access Point for Administering the Cluster screen, type SQL2K8R2CLSTR in the Cluster Name box, and then uncheck the iSCSI networks, 192.168.31.0/24 and 192.168.32.0/24, from the Network list, and then add the IP address 192.168.81.5 as the cluster network IP address to the 192.168.81.0/24 network, then click Next.

image_thumb[66]

On the Confirmation screen click Next, and the Windows Server Failover Cluster will be built.

image_thumb[67]image_thumb[68]

On the Summary screen click Finish to close the Create Cluster Wizard.

Validating the Quorum Configuration

When the cluster is formed, the default configuration should be to use Node and Disk Majority based on the number of cluster nodes being used, in this case 2 cluster nodes, and the disk witness configuration should be for the smallest shared disk, in this case Drive Q to be configured as the disk witness.  However, this configuration should be validated before the cluster is used to install SQL Server Failover Clustering in the environment.  To validate the Quorum settings, in Failover Cluster Manager, right-click on the cluster and expand More Actions and click on the Configure Cluster Quorum Settings menu option, or click on More Actions in the Actions pane and select the Configure Cluster Quorum Settings option to setup the Quorum Configuration for the Windows Server Failover Cluster.

image

In the Configure Cluster Quorum Wizard, click Next, and then validate that the option for Node and Disk Majority (recommended for your current number of nodes), and then click Next.

image

On the Configure Storage Witness screen, ensure that the Witness Disk is set to the 1GB shared disk on Drive Q.

image

If this matches the environment, click Cancel to close the dialog.  If it doesn’t change the Witness Disk to the 1GB disk on Drive Q and then click Next, then click Next again, and finally Finish to complete the reconfiguration of the Quorum settings.

Installing SQL Server Failover Clustering

Unfortunately as a part of building this blog series, I have also been using VirtualBox for the first time because it was free, and I wanted to focus this blog series on using things that are free.  However, when I went to build the failover cluster for SQL Server I quickly learned that not everything is equal in VirtualBox that I have always assumed would be based on years of working with SQL Server Failover Cluster configurations in VMware Workstation.  There happens to be a bug associated with the installation of the VirtualBox Guest Tools that prevents you from setting up a failover cluster when the tools are installed. If you attempt to install SQL as a failover cluster a series of errors like the following will occur with the primary failure being the following error:

The given network name is unusable because there was a failure trying to determine if the network name is valid for use by the clustered SQL instance due to the following error: 'The network address is invalid.'

imageimageimage

To work around this, the VirtualBox tools have to be removed from the cluster node VMs, but before we do this, we need to copy the installation media into the VMs using a Shared Folder from the host.  If you recall from the first post in this series, I recommended that you download SQL Server 2008 R2 Evaluation Edition, and once you have downloaded the SQLFULL_x64_ENU.exe file, it will need to be extracted to a folder on the host operating system which we will then enable as a shared folder in the two guests VMs that we are setting up as our cluster nodes.  In this case I chose to create a folder named SQL2K8R2_Setup under my Downloads folder where the file was downloaded.

imageimage

Once the files have been extracted, logon to the SQL2K8R2CLSTR1 VM and then open the VM settings from Machine | Settings menu item. 

image

Click on the Shared Folders item on the left hand side and then click on the Add button on the far right to add a shared folder to the VM.  Then browse to the path of the local folder or type in the path in the Folder Path box and provide a Folder Name, then click the Read-only checkbox and then click OK and OK again to return the VM.

imageimage

Inside the VM click Start | Computer to open the Explorer window for the local computer, then click on the Network icon on the left sidebar and then double-click on the VBOXSVR computer object to access the SQL2K8R2_Setup shared folder.  Now create a new folder name SQL2L8R2_Setup on the desktop of both of the cluster nodes and copy the contents of the shared folder into the Desktop folder.  Repeat these steps for both of the Cluster nodes.  Once this is complete, uninstall the VirtualBox tools from Start | Control Panel | Programs | Uninstall a Program and then reboot both of the nodes to continue.

For the purposes of this blog series, which was originally only planned to be three posts long but has been extended into infinity based on the number of tasks that could be accomplished in the configuration of an environment for SQL Server and learning inside of that environment, we are going to install a non-slipstream RTM install of SQL Server 2008 R2 into the test environment as a failover cluster.  The reason for doing this is that it will allow a future post to talk about rolling updates of the cluster nodes and how to best accomplish patching in a clustered environment with minimal downtime.  Open the SQL2K8R2_Setup folder on the Desktop that we previously created, then double-click on the setup application in the folder to begin SQL Server Setup on the cluster node.

imageimage

When the Open File – Security Warning dialog opens click on Run to begin Setup for SQL Server.

image

When the SQL Server Installation Center opens up, click on the Installation link on the left hand side and then click on the New SQL Server failover cluster installation link on the right hand side to launch the failover cluster setup.  If another Open File – Security Warning dialog opens click Run again.

image

On the Setup Support Rules screen click OK.  Then on the Product Key screen, leave the option set to Specify a free edition, and the dropdown set to Evaluation and then click Next.

imageimage

On the License Terms screen click the checkbox for I accept the license terms. and then click Next.  Then click Install on the Setup Support Files screen.

imageimage

When the Setup Support Rules checks complete, review the list of items checked and as long as there are only Warnings, click Next.  We will handle most of these warnings in later blog posts in this series, for example configuring MSDTC as a cluster resource, and reviewing the Network Binding order on the servers.

image

On the Features Selection screen click the Select All button and then uncheck the checkboxes for Analysis Services and Reporting Services, then click Next.

image

On the Instance Configuration screen type SQL2K8R2FC in the SQL Server Network Name text box, and then click Next.

image

Click Next on the Disk Space Requirements screen, then click Next on the Cluster Resource Group screen.  On the Cluster Disk Selection screen ensure the the checkbox next to Cluster Disk 1 is checked and then click Next.

image

On the Cluster Network Configuration screen, uncheck the DHCP checkbox and then type 192.168.81.6 in the Address box and then click Next.

image

For the Cluster Security Policy screen leave the option to Use service SIDs (recommended) selected and then click Next.

image

Switch to the Domain Controller VM and then logon and open the Active Directory Users and Computers snapin from Start | Administrative Tools | Active Directory Users and Computers.  Expand SQLskills.demos and then right-click on Users and create a new user from New | User.  Type SQL Server Services into the Full name textbox, and then type SQLServiceAcct in the User logon name textbox. Name the user SQLServiceAcct

image

Type pass@word1 in both the Password and Confirm password textboxes on the New Object – User screen, and then uncheck the User must change password at next logon checkbox, and check the User cannot change password and Password never expires checkboxes.  Click Finish on the subsequent screen to complete creating the new user.

image

Switch back to the SQL2K8R2CLSTR1 VM and type the SQLskills\SQLServiceAcct in the Account Name for the SQL Server Agent and SQL Server Database Engine services.  Then type in pass@word1 for the Password for both of the services and click next.

image

On the Database Engine Configuration screen, click the radio button for Mixed Mode (SQL Server authentication and Windows authentication) and then type pass@word1 into the Enter password and Confirm password textboxes.  Then click on the Add Current User button, and click Next.

Check the checkbox on the Error Reporting screen and click Next.  Click Next on the Cluster Installation Rules screen, and then click Install on the Ready to Install screen to begin the failover cluster installation.

Once the failover cluster setup on SQL2K8R2CLSTR1 completes reinstall the VirtualBox tools on the VM and let it reboot.  Then logon to the SQL2K8R2CLSTR2 VM and then open the SQL2K8R2_Setup folder on the Desktop that we previously created, then double-click on the setup application in the folder to begin SQL Server Setup on the cluster node.  When the Open File – Security Warning dialog opens click on Run to begin Setup for SQL Server.

image

When the SQL Server Installation Center opens up, click on the Installation link on the left hand side and then click on the Add node to a SQL Server failover cluster link on the right hand side to launch the failover cluster setup. Complete the initial setup steps by selecting Evaluation edition, accepting the License Agreement and installing the SQL Support Files.  Click Next on the Setup Support Rules screen and then on the Cluster Node Configuration screen ensure the the MSSQLSERVER instance is selected in the SQL Server instance name dropdown box, then click Next.

image

Type the password for the Service Accounts in the Password box of the Service Accounts screen and then click Next.

image

Check the checkbox on the Error Reporting screen and click Next.  Click Next on the Cluster Installation Rules screen, and then click Install on the Ready to Install screen to begin the failover cluster installation.

When the installation completes, you will have a fully functional SQL Server 2008 R2 failover cluster setup in your free test environment.

11 thoughts on “Building a Completely Free Playground for SQL Server – 4 – Creating the Cluster

  1. Thank you for taking the time to write these tutorials. Your effort has helped me to learn a great deal about setting up a cluster…

  2. Hi,

    Nice writeup, however, I cant remember seeing anywhere that had the 192.168.32.0 network used. So why was it created?

    1. Hey Michael,

      Looking back at post #3 it should have been used to configure multiple paths for the iSCSI Initiator so that there is redundancy and multi-path performance for the targets, but I see that wasn’t in the post. I’ll go back and update it at some point in the near future to include this information as a part of the configuration.

  3. Hi Jonathan,

    Very good lab post. I removed and added back the Failover cluster feature but still with the CVR i get a warning message: To manage a failover cluster you must be logged in with a Domain user account… and i am not able to view the “Validate a Configuration” link just wondering what i might be missing in the configuration ? thanks

    1. Make sure that you logged into the server with a domain account and not the local administrator account. If you just log in as Administrator, and don’t specify Domain\Administrator, it will use the local account and not the domain account.

  4. Please if you can share a structural diagram of complete configuration? The laptops and their dependencies?

    Thank You!

  5. THANK YOU for being one of the few to post about the removing vbox tools to rid the SQL network name invalid issue. I struggled with this multiple times over days and finally got it build today after finding your uninstall step.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.