Creating an SQL Cluster on VMWare

I’ve been working on a SQL cluster for a few weeks now and thought I’d share the build process for anyone that wants to do the same.

 

Configuration Differs depending on Single or Multi-host

 

Single Host – Multinode

Creating the First Cluster node virtual machine.

  1. Create the virtual machine with the required specs.
  2. Install Windows Data Centre
  3. Add 2nd Network Card (used for Cluster-to-Cluster communications)
  4. Set IP address for
    1. Production Network
    2. Cluster Heartbeat Network
  5. Join server to the domain
  6. Move to Correct AD OU
  7. Install Windows Updates
  8. Enable Remote Desktop
  9. Activate Windows License
  10. Shutdown Server
  11. Add additional drive for SQL Installs/Files etc
  12. Add the shared disks (DB, Quorum* etc)
    1. Edit the Virtual Machine settings.
    2. Add a “Hard Disk” – Select “Create a new virtual disk”
    3. Set the required size.
    4. Select Thick Provisions Eager Zeroed
    5. Leave location option on “Store with the virtual machine”
    6. Change Virtual Device Mode, from the drop down list select any address starting with (1:x)
    7. Add this disk will automatically create a new SCSI Controller card – finish the process
    8. Now select the newly added SCSI Card and check the following
      1. SCSI Controller Type = LSI Logic SAS
      2. Set SCSI Bust Sharing to “Virtual

 

* Quorum disk should be set to 1GB

 

Creating the Second cluster node virtual machine

The settings that allow the sharing of the VMDK files form the shared storage are all done via the second VM

  1. Create the virtual machine with the required specs.
  2. Install Windows Data Centre 2012
  3. Add 2nd Network Card (used for Cluster-to-Cluster communications)
  4. Set IP address for
    1. Production Network
    2. Cluster Heartbeat Network
  5. Join Server to Domain
  6. Move to Correct AD OU
  7. Install Windows Updates
  8. Enable Remote Desktop
  9. Activate Windows License
  10. Shutdown Server
  11. Add additional drive for SQL Installs/Files etc (non-shared cluster resources)
  12. Add the shared disks (DB, Quorum etc)
    1. Edit the Virtual Machine settings.
    2. Add a “Hard Disk” – Select “Use an existing disk”
    3. Browse to the Datastore and Folder of the first cluster node and select the shared disks (Quorum, DB etc)
    4. Change Virtual Device Mode, from the drop down list select any address starting with (1:x)
    5. Add this disk will automatically create a new SCSI Controller card – finish the process
    6. Now select the newly added SCSI Card and check the following
      1. SCSI Controller Type = LSI Logic SAS
      2. Set SCSI Bust Sharing to “Virtual”
    7. Repeat step 12 for all shared disks

 

Network Card Binding.

As the servers have two network cards it’s important adjust the binding order so the secondary Cluster node communication isn’t used for any other communications.

  1. Click Start, click Network, click Network and Sharing Centre, and then click Change Adapter Settings.
  2. Press the ALT key, click Advanced, and then click Advanced Settings. If you are prompted for an administrator password or confirmation, type the password or provide confirmation.
  3. Click the Adapters and Bindings tab, and then, under Connections, click the connection you want to modify.
  4. Move the Production Adapter to the top of the list.

 

Initialise Shared Disks

Now the shared disks must be brought online.

  1. Open Disk Management on Cluster Node 1
  2. Select the Quorum Disk
  3. Bring it online
  4. Initialise the Disk (MBR)
  5. Create New Simple Volume
    1. Set Drive Letter to Q:\
    2. Change Volume Label to Quorum.
  6. Repeat these steps for all the shared disks

AD Accounts and Permissions

Before installing the Cluster services, you will need to create AD accounts as noted below

Once created add the Cluster admin AD account to the local administrator group on both cluster nodes.

 

Component Permissions Required
Cluster Admin AD User Domain User Local Admin on Cluster Nodes

Create Computer Objects – OU Container

Read All – OU Container

SQL Accounts

Component Windows Server 2008 R2 or higher
Database Engine Provide a domain user account.
SQL Server Agent Provide a domain user account.
SSAS Provide a domain user account.
SSIS Virtual Account
SSRS Virtual Account
FD Launcher (Full-text Search) Virtual Account
SQL Server Browser LOCAL SERVICE
SQL Server VSS Writer LOCAL SYSTEM

 

Install Cluster Services – Node 1

On the first cluster node run the “Add Roles and Features Wizard”

  1. Login with the Cluster admin user AD account.
  2. Select “Role-based or features-based installation”
  3. Select the First cluster node server
  4. Skip the “Server Roles” and proceed to Features
  5. In the Features menu select “Failover Clustering” and all required sub-features.
  6. Select “Install”
  7. Complete a restart when required
  8. Once to Restarted select “Tools” from the server manager and select “Failover Cluster Manager”
  9. Validate Cluster – Select Validate Cluster from the Actions menu
  10. Enter the server name
  11. Select All tests
  12. Wait for tests to finish
  13. Resolve any errors that are displayed.

Begin cluster Install

Select “Create Cluster” from Actions menu

  1. Enter Cluster name
  2. Enter Cluster IP Address – This is the cluster management IP address.
  3. Select Next and follow the process through

You will receive the following error (we’ll address this in the next step.

Warnings

An appropriate disk was not found for configuring disk witness. The cluster is not configured with a quorum witness. As a best practice, configure a quorum witness to help achieve the highest availability of the cluster.

This will be corrected later on in the build process.

 

Now Set Permission for Computer Object

Now set the OU permissions to allow the Computer Object permission to create computer objects.

Cluster Computer Account Create Computer objects – OU Container

Read All Properties – OU Container

Full Control – Cluster Admin Account

 

Quorum Disk

To set the quorum disk you must bring the disk online and then add the storage

First make sure the Quorum disk is still online, if not bring it online.

  1. Expand the cluster from the “Failover Cluster Manager” and open Storage, then Disks
  2. Select Add Disk from the “Actions” menu
  3. This will launch the “Add Disks to a Cluster” wizard
  4. Select the Disk you’ve previously configured for the Quorum
  5. This disk will now be added and come online.
  6. Right Click the newly formed cluster and select “More Actions -> Configure Cluster Quorum Settings”
  7. Select “Advanced quorum configuration”
  8. Select “All Nodes”
  9. Select “Configure a disk witness”
  10. Select the Disk you brought online in 4
  11. Finish the setup.

 

Install Cluster Services – Node 2

On the first cluster node run the “Add Roles and Features Wizard”

  1. Login with the Cluster admin user AD account.
  2. Select “Role-based or features-based installation”
  3. Select the First cluster node server
  4. Skip the “Server Roles” and proceed to Features
  5. In the Features menu select “Failover Clustering” and all required sub-features.
  6. Select “Install”
  7. Complete a restart when required
  8. Once to Restarted select “Tools” from the server manager and select “Failover Cluster Manager”
  9. Validate Cluster – Select Validate Cluster from the Actions menu
  10. Enter the server name
  11. Select All tests
  12. Wait for tests to finish
  13. Resolve any errors that are displayed.

Joining the nodes together.

 

  1. Connect to Node 1
  2. Open Failover Cluster manager
  3. Select Nodes
  4. Select Add Node from Actions window
  5. Add the name of the Node 2
  6. Select “Yes..” on the Validation warning
  7. Select to include Quorum Disk
  8. Correct any warnings and then complete the install process.

 

Test Failover

The nodes should now be showing lone with the Quorum disk connect to one of the nodes.  You should now test if the Quorum moves and fails correctly.

  1. Open Failover Cluster manager
  2. Open nodes
  3. Right click Node 1 and select “Pause -> Drain Roles”
  4. This should now move the Quorum disk to the second node.
  5. Click Node and check the “Disks” tab in the bottom pane. It should now show the Quorum drive.
  6. Right click Node 1 and select “Resume -> Do not failback roles back”
  7. Now repeat the same steps with Node 2

You should see the disk fails between the two nodes.

Now repeat this process for all other shared drives.

 

SQL Server Install Node 1

 

  1. Login with admin account and add the Clusteradmin AD account to the Local Admin group then logout
  2. Select new SQL Server failover cluster
  3. Enter Product key
  4. Accept the license.
  5. Select the “Use Microsoft updates to check for updates”
  6. Select the product updates it there are any.
  7. Resolve any errors that occur (Windows Firewall and Microsoft Cluster Service (MSCS) can be ignored)
  8. Select the required Instance features.
  9. Set the Instance Root Directory, Share feature directory and Shared feature directory (x86) to be the Program Files drive you setup previously.
  10. Set the SQL Server Network Name
  11. Set an Instance name if required other
  12. Ignore the Cluster Resource Group Name errors is any show.
  13. Next Select the Shared cluster disk – This should be the drive you’d planned to contain the SQL Database
  14. Next configure the SQL Server IP Address
  15. Enter the usernames and password for the configured accounts.
  16. Select your method out Authentication and the SQL Server Administrators
  17. Check the Data directories are configured to use the shared drive
  18. Add any other settings you required for TempDB and Filestream
  19. Finish the install

 

SQL Server Install Node 2

 

  • Login with admin account and add the Clusteradmin AD account to the Local Admin group then logout
  • Select Add Node to a SQL server failover cluster
  • Enter Product key
  • Accept the license.
  • Select the “User Microsoft updates to check for updates
  • Select the product updates it there are any.
  • Resolve any errors that occur (Windows Firewall and Microsoft Cluster Service (MSCS) can be ignored)
  • Most of the settings now will be automatically detected from the first node.
  • Wait for the installer to finish.

 

Testing Failover

Once the install has finished try failing the cluster nodes between the two hosts.

  • Open Failover Cluster Manager
  • Expand Roles.
  • Right Click on the SQL Server cluster role
  • Select Move -> Select Node
  • Select the other cluster node and wait for role to move.
  • Now complete the action in reverse.

 

 

Multi-Host – Multinode

 

Creating the First Cluster node virtual machine.

  1. Create the virtual machine with the required specs.
  2. Install Windows Data Centre
  3. Add 2nd Network Card (used for Cluster-to-Cluster communications)
    1. Set IP address for Production Network
    2. Set IP address Cluster Heartbeat Network
  4. Join server to the domain
  5. Move to Correct AD OU
  6. Install Windows Updates
  7. Enable Remote Desktop
  8. Activate Windows License
  9. Shutdown Server
  10. Add additional drive for SQL Install/Files etc (non-shared cluster resources)
  11. Add the shared disks (DB, Quorum* etc)
    1. Edit the Virtual Machine settings.
    2. Add a “Hard Disk” – Select “Create a new virtual disk”
    3. Set the required size.
    4. Select Thick Provisions Eager Zeroed
    5. Leave location option on “Store with the virtual machine”
    6. Change Virtual Device Mode, from the drop down list select any address starting with (1:x)
    7. Add this disk will automatically create a new SCSI Controller card – finish the process
    8. Now select the newly added SCSI Card and check the following
      1. SCSI Controller Type = LSI Logic SAS
      2. Set SCSI Bust Sharing to “Physical”

 

* Quorum disk should be set to 1GB

 

Creating the Second cluster node virtual machine

The settings that allow the sharing of the VMDK files form the shared storage are all done via the second VM

  1. Create the virtual machine with the required specs.
  2. Install Windows Data Centre 2012
  3. Add 2nd Network Card (used for Cluster-to-Cluster communications)
    1. Set IP address for Production Network
    2. Set IP address Cluster Heartbeat Network
  4. Join Server to Domain
  5. Move to Correct AD OU
  6. Install Windows Updates
  7. Enable Remote Desktop
  8. Activate Windows License
  9. Shutdown Server
  10. Add additional drive for SQL Installs/Files etc (non-shared cluster resources)
  11. Add the shared disks (DB, Quorum etc)
    1. Edit the Virtual Machine settings.
    2. Add a “Hard Disk” – Select “Use an existing disk”
    3. Browse to the Datastore and Folder of the first cluster node and select the shared disks (Quorum, DB etc)
    4. Change Virtual Device Mode, from the drop down list select any address starting with (1:x)
    5. Add this disk will automatically create a new SCSI Controller card – finish the process
    6. Now select the newly added SCSI Card and check the following
      1. SCSI Controller Type = LSI Logic SAS
      2. Set SCSI Bust Sharing to “Virtual”
    7. Repeat step 11 for all shared disks

Network Card Binding.

As the servers have two network cards it’s important adjust the binding order so the secondary Cluster node communication isn’t used for any other communications.

  1. Click Start, click Network, click Network and Sharing Centre, and then click Change Adapter Settings.
  2. Press the ALT key, click Advanced, and then click Advanced Settings. If you are prompted for an administrator password or confirmation, type the password or provide confirmation.
  3. Click the Adapters and Bindings tab, and then, under Connections, click the connection you want to modify.
  4. Move the Production Adapter to the top of the list.

 

Initialise Shared Disks

Now the shared disks must be brought online.

  1. Open Disk Management on Cluster Node 1
  2. Select the Quorum Disk
  3. Bring it online
  4. Initialise the Disk (MBR)
  5. Create New Simple Volume
    1. Set Drive Letter to Q:\
    2. Change Volume Label to Quorum.
  6. Repeat these steps for all the shared disks

AD Accounts and Permissions

Before installing the Cluster services, you will need to create AD accounts as noted below

Once created add the Cluster admin AD account to the local administrator group on both cluster nodes.

 

Component Permissions Required
Cluster Admin AD User Domain User Local Admin on Cluster Nodes

Create Computer Objects – OU Container

Read All – OU Container

SQL Accounts

Component Windows Server 2008 R2 or higher
Database Engine Provide a domain user account.
SQL Server Agent Provide a domain user account.
SSAS Provide a domain user account.
SSIS Virtual Account
SSRS Virtual Account
FD Launcher (Full-text Search) Virtual Account
SQL Server Browser LOCAL SERVICE
SQL Server VSS Writer LOCAL SYSTEM

 

Install Cluster Services – Node 1

On the first cluster node run the “Add Roles and Features Wizard”

  1. Login with admin account and add the Clusteradmin AD account to the Local Admin group then logout
  2. Login with the Cluster admin user AD account.
  3. Select “Role-based or features-based installation”
  4. Select the First cluster node server
  5. Skip the “Server Roles” and proceed to Features
  6. In the Features menu select “Failover Clustering” and all required sub-features.
  7. Select “Install”
  8. Complete a restart when required
  9. Once to Restarted select “Tools” from the server manager and select “Failover Cluster Manager”
  10. Validate Cluster – Select Validate Cluster from the Actions menu
  11. Enter the server name
  12. Select All tests
  13. Wait for tests to finish
  14. Resolve any errors that are displayed.

Validate Cluster

Now validate the cluster and correct any errors.

  1. Open Failover Cluster Manager from the actions Menu
  2. Enter the Server name
  3. Select “Run all tests”

 

Begin cluster Install

Select “Create Cluster” from Actions menu

  1. Enter Cluster name
  2. Enter Cluster IP Address – This is the cluster management IP address.
  3. Select Next and follow the process through
  4. You may get the following error

Warnings

An appropriate disk was not found for configuring disk witness. The cluster is not configured with a quorum witness. As a best practice, configure a quorum witness to help achieve the highest availability of the cluster.

This will be corrected later on in the build process.

 

 

Now Set Permission for Computer Object

Now set the OU permissions to allow the Computer Object permission to create computer objects.

Cluster Computer Account Create Computer objects – OU Container

Read All Properties – OU Container

Full Control – Cluster Admin Account

 

 

Quorum Disk

To set the quorum disk you must bring the disk online and then add the storage

First make sure the Quorum disk is still online, if not bring it online.

  1. Expand the cluster from the “Failover Cluster Manager” and open Storage, then Disks
  2. Select Add Disk from the “Actions” menu
  3. This will launch the “Add Disks to a Cluster” wizard
  4. Select the Disk you’ve previously configured for the Quorum
  5. This disk will now be added and come online.
  6. Right Click the newly formed cluster and select “More Actions -> Configure Cluster Quorum Settings”
  7. Select “Advanced quorum configuration”
  8. Select “All Nodes”
  9. Select “Configure a disk witness”
  10. Select the Disk you brought online in 4
  11. Finish the setup.

 

Install Cluster Services – Node 2

On the first cluster node run the “Add Roles and Features Wizard”

  1. Login with admin account and add the Clusteradmin AD account to the Local Admin group
  2. Login with the Cluster admin user AD account.
  3. Select “Role-based or features-based installation”
  4. Select the First cluster node server
  5. Skip the “Server Roles” and proceed to Features
  6. In the Features menu select “Failover Clustering” and all required sub-features.
  7. Select “Install”
  8. Complete a restart when required
  9. Once to Restarted select “Tools” from the server manager and select “Failover Cluster Manager”
  10. Validate Cluster – Select Validate Cluster from the Actions menu
  11. Enter the server name
  12. Select All tests
  13. Wait for tests to finish
  14. Resolve any errors that are displayed.

Joining the nodes together.

 

  1. Connect to Node 1
  2. Open Failover Cluster manager
  3. Select Nodes
  4. Select Add Node from Actions window
  5. Add the name of the Node 2
  6. Select “Yes..” on the Validation warning
  7. Select to include Quorum Disk
  8. Correct any warnings and then complete the install process.

 

Test Failover

The nodes should now be showing lone with the Quorum disk connect to one of the nodes.  You should now test if the Quorum moves and fails correctly.

  1. Open Failover Cluster manager
  2. Open nodes
  3. Right click Node 1 and select “Pause -> Drain Roles”
  4. This should now move the Quorum disk to the second node.
  5. Click Node and check the “Disks” tab in the bottom pane. It should now show the Quorum drive.
  6. Right click Node 1 and select “Resume -> Do not failback roles back”
  7. Now repeat the same steps with Node 2

You should see the disk fails between the two nodes.

Now repeat this process for all other shared drives.

 

SQL Server Install Node 1

Login to the first cluster node with the cluster admin and run the SQL Setup.exe

  1. Select new SQL Server failover cluster
  2. Enter Product key
  3. Accept the license.
  4. Select the “Use Microsoft updates to check for updates”
  5. Select the product updates it there are any.
  6. Resolve any errors that occur (Windows Firewall and Microsoft Cluster Service (MSCS) can be ignored)
  7. Select the required Instance features.
  8. Set the Instance Root Directory, Share feature directory and Shared feature directory (x86) to be the Program Files drive you setup previously.
  9. Set the SQL Server Network Name
  10. Set an Instance name if required other
  11. Ignore the Cluster Resource Group Name errors is any show.
  12. Next Select the Shared cluster disk – This should be the drive you’d planned to contain the SQL Database
  13. Next configure the SQL Server IP Address
  14. Enter the usernames and password for the configured accounts.
  15. Select your method out Authentication and the SQL Server Administrators
  16. Check the Data directories are configured to use the shared drive
  17. Add any other settings you required for TempDB and Filestream
  18. Finish the install

 

 

SQL Server Install Node 2

Login to the second cluster node with the cluster admin and run the SQL Setup.exe

  1. Select Add Node to a SQL server failover cluster
  2. Enter Product key
  3. Accept the license.
  4. Select the “User Microsoft updates to check for updates
  5. Select the product updates it there are any.
  6. Resolve any errors that occur (Windows Firewall and Microsoft Cluster Service (MSCS) can be ignored)
  7. Most of the settings now will be automatically detected from the first node.
  8. Enter the service account passwords when prompted.
  9. Wait for the installer to finish.

 

Testing Failover

Once the install has finished try failing the cluster nodes between the two hosts.

  1. Open Failover Cluster Manager
  2. Expand Roles.
  3. Right Click on the SQL Server cluster role
  4. Select Move -> Select Node
  5. Select the other cluster node and wait for role to move.
  6. Now complete the action in reverse.

 

Leave a Reply

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