Hello folks

These three part article series are about MS SQL Server 2008 R2 clustering. First part is about the creation of virtual machines. In second part, I will build the cluster infrastructure and finally in third part I will install MS SQL Server 2008 R2 in the clustered environment. The operating system to use for clustering is Windows Server 2008 R2 Enterprise Edition and it is on a virtualization environment of VMware vSphere 4. Let me first introduce you the components that is going to be used in my clustering scenario. After that,  I can start to explain the procedures to cluster MS SQL product.

The operating system needed for clustering MS SQL has to be Enterprise Edition at least if Windows Server 2008 R2 operating system will be used. It can be Datacenter Edition as well but I prefer Enterprise Edition this time. Why we couldn’t use Standard Edition or Web Edition though? Because, MS SQL clustering is using the core windows failover clustering feature  and this feature is not a part of Standard or Web Editions.

MS SQL Server 2008 R2 product can be Standard or Enterprise Editions. The most significant differentiator for selection between these two editions is the number of cluster nodes. If you plan only two nodes for failover clustering, Standard Edition will be sufficient. But if more than two cluster node is planned (up to 16 nodes), Enterprise Edition MS SQL is a necessity for your environment. I am going to use Enterprise Edition MS SQL Server 2008 R2 in my scenario. At first, I will create a two node cluster but I want to extend it in the future.

In my clustering scenario, I will cluster two virtual machines located on two VMware ESX host machines. Therefore there will be some requirements for this environment. Table 1 and Figure 1 represent the requirements:

Table 1: Clustering requirements for virtual machines on different ESX hosts

Figure 1: Virtual machines with their requirements

By all these information, I can sketch my environment as in Figure 2. The figure shows the network environment and the products that are going to be used.

Figure 2: Network Topology

All right. As I clear out the planned environment, let me talk about the steps that I have to complete throughout the scenario. Here is the list:

1-      Create a virtual machine on one of the ESX hosts

2-      Install Windows OS on virtual machine

3-      Clone the virtual machine to another ESX host

4-      Bind necessary disk volumes of shared storage (SAN) to virtual machines

5-      Enable and configure Failover Clustering on virtual machines

6-      Install MS SQL Server 2008 R2 on the first virtual machine with failover cluster support

7-      Add the second virtual machine to the already created SQL cluster

First is first, lets create the virtual machines. I open the vSphere Client and on the left part of the interface, I right click on one of the ESX host machines of my Vsphere environment and select “New Virtual Machine…” from the menu. The “Create New Virtual Machine” wizard appears and throughout this wizard, I select the values which are represented in Table 2.

Table 2: The values for “Create New Virtual Machine” wizard

When the properties of the new machine is opened, I select New NIC (adding). Under Network Connection, select a label from the Network Label list. This interface can be either for Public or Private network (by the way, these networks has to be configured before this action. Otherwise, I couldn’t add the NIC to virtual machine). I select the related network label for Public network now. Later, after the creation of virtual machine, I will add a second network interface and select the related network label for Private network, but it is not now.

When I click Finish button, the wizard creates the virtual machine. I DO NOT add the shared cluster disks at this time. Because I will clone this machine to another host after a short time and disks that are mounted as Raw Device Mapping (RDM) to virtual machines are converted to virtual disks during a clone operation by ESX server. In the virtual machine properties dialog box, I press Add button. I select Ethernet Adapter from the list and click Next. Now is the time to select Private Network label for this adapter. As you remember, our first interface is for Public Network. By clicking Next and Finish respectively, I complete the creation of virtual machine. After that, I boot the machine and install Windows Server 2008 R2 Enterprise Edition on it.

Now I can clone the newly created Windows Server 2008 R2 machine to another ESX host. I shutdown the virtual machine first and then I right click the virtual machine. I select Clone from the menu. I select/give values according to the actions in Table 3.

Table 3: Actions taken throughout the cloning wizard

And our second virtual machine is ready with its two network adapters and Enterprise Edition operating system. Now, I can add shared disks to both virtual machines. Before that, I configure the Public and Private network IP addresses for both machines and test them whether a connection problem exists or not.

Note: For Private Network interface, no default gateway setting is set (Figure 3) and it has to be ordered after the Public Network interface via Advanced Ethernet Settings (Figure 4).

Figure 3: No default gateway is set for private network interface (heartbeat interface)

Figure 4: Public interface is listed before private interface

After I verify that everything is OK, I start to add the share disks to the first virtual machine. For this purpose, I select Edit Settingsfor one of my newly created virtual machines via the vSphere Client inventory.

On the opening screen, I select Add, Hard Disk and click Next. I select Raw Device Mappings on the next screen and click Next. A list of LUNs appears and I select an unformatted LUNhere (for LUNs to be listed here, all the creation of LUNs has to be finished on SAN storage device beforehand and these LUNs have to be configured as ESX servers can see them. Because these topics are not our concern in this article, I will not talk about them). After clicking Next, I select a datastore which has to be on a SAN. On the next screen, I select Physical as the compatibility mode and click Next.

The next screen is the controller selection screen. I select a new virtual device node (i.e. SCSI 1:0 or SCSI 2:0) and click Next. I have to select a new SCSI controller here. After that when I click Finish button, a new SCSI controller and a new hard disk are created by the wizard. There is one more thing to do about the hard disk that I created and this thing is the type of SCSI controller. In the virtual machine properties dialog box, I select the new SCSI controller and click Change Type. On the opening dialog box, I select the LSI Logis SAS (if it is not) for the controller (this type is for Windows Server 2008 R2. Controller types for previous operating systems vary). After that I set the SCSI Bus Sharing to Physicaland the job is done. Figure 5 shows the newly created virtual machine with two ethernet adapters and FC SAN quorum disk.

Note: I want to add something to the subject here. Be cautious about your first shared storage is the disk that you plan to use as Quorum disk. When you will create your first Windows cluster, the failover cluster wizard will not ask about which disk are you going to use as Quorum disk. It automatically use the first shared disk as the quorum disk (although you can change it via the failover cluster administrative interface).

Figure 5: Newly created virtual machine (first node of the cluster)

With the same procedures, I can add more shared disks to the virtual machine as well. Therefore, I add two additional shared disks to the same machine so in total, I have three shared disks for one virtual machine (I will use these three disks for MS DTC and SQL Server clustering). As I finish the addition of shared disks to my first virtual machine, I have to add them to the second machine now.

The procedures are similar but there are two points to be careful about the addition of disks to the second virtual machine. First one is; I have to select the “Use an existing virtual disk” instead of RDM, in the wizard. Second one is the virtual device node selection. I have to select exactly the samevirtual device node as I did for the first virtual machine. If I chose SCSI 1:0 for the first machine, I have to choose SCSI 1:0 for the second machine also. All of the remaining procedures are the same. When I finish the addition of shared disks to the second machine, all the work is done on the vSphere side. Figure 6 shows the final state of the two newly created virtual machines.

Figure 6: Newly created virtual machines for clustering

This is the end of part one. In this part, I talked about the software and hardware requirements for MS SQL 2008 clustering. I created two virtual machines for this purpose, binded three shared disks to each of these virtual machines. On the next part, I will enable and configure the failover clustering feature of Windows Server 2008 R2. After that I will install SQL Server 2008 R2 on the configured cluster servers. See you next time.

1. Clustering MS SQL Server 2008 R2 on VMware Virtual Windows Server 2008 R2 – 2
2. Clustering MS SQL Server 2008 R2 on VMware Virtual Windows Server 2008 R2 – 3
3. Step by step installation of Sharepoint Server 2007 on Windows Server 2008 R2 and MS SQL 2008 – 4