Install SQL Server 2008 R2 Cluster on Windows Server 2008 R2

Setting up and deploying Microsoft SQL Server 2008 R2 on Windows Server 2008 R2 though a simple task but it does require a lot of work and effort in delivering this solution. Great news! Microsoft has released iSCSI Target 3.3 software which now supports Windows Server 2008 R2 clustering. I have chosen to use the iSCSI target to mimic a SAN since its free 🙂 and simple to setup for the purpose of testing SQL clustering. In this first part, I will be setting up the following:

Task1: Install iSCSI Target 3.3
Task2: Create iSCSI Targets for the SQL servers
Task3: Create the virtual disks for the following cluster disks “Quorum, MSDTC, Database, Logs
Task4: Connect SQL servers to the virtual disks using iSCSI Initiator

Requirement:

– Windows Server 2008 R2 SP1
– Microsoft iSCSI Software Target 3.3
– Following firewall port required if SQL and iSCSI Target system are on separate network segment:

[table id=2 /]

– Refer to the Microsoft article for firewall exception if you have Windows Firewall running on the server with iSCSI initiator

Setup Guide:

1. Install iSCSI Target 3.3

– Download the iSCSI Target software from Microsoft

– Double-click the downloaded installer and click Next

– Accept the License Agreement and click Next

– Choose destination for installation and click Next

– Choose if you want to join or not and click Next

– Click Install to begin the installation

– You might see the following pop-up if your Windows firewall is disabled. Ignore the message and click Next

– Click Finish when installation is complete.

2. Create iSCSI Targets

– Click on Start -> Administrative Tools -> Microsoft iSCSI Software Target

– Right-click on “iSCSI Targets” and click “Create iSCSI Target”

– Click Next

– Type in a name for your Target under iSCSI Target Name, a description (Optional) and click Next

– Under IQN Identifier, type in a unique name for your Target and remember the name for later use. Click Next

– After the configuration is completed, click Finish

– Repeat the above steps to create a second Target. Set target name as “sysTarget2” and use existing name “iSQL1” as the IQN identifier or click the browse button and select the name as seen below:

– Click Finish and you should have two targets

3. Create Virtual Disks

– Click on Start -> Administrative Tools -> Microsoft iSCSI Software Target -> Right-click on first Target and click “Create virtual disk for iSCSI Target”

– Click Next

– Type the path and name for the virtual disk file, including the .vhd extension (Quorum.vhd) and click Next

– Type the size of the virtual disk in megabytes (MB), and click Next

– Enter a description (Unique identity) for the disk, and then click Next

– After the virtual disk had been created, click Finish

– Repeat steps 3 to create the rest virtual disks as seen below

– Right-click on the second target and click “Add Existing Virtual Disk to iSCSI Target”

– Select all the virtual disk which we earlier created and click OK

– Click Yes and all the disk should now be visible to both targets.

4. Connect each SQL server to a different target

SQL1: Click on Start -> Administrative Tools -> iSCSI Initiator.

– Click Yes to start the iSCSI service

– Click the Configuration tab and click change

– Under new intiator name, type in the name of the IQN initiator and click OK

– Click on Discovery tab -> Discovery Portal….

– Enter the IP address of the Target server. Ensure the firewall port 3260 had been opened if your Target server is in a separate network segment. Click Ok

– You should see the following at this stage

– Click on the Targets tab, under Discovered Targets the two targets we created should appear. Click refresh if no targets appeared.

– Select the first Target -> Click connect -> Accept the default and click OK

– Connection should now be established as seen below

– Click on Volumes and Devices tab – > Click Auto Configure button. All the volumes (disks) should now be populated in the volume list

– To confirm that the disks are now visible to the server, click Start -> Administrative Tools -> Computer Management

– Expand Storage and click on Disk Management. All the disks should appear on the right-hand pane

SQL2: Repeat steps 4 above and ensure you select the second Target. The second target should be connected as seen below:

In page2 I will continue with the setting up of the environment for SQL installation.

In continuation of setting up the environment for the installation of SQL Server 2008 R2 on Windows Server 2008 R2, I will in page2 focus on the following tasks:

Task1: Add additional Network adapter for Heartbeat connection
Task2: Adjust network settings and binding order
Task3: Format Disks with recommended Disk Allocation Unit Size
Task4: Install Failover Cluster feature
Task5: Validate the Cluster
Task6: Create Base Cluster
Task7: Configure Quorum
Task8: Create MSDTC Application

Setup Guide:

1. Add additional network adapter for Heartbeat connection (Hyper-V)

– Click on Start -> Administrative Tools -> Hyper-V manager

– Expand Hyper-V manager -> Right-click on the Hostname of the server running Hyper-V and click on Virtual Network Manager

– Click New virtual network -> select Private from the list -> click Add, enter a name for the network and click OK

– To assign the Heartbeat network to the SQL servers, right-click on the virtual machine -> click on Settings -> Select Network Adapter -> Click Add

– Select Heartbeat from the drop down and click OK. Repeat same for the second SQL server.

2. Adjust network settings and binding order

– Log onto the first SQL server, click on Start -> Network -> Click Network and Sharing Center

– Click Change adapter settings -> Right-click on the newly added Local Area Connection and click on Properties

– Untick the following:

[table id=4 /]

– Select IPv4 and click on properties

– Select Use the following IP address and assign the IP and Subnet mask for the Heartbeat connection

– Click on Advance -> DNS tab -> Untick Register this connection’s addresses in DNS

– Click on WINS tab -> Select Disable NetBIOS over TCP/IP and click OK. Note: You only disable this on any other NIC except the Public NIC

Note: At this point, a good practice would be to rename each Network adapters according to its use

– Set the Binding order by clicking on Advanced and Advanced settings (Press the Alt key if the menu bar is not showing)

– Move the Public NIC to the top followed by the Heartbeat and click OK

– Repeat the whole steps for the second SQL server

– Final check is to make sure that they both can PING each others Heartbeat IP. If PING fails, this needs to be fixed else the cluster validation would fail.

3. Format Disks with the recommended Disk Allocation Unit Size

The following value is recommended when formatting the partitions to be used by SQL Database and Log files:

– File Allocation Unit Size: 65,536 bytes (64KB)
– Alignment Partition: 1024KB (Default with Windows Server 2008)

Initialize Disks:

– Click Start -> Administrative Tools -> Computer Management

– Expand Storage and click on Disk Management

– Right-click each disks and click Online. Repeat same for the rest disks

– Right-click the first disk and click Initialize

– Ensure all disks are selected, select GPT for partition style. GPT is only required when you have a LUN that is over 2 terabyte. Click OK

Format Disks:

– Right-click each Unallocated disks and click New Simple Volume

– Click Next -> Leave Default volume size and click Next

– Assign drive letter and format each LUNs using the appropriate allocation size unit for each disks as follows:

[table id=5 /]

– Wait for the formatting to complete. Your setup should be as follows:

Verify Partition Alignment and Allocation Unit Size:

– Run the following command to verify the partition alignment:

wmic partition get Blocksize, Startingoffset, Name, Index

– Run the command to get the Allocation unit size:

fsutil fsinfo ntfsinfo : where Drive Letter is the assigned drive letter with a colon in front.

4. Install Failover Cluster feature

– Click on Start -> Administrative Tools -> Server Manager -> Select Features and click on Add Features

– Select Failover Clustering -> Click Next -> Click Install

– Click Close when installation is completed.

– Repeat task for second SQL server

5. Validate the cluster

– Click on Start -> Administrative Tools -> Failover Cluster Manager

– Right-click on Failover Cluster Manager and click Validate a Configuration

– If the page “Before you Begin” appears, click Next

– Enter the NetBIOS names of the first SQL server and click Add, repeat same for second server and click Next

– Accept default test and click Next

– On Confirmation page, click Next

– Wait for validation to complete and ensure that the report indicates success. Resolve any issues reported before configuring the cluster.

– Update the host entry file on both servers as seen below. Note that each NetBIOS names must not be more than 15 xters long. The “host” file is located at c:\windows\system32\drivers\etc

6. Create Base cluster

– Right-click on Failover Cluster Manager and click Create a cluster

– Click Next on “Before you Begin page”

– Enter the NetBIOS name of each server and the system would automatically populate the FQDN of each server. Click Next

– Enter the cluster name of the access point and its IP address. Note this is already included in the Host file.

– Wait for system to validate the settings, then on confirmation page click Next

– The system will begin creating the virtual objects in the domain

– On completion, review the report for any errors and fix. My installation completed but with a warning, reason been that I have another disk which is yet to be formatted but not meant to be part of the cluster resource. So I’ll ignore the warning and click Finish

7. Configure Quorum

Note: This would have been setup automatically during the creation of the NTClus (Base Cluster) but the system may have picked the wrong drive. Its important to verify this and assign the right drive for the resource.

– Open the Failover Cluster Manager, right-click on the NTClus resource -> More actions -> Configure Cluster Quorum Settings

– Click Next

– Select Node and Disk Majority, click Next

– Because we hadn’t renamed each of the cluster disks, click the plus sign (+) to identify which is the Q volume. Select that and click Next

– On confirmation page, click Next

– Click Finish.

Rename the Disks: Right-click on the Quorum resource -> Select properties -> Under Resource name change it from Cluster Disk 1 to Quorum. Click OK

– Repeat same for each cluster disks

8. Create MSDTC Application

– Open the Failover Cluster Manager -> right-click on the cluster name -> Configure a Service or Application

– Click Next

– Select Distributed Transaction Coordinator (DTC), click Next

– Enter the name of MSDTC and its IP address and click Next. Note this was already included in the host file

– Select the disk assigned to the MSDTC and click Next. Since we had renamed the disks, its much easier to pick the disk

– On confirmation page, click Next

– After the application had been configured and confirmed successful, click Finish.

Post Configuration:

1. Test the Base cluster resource

– Open the Failover Cluster Manager -> Click on the Cluster name -> In the middle pane click on the Custer Core Resources bar and you should see the newly created base cluster. Note the resource status should show “Online”

– Right-click on the Cluster name and click Take this resource offline.

– On confirmation action, click Take Name: offline. Wait for the resource to go offline.

– Right-click on the Cluster name and click Bring this resource online. Make sure the resource comes online without any errors.

2. Verify cluster groups and resources status

– Run the following command in command prompt to test the cluster failover:

cluster group – Display status of cluster groups and its current owner. They must all be online

cluster res – Displays status for all cluster resources and current owner of the resources

cluster group “” /move – Move each cluster groups from one node to another to test the failover. If this fails then the whole concept of SQL failover clustering is defeated 🙁

Hope this wasn’t too lengthy and boring :). In page3 I will be dealing with the actual SQL installation. Isn’t this exciting 🙂 …….

In pages 1&2 we did quite an amazing amount of work in getting all the necessary components in place in preparation for the installation of SQL Server 2008 R2. In this page I will guide us through installation of SQL cluster on the first node and add a second SQL node.

Requirements:

– Microsoft .Net Framework 3.5 SP1
– SQL Instance name and assigned IP address (Add this into the host file on both SQL servers)
– SQL group and service account (Service account needs to be a member of the Domain Users SQL service group)
– Set a STRONG password for your SA account like (vP1uJ6g8bNFG) 🙂
– Enable Remote Registry on both servers
– Decide which port your instance will be listening on (Default is 1433)
– For added security, ensure that the following ports are disable on the SQL server network segment: UDP 137, UDP 138, TCP 139 & TCP 445

Setup Guide:

1. Install Microsoft .Net Framework 3.5 SP1

– Click on Start -> Administrative Tools -> Server Manager

– Right-click on features -> Click Add Features

– Select .Net Framework 3.5.1 Features -> Click Add Required Role Services -> Click Next

– On Web Server (IIS) page -> click Next

– On Select Role Services page -> click Next

– On Confirm Installation Selections -> click Next

– Wait for installation to complete -> click Close

2. Install SQL on Node 1

– Insert the SQL installation disk -> Double-click Setup.exe -> Wait for the SQL application to load

– On SQL Server Installation Center page -> click Installation -> click New SQL Server failover cluster installation

– On Setup Support Rules page -> click Show details -> Ensure the status column shows Passed for all rules -> click OK

– On Product Key page -> Enter the product key or select Evaluation -> click Next

– Accept the license terms -> click Next

– On Setup Support Files page -> click Install and wait for the setup rules to identify any problem

Note: You can ignore the Network Binding Order warning but any other warning needs to be resolved before proceeding

– On Feature Selection page -> Select features you want installed -> Accept default install path -> click Next

– On Instance Configuration page -> Either enter a new Named Instance or Default Instance -> Enter location for the SQL binaries -> click Next

– On Disk Space requirements -> Review your configuration -> click Next

– On Cluster Resource group page -> Accept default -> click Next

– On Cluster Disk Selection page -> Select required disks for this instance -> click Next

– On Cluster Network Configuration page -> Deselect DHCP -> Enter IP address assigned for this instance (Note IP address was added to the host file) -> click Next

– On Cluster Security Policy page -> Select Use service SIDs (recommended) or Use domain group -> Enter the SQL group account created if you selected the domain group -> click Next

– On Server Configuration page -> Click Use the same account for all SQL Server services -> Enter the SQL service account created and password -> click OK

– Click Collation tab -> Select your collation depending on your region -> click Next

– On Database Engine Configuration page -> Select either Windows authentication mode or Mixed mode -> Add the relevant accounts

– Select Data Directories Tab -> Enter Data/Log/Temp/Backup location depending on your choice

– Select FILESTREAM Tab if you want to enable it -> click Next

– On Analysis Services Configuration page -> Add required accounts -> click Next

– On Reporting Services Configuration page -> Select Install, but do not configure the report server -> click Next

– On Error Reporting page -> Tick the box if you want to send error reports to Microsoft -> click Next

– On Cluster Installation Rules page -> Ensure that there are no warning or failed rules -> click Next

– On Ready to Install page -> Review your settings -> click Install

At this stage go grab yourself a cup of coffee or glass of wine as the case may be 🙂

– After installation is completed – click Close

3. Add a second SQL node to an existing SQL cluster

Adding a SQL server to an existing SQL cluster is pretty straight forward and not complex. In this post I will focus on adding another SQL server to a SQL cluster and recommend some configuration changes in post configuration.

Note:

[table id=6 /]

Requirements:

– Ensure that you have installed the first SQL cluster node

– Microsoft .Net Framework 3.5 SP1 (See section1 post for installation steps)

Setup Guide:

– Insert the SQL installation disk -> Double-click Setup.exe -> Wait for the SQL application to load

– On SQL Server Installation Center page -> click Installation -> click Add node to a SQL server failover cluster

– On Setup Support Rules page -> click Show details -> Ensure the status column shows Passed for all rules -> click OK

– On Setup Support Files page -> click Install

– On Setup Support Rules page -> click Show details -> Ensure the status column shows Passed for all rules -> click OK

– On Product Key page -> Enter the product key or select Evaluation -> click Next

– Accept the license terms -> click Next

– On Cluster Node Configuration -> Select the SQL instance name to join -> click Next

– On Service Accounts page -> Enter the SQL service password -> click Next

– On Error Reporting page -> Tick the box if you want to send error reports to Microsoft -> click Next

– On Add Node Rules page -> Ensure that there are no warning or failed rules -> click Next

– On Ready to Add Node page -> Review your settings -> click Install

– Wait till the installation is completed

– Click Close after installation is completed

At this stage we have successfully configured and installed a SQL Server 2008 R2 cluster on a Windows Server 2008 R2.

Next Step:

– Install SQL Server 2008 R2 SP1 in a cluster (Click Here)

 
comments powered by Disqus