Install SQL Server 2008 on a Windows Server 2008 Cluster Part 3

Problem

In a previous tip on SQL Server 2008 Installation Process, we have seen how different SQL Server 2008 installation is from its previous versions. Now, we have another challenge to face: installing SQL Server 2008 on a Windows Server 2008 Cluster. Windows Server 2008 has a lot of differences from its previous versions and one of them is the clustering feature. How do I go about building a clustered SQL Server 2008 running on Windows Server 2008?

Solution

To continue this series on Installing SQL Server 2008 on a Windows Server 2008 Cluster, we will look at installing SQL Server 2008 in a failover cluster. In Part 1, we have completed the installation of the Application Server role in both of the servers that we will be using as part of our cluster. Part 2 walked you through the installation of the Failover Cluster Feature, validating the servers that will be a part of the cluster, and creating the cluster. In this tip, we will proceed to install SQL Server 2008 in a clustered Windows Server 2008 environment.

Installing and Configuring MSDTC

The Microsoft Distributed Transaction Coordinator (MSDTC) is a transaction manager that permits client applications to include several different data sources in one transaction and which then coordinates committing the distributed transaction across all the servers that are enlisted in the transaction. A lot of people ask why we need to install MSDTC prior to installing SQL Server. If you are using distributed transactions or running SQL Server on a cluster, this is definitely a must. SQL Server uses the MSDTC service for distributed queries and two-phase commit transactions, as well as for some replication functionality.

Configuring MS DTC in Windows Server 2003 clusters as defined in this Microsoft KB article is not pretty straight-forward. Windows Server 2008 made it simpler by providing a more straightforward process with fewer steps and less configuration.

To install and configure MSDTC:

Open the Failover Cluster Management console on any of the cluster node.
Under the cluster name, right-click on Server and Applications and select Configure a Service or Application. This will run the High Availability Wizard
In the Service or Application dialog box, select Distributed Transaction Coordinator (DTC) and click Next.
In the Client Access Point dialog box, enter the name and IP address of the clustered MSDTC. This should be a different IP addresses and host name from the one that the Windows Server 2008 cluster is already using. Click Next.
In the Select Storage dialog box, select the disk subsystem that will be used by MSDTC. These disk subsystems have to be defined as available storage in your cluster. In the example below, I have used the disk volume F:\ and left the disk volume E:\ for SQL Server later in the installation process. Click Next
In the Confirmation dialog box, validate the configuration you have selected for MSDTC and click Next
In the Summary dialog box, click Close. This completes the installation of MSDTC on the cluster.

You can validate your installation of MSDTC by expanding the Services and Applications node and check the cluster name of MSDTC. Make sure that all of the dependency resources are online

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

Next Steps

Download and install an Evaluation copy of Windows Server 2008 and SQL Server 2008 for this tip
Read Part 1, Part 2 and Part4 of this series
Start working on building your test environment in preparation for building a SQL Server 2008 cluster on Windows Server 2008

About the author

Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2009-03-17

Comments For This Article

Tuesday, July 17, 2018 - 3:51:21 PM - bass_player

Follow the steps outlined here

Tuesday, July 17, 2018 - 10:32:20 AM - Gregory

Hi I have some issue in 14 number. How to resolve? Winserver2016 SQL SERVER 2017

Do i need domain?

Failover Cluster without Active Directory

Thursday, April 9, 2015 - 6:26:58 AM - Dacia	Back To Top (36874)
You're a very practical website; couldn't make it without ya!

Wednesday, September 24, 2014 - 10:33:51 AM - bass_player	Back To Top (34690)
Unfortunately, not. The clustered MSDTC will have it's own Active Directory vistual computer object with its corresponding DNS entry and, therefore, cannot be used to create a SQL Server failover clustered instance

Tuesday, September 23, 2014 - 9:45:44 PM - SANDEEP

Hi,

We have 2 node cluster setup in our environment by windows admin with msdtc configured using ip address. I as a sql server dba asked my admin to provide one ipaddress for active/passive sql server cluster but he inturn replied and asked me to use the msdtc ip address. Is ir possible to configure active/passive sql server cluster instance with the same ip address of msdtc.?

Thanks,

Sandeep

Sunday, August 17, 2014 - 8:47:02 PM - Aun

we have two node cluster installed in the virtualized environment with RDM mapping in single esxi host, when we are trying to validated the cluster am getting error, its stating “validate scsi -3 persistent reservation failed”

So what we have done is we added the data and log disk in the MSDTC resource, after that validation is success but am getting some of the warnings we skipped and installed the sql cluster.

this the correct way to configure it or it will make any problem moving to prodcution

Configuration details:-

Quorum disk, network ip

MSDTC Disk, Network ip

Data Disk under MSDTC resource groups

Log Disk under MSDTC resource groups

Wednesday, April 3, 2013 - 12:29:52 PM - bass_player

Keneth,

The error message points you to the culprit. Check your SQL Server Agent service account if it works fine. Use the runas command to verify that the credentials do work

Wednesday, April 3, 2013 - 4:17:14 AM - Keneth Langoyan

TITLE: Message Details

------------------------------

The credentials you provided for the SQL Server Agent service are invalid. To continue, provide a valid account and password for the SQL Server Agent service.

Hi,

I really need your help guys. Im trying to install cluster sql 2008. my OS running unser Enterprise 2008. im stuck on Server configuration. Just want to ask if any configuration under registry?

Thank,

Keneth

Tuesday, April 3, 2012 - 9:15:25 AM - Ahmed Sengab

In Installing and Configuring MSDTC at step 5 "Select Storage" :

Can i select the same disk subsystem that will be used by the shared date to be used by MSDTC ?

Thanks

Thursday, November 3, 2011 - 9:55:21 AM - bass_player

None that I know of. Make sure you remove the passive node from the Cluster Group Possible Owners before installing the service pack

Also, run it on a test environment first to be sure as every environment is different.

Wednesday, November 2, 2011 - 4:35:15 PM - Chris

Any gotchas when installing SP3 on a Passive/Active cluster running SQL 2008 (not R2)?

Tuesday, November 1, 2011 - 9:37:53 AM - bass_player

Yes, it is possible to add a second named instance on the failover cluster and bring it active on the first-instance passive node. Just make sure you have enough cluster disk resources for the second instance. Just go thru the process of installing a new failover cluster as outlined in the steps. In the installation wizard, you will now see an existing SQL Server instance as well as the available disk resources

However, your licensing model will definitely change as you now have to license both instances. If you intend to run both instances on just one of the nodes, you only need a license for one box times the number of CPU if you're going for the CPU license. Check with your reseller for more information on this matter

Tuesday, January 4, 2011 - 7:26:41 AM - max

Hallo,

it's possible to add a second (named) instance to the failover cluster and bring it active over the first-instance passive node?

If it's possible, where should i start? add new failover cluster?

thanks

regards

Tuesday, January 12, 2010 - 9:48:44 AM - tsquillario

I'm having the same issue as zeeshankhalid. The install works when I'm using physical 2008 R2 server. I'm trying to setup a testing environment with VMware Workstation 7 & 2008 R2 to simulate what's in production. I'm using the SQL 2008 SP1 slipstreamed install.

Any ideas help on this would be greatly appreciated!

There is also a related post on the MSDN Social site:

http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/d96afca2-9cb7-4a5c-b8a9-9ee2d3fedef1

Sunday, January 3, 2010 - 4:58:54 PM - bass_player	Back To Top (4629)
I'll try to reproduce your issue on a Hyper-V failover cluster and will report back on the updates. I did successfully install SQL Server 2008 Failover Cluster on a Windows Server 2008 R2 and documented the additional steps in this tip.

Sunday, January 3, 2010 - 5:48:06 AM - zeeshankhalid

Has anyone successfully installed a SQL Server 2008 R2 Failover Cluster on Server 2008 R2 ? I'm getting stuck at the Instance Configuration screen, i get an error when trying to detect the SQL Server Network Name, here is the error:

The given network name is unusable because there was a failure trying to determine if the network name is valid for use by the clustered SQL instance due to the following error: 'The network address is invalid.'

This issue is not happening on Win 2008 (Non R2)

Inference

Issue happens ONLY when:

Environment is VMWARE and O/S IS WINDOWS 2008 R2

Issue DOESNT happen when:

Environment is VMWARE and O/S IS WINDOWS 2008 R1
Environment is Physical and O/S IS WINDOWS 2008 R1/R2

Saturday, August 29, 2009 - 10:25:10 PM - bass_player	Back To Top (3978)
This might be caused by a lot of reasons. Did you create your server as a clone or an image? Does the account that you are using have permissions to create AD objects? Have you tried delegating control to the Computers OU in your AD?

Friday, August 28, 2009 - 12:01:53 AM - vengala_vas

Hi,

We are building SQL 2008 Cluster on Win 2008 Cluster. We have configured the MSDTC service as cluster resource as you mentioned in this tip. But the MSDTC resource is not coming up. It is giving the below error.

Cluster network name resource 'xyz' failed to create its associated computer object in domain 'abc.com' for the following reason: Unable to create computer account. The text for the associated error code is: Logon failure: unknown user name or bad password.

Please work with your domain administrator to ensure that: -The cluster identity 'pqr$' can create computer objects. By default all computer objects are created in the 'Computers' container; consult the domain administrator if this location has been changed. - The quota for computer objects has not been reached.- If there is an existing computer object, verify the Cluster Identity 'pqr$' has 'Full Control' permission to that computer object using the Active Directory Users and Computers tool.

The cluster service is running under Local System account (which has been given permission to create objects in AD).

We have tried by pre-creating a computer object with the MSDTC Network Name in disabled state and giving cluster identity full control on it. But, when we configure the MSDTC it is giving an error 'xyz' is already in use in the Active Directory.

Can you please let me know how can we resolve this issue?

Saturday, April 25, 2009 - 8:57:17 PM - bass_player	Back To Top (3249)
With the introduction of geographically dispersed clusters in Windows Server 2008, you can now have IP addresses that reside in different subnets across routed networks, eliminating the need to create a VLAN.

Friday, April 24, 2009 - 10:52:21 AM - nraja	Back To Top (3246)
Hi. What about Cluster (Private / heartbeat and Public / Network)?. Do we need to have same subnet or everywhere the limitation of same subnet and VLAN requirements are removed??.

Thursday, April 23, 2009 - 12:01:09 PM - bass_player

Your SQL Server IP could be in the same subnet as your Windows or on a different subnet as long as they get routed correctly to enable it to communicate with the rest of the network. We have settings where the SQL Server IP is on a diiferent subnet than that of the Windows IP but the IPs are routed to enable them to properly communicate with the infrastructure. You do have to check with your network engineers on how to properly configure the IP/subnet. It's not a recommended best practice but it can be done

Wednesday, April 22, 2009 - 8:52:45 AM - nraja

Hi. Do we need to have the same subnet for the SQL server 2008 to work on Windows Server 2008?. If yes, does this apply for Private and Public networks or only for Private?

Thank You

N.Raja

Wednesday, March 18, 2009 - 9:44:31 AM - bass_player

There are a couple of ways to look at this. The reason why MSDTC is moved into the same resource group as your SQL Server resource group is because of the fact that only your SQL Server will really take advantage of this. It would make sense to have them in the same resource group. This Microsoft TechNet article explains that scenario. A good reason why to put MSDTC in its own resource group is for performance

Wednesday, March 18, 2009 - 9:32:46 AM - bass_player	Back To Top (3035)
Installing SSRS requires a totally different mindset as you are working on a web application on an NLB and not a cluster so its not like having to install it on the cluster nodes separately. I'll work on that article in no time

Wednesday, March 18, 2009 - 4:44:00 AM - MOttaway

Hi,

I'm enjoying reading your article as a few months ago when installing a Windows 2008/SQL 2008 cluster I found very little information. This meant I spent sometime with the trial and error approach, installing and reinstalling. I would like your opinion regarding the DTC being installed as part of the cluster.

I first installed it as you suggest, as the SQL install flagged a warning, giving the DTC it's own IP, Storage, Network name and resource group. An article I found suggested, to to allow the DTC it to failover with the SQL, moving it into the SQL resource group. This however left an empty resouce group in cluster administrator and l felt it was not a good solution.

The installation I settled on was to install the DTC after the SQL cluster. Once the SQL is installed select the SQL Server group, right click, select add a resource the select the DTC. This means the DTC is installed as part the the SQL group and does not require it's own network name or IP address but will fail over with the SQL.

Good or Bad?

Thanks

Mark

Tuesday, March 17, 2009 - 8:38:46 AM - Bitoo	Back To Top (3025)
Ok, that will be great.. But just for information, if we have to install SSRS on this environment then we will have to install an instance of SSRS on each cluster node and configure them separately???

Tuesday, March 17, 2009 - 7:25:41 AM - bass_player	Back To Top (3024)
Thanks for the feedback. Reporting Services, as it runs on IIS, is not supported in a Failover Cluster environment. rather, it should be installed in an NLB environment. I'll complete this series first, then write an article on how you can install Reporting Services in an NLB environment

Tuesday, March 17, 2009 - 7:09:39 AM - Bitoo	Back To Top (3023)
Good article.. In the next part of this series can you include steps for the installation of Reporting Services too in this environment, because I read some where that Reporting Service is not supported in Failover cluster environment.... It would be of great help.. Eagerly waiting for next part... Bitoo