The long and winding road (AKA Installing a Windows 2008 R2 SQL Cluster) - Part 5

Installing the second node in the SQL cluster  

now on to the second node. Mount sql installation disc, run setup and this time choose add node to a sql server failover cluster
the installation process is much simpler, you will have to choose to which cluster add this node, you should be ok with default values here

next, you will need to specify with which users to run sql services, even here you should be ok with default values, you will need just to type the password of the accounts you chose earlier

that's it, after clicking next a couple of times, installation will start

and you are done. Now if you open the Failover Cluster Manager tool and connect to this cluster, you will see your clustered SQL server and you can start installing your db.

The long and winding road (AKA Installing a Windows 2008 R2 SQL Cluster) - Part 4

Installing a one-node SQL cluster

To install SQL, we will need to install it on both nodes of the cluster, but the option to choose when installing, changes slightly.

Check in the failover cluster manager what active node of the cluster, you can do so by looking at the value of the current host server in the main page. Go to that machine, mount the SQL 2008 R2 iso and start installation

on this node, you will need to choose the option new SQL server failover cluster installation. When we are going to install sql to the secondary cluster node, you will choose add node to a sql server failover cluster, very simple.
SQL Setup will run some checks that you should pass, click ok and move on.
You will need to enter a product key, which is already specified in the installation package you downloaded from msdn, no need to hunt for it. You accept license agreement and install setup files, just click ok.

even if you did everything by the book there are still a couple of warnings, nothing to worry about. Go on and click next where you will be asked which features you want install

next you will be asked to specify a network name for the sql server and you can choose if to install the default instance or a named one. The instance root directory needs to remain on C, do not change the location to the shared disks.

after a couple of clicks, you will get to this page where you can choose the sql server cluster resource group. Although as you can see there are a couple of red dots that seem to point to errors, leave the default and click next

you will be asked on which shared disk to install the sql server resource cluster group, you have only one available, so nothing to choose here

at this point you will be asked for an ip to assign to the sql cluster. We chose initially 10.1.2.5

next you will need to choose security policy, leave default and choose simply next.

next, it's time to define with which users should run SQL services. It is recommended to use domain users, and that is where those two users created earlier on the domain controller come handy. You also have to specify the password you chose for them.

next you can choose whether to adopt sql or mixed authentication and define administrators

The next screens are not very interesting you can just choose default settings and click next, until installation ends

congratulations, you installed a one-node sql cluster!!  now onto last part

The long and winding road (AKA Installing a Windows 2008 R2 SQL Cluster) - Part 3

Changing the binding order of NICs on cluster members

At this point you should have a domain controller up and running, the two cluster members still in the workgroup and you should have installed and configured Freenas.

As next step, you can join the cluster members to the domain you just created, I won't explain how to do it, all you need is the domain administrator password you set up previously.

There is one additional step that needs to be done before installing the cluster. Cluster members have two NICs. The public one, meaning the one on Vmnet5 in my case, needs to be the first one in the list of interfaces. To do so:

Click on Start, right-click on Network and select properties. This brings you to the Network and sharing center window

click on Change adapter settings. in this new window press the ALT key, this brings up this drop-down menu. You need to select Advanced/advanced settings

In the window that opens, you can change the order of the interfaces and put the one on Vmnet5 at the top.

NOTE: This needs to be done on both cluster members.

Connecting to the shared drives

On one of the cluster members, click start/administrative tools/iSCSI initiator, you will get this warning

go ahead and start the service, after that you will get to this window

type the ip of freenas as shown in picture and click quick connect

The windows machine connects to the Freenas server via iSCSI and shows you the two available disks.
Now connect to both one by one, the status of them changes from "inactive" to "connected", now you can click done.

Goto the volumes and devices tab and click auto-configure, here is how it will look after you do so

 

Clicking the Auto Configure button will make sure that at reboot the server will reconnect to the two shared drives.
Now we need to format the new two drives and give it a name, so head over to the disk management tool (start/right-click computer/manage/storage/disk management)

the disks are available although offline and not formatted. Right-click on disk1 and select online

right-click again on disk1 and select initialize disk, leave further options as default

now you can format the disk and assign a drive letter to it. I left all options as default. Repeat for disk 2. if you want you can also rename the disks here. Here is how it will look after it is done.

You will need to bring the disks offline now and repeat the steps with Iscsi initiator and disk management tool on the other cluster machine. In the case of the disk management tool, you will just need to bring the disks online and they should get a drive letter automatically and be ready to be used.

Installing and configuring the cluster

At this point your cluster members should be in the domain, share two disks installed on a nas and everything should be ready to install the clustering software (not the sql cluster, we are not there yet).

On both cluster members, click on start/right-click on computer/select manage/select features/add features and select failover clustering

click next and go on with the installation.

You will need to logoff at this point and login again on one of the cluster members because to access the failover cluster manager tool, you need to be logged in with a valid domain account. Administrator will do.

On one of the cluster members, click start/administrative tools/failover cluster manager

first thing we will need to do is validate the configuration, so click on the appropriate link, add cluster members and run all the tests.

NOTE: you might get an error stating that "open remote registry failed" when you add the two nodes. In this case, try to add the nodes one by one. I'm not sure where this error comes from, t happened to me when I tried to add both members at the same time by browsing the AD computer group.
the tests will run for a few minutes, after which if you did everything correct you will get here.

you can view the report if you want. now you can click on create a cluster. you will need to select servers as you did during validation.

you will need to assign a name to the cluster and an ip address for management. Remember that the public network is on vmnet5. After that you will just need to click next a couple of times and wait a couple of minutes.

going forward, you will be asked to click next a couple of times and get to this screen when the cluster is installed

after that, when you open the failover clustering tool and connect to the cluster you created, you will see this

here you can failover the active node (current host server) and check the status of everything. Congratulations! You are now the happy owner of a cluster.

Last steps before installing sql (almost there)

Before installing SQL, there is still two things that need to be done

• On the domain controller, create two regular domain users that will be used to run SQL services, I called mine sqlserver and sqlagent. I won't go into details on this task, should be pretty straight forward using the appropriate administrative tool (hint, it is called active directory users and computers and it is under administrative tools)
• On the two cluster machines, install .NET framework 3.5 SP1.

Fortunately, this last part can be done through the Add Features page, no need to hunt down installation packages.
Click on Start, right-click on Computer and choose manage. Scroll down to the features section and click on Add Features.
You don't need to install the whole package, otherwise you will be asked to install IIS too, just select the same as in the picture.

Click next a couple of times.

there, you are ready to install SQL. On to the next part

The long and winding road (AKA Installing a Windows 2008 R2 SQL Cluster) - Part 2

Let's put aside for a bit our Windows machines and install Freenas. Freenas manages the shared disk that the cluster will use to host SQL's DBs.

You need to create a new virtual machine for it and mount the iso you downloaded earlier

In the next menu, as guest OS choose Other/Freebsd 64-bit

Assign 1 GB of RAM to the machine

Regarding network configuration, we don't really care about it at this point. If you select NAT, the first time the machine boots it will get an ip address through DHCP, otherwise the DHCP requests will time out but you will be able to configure the machine through Vmware's console connection. In any case, we will put it later into the correct virtual network and assign the correct IP address to it. In my case, I set it to use host-only networking

 We will assign a disk size of 2GB. This disk will be used for the Freenas OS, we will need additional disks for storage. They will be added at a later point.

Before finishing the setup process, we will have the chance to change virtual network in which to put the NIC and put it in the cluster internal network.

When you boot the machine and reach this screen don't do anything, it will automatically boot the default option, which is fine

To install Freenas, choose the first option

Right now we have only a disk connected to this machine and we will install Freenas on that one

We will receive a warning that we can safely ignore

When the installation process finishes, you will get this message. I suggest you shutdown and then remove the CDROM. I had all sort of interesting errors when I tried to do it the way that this message seems to suggest or rebooted with the cd still in.

As I was saying earlier, when you reboot, the OS will try to get an ip from DHCP, so it will take a bit before you get to the configuration menu. Here you can set up the network interface, which is the only thing we care at this point.

Here you can see all the options I chose to configure the IP. After that you will be able to reach the LMI through the address http://10.1.1.3 from one of the 2 cluster machines

The welcome from the LMI, by default there is no password authentication enabled.

At this point we will go back the virtual machine settings and add 2 additional disks to it. You don't need to shutdown the machine to do so, just head over to settings and add two SCSI disks, a small 4GB one and a bigger where you will install your DBs. I'll make mine 10GB.

If you go back to the console connection, you will notice that Freenas will automatically detect the change. You can see here my two disks, called da1 (4Gb) and da2 (10gb).

Let's go back to the LMI and click on the Storage button on the ribbon at the top of the LMI. This will open this tab where we will create two volumes, one called quorum and one called storage.

The quorum volume is used by the cluster for internal operations. Details are cloudy and complicated. The only things you need to know is that it cannot be used as shared storage and that it does not need to be very big. Click on the Create Volume button and choose these options for the two disks.

Here is how the storage tab will look after you created the two volumes. You can notice that Freenas reserves 2GB of space on each volume for its management.

Now that we created our the volumes that will be used by the cluster, we need to configure Freenas so that it knows how the cluster members will connect to it. We will use iSCSI for that. Click on the Services button, scroll at the bottom of the list and enable iSCSI. After doing this, click on the little monkey wrench icon close to it to define the connection parameters.

There are several options in the tab, like extents, associated targets and so on. It's a pity that there is no wizard, you will need to configure them one by one.

The first one that needs to be configured is Portals. This will tell Freenas on which IP it should listen for incoming connections. You will need to create a rule like the one in this picture with the IP of the Freenas server and the port on which to listen, which by default is TCP 3260. Click Add Portal to create the rule

Next you will need to define who can connect to the volumes you created earlier. To do so, click on the Authorized Initiator button and create a rule with these parameters

• Initiators: ALL
  
• Authorized Network: 10.1.1.0/24 or whatever your cluster internal network is
  

This will allow access to the NAS from the whole cluster internal network

Now that we defined who can connect, we need to define what they can connect to. Click on the Targets button and create two rules, one for each of the disks. The parameters to use in the first rule will be

• Target Name: quorum
• Target Alias: quorum
• Type: Disk
• Portal Group ID: 1
• Initiator Group ID: 1

The name and alias can be whatever you like. The type is pretty self-explanatory. Portal group and initiator group IDs refer to the two rules we defined earlier. The second target rule for the storage disk will be the same, except for the target and alias names. The rest of the options that you see here can be left as default.

Now that we created targets, we need to map them to some disks. To do so, click on Extents and create two rules:

• Extent Name: extent0
• Path to the extent: /mnt/quorum/quorum
• Extent size: 1GB

• Extent Name: extent1
• Path to the extent: /mnt/storage/storage
• Extent size: 7GB

NOTE: you will need to partially type the path, since the file named quorum in the folder named quorum does not exist and will be created when you create the extent. The path which is required here is to a filename, not a folder. The sizes specified here are slightly less than the space available on the volumes for simplicity, if you want to specify thousands of MB instead of GB, you can do it. Just hover close to the box with the mouse and you will see the available keywords.

Now we need to map Targets and Extents. Click on Associated Targets and create two rules that do exactly this:

That's it, we are done on Freenas. No need to save or whatever. Disks are ready to be accessed remotely from cluster members.

Head for your cluster members and connect via iSCSI to the remote drives. See part 3 on how to do it