Eric Leong: SQL Server 2008 Failover Clustering on Windows Server 2008 R2

If you are interested to setup SQL Server 2008 Failover Clustering on Windows Server 2008 R2 without an actual SAN storage, then here is an end-to-end guide that will show you just that.

Application Server Role

For all nodes, add the Application Server role.

When prompted to Select Role Services, checked the Incoming Remote Transactions and Outgoing Remote Transactions under Distributed Transactions service:

Failover Clustering Feature

Next, you will add the Failover Clustering feature to all the nodes:

After you have successfully installed the Failover Clustering feature to all nodes, you can open up the Server Manager console (from Node 1), navigate to the Failover Cluster Manager, then Create new cluster. Follow the wizard to create a new cluster with a virtual server name and virtual IP address:

After the cluster has been created, expand the cluster, then click on Add Node to add all the nodes for the cluster:

Once the nodes are added, click on the cluster name to view the summary page. Under the Actions pane, click on More Actions and select Configure Cluster Quorum Settings for the virtual server:

Follow the popup wizard and configure the Quorum settings to "Node and file share majority". Note: You may want to create a file share that can be accessed by all nodes to store the cluster configuration files. In this example, I've configured a file share in my domain controller at file://win3k8r2dc/data.

At this point, you have successfully configured the Failover Cluster and added 2 nodes.

iSCSI Target Storage

Since I don't have an actual SAN storage, I will use StarWind as my iSCSI Target instead. You can download the free edition of StarWind from https://www.starwindsoftware.com/download-free-version.

Install StarWind in your storage server. In this example, I'm using my domain controller, win2k8r2dc, as my storage server.

After StarWind is installed, open up StarWind Management Console and add new Host (e.g. win2k8r2dc).

Connect to the newly added Host with the default credentials - Username: root, Password: starwind.

Add new iSCSI Target, specify the Target Alias that represents the storage disk:

Select Hard Disk as the storage type then click Next:

Select Basic Virtual as the device type then click Next:

Select Image File device then click Next:

Select Create new virtual disk as the device creation method then click Next:

Specify the new virtual disk location and name, the size of the disk in MB (Note: The virtual disk name must have .img extension), then click Next:

Checked the option Allow multiple concurrent iSCSI connection (clustering) and leave the rest as default, then click Next:

Select Normal (no caching) as the Cache mode, then click Next:

Verify the settings, then click Next:

You have successfully created the iSCSI Target device:

You should now have the new iSCSI Target device added to the StarWind Host server:

Repeat the same steps above to create another iSCSI Target device (Note: 1 storage device for Distributed Transaction Service, 1 storage device for SQL Server database):

iSCSI Initiator

For all Nodes, go to Administrative Tools, and open up iSCSI Initiator. When prompted to auto-start the service, click Yes. In the iSCSI Initiator Properties menu, specify the StarWind host name (In this example, the StarWind host name is win2k8r2dc), then click Quick Connect. In the Quick Connect popup menu, Select the available iSCSI Target devices then click on Connect. (Note: You should connect to the 2 iSCSI Target devices created previously):

Once the iSCSI Target devices are connected, open up Disk Management console under Storage from Server Manager. You should see 2 new disks have been added. Right click on the new Disks, then click Online. Again, right click on the new Disks, then click Initialize. Once the disks are initialized, right click on the volume and click Create Simple Volume. Follow the wizard to create the volume and format it to NTFS file system, then assign a drive letter:

Note: You must repeat the steps for iSCSI initiator and Disk Management in all nodes. The same drive letters are assigned to the respective disks to be consistent.

Cluster Storage

Expand the cluster server from the Server Manager, right click on Storage, then click on Add a disk. Select the disk you've just added and click OK:

Once added, right click on the disk, then select Change drive letter:

Repeat the same steps to add all the available disks created previously to the cluster:

Note: Ensure you assign drive letter to every drive in the cluster storage.

Clustering DTC service

Right click on the Services and applications, then click on Configure a Service or Application. Select Distributed Transaction Coordinator (DTC), then click Next:

Specify a virtual Name and virtual IP address for the DTC service, then click Next:

Select a disk (In my example, Disk 1) as the storage for DTC service, then click Next:

Verify the settings are correct, then click Next:

You have successfully created the clustered DTC service, click Finish.

Validate Cluster

At this point, you should validate your cluster by clicking on the Validate This Cluster option then follow the wizard instructions:

Note: It's important to ensure that you pass all the validation checks before proceed to creating a Clustered SQL Server service.

Clustered SQL Server 2008

For Windows Server 2008 R2, You will need to install SQL Server 2008 with slipstream SP1. Otherwise, your installation will fail with "RequireKerberos property error". To create slipstream SP1, please follow the guide at http://blogs.msdn.com/petersad/archive/2009/02/25/sql-server-2008-creating-a-merged-slisptream-drop.aspx.

In your Node 1 server, launch SQL Server 2008 installation center. From the installation menu, click on Install a SQL Server Failover Cluster. Select the necessary features when prompted:

Next, specify the virtual server name as your SQL Server Network Name (In my example, it's WIN2K8R2SQLVS), then click Next:

Verify the disk space is sufficient for the installation, then click Next:

Specify your SQL Server cluster resource group name (in my example, it's SQL Server (MSSQLSERVER), then click Next:

Select a cluster disk for the SQL Server storage, then click Next:

Leave the default settings for Cluster Network Configuration and click Next:

Select Use service SIDs (Recommended) for Cluster Security Policy, then click Next:

Specify the Authentication Mode and the SQL Server Administrators for the Database Engine Configuration, then click Next:

Leave the default values for Error and Usage Reporting, click Next:

Proceed with the installation and you will have SQL Server 2008 failover cluster successfully installed:

Open up your cluster server in the Server Manager, expand the Services and applications menu, you should see SQL Server (MSSQLSERVER) is now Clustered and Online:

In other server Nodes, proceed with SQL Server 2008 with slipstream SP1 installation by running the Add node to a SQL Server failover cluster option. Follow the wizard instructions to install the additional nodes to join the SQL Server cluster: