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 2^nd 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 2^nd 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 2^nd 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 2^nd 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.