Setup and configure SQL Server 2008 Cluster

2009-03-17T09:20:00Z

This will be a sort of "online-documentation" for my own use so I always can access my documentation of a SQL Server 2008 Cluster setup.
Please use this blog as a guideline and not a strict documentation.

My setup is done on my laptop with VMWare Workstation 6 where I will configure the SQL Cluster on Windows Server 2008.

Here we go.

Create shared disk for cluster

To be able to have disk image i use an application called StarWind where I create disk images on my local laptop which I will access through my virtual servers.

Present the shared disk for the cluster

Go to Administrative Tools and select iSCSI Initiator.

In the iSCSI Initiator Properties page, click on the Discovery tab.
Under the Target Portals section, click on the Add Portal button.
In the Add Target Portal dialog, enter the DNS name or IP address of your iSCSI Target and click OK. If you are hosting the target on another Windows host as an image file, make sure that you have your Windows Firewall configured to enable inbound traffic to port 3260.
In the iSCSI Initiator Properties page, click on the Targets tab. You should see a list of the iSCSI Targets that we have defined earlier.
Select one of the targets and click on the Log on button.
In the Log On to Target dialog, select the Automatically restore this connection when the computer starts checkbox. Click OK.
Once you are done, you should see the status of the target change to Connected. Repeat this process for all the target disks we initially created on both of the servers that will become nodes of your cluster.

Once the targets have been defined using the iSCSI Initiator tool, you can now bring the disks online, initialize them, and create new volumes using the Server Manager console. After the disks have been initialized and volumes created, you can try logging in to the other server and verify that you can see the disks there as well. You can rescan the disks if they haven’t yet appeared.

Adding Windows Server 2008 Application Server Role

Since we will be installing SQL Server 2008 later on, we will have to add the Application Server role on both of the nodes. A server role is a program that allows Windows Server 2008 to perform a specific function for multiple clients within a network. To add the Application Server role,

Open the Server Manager console and select Roles.
Click the Add Roles link. This will run the Add Roles Wizard.
In the Select Server Roles dialog box, select the Application Server checkbox. This will prompt you to add features required for Application Server role. Click Next.
In the Application Server dialog box, click Next.
In the Select Role Services dialog box, select Incoming Remote Transactions and Outgoing Remote Transactions checkboxes. These options will be used by MSDTC. Click Next
In the Confirm Installation Selections dialog box, click Install. This will go thru the process of installing the Application Server role
In the Installation Results dialog box, click Close. This completes the installation of the Application Server role on the first node. You will have to repeat this process for the other server

Adding the Failover Cluster Feature

Windows Server 2008 calls them features which are simply software programs that can support or augment the functionality of server roles. Since we've already installed the Application Server role in our server, let's define a feature from this perspective: failover clustering simply augments the role as an application server by making it highly available. It is disabled by default, unlike in Windows Server 2003 so we need to add it on both of the servers that will form a part of our cluster.

To add the Failover Clustering feature:

Open the Server Manager console and select Features.
Click the Add Features link. This will run the Add Features Wizard
In the Select Features dialog box, select the Failover Clustering checkbox and click Next.
In the Confirm Installation Selections dialog box, click Install to confirm the selection and proceed to do the installation of the Failover Clustering feature.
In the Installation Results dialog box, click Close. This completes the installation of the Failover Clustering feature on the first node.

That's how simple and easy it is to add the Failover Clustering feature in Windows Server 2008. You will have to do this on both nodes to complete the process. Once you have managed to install the Failover Cluster Feature on both nodes, we can proceed to validate our servers if they are ready for clustering.

Running the Windows Server 2008 Validate Cluster Configuration

Unlike in previous versions of Windows where Microsoft had some sort of a hardware compatibility list (HCL) from which we had to find and select components tested to be clustering-supported, this wizard is like the “seal” that tells you whether or not the hardware you are using is supported. In fact, Microsoft has partnered with hardware vendors to create the Failover Cluster Configuration Program to make the acquisition of hardware for Windows Server 2008 Failover Clustering very easy and simple. Basically, your hardware will be supported for clustering if it meets these two requirements: the server has a “Certified for Windows Server 2008” logo and it passes this wizard.

One word of caution: do not skip any error message that this wizard generates in the final report. Doing so would simply mean that your configuration going forward will be unsupported. You only need to run this wizard on either of the nodes.

To run the Validate Cluster Configuration Wizard:

Open the Failover Cluster Management console
Under the Management section, click the Validate a Configuration link. This will run the Validate a Configuration Wizard
In the Select Servers or a Cluster dialog box, enter the hostnames of the nodes that you want to add as members of your cluster and click Next.
In the Testing Options dialog box, click Next to run all the necessary tests to validate whether or not the nodes are OK for clustering. If this is the first time to run the wizard, you must run all the tests for validation. For succeding runs, especially when adding hardware like disk subsystems ot network cards on your cluster nodes, you can selectively choose which tests to run as long as you have initially validated your hardware by running all tests.
In the Confirmation dialog box, click Next. This will run all the necessary validation tests.
In the Summary dialog box, verify that all the report returns successful.

Creating the Windows Server 2008 Cluster

You've finally reached this step in the entire process. This means you are now ready to create your Windows Server 2008 cluster. It's as easy as running the Create Cluster Wizard on either of the nodes. Make sure that you have your virtual hostname and IP address ready before proceeding

To run the Create a Cluster Wizard:

Open the Failover Cluster Management console
Under the Management section, click the Create a Cluster link. This will run the Create Cluster Wizard
In the Select Servers dialog box, enter the hostnames of the nodes that you want to add as members of your cluster and click Next.
In the Access Point for Administering the Cluster dialog box, enter the virtual hostname and IP address that you will use to administer the cluster. Click Next
In the Confirmation dialog box, click Next. This will configure Failover Clustering on both nodes of the cluster, add DNS and Active Directory entries for the cluster hostname.
In the Summary dialog box, verify that all the report returns successful.

Configure of MSDTC for SQL Server 2008 Cluster

With Windows 2008 you now have the option to use multiple DTCs. There is a lot of confusion over whether to do that for SQL Server and what the exact steps are. I would recommend using one DTC per SQL Server instance in your cluster. You must also make sure you map each SQL Server to a specific DTC.

Here are the steps for creating DTC in a Windows 2008 cluster. The ability to use multiple DTCs in new in Windows 2008. The advantages of using one DTC per SQL Server instance in a cluster include:

· If you need to move or restart DTC it only affects one SQL Server. In Windows 2008 you can no longer make DTC dependent on the quorum disk so it either needs its own disk or can share with an application such as SQL Server.

· The performance impact of DTC can be spread over multiple groups.

· It can simplify troubleshooting as your testing can be isolated to one group.

1. Connect

· Start "Failover Cluster Management"

· Connect to your cluster (click on the local cluster on the left or choose "Manage a Cluster" on the right and enter your remote cluster name)

2. If you are using a firewall make sure it has an exception for DTC and/or the DTC IP addresses.

3. Add a new DTC resource using one of the two options below.

Option 2 from the blog mentioned at the end of this thread

· DTC will depend on the SQL Server resource name and IP.

· SQL must be installed first.

· You have no choice on the DTC resource name.

a. Install SQL Server on at least one node.

Add DTC

· Right click on your SQL Server group under "Services and Applications" and choose "Add a resource" then "More resources" then "2 - Add Distributed Transaction Coordinator".

· Right click on the new (offline) DTC resource and choose "Properties". Note that the DTC resource name is MSDTC-SQL Server (YourInstanceName) and it cannot be changed.

· Go to the "Dependencies" tab make DTC dependent on a disk in the SQL Server group. If DTC is not heavily used you can choose a disk already used for SQL Server.

· Go to the "Dependencies" tab make DTC dependent on the SQL Server name.

· Go to the "Policies" tab and uncheck "if restart is unsuccessful, fail over all resources in this service or application" for the DTC resource unless you are sure you want a DTC failure to cause SQL Server to fail over.

· Bring DTC online.

Map SQL Server and this DTC resource. If you skip this step SQL Server will use the default DTC. It will only use the DTC in the SQL Server group if that one is the default or if you do the mapping step.

· Open a dos prompt using "Run as Administrator".

· Modify the below statement with a mapping name (make up a name to id the mapping), service (the SQL Server service resource name), and ClusterResourceName (DTC resource name) then run it

· Example: Msdtc -tmMappingSet -name DTC_SQL01_Mapping -service SQL01 -ClusterResourceName "MSDTC-SQL Server (SQL01)"
Do you want to continue with this operation? [y/n]
y
[enter]

· Run "msdtc -tmmappingview *" to verify the mapping was created successfully. The mapping is also stored in HKEY_LOCAL_MACHINE\Cluster\MSDTC\TMMapping\Service\ as a new key named the same as the mapping parameter used in the msdtc command.

d. Enable network access

1. Run DComCnfg

2. Navigate to Computers.Component Services.My Computer.Distributed Transaction Coordinator.Clustered DTCs

3. Right click on the DTC service for this SQL Server group and select "Properties".

4. Go to the security tab and check "Network DTC Access", "Allow Inbound", and "Allow Outbound".

5. When you hit "Apply" or "OK" it will restart the DTC resource.

Hints:

Repeat the above steps for any additional SQL Server instances you install.
If you use DTC heavily you should consider putting it on its own disk for optimal performance. Otherwise you can associate it with your least used SQL Server disk.
Do NOT make SQL Server dependent on the DTC resource.
Do NOT check "if restart is unsuccessful, fail over all resources in this service or application" for the DTC resource unless you are sure you want a DTC failure to cause SQL Server to fail over.

Installing SQL Server 2008 on a Windows Server 2008 cluster

You've gone this far, don't stop now. Only after we have managed to prepare everything can we proceed to install SQL Server 2008 on this cluster. Since we've already installed .NET Framework 3.5 with Service Pack 1 and Windows Installer 4.5 from Part 1, we no longer have to worry about them as they both are prerequisites whether you are doing a single server or a cluster installation. There are two options to install SQL Server 2008 on a cluster. The first one is by using the Integrated failover cluster install with Add Node option and the second one is the Advanced/Enterprise installation option. The process outlined below will take into account the first option.

To install SQL Server 2008:

Run setup.exe from the installation media to launch SQL Server Installation Center. Click on the Installation link on the left-hand side
Click the New SQL Server failover cluster installation link. This will run the SQL Server 2008 Setup wizard
In the Setup Support Rules dialog box, validate that the checks return successful results and click Next.
In the Product Key dialog box, enter the product key that came with your installation media and click Next.
In the License Terms dialog box, click the I accept the license terms check box and click Next. You probably haven't read one of these, but if you feel inclined go for it.
In the Setup Support Rules dialog box, click Install. Validate that the checks return successful results. If the checks returned a few warnings, make sure you fix them before proceeding with the installation. An example of this is the Network binding order. The public network cards should be first on both nodes. Also, you can disable NETBIOS and DNS registration on the network cards to avoid network overhead. Be sure to check your binding order as well. For more details on the network binding order warning, see Microsoft KB 955963.
For the Windows Firewall, make sure that you open the appropriate port number on which SQL Server will communicate. You can do this after the installation. Alternatively, you can disable Windows Firewall during the installation and enable it later with the proper configuration. Click Next to proceed.
In the Feature Selection dialog box, select only the components that you want installed. For the Shared feature directory, you can keep the default path if you have sufficient disk space on your C:\ drive or anywhere that is a local disk as this will be used by the SQL Server installation process later on. The directory for the clustered database engine will be different. Click Next.
In the Instance Configuration dialog box, enter the SQL Server Network Name. This is the name that will be available on the network for the clients. This will vary depending on your selection of whether it is a default or named instance. In this example, default instance is selected.

A couple of things need highlighting in this section. By default, the instance name is used as the Instance ID. This is used to identify installation directories and registry keys for your instance of SQL Server and is helpful when you want to run multiple instances in a cluster. This is the case for default instances and named instances. For a default instance, the instance name and instance ID would be MSSQLSERVER. To use a non-default instance ID, you should select the Instance ID box and specify a value.

The section on Detected SQL Server instances and features on this computer would make sense if there are other SQL Server instances running on your server.
In the Disk Space Requirements dialog box, check that you have enough space on your local disks to install the SQL Server 2008 binaries and click Next.
In the Cluster Resource Group dialog box, check the resources available on your Windows Server 2008 cluster. This will tell you that a new Resource Group will be created on your cluster for SQL Server. To specify the SQL Server cluster resource group name, you can either use the drop-down box to specify an existing group to use or type the name of a new group to create it. Click Next.
In the Cluster Disk Selection dialog box, select the available disk groups that are on the cluster for SQL Server 2008 to use. In this example, two clustered disk groups – APPS and APPS2 – have been selected to be used by SQL Server 2008. I will be using one disk resource for the system databases while the other one for the user databases. Click Next.
In the Cluster Network Configuration dialog box, enter the IP address and subnet mask that your SQL Server 2008 cluster will use. Deselect the checkbox under the DHCP column as you will be using static IP addresses. If you have not disabled your IPv6 adapters and protocols, it would be better to uncheck the row for IPv6
In the Cluster Security Policy dialog box, accept the default value of Use service SIDs (recommended). In Windows Server 2003, we specify domain groups for all SQL Server services but in Windows Server 2008, this is the recommended option. For more information on using service SIDs for SQL Server 2008, check out this MSDN article
In the Server Configuration dialog box, enter the credentials that you will use for your SQL Server service accounts in the Service Accounts tab. In the Collation tab, select the appropriate collation to be used by SQL Server. Note that the startup type is set to manual for all cluster-aware services and cannot be changed during the installation process. Click Next.
In the Database Engine Configuration dialog box, select the appropriate Authentication Mode. If you want to add the currently logged on user to be a part of the SQL Server administrators group, click the Add Current User button.

On the Data Directories tab, enter the path where your system and user database files will be created. This will default to the first shared disk in the cluster so in case you want to change it to the other shared disks to be used by SQL Server 2008, modify accordingly. If you intend to use the new FILESTREAM feature, click the FILESTREAM tab and set the appropriate configurations. Click Next
In the Error and Usage Reporting dialog box, click Next.
In the Cluster Installation Rules dialog box, verify that all checks are successful and click Next.
In the Ready to Install dialog box, verify that all configurations are correct. Click Next.
In the Complete dialog box, click Close. This concludes the installation of a SQL Server 2008 Failover Cluster

At the completion of a successful installation and configuration of the node, you now have a fully functional failover cluster instance. To validate, open the Failover Cluster Management console, and click on SQL Server (MSSQLSERVER) under Services and Applications. Make sure that all dependencies are online

Although we do have a fully functioning SQL Server 2008 failover cluster, it does not have high-availability at this point in time because there is only one node in the failover cluster. We still have to add the second node to the SQL Server 2008 cluster. In the last part of this series, we will add the second node in the failover cluster and install the latest cumulative update

Daniel Back