Setting up a Two-node SQL Server 2008 Cluster from the Command Prompt – Preparation

SQL Server failover clustering provides the best high-availability solution for an entire SQL Server instance. When the active node in a SQL Server cluster goes offline due to hardware problems, OS problems or scheduled reboots, the SQL Server failover instance configured on the cluster automatically fails over to another node and continues running there. With the release of SQL Server 2008 RTM, the installation process of SQL Server cluster has significantly changed. We have two options now. They are Integrated installation and Advanced/Enterprise installation. In the Integrated installation, we first create a single-node SQL Server failover cluster instance. Then we run Setup on each node that we want to add to the cluster, and add the node to the cluster using the Add Node functionality in Setup. In Advanced/Enterprise installation, we prepare each node in the failover cluster to join the cluster using the Prepare Failover Cluster functionality in Setup. After we prepare the nodes, we complete the failover cluster instance on the active node and make it operational using the Complete Failover Cluster functionality in Setup.


In part I of this series, I will show you how to prepare for SQL Server cluster setup.


Before installing a SQL Server cluster, we need to configure a Microsoft Windows Server 2003/2008 cluster. In our example, we configure a Windows Server 2003 cluster with two computers, Node1 and Node2. Each node has two network interface cards (NICs) that connect to two networks individually, public and private. The public network is for communications between domain controller, client machines and the cluster. The private network is for heartbeat messages between the two cluster nodes. The two nodes share a quorum disk, Q:, which stores cluster configuration database checkpoints and log files that help manage the cluster and maintain consistency. It also acts as a tiebreaker if all network communication fails between cluster nodes. The cluster has two more shared disks SQL Data (Physical Disk D:), and SQL Log (Physical Disk L:), which will be used to store SQL Server data. We will use Disk D: to store the data files of the new SQL Server failover instance, and disk L: to store the log files. Moreover, the group contains a network name and an IP address assigned to the Windows server cluster. The cluster configuration is shown in the figure below.



cluster configuration


We also need to cluster the Microsoft Distributed Transaction Coordinator resource (MS DTC). Although MS DTC is not required for Database Engine-only installation, without it being clustered, you would receive a warning message during the setup configuration checks because of the incompliance of the Cluster_IsDTCClustered rule. If you need to install SSIS, Workstation Components or use distributed transactions, then MS DTC must be clustered. In our example, the MS DTC resource is clustered in a group called “MSDTC Group”. This group contains the MS DTC resource, a shared Physical Disk M: to store MS DTC log files, a network name, and an IP Address. Before clustering MS DTC, network MS DTC access must be enabled on both nodes. For more information, please refer to http://support.microsoft.com/kb/817064/. The MSCS Cluster Service account will need to have public rights to SQL Server so that it can run SELECT @@servername for the IsAlive cluster checks.




In addition to the shared disks (D: and L:), there are some other resources that need to be prepared.



  • A service account for the SQL Server failover cluster instance and it needs to be added to the local Administrators group on all the cluster nodes. In our example, we create a domain account PowerDomain\SqlService.
  • A domain group that contains the service account PowerDomain\SqlService. In our example, the group is a global domain group PowerDomain\SQLAdmins. This group will be added to the local Administrators group on both nodes during Setup and will be used to control access to registry keys, files, SQL Server objects, and other cluster resources. If the service account is not a member of this domain group at the time Setup is run, Setup will attempt to add it. However, if the account under which Setup is running cannot add the account to the group due to insufficient privileges, Setup will fail.
  • A network name for the SQL Server cluster group. We will call our cluster SQL2008Cluster.
  • A network IP address. In our 192.168.1.# subnet, we assign the IP address, 192.168.1.12, to the SQL Server cluster.
  • Although SQL Server Setup will install prerequisites, it is recommended to install the prerequisites on all the cluster nodes beforehand to speed up the installation process. Therefore, we install .NET Framework 3.5 SP1 and Windows Installer 4.5 on both Node1 and Node2. Because we configured SQL Server on Windows Server 2003, we also need to install hotfix KB937444 (http://support.microsoft.com/kb/937444). This hotfix is required for FILESTREAM to work on a Windows Server 2003-based cluster.

We also need to make sure the domain network is the first bound network. In our example, the public network connection needs to have a higher network binding order than the private network connection. Otherwise, a warning will be generated during the setup configuration checks. You change the network order by running ncpa.cpl. This command opens a Network Connections window. On the Advanced menu, click Advanced Settings.



On the Advanced menu, click Advanced Settings


On the Adapters and Bindings tab, make sure the public network connection is above the private network connection. Otherwise, use the arrow buttons to move them.



make sure the public network connection is above the private network connection


Conclusion


In this article, we discuss briefly the prerequisites for SQL Server cluster installation. In Part II and III of this series, we will provide step-by-step examples of SQL Server 2008 cluster installation.


» See All Articles by Columnist Yan Pan

Yan Pan
Yan Pan
Yan Pan (MCITP SQL Server 2008, MCITP SQL Server 2005, MCDBA SQL Server 2000, OCA Oracle 10g) is a Senior DBA for a leading global financial services firm, where her daily duties include administering hundreds of SQL Server and Oracle servers of every possible version, working with business units on software development, troubleshooting database issues, and tuning database performance. She has written a Wrox book titled “Microsoft SQL Server 2008 Administration with Windows PowerShell” with MAK who is also a columnist for DatabaseJournal.com. You can check out the book at many book stores, such as Barnes & Noble, Borders. Previously, Yan worked as a SQL Server DBA and a .NET developer at Data Based Ads, Inc., and developed two .NET Web applications. Before that, she worked at AT&T Research Labs. She designed OLAP cubes with SQL Server Analysis Services and developed PivotTable reports for regional managers. She has master’s degrees in Computer Science and Physics.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles